Solved

Strange SQL Server Deadlock Problem

Posted on 2013-05-20
3
507 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need help how to find where my error is in UFD 6 30
Sql Server group by 10 30
Insert query into temp tables using Coldfusion 3 22
SQL Query assistance 16 27
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

831 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