Solved

Strange SQL Server Deadlock Problem

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Export import database 4 42
SQL Pivot add row totals 2 10
MSSQL: Substring and Charindex error 7 12
MSDN Licensing query 5 0
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

911 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now