SQL Server 2008 - Error starting service - model.mdf not found?

Question

my SQL server 2008 was running fine. About an hour ago, it suddenly stopped - the MSSQLSERVER service had stopped I right clicked, clicked start, and it said the service had started, and stopped

I looked in the event log and saw these two errors:

17207 :
udopen: Operating system error 3(error not found) during the creation/opening of physical device C:\Program Files\Microsoft SQL Server\MSSQL\data\model.mdf.

17204 :
FCB::Open failed: Could not open device C:\Program Files\Microsoft SQL Server\MSSQL\data\model.mdf for virtual device number (VDN) 1.

The model.mdf db has NEVER been in that location - I specified drive F: to use for data / log during install.

I checked the SQL Configuration Manager, to try and set startup params, but SQL Server is not listed as one of the services.....

EDIT:

I've now moved the db to where it was looking for: C:\Program Files\Microsoft SQL Server\MSSQL\data\ directory.

Now if I start the service, it still does not work - I get this error message in the log:

Could not find row in sysindexes for database ID 3, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.

Interestingly, I checked the error log - around the time users reported problems, there is this:

2010-01-08 17:11:26.44 spid51      Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
2010-01-08 17:11:26.44 spid51      FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.
2010-01-08 17:11:26.44 spid51      Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.
2010-01-08 17:11:26.44 spid51      FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.
2010-01-08 17:11:26.44 spid51      Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
2010-01-08 17:11:26.44 spid51      FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.
2010-01-08 17:11:44.89 spid10s     Service Broker manager has shut down.
2010-01-08 17:11:47.83 spid7s      SQL Server is terminating in response to a 'stop' request from Service Control Manager. This is an informational message only. No user action is required.
2010-01-08 17:11:47.83 spid7s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

Answer 1

Firstly, it seems someone is changing options on your database using sp_configure, do you know who this is? looks like this has occurred with a script (times are close together) They may also have changed the location of your model database using a procedure similar to this After the change the sql server service has been shutdown, which is why you see the default trace & service broker stopping (normal). If it was changed to an incorrect location, then this would explain why sql server wont start

A database id of 3 is the model database, so you've either moved a bad copy of model, or its corrupt. You have 2 options depending on which is easier for you depending on your setup.

1. Start the database in single user mode and restore model from a backup.
2. (Sledgehammer approach) Rebuild the master databases Then re-attach user databases & re-created your SQL Agent jobs

Answer 2

Probably silly questions, but did you run chkdsk on the volume?

Have you verified that your F: drive and files are where you last put them? And chkdsk isn't finding issues with those?

Have you verified permissions on the directories (and drives) in question?

Answer 3

Silly question: have you simply tried rebooting the server?