Sql-Server-2008
如何使用損壞的模型數據庫啟動 MSSQL Server
在移動一些數據庫（恢復、刪除等）後，我們在創建新數據庫時遇到了問題。具體來說，當嘗試創建一個新的數據庫 MSSQL Server 時，它失敗了，因為“數據庫’模型’被標記為正在恢復，並且處於不允許執行恢復的狀態”。正如一些線上解決方案所建議的那樣，我們嘗試啟動和停止 MSSQL 服務。服務不會重新啟動，因為“無法創建 tempdb。您可能沒有足夠的可用磁碟空間。通過刪除 tempdb 驅動器上的其他文件來釋放額外的磁碟空間”（僅供參考：驅動器有 100gb 的可用空間）。嘗試重新啟動執行 MSSQL Server 的機器。當伺服器重新上線時，我們收到了同樣的錯誤。我們嘗試刪除 tempdb.mdf 並從模板文件夾中恢復 modeldb，但這些都沒有解決問題。即使在單使用者模式下，我們也無法連接到數據庫。許多線上解決方案讓我們對伺服器執行 SQL 命令，但我們無法連接（即使在單使用者模式下）到數據庫以對伺服器執行命令。
具體錯誤資訊：
無法打開數據庫“模型”。它正處於恢復過程中。（Microsoft SQL Server，錯誤：927）
SQL Server (MSSQLSERVER) 服務正在啟動。SQL Server (MSSQLSERVER) 服務無法啟動。
發生服務特定錯誤：1814。
我們需要伺服器盡快重新啟動並執行。
這實際上是在一段時間前（早在 12 年 7 月）通過來自 Microsoft 的付費支持電話修復的。
由於這已經獲得瞭如此多的意見，我決定添加我們與 Microsoft 通話的“電子郵件摘要”的編輯版本。
發布的是 pastebin 的連結：http: //pastebin.com/QqNz7xYd
希望此資訊對解決此問題的任何人有所幫助
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