Restore job gets stuck

MottyKohnDevMottyKohnDev Posts: 2 New member

After a restore job I can't read from database until I kill that long-running job. I'll try to give as much info as I can but being that I'm stuck I'm not sure what is pertinent.

  • We are using RedGate's SQL Backup V9 to restore log-shipping incrementals, this runs on a Sql Server job hourly. Files come from a vendor, and that is how they send us data.
  • Database is always in Read-Only/Standby mode.
  • Command we run is:
SET @cmd = CONCAT('-SQL "RESTORE LOG [DB] FROM DISK = ''', @SourceFolder, '\LOG*.SQB'' WITH  MOVETO=''', @SourceFolder, '\temp\'', STANDBY = ''D:\MSSQL11.MSSQLSERVER\MSSQL\DATA\db.dat'', CONTINUE_AFTER_ERROR, DISCONNECT_EXISTING,PASSWORD = ''', @Password,'''')
EXECUTE [master]..sqlbackup @cmd, @errorcode OUT, @sqlerrorcode OUT;
  • Job just times out and moves on even though it is set to fail on error - but that is not my question now.
  • Database is still in Read-Only/Standby mode, but any read times out.
  • Running sp_whoisactive returns: 

<li><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: roboto, "helvetica neue", Arial, sans-serif;">| dd hh:mm:ss.mss |&nbsp; &nbsp;sql_text&nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp;login_name&nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; wait_info &nbsp; &nbsp;|</span><br></li><li>+-----------------+----------------------------------------------------------------------------------------+</li><li>| 00 07:55:45.250 | <?query --sqlbackup--?> | _Service&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | (28543510ms)PREEMPTIVE_OS_GETPROCADDRES|</li><li>| 00 07:55:40.973 | <?query --RESTORE LOG [DB]&nbsp; FROM VIRTUAL_DEVICE = 'SQLBACKUP_4E37B8AF-7BCF-492F-91BC-AA1697A7B473' WITH BUFFERCOUNT = 6, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576 , NORECOVERY, CONTINUE_AFTER_ERROR--?> | NT AUTHORITY\SYSTEM | (28538667ms)BACKUPIO&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|</li><li>+ ---------------------------------------------------------------------------------------------------------+</li><li><br></li>
In the RedGate log I see: Warning 445: Get exclusive local data store access failed - timeout.
In the Event viewer I see a bunch of: SQLVDI: Loc=TriggerAbort. Desc=invoked. ErrorCode=(0). Process=3024. Thread=6560. Server. Instance=MSSQLSERVER. VD=Global\SQLBACKUP_3F946102-4D96-43A1-9596-29358E1589AA_SQLVDIMemoryName_0.


Sign In or Register to comment.