SQL Trace and event notifications

* Monitoring Events

SQL Server provides SQL Trace and event notifications to monitor events that occur in the Database Engine.

Topic Description
Introducing SQL Trace Explains how SQL Trace works and how to create traces with stored procedures.
Introducing SQL Server Profiler Explains how to trace with SQL Server Profiler.
Optimizing Utility and Tool Performance Contains information about server operations that can be tuned to improve utility and tool performance
Optimizing Server Performance Contains information about how you can tune SQL Server to optimize server performance.
Introducing Event Notifications Explains how event notifications work and how to use them to monitor and respond to database and server events.
Monitoring with SQL Server Management Studio Contains information about how you can use SQL Server Management Studio to monitor SQL Server performance.
Monitoring the Error Logs Contains information about how you can use the SQL Server error log and the Microsoft Windows application log to monitor SQL Server activity.
Monitoring with Transact-SQL Statements Contains information about how you can use Transact-SQL statements to monitor an instance of SQL Server.

 

* SQL Server Profiler Stored Procedures (Transact-SQL)

SQL Server supports the following system stored procedures that are used by SQL Server Profiler to monitor performance and activity.

sp_trace_create sp_trace_setfilter
sp_trace_generateevent sp_trace_setstatus
sp_trace_setevent  

 

* SQL Server Event Class Reference

The following table describes the event categories and lists their associated event classes.

Event Category Event Classes
The Broker Event Category includes event classes that are produced by the Service Broker. Broker:Activation Event ClassBroker:Connection Event ClassBroker:Conversation Event Class

Broker:Conversation Group Event Class

Broker:Corrupted Message Event Class

Broker:Forwarded Message Dropped Event Class

Broker:Forwarded Message Sent Event Class

Broker:Message Classify Event Class

Broker:Message Drop Event Class

Broker:Remote Message Ack Event Class

The Cursors Event Category includes event classes that are produced by cursor operations. CursorClose Event ClassCursorExecute Event ClassCursorImplicitConversion Event Class

CursorOpen Event Class

CursorPrepare Event Class

CursorRecompile Event Class

CursorUnprepare Event Class

The CLR Event Category includes event classes that are produced by the execution of .NET common language runtime (CLR) objects. Assembly Load Event Class
The Database Event Category includes event classes that are produced when data or log files grow or shrink automatically. Data File Auto Grow Event ClassData File Auto Shrink Event ClassDatabase Mirroring State Change Event Class

Log File Auto Grow Event Class

Log File Auto Shrink Event Class

The Deprecation Event Category includes deprecation related events. Deprecation Announcement Event ClassDeprecation Final Support Event Class
The Errors and Warnings Event Category (Database Engine) includes event classes that are produced when a SQL Server error or warning is returned, for example, if an error occurs during the compilation of a stored procedure or an exception occurs in SQL Server. Attention Event ClassBackground Job Error Event ClassBlocked Process Report Event Class

CPU Threshold Exceeded Event Class

ErrorLog Event Class

EventLog Event Class

Exception Event Class

Exchange Spill Event Class

Execution Warnings Event Class

Hash Warning Event Class

Missing Column Statistics Event Class

Missing Join Predicate Event Class

Sort Warnings Event Class

User Error Message Event Class

The Full Text Event Category includes event classes that are produced when full-text searches are started, interrupted, or stopped. FT:Crawl Aborted Event ClassFT:Crawl Started Event ClassFT:Crawl Stopped Event Class
The Locks Event Category includes event classes that are produced when a lock is acquired, cancelled, released, or has some other action performed on it. Deadlock Graph Event ClassLock:Acquired Event ClassLock:Cancel Event Class

Lock:Deadlock Chain Event Class

Lock:Deadlock Event Class

Lock:Escalation Event Class

Lock:Released Event Class

Lock:Timeout (timeout > 0) Event Class

Lock:Timeout Event Class

The Objects Event Category includes event classes that are produced when database objects are created, opened, closed, dropped, or deleted. Auto Stats Event ClassObject:Altered Event ClassObject:Created Event Class

Object:Deleted Event Class

The OLEDB Event Category includes event classes that are produced by OLE DB calls. OLEDB Call Event ClassOLEDB DataRead Event ClassOLEDB Errors Event Class

OLEDB Provider Information Event Class

OLEDB QueryInterface Event Class

The Performance Event Category includes event classes that are produced when SQL data manipulation language (DML) operators execute. Degree of Parallelism (7.0 Insert) Event ClassPerformance Statistics Event ClassShowplan All Event Class

