Solved

Strange SQL Server Deadlock Problem

Posted on 2013-05-20
3
511 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 78

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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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.

734 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