sql-server-2008 – 如何使用损坏的模型db启动MSSQL Server

移动一些数据库(规复,删除等)后,我们碰着了建设新数据库的题目.详细来说,当实行建设新数据库MSSQL Server时,它失败了,由于“数据库’模子’被标志为RESTORING而且处于不应承运行规复的状态”.正如一些在线办理方案所提议的,我们实行启动和遏制MSSQL处事.处事不会从头启动,由于“无法建设tempdb.您也许没有足够的可用磁盘空间.通过删除tempdb驱动器上的其他文件开释特另外磁盘空间”(仅供参考：驱动器有100GB的可用空间).实行从头启动运行MSSQL Server的计较机.当处事器从头上线时,我们收到了同样的错误.我们实行删除tempdb.mdf并从templates文件夹中规复modeldb,但这些都没有办理题目.纵然在单用户模式下,我们也无法毗连到数据库.很多在线办理方案让我们对处事器运行SQL呼吁,可是我们无法毗连(乃至在单用户模式下)数据库以对处事器运行呼吁.

特定错误动静：

数据库’model’无法打开.它正处于规复进程中. (Microsoft SQL Server,错误：927)

SQL Server(MSSQLSERVER)处事正在启动.
无法启动SQL Server(MSSQLSERVER)处事.

产生特定于处事的错误：1814.

我们必要尽快启动并运行处事器.

办理要领

因为这已经得到了云云多的概念,我抉择在微软的电话集会会议中添加我们“电子邮件择要”的编辑版本.

宣布是一个指向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.


AnalysisTroubleshooting:
=============================

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:MssqlDataMSSQL10_50.MSSQLSERVERMSSQLLogERRORLOG'.
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:MssqlDataMSSQL10_50.MSSQLSERVERMSSQLDATAmaster.mdf
  -e D:MssqlDataMSSQL10_50.MSSQLSERVERMSSQLLogERRORLOG
  -l D:MssqlDataMSSQL10_50.MSSQLSERVERMSSQLDATAmastlog.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 FILESMICROSOFT SQL SERVERMSSQL10_50.MSSQLSERVERMSSQLDATAMODEL.MDF')
      FOR ATTACH ;
================================

·         After that SQL SERVER service came online successfully

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!