locked
Deadlock happens while restoring SQL Database RRS feed

  • 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

    Thursday, July 7, 2016 7:26 AM

Answers

  • 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


    Thursday, July 7, 2016 7:57 AM

All replies

  • 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

    Thursday, July 7, 2016 7:52 AM
    Answerer
  • 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


    Thursday, July 7, 2016 7:57 AM
  • 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

    Thursday, July 7, 2016 8:53 AM