Standby Database restore appears stuck on rollback file

• Brandie Tarvin

  SSC Guru

  Points: 172995

  More actions

  May 5, 2016 at 9:04 am

  #326900

  We have a setup that works fine in our Test environment, but seems to be stuck in our QA environment.

  Two servers A and B. A has the OLTP database, B has the reporting database. Every day, a job runs on Server C which backs up Server A's database and restores it in Standby mode on Server B so that the reporting db can read from the OLTP database without affecting the OLTP database. Like I said, works fine in Test. But when we moved it to QA (which uses the same drives and paths as Test), the restore got hung. Running the below code indicates that the restore is completed (100%) but nothing shows up in the job log text file and the job thinks it's still running.

  SELECTsession_id AS spid

  ,r.command AS command_type

  ,a.[text] AS command_text

  ,r.start_time

  ,r.percent_complete

  ,CAST( ( estimated_completion_time / 1000 ) / 60. AS DECIMAL(16,2) ) AS estimated_minutes

  ,DATEADD ( SECOND, estimated_completion_time / 1000, CURRENT_TIMESTAMP ) AS estimated_completion_time

  ,CURRENT_TIMESTAMP AS [current_time]

  FROMsys.dm_exec_requests r

  CROSS APPLY sys.dm_exec_sql_text( r.[sql_handle] ) a

  WHEREr.command IN ( 'BACKUP DATABASE', 'RESTORE DATABASE', 'BACKUP LOG', 'RESTORE LOG' )

  ORDER BY r.percent_complete DESC

  Time till completion ALWAYS shows current time once it reaches 100%.

  So now I'm running the restore manually via SSMS and the GUI window shows the database has reached 100% of the restore, but also seems hung. I don't even see the Rollback file being created where it should be. I've also run the actual T-SQL of the restore in SSMS, and same thing. It hangs.

  I've verified all the accounts have access to the shares they should be able to reach, and am now at all loss.

  Any thoughts on how I can determine what SQL seems to think it's doing? When I run code to find the text of the SPID, there's nothing there. I get no results at this point of the restore.

  DECLARE @Handle BINARY(20);

  SELECT @Handle = sql_handle FROM SysProcesses WHERE SPID = 64 ; --Change this SPID as appropriate

  SELECT SUBSTRING(text,1,50) AS ProcName, * FROM ::fn_get_sql(@handle);

  Should I be running something else to find out what the SPID is doing?

  Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.