Saturday, February 25, 2012

Monitor Filegrowth

Hi All
With SQL Server 2005 what is the best way to be alerted when autogrowth on a
file occurs?
ThanksHello use system monitor to do this. See the below URL for more details:-
http://support.microsoft.com/?kbid=299921
Thanks
Hari
"David" <David@.discussions.microsoft.com> wrote in message
news:BDFD49B2-D639-4573-885A-EC64AFE56153@.microsoft.com...
> Hi All
> With SQL Server 2005 what is the best way to be alerted when autogrowth on
> a
> file occurs?
> Thanks|||David
You can run SQL Server Profiler to monitor it
"David" <David@.discussions.microsoft.com> wrote in message
news:BDFD49B2-D639-4573-885A-EC64AFE56153@.microsoft.com...
> Hi All
> With SQL Server 2005 what is the best way to be alerted when autogrowth on
> a
> file occurs?
> Thanks|||I believe the default trace in SQL 2005 already monitors for this event. You
can poll the trace files periodically to see if this event has occurred.
Andrew J. Kelly SQL MVP
"David" <David@.discussions.microsoft.com> wrote in message
news:BDFD49B2-D639-4573-885A-EC64AFE56153@.microsoft.com...
> Hi All
> With SQL Server 2005 what is the best way to be alerted when autogrowth on
> a
> file occurs?
> Thanks|||The default trace does monitor this however it does not alert.
Events in the Default Trace:
EventID(event class), Event Name, Description
18
Audit Server Starts and Stops
Occurs when the SQL Server service state is modified.
20
Audit Login Failed
Indicates that a login attempt to SQL Server from a client failed.
22
ErrorLog
Indicates that error events have been logged in the SQL Server error
log.
46
Object:Created
Indicates that an object has been created, such as for CREATE INDEX,
CREATE TABLE, and CREATE DATABASE statements.
47
Object:Deleted
Indicates that an object has been deleted, such as in DROP INDEX and
DROP TABLE statements.
55
Hash Warning
Indicates that a hashing operation (for example, hash join, hash
aggregate, hash union, and hash distinct) that is not processing on a
buffer partition has reverted to an alternate plan. This can occur
because of recursion depth, data skew, trace flags, or bit counting.
69
Sort Warnings
Indicates sort operations that do not fit into memory. Does not
include sort operations involving the creating of indexes; only sort
operations within a query (such as an ORDER BY clause used in a SELECT
statement).
79
Missing Column Statistics
Column statistics that could have been useful for the optimizer are
not available
80
Missing Join Predicate
Query that has no join predicate is being executed. This could result
in a long-running query.
81
Server Memory Change
SQL Server memory usage has increased or decreased by either 1
megabyte (MB) or 5 percent of the maximum server memory, whichever is
greater.
92
Data File Auto Grow
Indicates that a data file was extended automatically by the server.
93
Log File Auto Grow
Indicates that a data file was extended automatically by the server
94
Data File Auto Shrink
Indicates that a data file was shrunk automatically by the server.
95
Log File Auto Shrink
Indicates that a log file was shrunk automatically by the server.
102
Audit Statement GDR Event
Occurs every time a GRANT, DENY, REVOKE for a statement permission is
issued by any user in SQL Server.
103
Audit Object GDR Event
Occurs every time a GRANT, DENY, REVOKE for an object permission is
issued by any user in SQL Server.
104
Audit AddLogin Event
Occurs when a SQL Server login is added or removed; for sp_addlogin
and sp_droplogin.
105
Audit Login GDR Event
Occurs when a Windows login right is added or removed; for
sp_grantlogin, sp_revokelogin, and sp_denylogin.
106
Audit Login Change Property Event
Occurs when a property of a login, except passwords, is modified; for
sp_defaultdb and sp_defaultlanguage.
108
Audit Add Login to Server Role Event
Occurs when a login is added or removed from a fixed server role; for
sp_addsrvrolemember, and sp_dropsrvrolemember.
109
Audit Add DB User Event
Occurs when a login is added or removed as a database user (Windows
or SQL Server) to a database; for sp_grantdbaccess, sp_revokedbaccess,
sp_adduser, and sp_dropuser.
110
Audit Add Member to DB Role Event
Occurs when a login is added or removed as a database user (fixed or
user-defined) to a database; for sp_addrolemember, sp_droprolemember,
and sp_changegroup.
111
Audit Add Role Event
Occurs when a login is added or removed as a database user to a
database; for sp_addrole and sp_droprole.
115
Audit Backup/Restore Event
Occurs when a BACKUP or RESTORE command is issued.
116
Audit DBCC Event
Occurs when DBCC commands are issued.
152
Audit Change Database Owner
Occurs when ALTER AUTHORIZATION is used to change the owner of a
database and permissions are checked to do that.
153
Audit Schema Object Take Ownership Event
Occurs when ALTER AUTHORIZATION is used to assign an owner to an
object and permissions are checked to do that.
155
FT:Crawl Started
Occurs when a full-text crawl (population) starts. Use to check if a
crawl request is picked up by worker tasks.
156
FT:Crawl Stopped
Occurs when a full-text crawl (population) stops. Stops occur when a
crawl completes successfully or when a fatal error occurs.
157
FT:Crawl Aborted
Occurs when an exception is encountered during a full-text crawl.
Usually causes the full-text crawl to stop.
164
Object:Altered
Occurs when a database object is altered.
167
Database Mirroring State Change
Occurs when the state of a mirrored database changes.

No comments:

Post a Comment