You are here:

MS SQL/Deadlock in sql server 2008

Advertisement


Question
HI Mohit,
I'm not from Database domain and new to 'AllExperts', I want help regarding deadlocks in sql server 2008.

From net search I got two syntax to clear the deadlock manually.

select * from sys.sysprocesses where dbid =db_id() and spid <> @@SPID and blocked <>0 and lastwaittype LIKE 'LCK%'

from above query result I copy each and every row value from Spid & blocked field and paste on following command to find which task is executing.

dbcc inputbuffer (94) ' 94 value is from either spid or blocked field

if I found text 'Ledger' in EventInfo, the same id use for Kill

Kill 94

I want these steps in one sql syntax or automated this process if deadlocks generated search for eventinfo like 'ledger' and immediately kill those session whose eventinfo contain 'ledger'.

Thanks

Answer
Hi

Please make use of "sys.dm_exec_sql_text" to get the text and filter accordingly. Once done, you can simply generate the KILL command and execute the same.

Example 1:
SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],
   SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
       ((CASE qs.statement_end_offset
         WHEN -1 THEN DATALENGTH(st.text)
        ELSE qs.statement_end_offset
        END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time/execution_count DESC;

Example 2:
DECLARE @sql NVARCHAR(MAX) = N''

SELECT @sql += N'SELECT * FROM ' + quotename(schema_name(schema_id)) + N'.' + quotename(name) + N';'
FROM sys.tables
where name like '%ledger%'

print (@sql)
--EXEC sp_executesql @sql

Thanks
Mohit

MS SQL

All Answers


Answers by Expert:


Ask Experts

Volunteer


Mohit Nayyar

Expertise

Microsoft SQL Server: T-SQL development (stored procedures, triggers, functions), Database optimization, performance tuning, high availability (Clustering, Log Shipping, Mirroring, Replication), scalability, SQL Server migration (Sybase/Oracle to SQL Server), SSIS/DTS, Data Warehousing (Kimball Methodology), ETL (SSIS), Cube (SSAS), Reporting Services (SSRS), database modeling, database administration, Security implementation and typically anything related to Microsoft SQL Server.

Experience

With over 10 years of experience in Database Administration, Development, Business Intelligence solutions and managing enterprise level database solutions based on Microsoft SQL Server.
Publications
IT Magazine: ASP, SQL

Publications
IT Magazine: ASP, SQL
SQLServerCentral.com

Education/Credentials
MCA (Masters)

Awards and Honors
Brainbench Certified: RDBMS
MCTS: DBA/Developer/BI for SQL Server 2005/SQL Server 2008
MCITP: DBA/Developer/BI for SQL Server 2005/SQL Server 2008
MCDBA: SQL Server 2000
MCP: SQL Server 6.5

©2016 About.com. All rights reserved.