Solved

Strange SQL Server Deadlock Problem

Posted on 2013-05-20
3
508 Views
Last Modified: 2013-10-08
Hi,
When I try and rebuild indexes I am reviving  a strange error.
Msg 1205, Level 13, State 51, Line 15
Transaction (Process ID 62) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

I don't see any details within SQL server error logs. I looked into locks and sessions when this error returned and there is nothing which will guide me.

SELECT  L.request_session_id AS SPID,
    DB_NAME(L.resource_database_id) AS DatabaseName,
    O.Name AS LockedObjectName,
    P.object_id AS LockedObjectId,
    L.resource_type AS LockedResource,
    L.request_mode AS LockType,
    ST.text AS SqlStatementText,        
    ES.login_name AS LoginName,
    ES.host_name AS HostName,
    TST.is_user_transaction as IsUserTransaction,
    AT.name as TransactionName,
    CN.auth_scheme as AuthenticationMethod
FROM    sys.dm_tran_locks L
    JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
    JOIN sys.objects O ON O.object_id = P.object_id
    JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
    JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
    JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
    JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
    CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE   resource_database_id = db_id()
ORDER BY L.request_session_id

A rebuild SQL script have been scheduled on SQL 2008R2 for last many months ,I started receiving error recently and nothing has been changed. The DB have around 5K small tables. Every night this script rebuilds around 200 tables in less than 30 min.

Thanks




Thanks
0
Comment
Question by:crazywolf2010
3 Comments
 
LVL 77

Expert Comment

by:arnold
ID: 39183256
Not sure whether it is a good idea to rebuild indecies on a nightly basis.
http://blogs.msdn.com/b/joaol/archive/2008/01/28/script-to-rebuild-and-reorganize-database-indexes-sql-server-2005.aspx

Includes logic that uses fragmentation to choose between reorganize and rebuild.
0
 
LVL 35

Accepted Solution

by:
David Todd earned 500 total points
ID: 39183283
Hi,

Ola Hallengren's free script and Michelle Ufford have scripts that can do this for you. As in, why reinvent the wheel?

http://ola.hallengren.com/
http://sqlfool.com/

I have Ola's script in several servers, and the solution is very stable. No deadlocks or anything.

HTH
  David
0
 

Author Comment

by:crazywolf2010
ID: 39183693
Hi dtodd,
I have used http://ola.hallengren.com/ and even his procedure used for index result have exactly same error. Can you give me exact links than pasting home page URL?

Thanks
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question