I got a request to check out some errors on a 2008 sp3 server (10.0.5500.0 - Enterprise Edition (64-bit)) and see if I could find deadlocks that were happening earlier.

Traceflag 1222 wasn't enabled and the server owners did not want to enable it either. I used extended events and pulled out some info using the following query:

    SELECT  CAST(event_data.value('(event/data/value)[1]',
                               'varchar(max)') AS XML) AS DeadlockGraph
FROM    ( SELECT    XEvent.query('.') AS event_data
          FROM      (    -- Cast the target_data to XML 
                      SELECT    CAST(target_data AS XML) AS TargetData
                      FROM      sys.dm_xe_session_targets st
                                JOIN sys.dm_xe_sessions s
                                 ON s.address = st.event_session_address
                      WHERE     name = 'system_health'
                                AND target_name = 'ring_buffer'
                    ) AS Data -- Split out the Event Nodes 
                    CROSS APPLY TargetData.nodes('RingBufferTarget/
                    AS XEventData ( XEvent )
        ) AS tab ( event_data )

I received a lot of info back that looked like this:

    <victimProcess id="process14846ccbc8" />
    <process id="process14846ccbc8" taskpriority="0" logused="0" waitresource="KEY: 25:72057594099728384 (0800dfc12261)" waittime="2842" ownerId="7035766745" transactionname="UPDATE" lasttranstarted="2012-11-20T07:50:25.023" XDES="0x14d9ade3b0" lockMode="U" schedulerid="8" kpid="17268" status="background" spid="19" sbid="0" ecid="0" priority="0" trancount="2">
        <frame procname="" line="53" stmtstart="2674" stmtend="2980" sqlhandle="0x030019002cf5fc35c2449200d2a000000100000000000000" />
      <inputbuf />
    <process id="process3d9bdc8" taskpriority="0" logused="224" waitresource="KEY: 25:72057594099793920 (2f00faaf5d8e)" waittime="2315" ownerId="7035765940" transactionname="user_transaction" lasttranstarted="2012-11-20T07:50:24.913" XDES="0xdad541970" lockMode="U" schedulerid="6" kpid="15448" status="background" spid="23" sbid="0" ecid="0" priority="0" trancount="2">
        <frame procname="" line="549" stmtstart="32736" stmtend="33274" sqlhandle="0x03001900b9667f2bc41ade003ea000000100000000000000" />
        <frame procname="" line="549" stmtstart="34038" stmtend="35374" sqlhandle="0x03001900f28a732c051bde003ea000000100000000000000" />
        <frame procname="" line="81" stmtstart="4284" stmtend="4392" sqlhandle="0x030019002cf5fc35c2449200d2a000000100000000000000" />
      <inputbuf />
    <keylock hobtid="72057594099728384" dbid="25" objectname="" indexname="" id="lock6b6a4ba00" mode="X" associatedObjectId="72057594099728384">
        <owner id="process3d9bdc8" mode="X" />
        <waiter id="process14846ccbc8" mode="U" requestType="wait" />
    <keylock hobtid="72057594099793920" dbid="25" objectname="" indexname="" id="lock74edcf180" mode="U" associatedObjectId="72057594099793920">
        <owner id="process14846ccbc8" mode="U" />
        <waiter id="process3d9bdc8" mode="U" requestType="wait" />

How can I found out what the deadlocks are if objectname="" indexname="" ?

2 Answers 2


I can't answer as to why there is information missing, but I can point out how to extract it.

First, move to that database:


Take that result and:

USE <db_name>;

Now find out what these hobt_ids represent:

FROM sys.partitions 
WHERE hobt_id IN (72057594099728384, 72057594099793920);

You can also find the batches involved (if they're still around) using:

SELECT * FROM sys.dm_exec_sql_text(0x03001900b9667f2bc41ade003ea000000100000000000000);
SELECT * FROM sys.dm_exec_sql_text(0x03001900f28a732c051bde003ea000000100000000000000);
SELECT * FROM sys.dm_exec_sql_text(0x030019002cf5fc35c2449200d2a000000100000000000000);

The offsets are there in the ExecutionStack so you can drill deeper into that output if you like (many examples of using offsets online).

EDIT Mr. Sandwiches pointed out the following Connect item (no longer available), where Microsoft has stated that this bug is fixed in SQL Server 2012. It won't be fixed in 2008/R2.



As Aaron has already said, this is due to a bug in SQL Server 2008 / 2008 R2. The bug has been marked as fixed in a later version. It will not be fixed in SQL 2008 / 2008 R2.

I have a PowerShell script that I wrote to pull deadlocks from the event buffer, add the missing information if possible, and write the deadlock graphs out as .xdl files. The .xdl files can be opened with a text editor or in SQL Server Management Studio 2012 and above. The code below works in PowerShell 3.0. If you save the file as a .psm1 it can be imported using Import-Module and provides the commandlet Get-SqlDeadlockHistory. This script also works for later SQL Server versions.

I have also posted this as a workaround on the original bug along with a version that will work in PowerShell 2.0. The version that I posted here contains some improvements. Unfortunately, I can't edit the workaround I posted on the original bug to match.

# Begin hexadecimal conversion function
function ConvertFrom-Hexadecimal([string] $hexString)
    [byte[]] $data = @()

    if ([string]::IsNullOrEmpty($hexString) -eq $true -or $hexString.Length % 2 -ne 0)
        throw New-Object FormatException("Hexadecimal string must not be empty and must contain an even number of digits to be valid.");

    $hexString = $hexString.ToUpperInvariant()
    $data = New-Object byte[] -ArgumentList ($hexString.Length / 2)

    for ([int] $index = 0; $index -lt $hexString.Length; $index += 2)
        [int] $highDigitValue = if ($hexString[$index] -le ([char] '9')) { $hexString[$index] - ([char] '0') } else { $hexString[$index] - ([char] 'A') + 10 }
        [int] $lowDigitValue = if ($hexString[$index + 1] -le ([char] '9')) { $hexString[$index + 1] - ([char] '0') } else { $hexString[$index + 1] - ([char] 'A') + 10 }

        if ($highDigitValue -lt 0 -or $lowDigitValue -lt 0 -or $highDigitValue -gt 15 -or $lowDigitValue -gt 15)
            throw New-Object FormatException("An invalid digit was encountered. Valid hexadecimal digits are 0-9 and A-F.")
            [byte] $value = [byte](($highDigitValue -shl 4) -bor ($lowDigitValue -band 0x0F))
            $data[$index / 2] = $value;

    return ,$data
# End hexadecimal conversion function

function Get-SqlDeadlockHistory
    Gets the deadlock history from the system health extended events on the specified SQL Server instance.
    Gets the deadlock history from the system health extended events on the specified SQL Server instance and writes them to the specified folder or to a Deadlocks directory in the current user's documents folder if no folder is specified.
    When the process completes, SQL server startup date, most recent deadlock date, total number of deadlocks, and number of deadlock graph files written will be displayed.
    Get-SqlDeadlockHistory "SqlServerName\Instance"
    Get-SqlDeadlockHistory "SqlServerName"
    .PARAMETER $SqlServerInstance
    The SQL Server name and instance that Get-SqlDeadlockHistory will connect to.
    .PARAMETER $UserName
    If specified a SQL autenticated account will be used to connect to the SQL Server instance.
    .PARAMETER $Password
    If a UserName has been specified, uses the given password for the SQL authenticated account; otherwise, the user will be prompted for a password. If UserName has not been specified, this parameter is ignored.
    .PARAMETER $OutputDirectory
    If specified the deadlock files will be written to the given directory; otherwise, deadlock files are written to Deadlocks\<SqlServerInstance> under the current user's My Documents folder.
    .PARAMETER $Overwrite
    If Overwrite is present deadlock files will overwrite any pre-existing deadlock files that have the same name; otherwise, deadlocks corresponding to pre-exsiting files will be skipped.
    [CmdletBinding(SupportsShouldProcess = $false)]
        [Parameter(Mandatory = $true, ValueFromPipeLine = $true)]
        [string] $SqlServerInstance,
        [Parameter(Mandatory = $false, ValueFromPipeLine = $false)]
        [string] $UserName = $null,
        [Parameter(Mandatory = $false, ValueFromPipeLine = $false)]
        [string] $Password = $null,
        [Parameter(Mandatory = $false, ValueFromPipeLine = $false)]
        [string] $OutputDirectory = $null,
        [switch] $Overwrite

        if (([string]::IsNullOrWhiteSpace($OutputDirectory) -eq $true) -or ((Test-Path $OutputDirectory -PathType Container) -eq $false))
            $OutputDirectory = Join-Path $env:USERPROFILE "Documents\Deadlocks\$($SqlServerInstance.Replace(":", "_"))"

        Write-Host "Deadlock events will be retrieved from $($SqlServerInstance) and written to $($OutputDirectory)."

        [System.Data.SqlClient.SqlConnectionStringBuilder] $connectionStringBuilder = New-Object "System.Data.SqlClient.SqlConnectionStringBuilder"
        $connectionStringBuilder.PSBase.DataSource = $SqlServerInstance
        $connectionStringBuilder.PSBase.InitialCatalog = "master"

        if ($PSBoundParameters.ContainsKey("UserName") -eq $true)
            $connectionStringBuilder.PSBase.UserID = $UserName

            if ($PSBoundParameters.ContainsKey("Password") -eq $true)
                $connectionStringBuilder.PSBase.Password = $Password
                $connectionStringBuilder.PSBase.Password = (Read-Host "Password: ")
                $connectionStringBuilder.PSBase.IntegratedSecurity = $true

        $connectionStringBuilder.PSBase.WorkstationID = $env:COMPUTERNAME
        $connectionStringBuilder.PSBase.ApplicationName = $MyInvocation.MyCommand.Name

        if ((Test-Path $outputDirectory -PathType Container) -eq $false)
            New-Item $outputDirectory -ItemType Container

        [string] $startTimeQuery = "SELECT sqlserver_start_time FROM sys.dm_os_sys_info"

        [string] $deadlockQuery = "SELECT        ringBuffer.event.value(N'./@timestamp', N'datetimeoffset') AS timestamp
            , CONVERT(xml, CASE WHEN CONVERT(varchar(3), SERVERPROPERTY(N'ProductVersion')) = '10.' THEN ringBuffer.event.value(N'(./data/value/node())[1]', N'nvarchar(MAX)') ELSE ringBuffer.event.query(N'(./data/value/node())[1]') END) AS deadlockGraph
FROM            (
                    SELECT        CONVERT(xml, sys.dm_xe_session_targets.target_data) AS target_data
                    FROM            sys.dm_xe_session_targets INNER JOIN
                                              sys.dm_xe_sessions ON sys.dm_xe_session_targets.event_session_address = sys.dm_xe_sessions.address
                    WHERE        (sys.dm_xe_sessions.name = 'system_health')
                ) AS sessionTarget
                CROSS APPLY sessionTarget.target_data.nodes(N'/RingBufferTarget/event[@name=`"xml_deadlock_report`"]') AS ringBuffer(event)"

        [string] $partitionQuery = "CREATE TABLE #allPartitions
    partition_id bigint NOT NULL,
    database_id int NOT NULL,
    hobt_id bigint NOT NULL,
    object_id int NOT NULL,
    index_id int NOT NULL,
    database_name sysname NOT NULL,
    object_schema_name sysname NOT NULL,
    object_name sysname NOT NULL,
    index_name sysname NULL
    PK_#allPartitions PRIMARY KEY CLUSTERED 

DECLARE @partitionQuery nvarchar(2000)
DECLARE @dbName sysname

SET @partitionQuery = 'USE [?];
INSERT INTO [#allPartitions]
                         (partition_id, database_id, hobt_id, object_id, index_id, database_name, object_schema_name, object_name, index_name)
SELECT        sys.partitions.partition_id, DB_ID(N''?'') AS database_id, sys.partitions.hobt_id, sys.partitions.object_id, sys.partitions.index_id, ''?'' AS database_name, 
                         OBJECT_SCHEMA_NAME(sys.partitions.object_id) AS object_schema_name, OBJECT_NAME(sys.partitions.object_id) AS object_name, sys.indexes.name AS index_name
FROM            sys.partitions LEFT OUTER JOIN
                         sys.indexes ON sys.indexes.object_id = sys.partitions.object_id AND sys.indexes.index_id = sys.partitions.index_id'

EXEC sp_MSforeachdb @partitionQuery

SELECT        partition_id, database_id, hobt_id, object_id, index_id, database_name, object_schema_name, object_name, index_name
FROM            #allPartitions

DROP TABLE #allPartitions

        [string] $objectQuery = "
CREATE TABLE #allObjects
    database_id int NOT NULL,
    object_id int NOT NULL,
    database_name sysname NOT NULL,
    object_schema_name sysname NOT NULL,
    object_name sysname NOT NULL

DECLARE @objectQuery nvarchar(2000)
DECLARE @dbName sysname

SET @objectQuery = 'USE [?];
INSERT INTO [#allObjects]
                         (database_id, object_id, database_name, object_schema_name, object_name)
SELECT        DB_ID(N''?'') AS database_id, object_id, ''?'' AS database_name, SCHEMA_NAME(schema_id) AS object_schema_name,
                         name AS object_name
FROM            sys.objects'

EXEC sp_MSforeachdb @objectQuery

SELECT        database_id, object_id, database_name, object_schema_name, object_name
FROM            #allObjects

DROP TABLE #allObjects

        $sqlTextQuery = "SELECT dbid, objectid, text FROM sys.dm_exec_sql_text(@sqlHandle)"

        [System.Data.SqlClient.SqlConnection] $connection = New-Object "System.Data.SqlClient.SqlConnection" -ArgumentList @($connectionStringBuilder.ConnectionString)

        [System.Data.SqlClient.SqlCommand] $startTimeQueryCommand = $connection.CreateCommand()
        $startTimeQueryCommand.CommandText = $startTimeQuery
        $startTimeQueryCommand.CommandType = [System.Data.CommandType]::Text

        [System.Data.SqlClient.SqlCommand] $deadlockQueryCommand = $connection.CreateCommand()
        $deadlockQueryCommand.CommandText = $deadlockQuery
        $deadlockQueryCommand.CommandType = [System.Data.CommandType]::Text

        [System.Data.SqlClient.SqlDataAdapter] $deadlockQueryDataAdapter = New-Object "System.Data.SqlClient.SqlDataAdapter" -ArgumentList $deadlockQueryCommand
        [System.Data.DataTable] $deadlockTable = New-Object "System.Data.DataTable"

        [System.Data.SqlClient.SqlCommand] $partitionQueryCommand = $connection.CreateCommand()
        $partitionQueryCommand.CommandText = $partitionQuery
        $partitionQueryCommand.CommandType = [System.Data.CommandType]::Text

        [System.Data.SqlClient.SqlDataAdapter] $partitionQueryDataAdapter = New-Object "System.Data.SqlClient.SqlDataAdapter" -ArgumentList $partitionQueryCommand
        [System.Data.DataTable] $partitionTable = New-Object "System.Data.DataTable"

        [System.Data.SqlClient.SqlCommand] $objectQueryCommand = $connection.CreateCommand()
        $objectQueryCommand.CommandText = $objectQuery
        $objectQueryCommand.CommandType = [System.Data.CommandType]::Text

        [System.Data.SqlClient.SqlDataAdapter] $objectQueryDataAdapter = New-Object "System.Data.SqlClient.SqlDataAdapter" -ArgumentList $objectQueryCommand
        [System.Data.DataTable] $objectTable = New-Object "System.Data.DataTable"

        [System.Data.SqlClient.SqlCommand] $sqlTextQueryCommand = $connection.CreateCommand()
        $sqlTextQueryCommand.CommandText = $sqlTextQuery
        $sqlTextQueryCommand.CommandType = [System.Data.CommandType]::Text
        $sqlTextSqlHandleParam = $sqlTextQueryCommand.Parameters.Add("@sqlHandle", [System.Data.SqlDbType]::VarBinary, 64)

        Write-Verbose "Connecting to $($SqlServerInstance)."

        Write-Verbose "Getting SQL Server start time."
        [DateTime] $startTime = $startTimeQueryCommand.ExecuteScalar()

        Write-Verbose "Getting SQL deadlock events from the ring buffer."
        [int] $deadlockRowsReturned = $deadlockQueryDataAdapter.Fill($deadlockTable)

        Write-Verbose "Getting partiton information."
        [int] $partitionRowsReturned = $partitionQueryDataAdapter.Fill($partitionTable)

        Write-Verbose "Getting object information."
        [int] $objectRowsReturned = $objectQueryDataAdapter.Fill($objectTable)


        $deadlocksWritten = 0
        [DateTimeOffset] $mostRecentDeadlock = [DateTimeOffset]::MinValue

        [int] $deadlockCount = $deadlockTable.Rows.Count
        [int] $deadlockItem = 0;
        Write-Verbose "Processing deadlock $($deadlockCount) graphs."

        ForEach ($deadlockRow in $deadlockTable.Rows)
            Write-Progress -Id 1 -Activity "Processing Deadlock Graphs" -PercentComplete ($deadlockItem / $deadlockCount * 100)

            $deadlockLocalTime = $deadlockRow.timestamp.ToLocalTime()

            if ($deadlockLocalTime -gt $mostRecentDeadlock)
                $mostRecentDeadlock = $deadlockLocalTime

            [string] $xdlFileName = "Deadlock_$($deadlockLocalTime.ToString("yyyyMMdd_HHmmss_fff")).xdl"
            [string] $xdlFilePath = Join-Path $OutputDirectory $xdlFileName

            Write-Verbose "Using deadlock graph file $($xdlFileName)."

            if (((Test-Path $xdlFilePath) -eq $false) -or ($Overwrite -eq $true))
                [XML] $deadlockGraphDocument = $deadlockRow.deadlockGraph

                $missingObjectNameNodes = $deadlockGraphDocument.deadlock.'resource-list'.SelectNodes("child::node()[@objectname='']")
                Write-Verbose "Found $($missingObjectNameNodes.Count) resource node(s) with no object names."

                ForEach ($missingObjectNameNode in $missingObjectNameNodes)
                    $objectName = $null
                    $indexName = $null

                    if ($missingObjectNameNode.hobtid -ne $null)
                        Write-Verbose "Looking up Heap or B-Tree ID $($missingObjectNameNode.hobtid)."
                        $objectRow = $partitionTable.Where({ ($_.hobt_id -eq $missingObjectNameNode.hobtid) -and ($_.database_id -eq $missingObjectNameNode.dbid) })
                        $objectName = "$($objectRow.database_name).$($objectRow.object_schema_name).$($objectRow.object_name)"
                        $indexName = $objectRow.index_name
                    elseif ($missingObjectNameNode.associatedObjectId -ne $null)
                        Write-Verbose "Looking up Partition ID $($missingObjectNameNode.associatedObjectId)."
                        $objectRow = $partitionTable.Where({ ($_.partition_id -eq $missingObjectNameNode.associatedObjectId) -and ($_.database_id -eq $missingObjectNameNode.dbid) })
                        $objectName = "$($objectRow.database_name).$($objectRow.object_schema_name).$($objectRow.object_name)"

                    if ($objectName -ne $null)
                        Write-Verbose "Found object $($objectName)."
                        $missingObjectNameNode.objectname = $objectName

                    if ($indexName -ne $null)
                        Write-Verbose "Found index $($indexName)."
                        $missingObjectNameNode.indexname = $indexName

                $executionStackFrames = $deadlockGraphDocument.deadlock.'process-list'.SelectNodes('//process/executionStack/frame[@procname="" or text() = "" or normalize-space(text())="unknown" or contains(text(), "*password----")]')
                Write-Verbose "Found $($executionStackFrames.Count) execution stack frame(s) with no proc name or no statement text."

                ForEach ($executionStackFrame in $executionStackFrames)
                    if ([string]::IsNullOrWhiteSpace($executionStackFrame.sqlhandle) -eq $false)
                        Write-Verbose "Looking up sql handle $($executionStackFrame.sqlhandle)."

                        $sqlTextSqlHandleParam.Value = ConvertFrom-Hexadecimal $executionStackFrame.sqlhandle.Replace("0x", "")
                        $sqlTextReader = $sqlTextQueryCommand.ExecuteReader()

                        if ($sqlTextReader.HasRows -eq $true)
                            $currentDatabaseID = $executionStackFrame.ParentNode.ParentNode.currentdb

                            $rowAvailable = $sqlTextReader.Read()
                            $dbid = $sqlTextReader["dbid"]
                            $objectID = $sqlTextReader["objectid"]
                            $queryText = $sqlTextReader["text"]

                            if ([string]::IsNullOrWhitespace($executionStackFrame.procname) -eq $true -and $objectID -ne $null -and $objectID -ne [System.DBNull]::Value)
                                if ($dbid -eq $null -or $dbid -eq [System.DBNull]::Value)
                                    $dbid = $currentDatabaseID

                                $procRow = $objectTable.Where({ ($_.database_id -eq $currentDatabaseID) -and ($_.object_id -eq $objectID) })
                                $procName = "$($procRow.database_name).$($procRow.object_schema_name).$($procRow.object_name)"
                                Write-Verbose "Found proc $($procName)."

                                $executionStackFrame.procname = $procName

                            $isQueryTextMissing = [string]::IsNullOrWhitespace($executionStackFrame.InnerText) -eq $true -or $executionStackFrame.InnerText.Trim() -eq "unknown" -or $executionStackFrame.InnerText -match "\*password-+\s*$"
                            $hasQueryLookupResult = $queryText -ne $null -and $queryText -ne [DBNull]::Value

                            if ($isQueryTextMissing -eq $true -and $hasQueryLookupResult -eq $true)
                                $statementStart = 0
                                if ([string]::IsNullOrWhiteSpace($executionStackFrame.stmtstart) -eq $false)
                                    $statementStart = [int]::Parse($executionStackFrame.stmtstart) / 2

                                $statementEnd = $queryText.Length - 1
                                if ([string]::IsNullOrWhiteSpace($executionStackFrame.stmtend) -eq $false -and $executionStackFrame.stmtend -ne '-1')
                                     $statementEnd = [int]::Parse($executionStackFrame.stmtend) / 2

                                $statementLength = ($statementEnd - $statementStart) + 1
                                $statementText = $queryText.Substring($statementStart, $statementLength)
                                Write-Verbose "Found statement text."

                                $executionStackFrame.InnerText = $statementText


                Write-Verbose "Writing deadlock graph to file."
                [System.Xml.XmlWriterSettings] $xdlWriterSettings = New-Object "System.Xml.XmlWriterSettings"
                $xdlWriterSettings.OmitXmlDeclaration = $false
                $xdlWriterSettings.Indent = $true
                $xdlWriterSettings.NewLineHandling = [System.Xml.NewLineHandling]::Replace
                $xdlWriterSettings.CloseOutput = $true

                [System.Xml.XmlWriter] $xdlWriter = [System.Xml.XmlWriter]::Create($xdlFilePath, $xdlWriterSettings)



                Write-Verbose "Pre-existing deadlock graph file will not be overwritten."
        Write-Progress -Id 1 -Activity "Processing Deadlock Graphs" -Completed


        Write-Host "$($SqlServerInstance) up since $($startTime)."

        if ($mostRecentDeadlock -gt [DateTimeOffset]::MinValue)
            Write-Host "Most recent deadlock occurred at $($mostRecentDeadlock)."

        Write-Host "$($deadlocksWritten) of $($deadlockRowsReturned) deadlock graphs written to $($outputDirectory)."
  • This is pretty awesome. I'm definitely going to be using this. Thank you. Commented Feb 11, 2016 at 15:31
  • I saved this script as a .ps1 and nothing happens when I run it. Is there something that you have to do with powershell scripts to have it run properly? I was running the Powershell as an administrator.
    – John Odom
    Commented Apr 16, 2020 at 17:24
  • 1
    @JohnOdom The script by itself does nothing but create the Get-SqlDeadlockHistory commandlet. You still have to run the Get-SqlDeadlockHistory commandlet and give it a server to target. Try Get-Help Get-SqlDeadlockHistory -ShowWindow after running the scrip to see how it's used. You can also save this as a .psm1 in the PowerShell Modules folder and it will auto-load whenever the commandlet is referenced.
    – JamieSee
    Commented May 7, 2020 at 18:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.