womansetr.blogg.se

Finding SQL Server Deadlocks Using Trace Flag 1222Īlso note that if your system experiences a lot of deadlocks, this can really hammer your error log, and can become quite a lot of noise, drowning out other, important errors.However, you can run it only for the current running instance of the service (which won't require a restart, but which won't resume upon the next restart) using the following global trace flag command: DBCC TRACEON(1222, -1) I would set this as a startup trace flag (in which case you'll need to restart the service). However, the error log is textual, so you won't get nice deadlock graph pictures - you'll have to read the text of the deadlocks to figure it out. This will write deadlock information to the error log. In order to capture deadlock graphs without using a trace (you don't need profiler necessarily), you can enable trace flag 1222. JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_idĬROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST

JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id JOIN sys.objects O ON O.object_id = P.object_id JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id TST.is_user_transaction as IsUserTransaction, SELECT L.request_session_id AS SPID,ĭB_NAME(L.resource_database_id) AS DatabaseName, The only other way I could suggest is digging through the information by using EXEC SP_LOCK (Soon to be deprecated), EXEC SP_WHO2 or the sys.dm_tran_locks table. You can use a deadlock graph and gather the information you require from the log file.