Microsoft® SQL Server® 2012 Bible

928

Part VII: Monitoring and Auditing

To programmatically view the events and data columns collected by a trace, use the follow-

ing query (you need to modify the parameter for fn_trace_geteventinfo):

SELECT tcat.name +':' + te.name AS 'Event', tcol.NAME AS 'Column'

FROM fn_trace_geteventinfo (2) tinfo

JOIN sys.trace_events te

ON tinfo.eventid = te.trace_event_id

JOIN sys.trace_categories tcat

ON te.category_id = tcat.category_id

JOIN sys.trace_columns tcol

ON tinfo.columnid = tcol.trace_column_id

Result:

Event Column

-------------------------- -----------------------

TSQL:SQL:StmtCompleted TextData

TSQL:SQL:StmtCompleted DatabaseID

TSQL:SQL:StmtCompleted ApplicationName

TSQL:SQL:StmtCompleted SPID

TSQL:SQL:StmtCompleted Duration

TSQL:SQL:StmtCompleted StartTime

TSQL:SQL:StmtCompleted RowCounts

TSQL:SQL:StmtCompleted IsSystem

TSQL:SQL:StmtCompleted EndTime

TSQL:SQL:StmtCompleted Reads

TSQL:SQL:StmtCompleted Writes

TSQL:SQL:StmtCompleted CPU

TSQL:SQL:StmtCompleted DatabaseName

To stop a server-side trace, use the sp_trace_setstatus system stored procedure. The first

parameter is the traceid, and the second parameter specifies the action: 0  = stop the trace,

1  = start the trace, and 2  = close and delete the trace. The sample code uses the trace as 2:

EXEC sp_trace_setstatus 2, 0

Another useful trace system stored procedure is fn_trace_gettable, which reads a trace

file and returns the data in table form:

SELECT *

FROM fn_trace_gettable

('C:\Program Files\Microsoft SQL Server

\MSSQL11.MSSQLSERVER\MSSQL\Log\log_195.trc', 1)

Preconfi gured Traces

SQL Server automatically runs a trace called the Default Trace that gathers basic events

such as server start and stop, file growth, and creating or dropping objects. As the default

trace, its trace ID is 1. Theoretically, it could be stopped without any ill effects, but there’s

no reason to do so.

c38.indd 928c38.indd 928 7/31/2012 10:04:20 AM7/31/2012 10:04:20 AM

http://www.it-ebooks.info