Example Analysis of sql server tracking Database
Today, I will talk to you about the example analysis of sql server tracking database, many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.
Select * FROM:: fn_trace_getinfo (NULL) WHERE property=5 AND traceid > 1
-- set blocking threshold
Use master
GO
Sp_configure 'show advanced options',1
RECONFIGURE WITH override
GO
Sp_configure 'blocked process threshold',1
RECONFIGURE WITH override
GO
-- Create a Queue
Declare @ rc int
Declare @ TraceID int
Declare @ maxfilesize bigint
Declare @ DateTime datetime
-- set the trace end time
Set @ DateTime = '2017-03-31 12 purl 01purl 00.000'
-- Reserve 30g of space
Set @ maxfilesize = 1000
-- 2 indicates that the file storage directory needs to be set.
Exec @ rc = sp_trace_create @ TraceID output, 2, sqltrace',:\ sqltrace', @ maxfilesize, @ Datetime,30
If (@ rc! = 0) goto error
-- set trace events
Declare @ on bit
Set @ on = 1
-- tracking blocking
Exec sp_trace_setevent @ TraceID, 137,3, @ on
Exec sp_trace_setevent @ TraceID, 137,15, @ on
Exec sp_trace_setevent @ TraceID, 137,1, @ on
Exec sp_trace_setevent @ TraceID, 137,13, @ on
-- tracking deadlock
Exec sp_trace_setevent @ TraceID, 148,11, @ on
Exec sp_trace_setevent @ TraceID, 148,12, @ on
Exec sp_trace_setevent @ TraceID, 148,14, @ on
Exec sp_trace_setevent @ TraceID, 148,1, @ on
-- tracking stored procedure calls
Exec sp_trace_setevent @ TraceID, 10, 15, @ on-- EndTime
Exec sp_trace_setevent @ TraceID, 10, 16, @ on-- Reads
Exec sp_trace_setevent @ TraceID, 10, 1, @ on-- TextData
Exec sp_trace_setevent @ TraceID, 10, 3, @ on-- DatabaseID
Exec sp_trace_setevent @ TraceID, 10, 17, @ on-- Writes
Exec sp_trace_setevent @ TraceID, 10, 10, @ on-- ApplicationName
Exec sp_trace_setevent @ TraceID, 10, 18, @ on-- CPU
Exec sp_trace_setevent @ TraceID, 10, 12, @ on-- SPID
Exec sp_trace_setevent @ TraceID, 10, 14, @ on-- StartTime
-- tracking T-SQL calls
Exec sp_trace_setevent @ TraceID, 12, 15, @ on-- EndTime
Exec sp_trace_setevent @ TraceID, 12, 16, @ on-- Reads
Exec sp_trace_setevent @ TraceID, 12, 1, @ on-- TextData
Exec sp_trace_setevent @ TraceID, 12, 17, @ on-- Writes
Exec sp_trace_setevent @ TraceID, 12, 10, @ on-- ApplicationName
Exec sp_trace_setevent @ TraceID, 12, 14, @ on-- StartTime
Exec sp_trace_setevent @ TraceID, 12, 18, @ on-- CPU
Exec sp_trace_setevent @ TraceID, 12, 12, @ on-- SPID
Exec sp_trace_setevent @ TraceID, 12, 3, @ on-- DatabaseID
-- set filtering
Declare @ intfilter int
Declare @ bigintfilter bigint
-- filter non-SQL trace
Exec sp_trace_setfilter @ TraceID, 10, 0, 7, N'SQL Server Profiler'
-- filter read greater than or equal to 1000 before being tracked
Set @ bigintfilter = 1000
Exec sp_trace_setfilter @ TraceID, 16,0,4, @ bigintfilter
-- start tracking
Exec sp_trace_setstatus @ TraceID, 1
-- return TraceID
Select TraceID=@TraceID
Goto finish
Error:
Select ErrorCode=@rc
Finish:
Go
After reading the above, do you have any further understanding of the sample analysis of the sql server tracking database? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.
