locked
Deadlock RRS feed

  • Question

  • Hi Guys,

               In my environment on one of my server daily deadlock is occuring.. How i can know which query is causing the dealock and in which database. Its happening daily early morning..

    Saturday, July 30, 2011 6:04 AM

Answers

  • Hi Vinoth

    Which version of MSSQL Server user using

    You can setup Profiler and capture the Deadloack check the below link

    http://msdn.microsoft.com/en-us/library/ms188246.aspx


    Nag Pal MCTS/MCITP (SQL Server 2005/2008) :: Please Mark Answer/vote if it is helpful ::
    Saturday, July 30, 2011 6:18 AM
  • Hi,

    there are multiple ways to trouble shoot this situation.let me give you simple steps

    1.Trace flags

    you can use trace flags to record dead lock occurrence form database level (dont require any tools)

    1204 -will work on all versions

    DBCC Traceon(1204,-1)

    execute above command ,this will enable trace flag for all secessions ,until you restart sql service.we can stop this flag later.

    after enabling 1204 flag ,sql error logs will record dead lock information .this info is enough for you to trouble shoot

    Another flag 1222 --may not work in old versions ( <sql 2000)

    this flag will record more detailed information in xml format in sql error logs

    There are undocumented trace flags available but i am not mentioning here.

    you can monitor error logs and find out the tables getting effected at the time of dead lock occurance.

     

    another option is profiler

    using profiler you can trace out dead lock in graphical format.But running profiler in production is not recommended in business hours ,require lots of approvals

    if you still want to do that

    select lock>>dead lock graph event and run the trace

    hope you got the idea

     

     

     

     

     

     


    Sivaprasad.L Together We can Achieve
    Saturday, July 30, 2011 7:05 AM
  • Deadlock bible

    http://blogs.msdn.com/bartd/archive/2006/09/09/747119.aspx

    http://blogs.msdn.com/bartd/archive/2006/09/25/770928.aspx


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, July 31, 2011 5:30 AM

