(4.7) How do I capture and record deadlocks that occur in SQL Server?

Turn from: 19498327 How do I capture and record deadlocks that occur in SQL Server?

How SQL Server makes the error log record deadlock

February 19, 2014 18:25:55Hits: 1313

We know that you can use the Profiler tool that comes with SQL Server to track the deadlock information. But this way there is a big one, that is, the consumption is very large. According to a major foreign God test, Profiler can even account for the total server bandwidth of 35%, so in a busy system, using Profiler is obviously not a good idea, below I introduce two ways to consume less. The second is the least expensive and can be used in the busiest system. The first is the most flexible and can meet a wide range of applications.

Method One: Leverage SQL Server Agent (alert+job)

The steps are as follows:

1. First use the following command to enable the related trace flag.

[C-sharp] View plaincopy

1. DBCC TRACEON (3605,1204,1222,-1)

Description

3605 outputs the results of the DBCC to the error log.

1204 returns the resource and type of the lock that participates in the deadlock, and the current command that is affected.

1222 returns the resources and types of locks that participate in deadlocks, and the affected current commands that use XML formats that do not conform to any XSD schema (more than 1204, SQL 2005 and more are available).

1 opens the specified trace flag in a global fashion.

The trace flag scope above is global, which will continue to work during SQL Server operation until SQL Server restarts.

If you want to ensure that SQL Server automatically turns on these flags after a restart, you can use the/T startup option in the SQL Server service startup option to specify that the trace flag is set to on during startup. (In SQL Server Configuration Manager->sql Server service->sql server-> properties, advanced startup parameters)

After running the above statement, when a deadlock occurs in SQL Server, you can already see it in the error log, but it is not intuitive (mixed with other information). (SQL Server log, management---SQL Server instance, SSMS)

2. Build the table and store the deadlock record

[C-sharp] View plaincopy

1. Use [Cole]--cole is my sample database, you can modify it according to the actual situation.
2. GO
3. CREATE TABLE Deadlocklog (
4. ID int IDENTITY (1, 1) not NULL,
5. Logdate DATETIME,
6. ProcessInfo VARCHAR (10),
7. ErrorText VARCHAR (MAX)
8. )
9. GO

3. Create a job

Create a new job (assuming the name is Deadlockjob), create a new step in step, write a step name, and the database is "Cole" (see 2. Build table), enter the following statement in the command field:

[C-sharp] View plaincopy

2. --Create a new temporary table
4. IF object_id (' tempdb.dbo. #ErrorLog ') is not Null
6. DROP TABLE #ErrorLog
8. CREATE TABLE #ErrorLog (Id int IDENTITY (1, 1) not NULL, a DATETIME, B varchar (ten), C varchar (MAX))
10. --Inserting the current log record into a temporary table
12. INSERT into #ErrorLog EXEC master.dbo.sp_readerrorlog
14. --inserting deadlock information into the user table
16. Insert Deadlocklog
18. Select a, B, c
20. From #ErrorLog
22. Where ID >= (select MAX (ID) from #ErrorLog where C like '%deadlock encountered% ')
24. DROP TABLE #ErrorLog

4. Create a new alert

In the new alert form, on the General tab, make the following settings:

Name: Can be according to the actual self-naming, here I use Deadlockalert

Type: Select "SQL Server performance condition alert"

Object: Sqlserver:locks

Counter: Number of Deadlocks/sec

Example: _total

The counter triggers an alert when the following conditions are met: higher than

Value: 0

After Setup is complete, it should look like the following:

In the Response tab, select Execute Job and select the job we created in step 3 (that is, deadlockjob)

So far, we've done all the steps, so you can check the Deadlocklog table at any time to show the deadlock information.

Method Two: Take advantage of server-side tracking.

The specific implementation steps are as follows:

1. Write the following script and execute

[C-sharp] View plaincopy

2. --Defining parameters
4. DECLARE @rc int
6. DECLARE @TraceID int
8. DECLARE @maxfilesize bigint
10. Set @maxfilesize = 5
12. --Initialization tracking
14. exec @rc = sp_trace_create @TraceID output, 0, N' E:/dblog/deadlockdetect ', @maxfilesize, NULL
16. --The E:/dblog/deadlockdetect here is the file name (which can be modified by itself), and SQL automatically appends the. trc extension
18. if (@rc! = 0) goto error
20. --Set trace events
22. DECLARE @on bit
24. Set @on = 1
26. --148 in the following statement refers to the Locks:deadlock graph event (see sys.trace_events) and 12 to the SPID column (see SYS.TRACE_COLUMNS)
28. exec sp_trace_setevent @TraceID, 148, @on
30. exec sp_trace_setevent @TraceID, 148, one, @on
32. exec sp_trace_setevent @TraceID, 148, 4, @on
34. exec sp_trace_setevent @TraceID, 148, @on
36. exec sp_trace_setevent @TraceID, 148, @on
38. exec sp_trace_setevent @TraceID, 148, @on
40. exec sp_trace_setevent @TraceID, 148, 1, @on
42. --Start tracking
44. exec sp_trace_setstatus @TraceID, 1
46. --record the Tracking ID for later use
48. Select traceid = @TraceID
50. Goto Finish
52. Error
54. Select [Email protected]
56. Finish
58. Go

Once the above statement is run, a record is automatically inserted into the file e:/dblog/deadlockdetect.trc whenever a deadlock event occurs in SQL Server.

2. Pausing and stopping server-side tracking

To pause the server-side trace above, run the following statement:

EXEC sp_trace_setstatus 1, 0--The first parameter represents Traceid, which is the output parameter in step 1. The second parameter indicates that the state is changed to 0, which is a pause

To stop the above server-side trace, run the following statement:

EXEC sp_trace_setstatus 1, 2--the first parameter represents Traceid, which is the output parameter in step 1. The second parameter indicates that the state is changed to 2, that is, stop

3. View the contents of the trace file

There are two ways to view the trace file (E:/DBLOG/DEADLOCKDETECT.TRC) generated above:

1). Execute T-SQL command

[C-sharp] View plaincopy

1. SELECT * from fn_trace_gettable (' e:/dblog/deadlockdetect.trc ', 1)

The TextData column in the result returns the details of the deadlock in the form of XML.

2). Open in SQL Server Profiler.

Go to Profiler, open trace file, select E:/dblog/deadlockdetect.trc, and you can see the deadlock information in graphical form.

(4.7) How do I capture and record deadlocks that occur in SQL Server?