none
sql server will not start - Database 'model' cannot be opened. It is in the middle of a restore.

    Question

  • Sql server service will not start after server reboot:

    2009-08-13 14:15:43.94 spid7s      SQL Trace ID 1 was started by login "sa".
    2009-08-13 14:15:43.97 spid7s      Starting up database 'mssqlsystemresource'.
    2009-08-13 14:15:43.97 spid7s      The resource database build version is 10.00.1600. This is an informational message only. No user action is required.
    2009-08-13 14:15:44.30 Server      A self-generated certificate was successfully loaded for encryption.
    2009-08-13 14:15:44.30 spid7s      Server name is 'WIN-37R9WFWAJIB\MSDB1'. This is an informational message only. No user action is required.
    2009-08-13 14:15:44.32 spid10s     Starting up database 'model'.
    2009-08-13 14:15:44.32 Server      Server is listening on [ 'any' <ipv6> 49167].
    2009-08-13 14:15:44.33 Server      Server is listening on [ 'any' <ipv4> 49167].
    2009-08-13 14:15:44.33 Server      Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSDB1 ].
    2009-08-13 14:15:44.33 Server      Server named pipe provider is ready to accept connection on [ \\.\pipe\sql\query ].
    2009-08-13 14:15:44.35 spid10s     The database 'model' is marked RESTORING and is in a state that does not allow recovery to be run.
    2009-08-13 14:15:44.38 Server      Server is listening on [ ::1 <ipv6> 49168].
    2009-08-13 14:15:44.38 Server      Server is listening on [ 127.0.0.1 <ipv4> 49168].
    2009-08-13 14:15:44.38 Server      Dedicated admin connection support was established for listening locally on port 49168.
    2009-08-13 14:15:44.39 spid10s     Error: 927, Severity: 14, State: 2.
    2009-08-13 14:15:44.39 spid10s     Database 'model' cannot be opened. It is in the middle of a restore.
    2009-08-13 14:15:44.49 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.
    2009-08-13 14:15:44.50 spid10s     SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informa



    there is plenty of room on the server, so i dont think its a disk space issue.  i am able to start the service as such:

    C:\Users\Administrator>net start MSSQL$MSDB1 /c /m /T3608
    The SQL Server (MSDB1) service is starting.
    The SQL Server (MSDB1) service was started successfully

    sqlcmd -e

    1> select name, status from sysdatabases;
    2> go
    select name, status from sysdatabases;

    name
                                                     status
    --------------------------------------------------------------------------------
    ------------------------------------------------ -----------
    master
                                                           65544
    tempdb
                                                           65544
    model
                                                           65568
    msdb
                                                      1073807368
    SS_DBA_Dashboard
                                                      1073807368

    (5 rows affected)


    model is in RESTORING state and I dont know how to fix it.  I am running simple backups, so I have a somewhat recent backup of model.bak but when I attempt to restore it I get an error:

    1> restore database model
    2> go
    restore database model

    Msg 3112, Level 16, State 1, Server WIN-37R9WFWAJIB\MSDB1, Line 1
    Cannot restore any database other than master when the server is in single user
    mode.
    Msg 3013, Level 16, State 1, Server WIN-37R9WFWAJIB\MSDB1, Line 1
    RESTORE DATABASE is terminating abnormally.


    other forums suggest manually updating the status column but i dont know if my syntax is wrong or it just cant be done in sql server 2008:

    1> begin transaction
    2> update sysdatabases set status=16 where name='model'
    3> ;
    4> go
    begin transaction
    update sysdatabases set status=16 where name='model'
    ;

    Msg 4406, Level 16, State 1, Server WIN-37R9WFWAJIB\MSDB1, Line 2
    Update or insert of view or function 'sysdatabases' failed because it contains a
     derived or constant field.
    1>


    yet another forum suggested taking a cleanly shut down model.mdf and model.ldf from another sql server 2008 instance and placing those in the data directory while moving the 'restoring' ones to a backup location.  this didnt work either.  any help is appreciated im new to sql server and this is very frustrating!  thanks in advance!
    Thursday, August 13, 2009 7:37 PM

