T-SQL 生成trc抓死锁
问题出现：
陈争：大家有通过邮件接收死锁的经验吗？
如果需要停止当前跟踪，这个也可以排job
/*stop tracking
Declare @ID int
*/
开始：
在C: 建个文件夹ProfilerLog 用来存文件 比如C:\ProfilerLog\Perfworkload.trc
代码见checkdeadlock.sql：
create PROCEDURE [dbo].[Profiler_Perf_Start] @TracePath nvarchar(128), @TraceFile nvarchar(20), @StopTime datetime=null, @MaxSize bigint=1000, @DurationFilterValue bigint=200000, @TraceID int output, @dbid int AS Set nocount on declare @result int declare @ID int declare @pathExist as bit declare @fullPath nvarchar(200) declare @Sql nvarchar(300) if exists (SELECT top 1 * FROM :: fn_trace_getinfo(default) where traceid<>'1') begin SELECT top 1 @ID=traceID FROM :: fn_trace_getinfo(default) where traceid<>'1' exec sp_trace_setstatus @ID,0 --Stops the specified trace. exec sp_trace_setstatus @ID,2 --Closes the specified trace and deletes its definition from the server. end CREATE TABLE #tmp ([File Exists] BIT, [File is a Directory] BIT, [Parent Directory Exists] BIT) insert into #tmp exec xp_fileexist @TracePath select @pathExist=[Parent Directory Exists] from #tmp if @pathExist=0 begin set @Sql='MD '+@TracePath exec master..xp_cmdshell @Sql end set @fullPath=@TracePath+'\'+@TraceFile set @Sql=N'del '+@fullPath+'.trc' execute master..xp_cmdshell @sql set @fullPath=@TracePath+'\'+@TraceFile exec @result=sp_trace_create @traceID output,0,@fullPath,@MaxSize,@StopTime if @result!=0 goto error exec sp_trace_setevent @TraceID, 25, 1, 1 exec sp_trace_setevent @TraceID, 25, 3, 1 --databaseID exec sp_trace_setevent @TraceID, 25, 8, 1 --hostname exec sp_trace_setevent @TraceID, 25, 10, 1 --applicationName exec sp_trace_setevent @TraceID, 25, 12, 1 --spid exec sp_trace_setevent @TraceID, 25, 14, 1 --start time exec sp_trace_setevent @TraceID, 25, 15, 1 --end time /* --SQL Server Profiler SQLAgent EXECUTE sp_trace_setfilter @traceID, 13, 0, 2,@DurationFilterValue */ exec sp_trace_setstatus @traceid,1 print 'Trace ID:'+cast(@traceID as varchar(10))+',Trace File :'+@traceFile+'' goto finish error: print ('Error Code:'+cast(@result as varchar(20))) finish:
核心：
|
evenID： |
25 |
Lock:Deadlock |
exec sp_trace_setevent @TraceID, 25, 1, 1
exec sp_trace_setevent @TraceID, 25, 3, 1 --databaseID
exec sp_trace_setevent @TraceID, 25, 8, 1 --hostname
exec sp_trace_setevent @TraceID, 25, 10, 1 --applicationName
exec sp_trace_setevent @TraceID, 25, 12, 1 --spid
exec sp_trace_setevent @TraceID, 25, 14, 1 --start time
exec sp_trace_setevent @TraceID, 25, 15, 1 --end time
如果需要更多信息查找 http://technet.microsoft.com/en-us/library/ms186265.aspx
Declare @traceID Int, @dbid Int
Set @dbid = DB_ID('mytest')
Exec Profiler_Perf_Start @TracePath = N'C:\ProfilerLog\',
@TraceFile = N'Perfworkload',
@dbid = @dbid,
@traceID = @traceID Output
建立测试 制造一次死锁
建立存储表
CREATE TABLE [dbo].[Profiler_Log](
[DBName] [nvarchar](128) NULL,
[TextData] [nvarchar](max) NULL,
[SPID] [int] NULL,
[Duration] [bigint] NULL,
[StartTime] [datetime] NULL,
[EndTime] [datetime] NULL,
[HostName] [nvarchar](256) NULL,
[ApplicationName] [nvarchar](256) NULL,
[LoginName] [nvarchar](256) NULL,
[ServerName] [nvarchar](256) NULL
) ON [PRIMARY]
GO
插入trc中记录
insert into Profiler_Log(DBName,TextData,SPID,Duration,StartTime,EndTime,HostName,ApplicationName,LoginName,ServerName)
SELECT db_name([DatabaseID]) as DBName,Cast([TextData] as nvarchar(max)) as TextData,
[SPID],[Duration],[StartTime],[EndTime],
[HostName], [ApplicationName], [LoginName],
[ServerName]
FROM ::fn_trace_gettable(N'C:\ProfilerLog\Perfworkload.trc', default)
where TextData not like '--%' and TextData is not null --(0001)
查看结果：
select * from Profiler_Log
附文:
http://msdn.microsoft.com/zh-tw/library/ms186265%28v=sql.120%29.aspx
sp_trace_setevent sp_trace_setevent [ @traceid = ] trace_id, [ @eventid = ] event_id, [ @columnid = ] column_id, [ @on = ] on
--@eventid
10 RPC:Completed 發生在遠端程序呼叫 (RPC) 已完成之時。
14 Audit Login 發生在使用者成功登入 SQL Server 之時。
15 Audit Logout 發生在使用者登出 SQL Server 之時。
17 ExistingConnection 在啟動追蹤之前，偵測連接到 SQL Server 之使用者的所有活動。
--@columnid
1 TextData 這是一個文字值，會隨著追蹤所擷取的事件類別而不同。
2 BinaryData 這是一個二進位值，會隨著追蹤所擷取的事件類別而不同。
3 DatabaseID 這是 USE database 陳述式所指定的資料庫識別碼，如果給定連接未發出 USE database 陳述式，就是預設資料庫。您可以利用 DB_ID 函數來決定資料庫的值。
4 TransactionID 由系統指派給交易的識別碼。
5 LineNumber 包含錯誤行號。如果事件與 Transact-SQL 陳述式 (例如 SP:StmtStarting) 有關，LineNumber 便會將陳述式的行數包含在預存程序或批次中。
6 NTUserName Microsoft Windows 使用者名稱。
7 NTDomainName 使用者所隸屬的 Windows 網域。
8 HostName 引發要求的用戶端電腦名稱。
9 ClientProcessID 用戶端電腦指派給執行用戶端應用程式之處理序的識別碼。
10 ApplicationName 建立 SQL Server 執行個體之連接的用戶端應用程式名稱。這個資料行會填入應用程式所傳送的值，而非程式的顯示名稱。
11 LoginName 用戶端的 SQL Server 登入名稱。
12 SPID 由 SQL Server 指派給用戶端關聯之處理序的伺服器處理序識別碼。
13 Duration 事件所經歷的時間 (以百萬分之一秒為單位)。這個資料行不是由 Hash Warning 事件來擴展。
14 StartTime 事件的開始時間 (如果可以取得的話)。
15 EndTime 事件結束的時間。啟動事件類別 (如 SQL:BatchStarting 或 SP:Starting) 不會擴展這個資料行。它也不是由 Hash Warning 事件來擴展。
16 Reads 伺服器代表事件執行的邏輯磁碟讀取數。這個資料行不是由 Lock:Released 事件來擴展。
17 Writes 伺服器代表事件執行的實體磁碟寫入數。
18 CPU 事件所用的 CPU 時間 (以毫秒為單位)。
19 Permissions 代表權限的點陣圖；供安全性稽核使用。
20 Severity 例外狀況的嚴重性層級。
21 EventSubClass 事件子類別的類型。所有事件類別的這個資料行都不會擴展。
22 ObjectID 系統指派給物件的識別碼。
23 Success 嘗試使用權限成功；用來進行稽核。1 = 成功 0 = 失敗
24 IndexID 事件所影響之物件的索引識別碼。若要確定物件的索引識別碼，請使用 sysindexes 系統資料表的 indid 資料行。
25 IntegerData 這是一個整數值，會隨著追蹤所擷取的事件類別而不同。
26 ServerName 正在追蹤之 SQL Server 執行個體的名稱，可以是 servername 或 servername\instancename。
27 EventClass 正在記錄之事件類別的類型。
28 ObjectType 物件類型，例如資料表、函數或預存程序。
29 NestLevel 這個預存程序正在執行的巢狀層級。請參閱＜@@NESTLEVEL (Transact-SQL)＞。
30 State 發生錯誤時的伺服器狀態。
31 Error 錯誤號碼。
32 Mode 取得的鎖定之鎖定模式。這個資料行不是由 Lock:Released 事件來擴展。
33 Handle 事件所參考之物件的控制代碼。
34 ObjectName 所存取之物件的名稱。
35 DatabaseName USE database 陳述式所指定的資料庫名稱。
36 FileName 修改的檔案名稱之邏輯名稱。
37 OwnerName 參考的物件之擁有者名稱。
38 RoleName 陳述式的目標資料庫或伺服器範圍的角色名稱。
39 TargetUserName 某動作的目標使用者名稱。
40 DBUserName 用戶端的 SQL Server 資料庫使用者名稱。
41 LoginSid 已登入之使用者的安全性識別碼 (SID)。
42 TargetLoginName 某動作的目標登入名稱。
43 TargetLoginSid 某動作之目標登入的 SID。
44 ColumnPermissions 資料行層級的權限狀態；供安全性稽核使用。
45 LinkedServerName 連結伺服器的名稱。
46 ProviderName OLE DB 提供者的名稱。
47 MethodName OLE DB 方法的名稱。
48 RowCounts 批次中的資料列數。
49 RequestID 包含陳述式之要求的識別碼。
50 XactSequence 用來描述目前交易的 Token。
51 EventSequence 這個事件的序號。
52 BigintData1 這是一個 bigint 值，會隨著追蹤所擷取的事件類別而不同。
53 BigintData2 這是一個 bigint 值，會隨著追蹤所擷取的事件類別而不同。
54 GUID 這是一個 GUID 值，會隨著追蹤所擷取的事件類別而不同。
55 IntegerData2 這是一個整數值，會隨著追蹤所擷取的事件類別而不同。
56 ObjectID2 相關物件或實體的識別碼 (如果可以取得的話)。
57 Type 這是一個整數值，會隨著追蹤所擷取的事件類別而不同。
58 OwnerID 擁有鎖定的物件類型。只適用於鎖定事件。
59 ParentName 物件所在結構描述的名稱。
60 IsSystem 指出事件是發生在系統處理序或使用者處理序。1 = 系統 0 = 使用者。
61 Offset 預存程序或批次內之陳述式的起始位移。
62 SourceDatabaseID 物件來源所在的資料庫識別碼。
63 SqlHandle 這是一個 64 位元雜湊，以特定查詢的文字或 SQL 物件的資料庫和物件識別碼為基礎。這個值可以傳給 sys.dm_exec_sql_text()，以擷取相關聯的 SQL 文字。
64 SessionLoginName 引發工作階段的使用者登入名稱。例如，如果您使用 Login1 連接到 SQL Server，卻以 Login2 執行陳述式，則 SessionLoginName 會顯示 Login1，而 LoginName 會顯示 Login2。此資料行會同時顯示 SQL Server 和 Windows 登入。
sp_trace_setfilter [ @traceid = ] trace_id, [ @columnid = ] column_id, [ @logical_operator = ] logical_operator,
[ @comparison_operator = ] comparison_operator, [ @value = ] value
--@comparison_operator
0 = (等於)
1 <> (不等於)
2 > (大於)
3 < (小於)
4 >= (大於或等於)
5 <= (小於或等於)
6 LIKE
7 NOT LIKE
