?
Solved

Strange SQL Server Deadlock Problem

Posted on 2013-05-20
3
Medium Priority
?
521 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 81

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 2000 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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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 part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

757 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