Karthick PK 's Blog

Karthick PK's technical blog covering topics such as SQLServer troubleshooting, technologies and security.

The database 'model' is marked RESTORING and is in a state that does not allow recovery to be run.Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive

The database 'model' is marked RESTORING and is in a state that does not allow recovery to be run.Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive

Rate This
  • Comments 4

Error

The database 'model' is marked RESTORING and is in a state that does not allow recovery to be run.

Error: 927, Severity: 14, State: 2.

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

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.

 

 

Cause

We get above error if the Model database is corrupted. TempDB is recreated every time when SQLServer restarts using model database, We receive "Could not create tempdb" because model database is corrupted

 

 

Resolution

 

1. Start SQLServer with Traceflag 3608,3609,-c,-f

 

-T3609 Will keep the existing TEMPDB, Which means when SQLServer is restarted SQLServer uses the existing tempdb instead of re-creating it as long as checkpoint in the tempdb had been done immediately before the last server shutdown

 

 

{

 

Sqlservr.exe -sInstanceName -T3608 -c -f -T3609

 

}

 

2. Open SQLCMD and make DAC connection

 

{

    SQLCMD -E -SADMIN:Servername\InstanceName

 

}

 

 

3. Use Tempdb

   Go

 

     {

      

       Above command will open the TempDB. If you get error while executing "Use Tempdb" your tempdb is not cleanly shutdown.

       To work around this copy a Tempdb.mdf and templog.ldf from Cleanly shutdown SQLServer of same version and replace it in TEMPDB location.

       Also note Transaction log location is stored in Tempdb.mdf.So you may have to copy Tlog file in destination server in same directory structure as it existed source server .

       Once you copy the file restart SQLServer using   -T3608 -c -f -T3609 Then run

       Use Tempdb

       Go

     }        

  

 

4. sp_detach_db 'model

5. Replace model.mdf and model.ldf from different server of same build

5. sp_attach_db 'model','X:\PAth\model.mdf','x:\modellog.ldf'

{

 Note:  If you are in SQLServer2008 we can use "create database with attach" option

  CREATE DATABASE [model] ON
 ( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.R2\MSSQL\DATA\model.mdf' ),
 ( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.R2\MSSQL\DATA\modellog.ldf' )
 FOR ATTACH
 go

}

 

Now we can start SQLServer normally and if you find any database in restoring state if can use RESTORE database DBNAME WITH RECOVERY to recover and open the database

 

  

If you liked this post, do like us on FaceBook at https://www.facebook.com/mssqlwiki and join our FaceBook group https://www.facebook.com/mssqlwiki#!/groups/454762937884205/

 

Thank You and Best Regards,

Karthick P.K

Microsoft SQL Server Support

Technical Lead

Comments
  • Hi karthik,

    Fantastic article .

    In SQL Server 2008 i am able to detach the model database after starting the instances as "Sqlservr.exe

    -sInstanceName -T3608 -c -f -T3609".

    But In SQL Server 2008 R2 the detach of model Database works, only  if i start instance using command "Net Start Instancename /f /T3608 /T4022".

  • This helped in our scenario

  • Perfect! Thank you for help. [use tempDB] did not work for me so skipped this step and continued with [sp_detach...] and [create database model...] and, magically, the instance service restarted. Then, I restored some databases which were in restoring mode with [restore files and groups] in SSMS choosing my last full backup, my last diff backup and my log backups up to crash's time. I'll definetely join your group!

    Thank you very much!

  • Where do you execute this command? in Command Prompt, SQLPlus or somewhere else? Thanks.

Page 1 of 1 (4 items)
Leave a Comment
  • Please add 7 and 4 and type the answer here:
  • Post