Discovered that our BETA database which we restore each Saturday morning was unavailable.
Sought to find out why it was stuck in recovery mode
Here is the trail end of our log file
80 percent processed. 81 percent processed. 82 percent processed. 83 percent processed. 84 percent processed. 85 percent processed. 86 percent processed. 87 percent processed. 88 percent processed. 89 percent processed. 90 percent processed. 91 percent processed. Sqlcmd: Warning: The last operation was terminated because the user pressed CTRL+C. D:\Scripts\RestoreDB>echo ERRORLEVEL is 1 ERRORLEVEL is 1
- Set: Stop the task if it runs longer than: 3 days
- Reality: yes, our restore takes time. It started on Saturday @ 12:15 AM and aborted at 3:11 AM
- And, so at about 3 hours
- We should still have about 2 days and 21 hours of grace
Sqlcmd – Payload
D:\Scripts\RestoreDB>sqlcmd -S (local) -d master -e -E -i D:\Scripts\RestoreDB\restoreDBCSTrafficSchoolBetaSingleFile.sql
- We are not passing in the -t parameter
- BTW, -t sets the query timeout
Checked the error logs
Nothing in the current log.
2016-07-30 03:10:01.29 spid62 Setting database option SINGLE_USER to ON for database nettraffic_db. 2016-07-30 03:10:01.29 spid62 Setting database option SINGLE_USER to ON for database nettraffic_db. 2016-07-30 03:10:01.64 spid62 Starting up database 'nettraffic_db'. 2016-07-30 03:10:01.65 spid62 The database 'nettraffic_db' is marked RESTORING and is in a state that does not allow recovery to be run. 2016-07-30 03:10:01.83 spid62 Recovery is writing a checkpoint in database 'nettraffic_db' (14). This is an informational message only. No user action is required. 2016-07-30 03:10:02.09 spid62 Starting up database 'nettraffic_db'. 2016-07-30 03:10:02.13 spid62 CHECKDB for database 'nettraffic_db' finished without errors on 2011-08-03 00:53:32.183 (local time). This is an informational message only; no user action is required. 2016-07-30 03:10:02.24 spid62 Setting database option MULTI_USER to ON for database nettraffic_db. 2016-07-30 03:10:02.24 spid62 Setting database option MULTI_USER to ON for database nettraffic_db. 2016-07-30 03:11:02.27 Server SQL Server is terminating because of a system shutdown. This is an informational message only. No user action is required. 2016-07-30 03:11:12.56 spid14s Service Broker manager has shut down. 2016-07-30 03:11:12.61 spid14s Error: 17054, Severity: 16, State: 1. 2016-07-30 03:11:12.61 spid14s The current event was not reported to the Windows Events log. Operating system error = 1717(The interface is unknown.). You may need to clear the Windows Events log if it is full. 2016-07-30 03:11:13.48 spid7s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
SQL Server is being shut down at 3:11 AM.
Event Viewer – Setup
Microsoft updates were being applied on Saturday early morning between 3 and 3:15 AM.
Looks like the message “Sqlcmd: Warning: The last operation was terminated because the user pressed CTRL+C.” is due to the application of MS Windows Update against the server.