All replies

  • Hi Guys,

               In my environment on one of my server daily deadlock is occuring.. How i can know which query is causing the dealock and in which database. Its happening daily early morning..

    Saturday, July 30, 2011 6:12 AM
  • Hi Vinoth

    Which version of MSSQL Server user using

    You can setup Profiler and capture the Deadloack check the below link

    http://msdn.microsoft.com/en-us/library/ms188246.aspx


    Nag Pal MCTS/MCITP (SQL Server 2005/2008) :: Please Mark Answer/vote if it is helpful ::
    Saturday, July 30, 2011 6:18 AM
  • Hi,

    there are multiple ways to trouble shoot this situation.let me give you simple steps

    1.Trace flags

    you can use trace flags to record dead lock occurrence form database level (dont require any tools)

    1204 -will work on all versions

    DBCC Traceon(1204,-1)

    execute above command ,this will enable trace flag for all secessions ,until you restart sql service.we can stop this flag later.

    after enabling 1204 flag ,sql error logs will record dead lock information .this info is enough for you to trouble shoot

    Another flag 1222 --may not work in old versions ( <sql 2000)

    this flag will record more detailed information in xml format in sql error logs

    There are undocumented trace flags available but i am not mentioning here.

    you can monitor error logs and find out the tables getting effected at the time of dead lock occurance.

     

    another option is profiler

    using profiler you can trace out dead lock in graphical format.But running profiler in production is not recommended in business hours ,require lots of approvals

    if you still want to do that

    select lock>>dead lock graph event and run the trace

    hope you got the idea

     

     

     

     

     

     


    Sivaprasad.L Together We can Achieve
    Saturday, July 30, 2011 7:05 AM
  • Hi Vinod,

    I think its a duplicate post.

    I have already answered your question.

    No problem moderator will merge both threads


    Sivaprasad.L Together We can Achieve
    Saturday, July 30, 2011 7:36 AM
  • Deadlock bible

    http://blogs.msdn.com/bartd/archive/2006/09/09/747119.aspx

    http://blogs.msdn.com/bartd/archive/2006/09/25/770928.aspx


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, July 31, 2011 5:30 AM
  • Turn on SQL Server profiler to see graphical implemention 

     

    /****************************************************/

    /* Created by: SQL Server Profiler 2005             */

    /* Date: 21/07/2008  11:30:52         */

    /****************************************************/

     

     

    -- Create a Queue

    declare @rc int

    declare @TraceID int

    declare @maxfilesize bigint

    set @maxfilesize = 1000000 

     

    -- Please replace the text InsertFileNameHere, with an appropriate

    -- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension

    -- will be appended to the filename automatically. If you are writing from

    -- remote server to local drive, please use UNC path and make sure server has

    -- write access to your network share

     

    exec @rc = sp_trace_create @TraceID output, 0, N'n:\deadlocks', @maxfilesize, NULL 

    if (@rc != 0) goto error

     

    -- Client side File and Table cannot be scripted

     

    -- Set the events

    declare @on bit

    set @on = 1

    exec sp_trace_setevent @TraceID, 148, 11, @on

    exec sp_trace_setevent @TraceID, 148, 12, @on

    exec sp_trace_setevent @TraceID, 148, 14, @on

    exec sp_trace_setevent @TraceID, 148, 1, @on

    exec sp_trace_setevent @TraceID, 25, 15, @on

    exec sp_trace_setevent @TraceID, 25, 1, @on

    exec sp_trace_setevent @TraceID, 25, 9, @on

    exec sp_trace_setevent @TraceID, 25, 2, @on

    exec sp_trace_setevent @TraceID, 25, 10, @on

    exec sp_trace_setevent @TraceID, 25, 3, @on

    exec sp_trace_setevent @TraceID, 25, 11, @on

    exec sp_trace_setevent @TraceID, 25, 12, @on

    exec sp_trace_setevent @TraceID, 25, 13, @on

    exec sp_trace_setevent @TraceID, 25, 6, @on

    exec sp_trace_setevent @TraceID, 25, 14, @on

    exec sp_trace_setevent @TraceID, 59, 1, @on

    exec sp_trace_setevent @TraceID, 59, 2, @on

    exec sp_trace_setevent @TraceID, 59, 14, @on

    exec sp_trace_setevent @TraceID, 59, 3, @on

    exec sp_trace_setevent @TraceID, 59, 12, @on

    exec sp_trace_setevent @TraceID, 189, 15, @on

    exec sp_trace_setevent @TraceID, 189, 1, @on

    exec sp_trace_setevent @TraceID, 189, 9, @on

    exec sp_trace_setevent @TraceID, 189, 2, @on

    exec sp_trace_setevent @TraceID, 189, 10, @on

    exec sp_trace_setevent @TraceID, 189, 3, @on

    exec sp_trace_setevent @TraceID, 189, 11, @on

    exec sp_trace_setevent @TraceID, 189, 12, @on

    exec sp_trace_setevent @TraceID, 189, 13, @on

    exec sp_trace_setevent @TraceID, 189, 6, @on

    exec sp_trace_setevent @TraceID, 189, 14, @on

    exec sp_trace_setevent @TraceID, 10, 7, @on

    exec sp_trace_setevent @TraceID, 10, 15, @on

    exec sp_trace_setevent @TraceID, 10, 31, @on

    exec sp_trace_setevent @TraceID, 10, 8, @on

    exec sp_trace_setevent @TraceID, 10, 16, @on

    exec sp_trace_setevent @TraceID, 10, 48, @on

    exec sp_trace_setevent @TraceID, 10, 64, @on

    exec sp_trace_setevent @TraceID, 10, 1, @on

    exec sp_trace_setevent @TraceID, 10, 9, @on

    exec sp_trace_setevent @TraceID, 10, 17, @on

    exec sp_trace_setevent @TraceID, 10, 41, @on

    exec sp_trace_setevent @TraceID, 10, 49, @on

    exec sp_trace_setevent @TraceID, 10, 2, @on

    exec sp_trace_setevent @TraceID, 10, 10, @on

    exec sp_trace_setevent @TraceID, 10, 18, @on

    exec sp_trace_setevent @TraceID, 10, 26, @on

    exec sp_trace_setevent @TraceID, 10, 34, @on

    exec sp_trace_setevent @TraceID, 10, 50, @on

    exec sp_trace_setevent @TraceID, 10, 3, @on

    exec sp_trace_setevent @TraceID, 10, 11, @on

    exec sp_trace_setevent @TraceID, 10, 35, @on

    exec sp_trace_setevent @TraceID, 10, 51, @on

    exec sp_trace_setevent @TraceID, 10, 4, @on

    exec sp_trace_setevent @TraceID, 10, 12, @on

    exec sp_trace_setevent @TraceID, 10, 60, @on

    exec sp_trace_setevent @TraceID, 10, 13, @on

    exec sp_trace_setevent @TraceID, 10, 6, @on

    exec sp_trace_setevent @TraceID, 10, 14, @on

    exec sp_trace_setevent @TraceID, 12, 7, @on

    exec sp_trace_setevent @TraceID, 12, 15, @on

    exec sp_trace_setevent @TraceID, 12, 31, @on

    exec sp_trace_setevent @TraceID, 12, 8, @on

    exec sp_trace_setevent @TraceID, 12, 16, @on

    exec sp_trace_setevent @TraceID, 12, 48, @on

    exec sp_trace_setevent @TraceID, 12, 64, @on

    exec sp_trace_setevent @TraceID, 12, 1, @on

    exec sp_trace_setevent @TraceID, 12, 9, @on

    exec sp_trace_setevent @TraceID, 12, 17, @on

    exec sp_trace_setevent @TraceID, 12, 41, @on

    exec sp_trace_setevent @TraceID, 12, 49, @on

    exec sp_trace_setevent @TraceID, 12, 6, @on

    exec sp_trace_setevent @TraceID, 12, 10, @on

    exec sp_trace_setevent @TraceID, 12, 14, @on

    exec sp_trace_setevent @TraceID, 12, 18, @on

    exec sp_trace_setevent @TraceID, 12, 26, @on

    exec sp_trace_setevent @TraceID, 12, 50, @on

    exec sp_trace_setevent @TraceID, 12, 3, @on

    exec sp_trace_setevent @TraceID, 12, 11, @on

    exec sp_trace_setevent @TraceID, 12, 35, @on

    exec sp_trace_setevent @TraceID, 12, 51, @on

    exec sp_trace_setevent @TraceID, 12, 4, @on

    exec sp_trace_setevent @TraceID, 12, 12, @on

    exec sp_trace_setevent @TraceID, 12, 60, @on

    exec sp_trace_setevent @TraceID, 12, 13, @on

     

     

    -- Set the Filters

    declare @intfilter int

    declare @bigintfilter bigint

     

    exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - f8f14eae-71b0-4b2d-a6ac-ebea4e55f4a7'

    -- Set the trace status to start

    exec sp_trace_setstatus @TraceID, 1

     

    -- Set the trace status to stop

    --exec sp_trace_setstatus 4, 0

     

    -- Set the trace status to clear

    --exec sp_trace_setstatus 4, 2

     

     

    -- display trace id for future references

    select TraceID=@TraceID

    goto finish

     

    error: 

    select ErrorCode=@rc

     

    finish: 

    go

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, July 31, 2011 5:34 AM
  • Analyzing deadlocks with SQL Server Profiler
    Happy to help! Thanks. Regards and good Wishes, Deepak. In a revamp of my Blog!
    Sunday, July 31, 2011 7:33 AM