Deadlock happens while restoring SQL Database

Question

Hi everyone,

We are trying to restoring LiveDB backup to test server, Once it reaches 100% we are getting restoring deadlock error.
Please check the below details for further information. We also tried to restore through TSQL and aslo getting same deadlock issue while restoring.

deadlock-list
 deadlock victim=process105b9a9c38
  process-list
   process id=process105b9a9c38 taskpriority=0 logused=0 waitresource=KEY: 1:281474978545664 (70ed84e0d402) waittime=2538 ownerId=21413488443 transactionname=RESTORE dboptions Maintenance lasttranstarted=2016-07-07T12:18:29.030 XDES=0xbee0356a8 lockMode=X schedulerid=26 kpid=25164 status=suspended spid=216 sbid=0 ecid=0 priority=0 trancount=0 lastbatchstarted=2016-07-07T12:12:08.517 lastbatchcompleted=2016-07-07T12:12:08.513 lastattention=2016-07-07T12:12:08.513 clientapp=Microsoft SQL Server Management Studio hostname=TEST hostpid=18016 loginname=TESTLOGIN isolationlevel=read committed (2) xactid=0 currentdb=34 lockTimeout=10000 clientoption1=671088672 clientoption2=128056
    executionStack
     frame procname=unknown line=1 sqlhandle=0x01000100e2ce1c1320b93f1d0d00000000000000000000000000000000000000000000000000000000000000
unknown     
    inputbuf
RESTORE DATABASE [MYDB] FROM  DISK = N'D:\Testing\MYSQL\BACKUP\MYDB_backup_2016_07_05_050001_3051191.bak' WITH  FILE = 1,  MOVE N'MYDB' TO N'D:\Testing\MYSQL\DATA\MYDB.mdf',  MOVE N'MYDBLOGS' TO N'D:\Testing\MYSQL\DATA\MYDB_1.ndf',  MOVE N'MYDBLOB' TO N'D:\Testing\MYSQL\DATA\MYDB_2.ndf',  MOVE N'MYDBLOG' TO N'D:\Testing\MYSQL\DATA\MYDB_3.ndf',  MOVE N'MYDB_log' TO N'D:\Testing\MYSQL\LOG\MYDB_4.ldf',  NOUNLOAD,  STATS = 10    
   process id=processf1ead6cf8 taskpriority=0 logused=10000 waitresource=DATABASE: 34  waittime=329533 schedulerid=8 kpid=17664 status=suspended spid=174 sbid=0 ecid=0 priority=0 trancount=0 lastbatchstarted=2016-07-07T12:13:00.123 lastbatchcompleted=2016-07-07T12:13:00.123 lastattention=1900-01-01T00:00:00.123 clientapp=Microsoft SQL Server Management Studio - Transact-SQL IntelliSense hostname=NEWTON hostpid=5852 loginname=MYLOGIN isolationlevel=read committed (2) xactid=21412222304 currentdb=1 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
    executionStack
     frame procname=unknown line=1 stmtstart=56 sqlhandle=0x020000003610da10b1d5533be56e2f3e9316b5ebd03f0b050000000000000000000000000000000000000000
unknown     
     frame procname=unknown line=1 sqlhandle=0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
unknown     
    inputbuf
(@_msparam_0 nvarchar(4000))SELECT
ISNULL((case dmi.mirroring_redo_queue_type when N'UNLIMITED' then 0 else dmi.mirroring_redo_queue end),0) AS [MirroringRedoQueueMaxSize],
ISNULL(dmi.mirroring_connection_timeout,0) AS [MirroringTimeout],
ISNULL(dmi.mirroring_partner_name,'') AS [MirroringPartner],
ISNULL(dmi.mirroring_partner_instance,'') AS [MirroringPartnerInstance],
ISNULL(dmi.mirroring_role,0) AS [MirroringRole],
ISNULL(dmi.mirroring_safety_level + 1, 0) AS [MirroringSafetyLevel],
ISNULL(dmi.mirroring_state + 1, 0) AS [MirroringStatus],
ISNULL(dmi.mirroring_witness_name,'') AS [MirroringWitness],
ISNULL(dmi.mirroring_witness_state + 1, 0) AS [MirroringWitnessStatus],
CAST(case when dmi.mirroring_partner_name is null then 0 else 1 end AS bit) AS [IsMirroringEnabled],
ISNULL(dmi.mirroring_guid,'00000000-0000-0000-0000-0000000000000000') AS [MirroringID],
ISNULL(dmi.mirroring_role_sequence,0) AS [MirroringRoleSequence],
ISNULL(dmi.mirroring_safety_sequence,0) AS [MirroringSafetySequence],
ISNULL(dmi.mirror    
  resource-list
   keylock hobtid=281474978545664 dbid=1 objectname=unknown indexname=unknown id=lockd8a96dc00 mode=U associatedObjectId=281474978545664
    owner-list
     owner id=processf1ead6cf8 mode=S
    waiter-list
     waiter id=process105b9a9c38 mode=X requestType=convert
   databaselock subresource=FULL dbid=34 dbname=unknown id=lockba1f8cd80 mode=X
    owner-list
     owner id=process105b9a9c38 mode=S
     owner id=process105b9a9c38 mode=X
    waiter-list
     waiter id=processf1ead6cf8 mode=S requestType=wait

Please let me know hoe to troubleshoot this issue.

Thanks

---

DBA

Answer 1

Does the database participate in mirroring?

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 2

First let me know what is output of Select @@Vsersion. What you are facing is bug in SQL Server 2012 I know which was fixed in CU3 release. If above is true then you are restoring database on 2012 and this database is RTM version. Is this the case to be on safer side apply SQL Server 2012 SP3 this might have been missed in SP1.

You can try restoring database by truning off intellisense for workaround

I have answered similar question here

A restore can never cause deadlock unless you hit some bug. I can see deadlock graph and can confirm its intellisense.

Management Studio - Transact-SQL IntelliSense hostname=NEWTON hostpid=5852 loginname=MYLOGIN 
isolationlevel=read committed (2) xactid=21412222304 currentdb=1 lockTimeout=4294967295
 clientoption1=671088672 clientoption2=128056

---

Cheers,

Shashank

Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

My TechNet Wiki Articles
MVP

Answer 3

Hi Shanky/Uri Dimant,

Thanks for your response..We are not using database Mirroring for this server

Version Details:

Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) -Developer Edition

I will update it to latest service pack and see if it resolves and also let me try to restore the DB  by truning off intellisense.

Thanks again!

---

DBA