0

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.

JeremyK
  • 101
  • 1
  • 2
  • 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 09 '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. –  Dec 09 '11 at 19:22

2 Answers2

1

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?

Peter Schofield
  • 1,639
  • 9
  • 11
  • 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 09 '11 at 20:53
0

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.

JeremyK
  • 101
  • 1
  • 2