Symptoms
Assume that you back up the tail of the log for the model database that uses norecovery option in Microsoft SQL Server 2014 or SQL Server 2012, and the database is put into restoring state. When the backup is complete, you cannot restart the SQL Server service, and you receive the following error message in SQL Server error log:
<Date> <Time> spid10s The database 'model' is marked RESTORING and is in a state that does not allow recovery to be run.
<Date> <Time> spid10s Error: 927, Severity: 14, State: 2.
<Date> <Time> spid10s Database 'model' cannot be opened. It is in the middle of a restore.
<Date> <Time> 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.
<Date> <Time> spid10s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
Cause
This issue occurs because the model database is used as the template for all databases that are created on an instance of SQL Server. If the model database is marked as not allowing for recovery status, tempdb cannot be created, and the SQL Server service cannot start.
Resolution
Service pack information
To resolve this issue, obtain Service Pack 1 for SQL Server 2014.
For more information about SQL Server 2014 Service Pack 1 (SP1), see bugs that are fixed in SQL Server 2014 Service Pack 1.
Workaround
To work around this issue, use the following method:
-
Prevent all connections from the application to SQL Server
-
Start the SQL Server service together with trace flag 3608
-
Execute the "restore database Model with recovery" command
-
Remove trace flag 3608, and restart SQL Server
Status
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.