Embedded Files
Applicability:
Applicability:
SQL Server 2000: Tested
SQL Server 2005: Tested
SQL Server 2008: Tested
SQL Server 2008R2: Tested
SQL Server 2012: Tested
SQL Server 2014: Tested
SQL Server 2016: Tested
SQL Server 2017: Not Tested
Credits:
Credits:
Author: ChillyDBA
Date: 7 Jun 2020
Description
Description
This is a more lightweight revision of the code on the page Performance - SQL Server Profiler - Scheduled Server-Side Recording and Analysis.
Over the years, I have found it much easier to run the code manually on each target server/database - usually this is to try and determine whether the DB is still used before wasting resource on upgrade as users are usually pretty unwilling to commit to decommissioning anything without irrefutable evidence.....
Code
Code
/*
SP Created on the followign servers
PROD
XXXXXXX
DEV
SIG
*/
/*
EXEC [dbo].[usp_ProfilerServerSideTrace_Execute]
@Folder = 'D:\DBA\Andy Hughes'
,@Job_Name = 'Jobxxx'
,@Trace_Purpose = 'Trace AAAAA_USER in AAAAA on XXXXXXX'
,@Trace_Duration = 1
,@Trace_MaxFileSize = 20
-- ** NOTE ** -- data types for the filter values must match the data type of the trace column being filtered.
-- These can be looked up in SQL BOL
,@Filter_Text_Value = NULL
,@Filter_CPU_Operator = NULL
,@Filter_CPU_Value = NULL
,@Filter_Reads_Operator = NULL
,@Filter_Reads_Value = NULL
,@Filter_Writes_Operator = NULL
,@Filter_Writes_Value = NULL
,@Filter_Duration_Operator = NULL
,@Filter_Duration_Value = NULL
,@Filter_DB_Value = 'AAAAA'
,@Filter_User_Value = 'AAAAA'
*/
DROP PROCEDURE [dbo].[usp_ProfilerServerSideTrace_Execute]
GO
CREATE PROCEDURE [dbo].[usp_ProfilerServerSideTrace_Execute]
@Folder NVARCHAR(500), -- name of the output folder
@Job_Name VARCHAR(100), -- name of the job that is scheduled
@Trace_Purpose VARCHAR(100), -- just to help us remember why we did it.
--also forms part of the file name (and table name if loaded into DB)
@Trace_Duration BIGINT, --#minutes that the trace will run for
@Trace_MaxFileSize BIGINT, --#MB that the trace file will be allowed to grow to.
--Trace will stiop at this point regadless of the duration setting
-- ** NOTE ** -- data types for the filter values must match the data type of the trace column being filtered.
-- These can be looked up in SQL BOL
@Filter_Text_Value NVARCHAR(100), -- the text to be wildcard matched
@Filter_CPU_Operator VARCHAR(2), -- the comparison operator -- '>', '<', '<>' or '='
@Filter_CPU_Value INT, -- the CPU limit to be applied
@Filter_Reads_Operator VARCHAR(2), -- the comparison operator -- '>', '<', '<>' or '='
@Filter_Reads_Value BIGINT, -- the Reads limit to be applied
@Filter_Writes_Operator VARCHAR(2), -- the comparison operator -- '>', '<', '<>' or '='
@Filter_Writes_Value BIGINT, -- the Writes limit to be applied
@Filter_Duration_Operator VARCHAR(2), -- the comparison operator -- '>', '<', '<>' or '='
@Filter_Duration_Value BIGINT, -- the Duration limit to be applied
@Filter_DB_Value NVARCHAR(100), -- the database to be monitored
@Filter_User_Value NVARCHAR(100) -- the user name to be monitored
AS
/***************************************************************************************
Purpose: Checks constructs server side trace metadata and runs a trace it.
Intended for unattended profiler trace runs.
Lower load than running through GUI
Scope of this SP:
1. Operates at Server level
2. Creates trace instance
3. Adds trace counters (a fixed set)
4. Adds trace filters (determined by parameters)
5. Starts the trace
Note: Trace instance automatically delelted on completion
--select * from msdb.sys.traces
-- in case it is not deleted, use the command below and insert the correct @TraceID from the results of the statmeent above
-- EXEC sp_trace_setstatus @TraceID, 2
Author: Andy Hughes
History: 10 Apr 2018
***************************************************************************************/
SET NOCOUNT ON
-- Declare local variables
DECLARE
@StopTime DATETIME,
@StartDateString VARCHAR(13),
@On BIT,
@ComparisonOperatorID INT,
@Servername SYSNAME,
@TraceID INT,
@TraceFile NVARCHAR(1000),
@TraceName NVARCHAR(1000),
@intfilter INT
--initialise local variables
SELECT
@StopTime = DATEADD(mi,@Trace_Duration,GETDATE()), -- add the trace duration offset to current datetime
@StartDateString = CONVERT(CHAR(8),GETDATE(),112) -- generate a datetime string for use in the file name --['YYYYMMDD_HHMM']
+ '_'
+ CAST(REPLACE(CONVERT(VARCHAR(5),GETDATE(),108),':','') AS CHAR(4)),
@On = 1, -- used to strart the trace
@ServerName = REPLACE(@@SERVERNAME, '\','_') + '_' -- create a server name string for the file name - replace '\' with '_' for instance names
-- add a '\' to the end of the folder string if it doesn't already have one.
IF RIGHT(@Folder,1)<>'\'
SELECT @Folder = @Folder + '\'
-- now create the trace name and trace filename strings from their component parts
SELECT @TraceName = 'Trace_' + @ServerName + @StartDateString + '_' + @Trace_Purpose
SELECT @TraceFile = @Folder + @TraceName
-- create the trace instance and record the ID in @TraceID
EXEC sp_trace_create
@TraceID = @TraceID OUTPUT,
@options = 0,
@tracefile = @TraceFile,
@maxfilesize = @Trace_MaxFileSize,
@stoptime = @StopTime
-- if the trace didn't create correctly, then quit.
IF (ISNULL(@TraceID, 1) < 1) RETURN(-1)
BEGIN
-- Set the trace events and columns
/*
These are a subset of events and columns and are ones that I have found to be the most
commonly used.
Events
10 - RPC:Completed;
12 - SQL:BatchCompleted
20 - Audit Login Event Failed
For a full list, see the comments at the bottom of this SP
Column IDs
1 TextData;
3 DatabaseID
6 NTUserName;
9 ClientProcessID;
10 ApplicationName;
11 LoginName;
12 SPID;
13 Duration;
14 StartTime;
15 EndTime;
16 Reads;
17 Writes;
18 CPU;
21 EventSubClass;
27 EventClass;
35 DatabaseName;
*/
-- add trace columns for RPC: Completed Events
EXEC sp_trace_setevent @TraceID, 10, 1, @On
EXEC sp_trace_setevent @TraceID, 10, 3, @On
EXEC sp_trace_setevent @TraceID, 10, 6, @On
EXEC sp_trace_setevent @TraceID, 10, 9, @On
EXEC sp_trace_setevent @TraceID, 10, 10, @On
EXEC sp_trace_setevent @TraceID, 10, 11, @On
EXEC sp_trace_setevent @TraceID, 10, 12, @On
EXEC sp_trace_setevent @TraceID, 10, 13, @On
EXEC sp_trace_setevent @TraceID, 10, 14, @On
EXEC sp_trace_setevent @TraceID, 10, 15, @On
EXEC sp_trace_setevent @TraceID, 10, 16, @On
EXEC sp_trace_setevent @TraceID, 10, 17, @On
EXEC sp_trace_setevent @TraceID, 10, 18, @On
EXEC sp_trace_setevent @TraceID, 10, 21, @On
EXEC sp_trace_setevent @TraceID, 10, 27, @On
EXEC sp_trace_setevent @TraceID, 10, 35, @On
-- add trace columns for SQL:BatchCompleted Events
EXEC sp_trace_setevent @TraceID, 12, 1, @On
EXEC sp_trace_setevent @TraceID, 12, 3, @On
EXEC sp_trace_setevent @TraceID, 12, 6, @On
EXEC sp_trace_setevent @TraceID, 12, 9, @On
EXEC sp_trace_setevent @TraceID, 12, 10, @On
EXEC sp_trace_setevent @TraceID, 12, 11, @On
EXEC sp_trace_setevent @TraceID, 12, 12, @On
EXEC sp_trace_setevent @TraceID, 12, 13, @On
EXEC sp_trace_setevent @TraceID, 12, 14, @On
EXEC sp_trace_setevent @TraceID, 12, 15, @On
EXEC sp_trace_setevent @TraceID, 12, 16, @On
EXEC sp_trace_setevent @TraceID, 12, 17, @On
EXEC sp_trace_setevent @TraceID, 12, 18, @On
EXEC sp_trace_setevent @TraceID, 12, 21, @On
EXEC sp_trace_setevent @TraceID, 12, 27, @On
EXEC sp_trace_setevent @TraceID, 12, 35, @On
-- add trace columns for Audit Login Event Failed
EXEC sp_trace_setevent @TraceID, 20, 1, @On
EXEC sp_trace_setevent @TraceID, 20, 3, @On
EXEC sp_trace_setevent @TraceID, 20, 6, @On
EXEC sp_trace_setevent @TraceID, 20, 9, @On
EXEC sp_trace_setevent @TraceID, 20, 10, @On
EXEC sp_trace_setevent @TraceID, 20, 11, @On
EXEC sp_trace_setevent @TraceID, 20, 12, @On
EXEC sp_trace_setevent @TraceID, 20, 13, @On
EXEC sp_trace_setevent @TraceID, 20, 14, @On
EXEC sp_trace_setevent @TraceID, 20, 15, @On
EXEC sp_trace_setevent @TraceID, 20, 16, @On
EXEC sp_trace_setevent @TraceID, 20, 17, @On
EXEC sp_trace_setevent @TraceID, 20, 18, @On
EXEC sp_trace_setevent @TraceID, 20, 21, @On
EXEC sp_trace_setevent @TraceID, 20, 27, @On
EXEC sp_trace_setevent @TraceID, 20, 35, @On
-- Set the trace Filters
-- NOTE: variables are used for the BIGINT filter values as this SP does not seem to accept
-- values with or without or CAST statements
-- use sp_trace_setfilter <TraceID>, <ColumnID>, <Logical Operator>, <Comparison Operator>, <Comparison Value>
-- <Logical Operator>: 0 = AND, 1 == OR. AND will always be used here
-- standard exclustion for Application Name to exlude profiler
EXEC sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler '
END
-- now set the filters defined by the values stored agains the trace definition and passed as parameters
-- but only if they are not null and valid
--set up the Text value filter if supplied
IF (@Filter_Text_Value IS NOT NULL)
BEGIN
SELECT
@ComparisonOperatorID = 6, -- this will always be a LIKE operation
@Filter_Text_Value = N'%' + @Filter_Text_Value + N'%'
EXEC sp_trace_setfilter @TraceID, 1, 0, @ComparisonOperatorID, @Filter_Text_Value
END
--set up the DB name value filter if supplied
IF (@Filter_DB_Value IS NOT NULL)
BEGIN
SELECT
@ComparisonOperatorID = 6, -- this will always be a LIKE operation
@Filter_DB_Value = N'%' + @Filter_DB_Value + N'%'
EXEC sp_trace_setfilter @TraceID, 35, 0, @ComparisonOperatorID, @Filter_DB_Value
END
--set up the Login Name value filter if supplied
IF (@Filter_User_Value IS NOT NULL)
BEGIN
SELECT
@ComparisonOperatorID = 6, -- this will always be a LIKE operation
@Filter_User_Value = N'%' + @Filter_User_Value + N'%'
EXEC sp_trace_setfilter @TraceID, 11, 0, @ComparisonOperatorID, @Filter_User_Value
END
-- set up the CPU filter if defined
IF (@Filter_CPU_Operator IS NOT NULL)
AND (@Filter_CPU_Value IS NOT NULL)
AND (@Filter_CPU_Operator IN ('>', '<', '<>', '>=', '<=', '='))
BEGIN
SELECT @ComparisonOperatorID = NULL -- reset it
SELECT @ComparisonOperatorID = CASE @Filter_CPU_Operator
WHEN '=' THEN 0
WHEN '<>' THEN 1
WHEN '>' THEN 2
WHEN '<' THEN 3
WHEN '>=' THEN 4
WHEN '<=' THEN 5
END
EXEC sp_trace_setfilter @TraceID, 18, 0, @ComparisonOperatorID, @Filter_CPU_Value
END
-- set up the Reads filter if defined
IF (@Filter_Reads_Operator IS NOT NULL)
AND (@Filter_Reads_Value IS NOT NULL)
AND (@Filter_Reads_Operator IN ('>', '<', '<>', '>=', '<=', '='))
BEGIN
SELECT @ComparisonOperatorID = NULL -- reset it
SELECT @ComparisonOperatorID = CASE @Filter_Reads_Operator
WHEN '=' THEN 0
WHEN '<>' THEN 1
WHEN '>' THEN 2
WHEN '<' THEN 3
WHEN '>=' THEN 4
WHEN '<=' THEN 5
END
EXEC sp_trace_setfilter @TraceID, 16, 0, @ComparisonOperatorID, @Filter_Reads_Value
END
-- set up the Writes filter if defined
IF (@Filter_Writes_Operator IS NOT NULL)
AND (@Filter_Writes_Value IS NOT NULL)
AND (@Filter_Writes_Operator IN ('>', '<', '<>', '>=', '<=', '='))
BEGIN
SELECT @ComparisonOperatorID = NULL -- reset it
SELECT @ComparisonOperatorID = CASE @Filter_Writes_Operator
WHEN '=' THEN 0
WHEN '<>' THEN 1
WHEN '>' THEN 2
WHEN '<' THEN 3
WHEN '>=' THEN 4
WHEN '<=' THEN 5
END
EXEC sp_trace_setfilter @TraceID, 17, 0, @ComparisonOperatorID, @Filter_Writes_Value
END
-- set up the Duration filter if defined (in NANOSECONDS)
IF (@Filter_Duration_Operator IS NOT NULL)
AND (@Filter_Duration_Value IS NOT NULL)
AND (@Filter_Duration_Operator IN ('>', '<', '<>', '>=', '<=', '='))
BEGIN
SELECT @ComparisonOperatorID = NULL -- reset it
SELECT @ComparisonOperatorID = CASE @Filter_Duration_Operator
WHEN '=' THEN 0
WHEN '<>' THEN 1
WHEN '>' THEN 2
WHEN '<' THEN 3
WHEN '>=' THEN 4
WHEN '<=' THEN 5
END
EXEC sp_trace_setfilter @TraceID, 13, 0, @ComparisonOperatorID, @Filter_Duration_Value
END
-- Set the trace status to start
EXEC sp_trace_setstatus @TraceID, 1
RETURN;
SET NOCOUNT OFF
/*
Full list of Trace Event IDs
10 RPC:Completed Occurs when a remote procedure call (RPC) has completed.
11 RPC:Starting Occurs when an RPC has started.
12 SQL:BatchCompleted Occurs when a Transact-SQL batch has completed.
13 SQL:BatchStarting Occurs when a Transact-SQL batch has started.
14 Audit Login Occurs when a user successfully logs in to SQL Server.
15 Audit Logout Occurs when a user logs out of SQL Server.
16 Attention Occurs when attention events, such as client-interrupt requests or broken client connections, happen.
17 ExistingConnection Detects all activity by users connected to SQL Server before the trace started.
18 Audit Server Starts and Stops Occurs when the SQL Server service state is modified.
19 DTCTransaction Tracks Microsoft Distributed Transaction Coordinator (MS DTC) coordinated transactions between two or more databases.
20 Audit Login Failed Indicates that a login attempt to SQL Server from a client failed.
21 EventLog Indicates that events have been logged in the Windows application log.
22 ErrorLog Indicates that error events have been logged in the SQL Server error log.
23 Lock:Released Indicates that a lock on a resource, such as a page, has been released.
24 Lock:Acquired Indicates acquisition of a lock on a resource, such as a data page.
25 Lock:Deadlock Indicates that two concurrent transactions have deadlocked each other by trying to obtain incompatible locks on resources the other transaction owns.
26 Lock:Cancel Indicates that the acquisition of a lock on a resource has been canceled (for example, due to a deadlock).
27 Lock:Timeout Indicates that a request for a lock on a resource, such as a page, has timed out due to another transaction holding a blocking lock on the required resource. Time-out is determined by the @@LOCK_TIMEOUT function, and can be set with the SET LOCK_TIMEOUT statement.
28 Degree of Parallelism Event (7.0 Insert) Occurs before a SELECT, INSERT, or UPDATE statement is executed.
29-31 Reserved Use Event 28 instead.
32 Reserved Reserved
33 Exception Indicates that an exception has occurred in SQL Server.
34 SP:CacheMiss Indicates when a stored procedure is not found in the procedure cache.
35 SP:CacheInsert Indicates when an item is inserted into the procedure cache.
36 SP:CacheRemove Indicates when an item is removed from the procedure cache.
37 SP:Recompile Indicates that a stored procedure was recompiled.
38 SP:CacheHit Indicates when a stored procedure is found in the procedure cache.
39 Deprecated Deprecated
40 SQL:StmtStarting Occurs when the Transact-SQL statement has started.
41 SQL:StmtCompleted Occurs when the Transact-SQL statement has completed.
42 SP:Starting Indicates when the stored procedure has started.
43 SP:Completed Indicates when the stored procedure has completed.
44 SP:StmtStarting Indicates that a Transact-SQL statement within a stored procedure has started executing.
45 SP:StmtCompleted Indicates that a Transact-SQL statement within a stored procedure has finished executing.
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.
48 Reserved
49 Reserved
50 SQL Transaction Tracks Transact-SQL BEGIN, COMMIT, SAVE, and ROLLBACK TRANSACTION statements.
51 Scan:Started Indicates when a table or index scan has started.
52 Scan:Stopped Indicates when a table or index scan has stopped.
53 CursorOpen Indicates when a cursor is opened on a Transact-SQL statement by ODBC, OLE DB, or DB-Library.
54 TransactionLog Tracks when transactions are written to the transaction log.
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.
56-57 Reserved
58 Auto Stats Indicates an automatic updating of index statistics has occurred.
59 Lock:Deadlock Chain Produced for each of the events leading up to the deadlock.
60 Lock:Escalation Indicates that a finer-grained lock has been converted to a coarser-grained lock (for example, a page lock escalated or converted to a TABLE or HoBT lock).
61 OLE DB Errors Indicates that an OLE DB error has occurred.
62-66 Reserved
67 Execution Warnings Indicates any warnings that occurred during the execution of a SQL Server statement or stored procedure.
68 Showplan Text (Unencoded) Displays the plan tree of the Transact-SQL statement executed.
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).
70 CursorPrepare Indicates when a cursor on a Transact-SQL statement is prepared for use by ODBC, OLE DB, or DB-Library.
71 Prepare SQL ODBC, OLE DB, or DB-Library has prepared a Transact-SQL statement or statements for use.
72 Exec Prepared SQL ODBC, OLE DB, or DB-Library has executed a prepared Transact-SQL statement or statements.
73 Unprepare SQL ODBC, OLE DB, or DB-Library has unprepared (deleted) a prepared Transact-SQL statement or statements.
74 CursorExecute A cursor previously prepared on a Transact-SQL statement by ODBC, OLE DB, or DB-Library is executed.
75 CursorRecompile A cursor opened on a Transact-SQL statement by ODBC or DB-Library has been recompiled either directly or due to a schema change. Triggered for ANSI and non-ANSI cursors.
76 CursorImplicitConversion A cursor on a Transact-SQL statement is converted by SQL Server from one type to another. Triggered for ANSI and non-ANSI cursors.
77 CursorUnprepare A prepared cursor on a Transact-SQL statement is unprepared (deleted) by ODBC, OLE DB, or DB-Library.
78 CursorClose A cursor previously opened on a Transact-SQL statement by ODBC, OLE DB, or DB-Library is closed.
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.
82-91 User Configurable (0-9) Event data defined by the user.
92 Data File Auto Grow Indicates that a data file was extended automatically by the server.
93 Log File Auto Grow Indicates that a log 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.
96 Showplan Text Displays the query plan tree of the SQL statement from the query optimizer. Note that the TextData column does not contain the Showplan for this event.
97 Showplan All Displays the query plan with full compile-time details of the SQL statement executed. Note that the TextData column does not contain the Showplan for this event.
98 Showplan Statistics Profile Displays the query plan with full run-time details of the SQL statement executed. Note that the TextData column does not contain the Showplan for this event.
99 Reserved
100 RPC Output Parameter Produces output values of the parameters for every RPC.
101 Reserved
102 Audit Database Scope GDR Occurs every time a GRANT, DENY, REVOKE for a statement permission is issued by any user in SQL Server for database-only actions such as granting permissions on a database.
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.
107 Audit Login Change Password Event Occurs when a SQL Server login password is changed. Passwords are not recorded.
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.
112 Audit App Role Change Password Event Occurs when a password of an application role is changed.
113 Audit Statement Permission Event Occurs when a statement permission (such as CREATE TABLE) is used.
114 Audit Schema Object Access Event Occurs when an object permission (such as SELECT) is used, both successfully or unsuccessfully.
115 Audit Backup/Restore Event Occurs when a BACKUP or RESTORE command is issued.
116 Audit DBCC Event Occurs when DBCC commands are issued.
117 Audit Change Audit Event Occurs when audit trace modifications are made.
118 Audit Object Derived Permission Event Occurs when a CREATE, ALTER, and DROP object commands are issued.
119 OLEDB Call Event Occurs when OLE DB provider calls are made for distributed queries and remote stored procedures.
120 OLEDB QueryInterface Event Occurs when OLE DB QueryInterface calls are made for distributed queries and remote stored procedures.
121 OLEDB DataRead Event Occurs when a data request call is made to the OLE DB provider.
122 Showplan XML Occurs when an SQL statement executes. Include this event to identify Showplan operators. Each event is stored in a well-formed XML document. Note that the Binary column for this event contains the encoded Showplan. Use SQL Server Profiler to open the trace and view the Showplan.
123 SQL:FullTextQuery Occurs when a full text query executes.
124 Broker:Conversation Reports the progress of a Service Broker conversation.
125 Deprecation Announcement Occurs when you use a feature that will be removed from a future version of SQL Server.
126 Deprecation Final Support Occurs when you use a feature that will be removed from the next major release of SQL Server.
127 Exchange Spill Event Occurs when communication buffers in a parallel query plan have been temporarily written to the tempdb database.
128 Audit Database Management Event Occurs when a database is created, altered, or dropped.
129 Audit Database Object Management Event Occurs when a CREATE, ALTER, or DROP statement executes on database objects, such as schemas.
130 Audit Database Principal Management Event Occurs when principals, such as users, are created, altered, or dropped from a database.
131 Audit Schema Object Management Event Occurs when server objects are created, altered, or dropped.
132 Audit Server Principal Impersonation Event Occurs when there is an impersonation within server scope, such as EXECUTE AS LOGIN.
133 Audit Database Principal Impersonation Event Occurs when an impersonation occurs within the database scope, such as EXECUTE AS USER or SETUSER.
134 Audit Server Object Take Ownership Event Occurs when the owner is changed for objects in server scope.
135 Audit Database Object Take Ownership Event Occurs when a change of owner for objects within database scope occurs.
136 Broker:Conversation Group Occurs when Service Broker creates a new conversation group or drops an existing conversation group.
137 Blocked Process Report Occurs when a process has been blocked for more than a specified amount of time. Does not include system processes or processes that are waiting on non deadlock-detectable resources. Use sp_configure to configure the threshold and frequency at which reports are generated.
138 Broker:Connection Reports the status of a transport connection managed by Service Broker.
139 Broker:Forwarded Message Sent Occurs when Service Broker forwards a message.
140 Broker:Forwarded Message Dropped Occurs when Service Broker drops a message that was intended to be forwarded.
141 Broker:Message Classify Occurs when Service Broker determines the routing for a message.
142 Broker:Transmission Indicates that errors have occurred in the Service Broker transport layer. The error number and state values indicate the source of the error.
143 Broker:Queue Disabled Indicates a poison message was detected because there were five consecutive transaction rollbacks on a Service Broker queue. The event contains the database ID and queue ID of the queue that contains the poison message.
144-145 Reserved
146 Showplan XML Statistics Profile Occurs when an SQL statement executes. Identifies the Showplan operators and displays complete, compile-time data. Note that the Binary column for this event contains the encoded Showplan. Use SQL Server Profiler to open the trace and view the Showplan.
148 Deadlock Graph Occurs when an attempt to acquire a lock is canceled because the attempt was part of a deadlock and was chosen as the deadlock victim. Provides an XML description of a deadlock.
149 Broker:Remote Message Acknowledgement Occurs when Service Broker sends or receives a message acknowledgement.
150 Trace File Close Occurs when a trace file closes during a trace file rollover.
151 Reserved
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.
154 Reserved
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.
158 Audit Broker Conversation Reports audit messages related to Service Broker dialog security.
159 Audit Broker Login Reports audit messages related to Service Broker transport security.
160 Broker:Message Undeliverable Occurs when Service Broker is unable to retain a received message that should have been delivered to a service.
161 Broker:Corrupted Message Occurs when Service Broker receives a corrupted message.
162 User Error Message Displays error messages that users see in the case of an error or exception.
163 Broker:Activation Occurs when a queue monitor starts an activation stored procedure, sends a QUEUE_ACTIVATION notification, or when an activation stored procedure started by a queue monitor exits.
164 Object:Altered Occurs when a database object is altered.
165 Performance statistics Occurs when a compiled query plan has been cached for the first time, recompiled, or removed from the plan cache.
166 SQL:StmtRecompile Occurs when a statement-level recompilation occurs.
167 Database Mirroring State Change Occurs when the state of a mirrored database changes.
168 Showplan XML For Query Compile Occurs when an SQL statement compiles. Displays the complete, compile-time data. Note that the Binary column for this event contains the encoded Showplan. Use SQL Server Profiler to open the trace and view the Showplan.
169 Showplan All For Query Compile Occurs when an SQL statement compiles. Displays complete, compile-time data. Use to identify Showplan operators.
170 Audit Server Scope GDR Event Indicates that a grant, deny, or revoke event for permissions in server scope occurred, such as creating a login.
171 Audit Server Object GDR Event Indicates that a grant, deny, or revoke event for a schema object, such as a table or function, occurred.
172 Audit Database Object GDR Event Indicates that a grant, deny, or revoke event for database objects, such as assemblies and schemas, occurred.
173 Audit Server Operation Event Occurs when Security Audit operations such as altering settings, resources, external access, or authorization are used.
175 Audit Server Alter Trace Event Occurs when a statement checks for the ALTER TRACE permission.
176 Audit Server Object Management Event Occurs when server objects are created, altered, or dropped.
177 Audit Server Principal Management Event Occurs when server principals are created, altered, or dropped.
178 Audit Database Operation Event Occurs when database operations occur, such as checkpoint or subscribe query notification.
180 Audit Database Object Access Event Occurs when database objects, such as schemas, are accessed.
181 TM: Begin Tran starting Occurs when a BEGIN TRANSACTION request starts.
182 TM: Begin Tran completed Occurs when a BEGIN TRANSACTION request completes.
183 TM: Promote Tran starting Occurs when a PROMOTE TRANSACTION request starts.
184 TM: Promote Tran completed Occurs when a PROMOTE TRANSACTION request completes.
185 TM: Commit Tran starting Occurs when a COMMIT TRANSACTION request starts.
186 TM: Commit Tran completed Occurs when a COMMIT TRANSACTION request completes.
187 TM: Rollback Tran starting Occurs when a ROLLBACK TRANSACTION request starts.
188 TM: Rollback Tran completed Occurs when a ROLLBACK TRANSACTION request completes.
189 Lock:Timeout (timeout > 0) Occurs when a request for a lock on a resource, such as a page, times out.
190 Progress Report: Online Index Operation Reports the progress of an online index build operation while the build process is running.
191 TM: Save Tran starting Occurs when a SAVE TRANSACTION request starts.
192 TM: Save Tran completed Occurs when a SAVE TRANSACTION request completes.
193 Background Job Error Occurs when a background job terminates abnormally.
194 OLEDB Provider Information Occurs when a distributed query runs and collects information corresponding to the provider connection.
195 Mount Tape Occurs when a tape mount request is received.
196 Assembly Load Occurs when a request to load a CLR assembly occurs.
197 Reserved
198 XQuery Static Type Occurs when an XQuery expression is executed. This event class provides the static type of the XQuery expression.
199 QN: subscription Occurs when a query registration cannot be subscribed. The TextData column contains information about the event.
200 QN: parameter table Information about active subscriptions is stored in internal parameter tables. This event class occurs when a parameter table is created or deleted. Typically, these tables are created or deleted when the database is restarted. The TextData column contains information about the event.
201 QN: template A query template represents a class of subscription queries. Typically, queries in the same class are identical except for their parameter values. This event class occurs when a new subscription request falls into an already existing class of (Match), a new class (Create), or a Drop class, which indicates cleanup of templates for query classes without active subscriptions. The TextData column contains information about the event.
202 QN: dynamics Tracks internal activities of query notifications. The TextData column contains information about the event.
212 Bitmap Warning Indicates when bitmap filters have been disabled in a query.
213 Database Suspect Data Page Indicates when a page is added to the suspect_pages table in msdb.
214 CPU threshold exceeded Indicates when the Resource Governor detects a query has exceeded the CPU threshold value (REQUEST_MAX_CPU_TIME_SEC).
215 PreConnect:Starting Indicates when a LOGON trigger or Resource Governor classifier function starts execution.
216 PreConnect:Completed Indicates when a LOGON trigger or Resource Governor classifier function completes execution.
217 Plan Guide Successful Indicates that SQL Server successfully produced an execution plan for a query or batch that contained a plan guide.
218 Plan Guide Unsuccessful Indicates that SQL Server could not produce an execution plan for a query or batch that contained a plan guide. SQL Server attempted to generate an execution plan for this query or batch without applying the plan guide. An invalid plan guide may be the cause of this problem. You can validate the plan guide by using the sys.fn_validate_plan_guide system function.
235 Audit Fulltext
*/
Page updated
Google Sites
Report abuse