Almost every time I restart the machine hosting a SQL Server 2008 R2 express server, the database goes through the restore process. Most of the time, the database will become stuck in the restore process.

This error was found in the windows event log: The SQL Server (__NAMEHERE__) service terminated unexpectedly. It has done this 1 time(s).

This was found in the sql logs:

2011-12-09 10:12:51.16 Server SQL Server is terminating because of a system shutdown. This is an informational message only. No user action is required. 2011-12-09 10:13:18.86 spid13s Service Broker manager has shut down. 2011-12-09 10:13:18.86 spid13s Error: 17054, Severity: 16, State: 1. 2011-12-09 10:13:18.86 spid13s The current event was not reported to the Windows Events log. Operating system error = (null). You may need to clear the Windows Events log if it is full.

Any obvious reason as to why I cannot restart the system without the database needing to go through a restore.

migrated from stackoverflow.com Dec 9 '11 at 20:10

This question came from our site for professional and enthusiast programmers.

  • What do you mean "Most of the time, the database will become stuck in the restore process."? It never completes or just restore takes more time than you would like? Do you potentially have long running transactions in flight when you restart the instance? – Martin Smith Dec 9 '11 at 18:12
  • It will forever hang at 99% and the database will never be usable. I have to stop the service and delete the data files and do a fresh restore. – JeremyK Dec 9 '11 at 19:22

Are you sure it is a restore process? When SQL Server starts, all the databases go through a process called recovery - which ensures all the transactions that were committed are ok, and those that weren't committed are rolled back. (when a database is restored, it goes through recovery as well)

Those logs just tell me that SQL Server's service stopped because of a system shutdown (reboot?). What happens when you start the SQL Server service manually? Does it run under a domain Windows account, or a local system account?

  • You are correct, it gets stuck in the Restore Process. I watch the Windows event system logs and will see it reach 99%, however the database never becomes accessible. There is no domain involved, just the local admin account. If I try to restart the sql server, it will just get stuck in th e recovery process again. – JeremyK Dec 9 '11 at 20:53

It turns out the problem was that the database was out of internal memory due to issues with the Service Broker. After restarting the service, all of the memory that was consumed by the broker is still being reloaded and maxing out the available memory. I do not know the cause of the memory consumption, however it answers the problem I was having here. The database simply ran out of allowed memory in express edition.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.