How to start MSSQL Server with corrupt model db

Question

After moving some databases around (restoring, deleting, etc) we experienced an issue creating new databases. Specifically, When trying to create a new database MSSQL Server it failed because the "The database 'model' is marked RESTORING and is in a state that does not allow recovery to be run". As some online solutions suggested, we tried to Start and Stop the MSSQL Service. Service would not restart because "Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive" (FYI: the drive has 100gb of free space). Tried restarting the machine the MSSQL Server is running on. When the server came back online, we received the same error. We have tried deleting tempdb.mdf and restoring the modeldb from the templates folder, but neither of these solved the issue. We are unable to connect to the database, even in single user mode. Many of the online solutions have us running SQL commands against the server, but we are unable to connect (even in single user mode) to the DB to run commands against the server.

Specific error messages:

Database 'model' cannot be opened. It is in the middle of a restore. (Microsoft SQL Server, Error: 927)

The SQL Server (MSSQLSERVER) service is starting. The SQL Server (MSSQLSERVER) service could not be started.

A service specific error occurred: 1814.

We need the server up and running again ASAP.

Oh my god... how did this story continue ..? – Agustin Garzon Oct 15 '20 at 03:06 — Agustin Garzon, Oct 15 '20 at 03:06

score 4 · Accepted Answer · edited Apr 09 '13 at 06:34

This was actually fixed awhile (back in July '12) ago with a paid support call from Microsoft.

Since this has gotten so many views, I've decided to add a redacted version of our "email summary" of the call with Microsoft.

Posted is a link to pastebin: http://pastebin.com/QqNz7xYd

Hope this info will help anyone with this issue

Problem Description:
=============================
You Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)  default instance on SERVERNAME server and you were trying to start the SQL SERVER service which was failing to start.


Analysis\Troubleshooting:
=============================

We checked the SQLS SERVER ERRORLOG

===========================
2012-06-20 19:33:41.48 Server      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
 Apr  2 2010 15:48:46
 Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6001: Service Pack 1)

2012-06-20 19:33:41.50 Server      (c) Microsoft Corporation.
2012-06-20 19:33:41.50 Server      All rights reserved.
2012-06-20 19:33:41.50 Server      Server process ID is 2596.
2012-06-20 19:33:41.51 Server      System Manufacturer: 'HP', System Model: 'ProLiant DL360 G5'.
2012-06-20 19:33:41.51 Server      Authentication mode is MIXED.
2012-06-20 19:33:41.51 Server      Logging SQL Server messages in file 'D:\Mssql\Data\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG'.
2012-06-20 19:33:41.51 Server      This instance of SQL Server last reported using a process ID of 2816 at 6/20/2012 6:10:15 PM (local) 6/20/2012 10:10:15 PM (UTC). This is an informational message only; no user action is required.
2012-06-20 19:33:41.51 Server      Registry startup parameters:
  -d D:\Mssql\Data\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf
  -e D:\Mssql\Data\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG
  -l D:\Mssql\Data\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
2012-06-20 19:33:41.56 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2012-06-20 19:33:41.56 Server      Detected 4 CPUs. This is an informational message; no user action is required.
2012-06-20 19:33:41.65 Server      Using locked pages for buffer pool.
2012-06-20 19:33:41.88 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.
2012-06-20 19:33:42.33 Server      Node configuration: node 0: CPU mask: 0x000000000000000f:0 Active CPU mask: 0x000000000000000f:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2012-06-20 19:33:42.49 spid7s      Starting up database 'master'.
2012-06-20 19:33:42.86 spid7s      Resource governor reconfiguration succeeded.
2012-06-20 19:33:42.86 spid7s      SQL Server Audit is starting the audits. This is an informational message. No user action is required.
2012-06-20 19:33:42.87 spid7s      SQL Server Audit has started the audits. This is an informational message. No user action is required.
2012-06-20 19:33:42.87 spid7s      FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.
2012-06-20 19:33:43.02 spid7s      SQL Trace ID 1 was started by login "sa".
2012-06-20 19:33:43.03 spid7s      Starting up database 'mssqlsystemresource'.
2012-06-20 19:33:43.05 spid7s      The resource database build version is 10.50.1600. This is an informational message only. No user action is required.
2012-06-20 19:33:43.24 spid10s     Starting up database 'model'.
2012-06-20 19:33:43.24 spid7s      Server name is 'SERVERNAME'. This is an informational message only. No user action is required.
2012-06-20 19:33:43.27 spid10s     The database 'model' is marked RESTORING and is in a state that does not allow recovery to be run.
2012-06-20 19:33:43.28 spid10s     Error: 927, Severity: 14, State: 2.
2012-06-20 19:33:43.28 spid10s     Database 'model' cannot be opened. It is in the middle of a restore.
2012-06-20 19:33:43.36 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.
2012-06-20 19:33:43.36 spid10s     SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
===========================

From ERRORLOG we can see that SQL SERVER trying to bring model database online but since model database in recovery state hence we were unable to run database recovery.

We also know that whenever SQL SERVER start we recreate TEMPDB database by coping the content from model database.

Now since model database didn’t started hence TEMPDB startup also failed which is one of critical system database and this caused SQL SERVER service to failed


Solution:
=====================================
·         To fix this issue we started SQL SERVER EXE with following parameter from command prompt


SQLSERVR.EXE -c -T3608

·         Then after we detached model database using following T-SQL


sp_detach_db model

·         Then after we attach the model database using following T-SQL


================================
USE MASTER
GO
CREATE DATABASE MODEL
      ON (FILENAME = 'C:\PROGRAM FILES\MICROSOFT SQL SERVER\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MODEL.MDF')
      FOR ATTACH ;
================================

·         After that SQL SERVER service came online successfully

How much time elapsed between the incident and the recovery ? Were there consequences with clients or staff in the company because of this ? — Agustin Garzon, Oct 15 '20 at 03:11

score 1 · Answer 2 · answered Jun 20 '12 at 22:01

1

Can you start the sql service by bypassing the autorecover?

net start mssqlserver /f /m /t3608

then try to recover the model?

sqlcmd -e 
restore database 
model go

edit: found this link as well, which goes into more depth and is probably more accurate..

answered Jun 20 '12 at 22:01

Rex

7,895
3
29
45

sqlcmd -e HResult 0x2, Level 16, State 1 Named Pipes Provider: Could not open a connection to SQL Server [2]. Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or in stance-specific error has occurred while establishing a connection to SQL Server . Server is not found or not accessible. Check if instance name is correct and i f SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.. Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired. – Julia McGuigan Jun 20 '12 at 22:11
Try disabling Named Pipes, in that case. It's a PITA anyway, but you can enable it again if you get your db back up and running. – HopelessN00b Jun 21 '12 at 21:46
Turns out that the reason we could not connect to the database because other connections were using the "single user" that the single user mode command initiates. We changed the port so that they other applications could not connect to the database and use the "single user" and were then able to repair the model database. The model DB is infact corrupted, and we had to start the server using a model DB from a similar server with the exact same configuration but running a different set of DBs in order to restore the Model DB. I'll post a full solution to what we did when I get back from work. – Julia McGuigan Jun 22 '12 at 15:45

How to start MSSQL Server with corrupt model db

2 Answers2