0

I need some serious help. I have a major database server down and am scratching my head at how to fix it. The server was hit by rolling black outs last week in Dallas and sense then, Microsoft SQL 2005 SP2 will not start up. I am getting the following errors (both when starting the service and while trying to execute mssqlsrv.exe -c -f -m:

Event Type: Error Event Source: MSSQLSERVER Event ID: 913 Could not find database ID 3. Database may not be activated yet or may be in transition. Reissue the query once the database is available. If you do not think this error is due to a database that is transitioning its state and this error continues to occur, contact your primary support provider. Please have available for review the Microsoft SQL Server error log and any additional information relevant to the circumstances when the error occurred.

and...

Event Type: Information Event Source: MSSQLSERVER Event ID: 1814 Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.

I have tried to rename the tempdb.mdf to tempdb.old with no success. I have checked and have 193 GB of free hard drive space. What else might cause this problem? Could the server need a chkdsk ran on it or do I need to be looking at some area of the database server?

Any help is greatly appreciated. Thank you in advance.

ThaKidd KG5ORD
  • 329
  • 1
  • 7
  • 17

1 Answers1

4

Database ID 3 is the model database. Without it the SQL Server won't start. Open a cmdshell and navigate to the C:\program files\microsoft sql server\mssql\binn\ folder (or where ever the .exe files are stored). Run "SQLSERVR.exe -c -m" This will run the SQL Server within that command window in single user mode. See if that will get it started up. Don't close the window.

You can now connect and look to see where the files for the model and tempdb databases should be. Verify that those files and paths to them actually exist. You may need to restore the model database from a backup while the server is in single user mode.

mrdenny
  • 27,074
  • 4
  • 40
  • 68
  • Yes I have gotten that far. It keeps telling me that the problems are with tempdb. I have not moved any of these databases around in the filesystem. Where do you determine where the database thinks the files are? Is there a way to rebuild it? I do not believe we have backups of the file. – ThaKidd KG5ORD Feb 07 '11 at 23:47
  • The tempdb database is rebuilt every time the instance is restarted, as long as the drive and path exist and have enough free space. The model database would need to be restored from a backup, or if there is no backup it would need to be backed up from another server, and restored to the problem machine. You can query for the database files path using select * from sys.database_files. – mrdenny Feb 08 '11 at 23:32