Database ‘model’ cannot be opened. It is in the middle of a restore.

-> 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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s