Answers

  • FIXED IT.

    since this is simple backup i had to:



    C:\Users\Administrator>net start mssql$msdb1 /T3608
    The SQL Server (MSDB1) service is starting.
    The SQL Server (MSDB1) service was started successfully.


    C:\Users\Administrator>sqlcmd -e
    1> restore database model
    2> go
    restore database model

    RESTORE DATABASE successfully processed 0 pages in 0.101 seconds (0.000 MB/sec).


    now the database starts appropriately.  Not sure why the restore from disk backup was failing so miserably, but its working now so i am happy.  hopefully this helps someone else in the future.


    Thursday, August 13, 2009 9:54 PM

All replies

  • The service is failing to start because it can't open or create TEMPDB, not model.  What did you change?  The directory must have been moved or deleted.


    Thursday, August 13, 2009 8:43 PM
    Moderator
  • thanks for reviewing this.  nothing has changed in terms of file existence or location.   the model db is still in the data directory with all the other mdf/ldf files.  either way i have a backup,

    but i think the crux of the issue is:  how do i force the model database OUT of RESTORING status in sql server 2008?

    one other update:  i am able to start the server using this syntax: net start mssql$msdb1 /T3608 and this allows me to run restore without the 'cannot restore any database other than master in single user mode error'  Now when i attempt restore commands on model it just kicks me out of sqlcmd and stops the sql server, or tells me it cannot recover because there is no logfile, although i can confirm my backup includes the logfile:

    1> restore database model with recovery
    2> go
    restore database model with recovery

    Msg 4333, Level 16, State 1, Server WIN-37R9WFWAJIB\MSDB1, Line 1
    The database cannot be recovered because the log was not restored.
    Msg 3013, Level 16, State 1, Server WIN-37R9WFWAJIB\MSDB1, Line 1
    RESTORE DATABASE is terminating abnormally.

    1> restore filelistonly from disk='c:\backup\model_backup_2009_08_11_153929_6237
    020.bak'
    2> go
    restore filelistonly from disk='c:\backup\model_backup_2009_08_11_153929_6237020
    .bak'

    LogicalName
                                                     PhysicalName


                                                                          Type FileG
    roupName
                                                Size                 MaxSize
          FileId               CreateLSN                   DropLSN
       UniqueId                             ReadOnlyLSN                 ReadWriteLSN
                    BackupSizeInBytes    SourceBlockSize FileGroupId LogGroupGUID
                          DifferentialBaseLSN         DifferentialBaseGUID
           IsReadOnly IsPresent TDEThumbprint
    --------------------------------------------------------------------------------
    modeldev
                                                     C:\Program Files\Microsoft SQL
    Server\MSSQL10.MSDB1\MSSQL\DATA\model.mdf

                                                                          D    PRIMA
    RY
                                                             1310720       351843720
    80640                    1                           0
     0 00000000-0000-0000-0000-000000000000                           0
                  0              1310720             512           1 NULL
                                    17000000045600037 E0E8B0FE-97A8-4AC2-B935-E047E1
    538A2E          0         1 NULL
    modellog
                                                     C:\Program Files\Microsoft SQL
    Server\MSSQL10.MSDB1\MSSQL\DATA\modellog.ldf

                                                                          L    NULL

                                                              786432       351843720
    80640                    2                           0
     0 00000000-0000-0000-0000-000000000000                           0
                  0                    0             512           0 NULL
                                                    0 00000000-0000-0000-0000-000000
    000000          0         1 NULL


    1> restore database model
    2> from disk='model_backup_2009_08_11_153929_6237020'
    3> go
    restore database model
    from disk='model_backup_2009_08_11_153929_6237020'

    HResult 0x6D, Level 16, State 1
    Named Pipes Provider: The pipe has been ended.

    Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Communication link fail
    ure.
    Thursday, August 13, 2009 9:03 PM
  • FIXED IT.

    since this is simple backup i had to:



    C:\Users\Administrator>net start mssql$msdb1 /T3608
    The SQL Server (MSDB1) service is starting.
    The SQL Server (MSDB1) service was started successfully.


    C:\Users\Administrator>sqlcmd -e
    1> restore database model
    2> go
    restore database model

    RESTORE DATABASE successfully processed 0 pages in 0.101 seconds (0.000 MB/sec).


    now the database starts appropriately.  Not sure why the restore from disk backup was failing so miserably, but its working now so i am happy.  hopefully this helps someone else in the future.


    Thursday, August 13, 2009 9:54 PM
  • Thank you, you really helped me
    Tuesday, September 28, 2010 3:33 PM
  • I have the same issue on SQL Server 2008R2, after a server reboot, but none of the NET START commands or command line attempts are successful to start the Service.

    NET START command results in "Access Denied" "System Error 5" and the Command line attempts result in

     Server      Error: 17058, Severity: 16, State: 1.
     Server      initerrlog: Could not open error log file ''.

    Any help?

    SQL Error log file:

    2011-03-29 16:50:49.40 Server      (c) Microsoft Corporation.
    2011-03-29 16:50:49.40 Server      All rights reserved.
    2011-03-29 16:50:49.40 Server      Server process ID is 7292.
    2011-03-29 16:50:49.40 Server      System Manufacturer: 'Dell Inc.', System Model: 'PowerEdge T710'.
    2011-03-29 16:50:49.40 Server      Authentication mode is MIXED.
    2011-03-29 16:50:49.40 Server      Logging SQL Server messages in file 'E:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG'.
    2011-03-29 16:50:49.40 Server      This instance of SQL Server last reported using a process ID of 7448 at 3/29/2011 4:30:06 PM (local) 3/29/2011 8:30:06 PM (UTC). This is an informational message only; no user action is required.
    2011-03-29 16:50:49.40 Server      Registry startup parameters:
      -d E:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf
      -e E:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG
      -l E:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
      -T 4616
    2011-03-29 16:50:49.42 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
    2011-03-29 16:50:49.42 Server      Detected 24 CPUs. This is an informational message; no user action is required.
    2011-03-29 16:50:49.84 Server      Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
    2011-03-29 16:50:49.84 Server      Lock partitioning is enabled.  This is an informational message only. No user action is required.
    2011-03-29 16:50:49.86 Server      Node configuration: node 0: CPU mask: 0x0000000000fff000:0 Active CPU mask: 0x0000000000fff000:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
    2011-03-29 16:50:49.86 Server      Node configuration: node 1: CPU mask: 0x0000000000000fff:0 Active CPU mask: 0x0000000000000fff:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
    2011-03-29 16:50:49.89 spid9s      Starting up database 'master'.
    2011-03-29 16:50:49.95 spid9s      CHECKDB for database 'master' finished without errors on 2011-03-18 08:46:34.257 (local time). This is an informational message only; no user action is required.
    2011-03-29 16:50:49.95 spid9s      FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.
    2011-03-29 16:50:49.97 spid9s      SQL Trace ID 1 was started by login "sa".
    2011-03-29 16:50:49.97 spid9s      Starting up database 'mssqlsystemresource'.
    2011-03-29 16:50:49.99 spid9s      The resource database build version is 10.50.1600. This is an informational message only. No user action is required.
    2011-03-29 16:50:50.20 spid13s     Starting up database 'model'.
    2011-03-29 16:50:50.20 spid9s      Server name is 'XXXX'. This is an informational message only. No user action is required.
    2011-03-29 16:50:50.21 Server      The certificate [Cert Hash(sha1) "C41DC20703F049763778AC2842C3C8A3A2C6EED4"] was successfully loaded for encryption.
    2011-03-29 16:50:50.21 Server      Server is listening on [ 'any' <ipv6> 1433].
    2011-03-29 16:50:50.21 Server      Server is listening on [ 'any' <ipv4> 1433].
    2011-03-29 16:50:50.21 Server      Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
    2011-03-29 16:50:50.21 Server      Server named pipe provider is ready to accept connection on [ \\.\pipe\sql\query ].
    2011-03-29 16:50:50.21 Server      Server is listening on [ ::1 <ipv6> 1434].
    2011-03-29 16:50:50.21 spid13s     The database 'model' is marked RESTORING and is in a state that does not allow recovery to be run.
    2011-03-29 16:50:50.21 Server      Server is listening on [ 127.0.0.1 <ipv4> 1434].
    2011-03-29 16:50:50.21 spid13s     Error: 927, Severity: 14, State: 2.
    2011-03-29 16:50:50.21 spid13s     Database 'model' cannot be opened. It is in the middle of a restore.
    2011-03-29 16:50:50.21 Server      Dedicated admin connection support was established for listening locally on port 1434.
    2011-03-29 16:50:50.22 spid13s     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.
    2011-03-29 16:50:50.22 spid13s     SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

     

    Tuesday, March 29, 2011 9:05 PM
  • 1. Stop the SQL services.

    2. Manually move Model database data and log files from C drive or source drive to some other drive.

    3. Try to restart the SQL services.

    Friday, February 14, 2014 4:29 PM