Shulou(Shulou.com)05/31 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.