Showplan All for Query Compile Event Class

Showplan Statistics Profile Event Class

Showplan Text Event Class

Showplan Text (Unencoded) Event Class

Showplan XML Event Class

Showplan XML for Query Compile Event Class

Showplan XML Statistics Profile Event Class

SQL:FullTextQuery Event Class

The Progress Report Event Category includes the Progress Report: Online Index Operation event class. Progress Report: Online Index Operation Event Class
The Scans Event Category includes event classes that are produced when tables and indexes are scanned. Scan:Started Event ClassScan:Stopped Event Class
The Security Audit Event Category includes event classes that are used to audit server activity. Audit Add DB User Event ClassAudit Add Login to Server Role Event ClassAudit Add Member to DB Role Event Class

Audit Add Role Event Class

Audit Addlogin Event Class

Audit App Role Change Password Event Class

Audit Backup/Restore Event Class

Audit Broker Conversation Event Class

Audit Broker Login Event Class

Audit Change Audit Event Class

Audit Change Database Owner Event Class

Audit Database Management Event Class

Audit Database Object Access Event Class

Audit Database Object GDR Event Class

Audit Database Object Management Event Class

Audit Database Object Take Ownership Event Class

Audit Database Operation Event Class

Audit Database Principal Impersonation Event Class

Audit Database Principal Management Event Class

Audit Database Scope GDR Event Class

Audit DBCC Event Class

Audit Login Change Password Event Class

Audit Login Change Property Event Class

Audit Login Event Class

Audit Login Failed Event Class

Audit Login GDR Event Class

Audit Logout Event Class

Audit Object Derived Permission Event Class

Audit Schema Object Access Event Class

Audit Schema Object GDR Event Class

Audit Schema Object Management Event Class

Audit Schema Object Take Ownership Event Class

Audit Server Alter Trace Event Class

Audit Server Object GDR Event Class

Audit Server Object Management Event Class

Audit Server Object Take Ownership Event Class

Audit Server Operation Event Class

Audit Server Principal Impersonation Event Class

Audit Server Principal Management Event Class

Audit Server Scope GDR Event Class

Audit Server Starts and Stops Event Class

Audit Statement Permission Event Class

The Server Event Category contains general server events. Mount Tape Event ClassServer Memory Change Event ClassTrace File Close Event Class
The Sessions Event Category includes event classes produced by clients connecting to and disconnecting from an instance of SQL Server. ExistingConnection Event Class
The Stored Procedures Event Category includes event classes produced by the execution of stored procedures. PreConnect:Completed Event ClassPreConnect:Starting Event ClassRPC:Completed Event Class

RPC Output Parameter Event Class

RPC:Starting Event Class

SP:CacheHit Event Class

SP:CacheInsert Event Class

SP:CacheMiss Event Class

SP:CacheRemove Event Class

SP:Completed Event Class

SP:Recompile Event Class

SP:Starting Event Class

SP:StmtCompleted Event Class

SP:StmtStarting Event Class

The Transactions Event Category includes event classes produced by the execution of Microsoft Distributed Transaction Coordinator transactions or by writing to the transaction log. DTCTransaction Event ClassSQLTransaction Event ClassTM: Begin Tran Completed Event Class

TM: Begin Tran Starting Event Class

TM: Commit Tran Completed Event Class

TM: Commit Tran Starting Event Class

TM: Promote Tran Completed Event Class

TM: Promote Tran Starting Event Class

TM: Rollback Tran Completed Event Class

TM: Rollback Tran Starting Event Class

TM: Save Tran Completed Event Class

TM: Save Tran Starting Event Class

TransactionLog Event Class

The TSQL Event Category includes event classes produced by the execution of Transact-SQL statements passed to an instance of SQL Server from the client. Exec Prepared SQL Event ClassPrepare SQL Event ClassSQL:BatchCompleted Event Class

SQL:BatchStarting Event Class

SQL:StmtCompleted Event Class

SQL:StmtRecompile Event Class

SQL:StmtStarting Event Class

Unprepare SQL Event Class

XQuery Static Type Event Class

The User-Configurable Event Category includes event classes that you can define. User-Configurable Event Class

 

.

Khan – MCTS www.addarr.com

Advertisements

One thought on “SQL Trace and event notifications

  1. Brock 07/27/2015 at 19:21 Reply

    Hey there! I’ve been following your web site for a long time now and
    finally got the bravery to go ahead and give you a shout out from Lubbock Tx!

    Just wanted to tell you keep up the good job!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: