Monday 18 February 2013

SQL Server 2012: Restore Fails With Deadlock

We had an interesting situation at a client where we could not restore a SQL Server 2008 R2 backup onto SQL Server 2012. The restore would fail at 100% with a deadlock.


Here is what we saw:

10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
100 percent processed.
Processed 2873048 pages for database 'TestRestore', file 'SourceDb' on file 1.
Processed 3 pages for database 'TestRestore', file 'SourceDb_log' on file 1.
Msg 1205, Level 13, State 51, Line 1
Transaction (Process ID 183) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.


We then ran a trace to get a deadlock graph. Frustratingly this was what our graph showed:

Failed to initializa deadlock control.
    There is an error in the XML document (1,2).
        <deadlock-list xmlns=''> was not expected.


However the TextData portion within profiler showed something interesting. Here is a snippet.

clientapp="Microsoft SQL Server Management Studio - Transact-SQL IntelliSense"

So IntelliSense is blocking the restore!

With some help from www.sqlservercentral.com we found it was indeed a bug.

Here is the knowledgebase article: http://support.microsoft.com/kb/2725950

And if you are also battling with this bug, here is the cumulative update with the fix: http://support.microsoft.com/kb/2723749


1 comment:

  1. I can see that you are are genuinely passionate about this! I am trying to build my own website and youve helped me with some great information.
    http://www.sqlservermasters.com/

    ReplyDelete