-> SQL server was not coming online in one of the database server.
-> I checked the SQL server log and found the below error,
.
.
2016-12-25 19:43:43.02 spid12s Starting up database ‘msdb’.
2016-12-25 19:43:43.03 spid10s Starting up database ‘model’.
2016-12-25 19:43:43.05 spid10s The database ‘model’ is marked RESTORING and is in a state that does not allow recovery to be run.
2016-12-25 19:43:43.05 spid10s Error: 927, Severity: 14, State: 2.
2016-12-25 19:43:43.05 spid10s Database ‘model’ cannot be opened. It is in the middle of a restore.
2016-12-25 19:43:43.05 spid10s 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.
2016-12-25 19:43:43.05 spid10s SQL Trace was stopped due to server shutdown. Trace ID = ‘1’. This is an informational message only; no user action is required.
.
.
-> As per the SQL server errorlog, it seems like the model database is restoring. But in reality we see this error when model database is corrupted. The error related to tempdb is because tempdb is recreated everytime SQL server starts using model database.
-> Open services.msc and get to the SQL server services. Right click the service and click on properties. In General tab, look for “path to executable”. Copy the SQLServr.exe location from there.
-> Open a command prompt and navigate to the location. Now type the below command,
sqlservr.exe -s<InstanceName> -T3608 -T3609 -c -f
| 3608 | Recovers master database only. Skips automatic recovery (at startup) for all databases except the master database. |
| 3609 | Skips the creation of the tempdb database at startup. Use this trace flag if the tempdb database is problematic or problems exist in the model database. |
| -c | Start as a console and not service. |
| -f | Minimal configuration mode. |
-> Open another command prompt and use the below command,
SQLCMD -E -SADMIN:<Hostname\InstanceName>
-> Execute the below command,
use master
go
sp_detach_db ‘model
go
-> Execute the below command to attach the database,
CREATE DATABASE [model] ON (FILENAME = N'<Location>\model.mdf’ ),( FILENAME = N'<Location>\modellog.ldf’ )
FOR ATTACH
-> If you get an error that the files cannot be attached. You will have to copy the MDF and LDF file from a different instance of same build and issue the above command again.
Thank You,
Vivek Janakiraman
Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.