SQL Server Instance無法啟動了, 因為TempDB所在的分區沒有了, 怎麼辦?

標籤：blog   http   color   io   os   使用   ar   strong   for   

我的SQL 2014的虛擬機器在遷移的時候, 存放TempDB的LUN被刪掉了. 在虛擬機器的作業系統啟動了之後, SQL Server Instance卻啟動不起來了. 檢查Event Log, 報錯.

於是, 找到了這篇文章. 其目標是強制SQL Server啟動, 並允許接收sqlcmd的修改一些選項的命令.  一旦SQL可以接受命令, 那我們就可以重新指定tempdb的路徑了.

 

然而, 使用文章中的步驟, 我還是無法強制啟動SQL Server.

檢查Sqlservr.exe /f /c的輸出, 發現報錯如下:

2014-09-18 15:51:36.88 spid10s     Clearing tempdb database.
2014-09-18 15:51:36.89 spid10s     Error: 5123, Severity: 16, State: 1.
2014-09-18 15:51:36.89 spid10s     CREATE FILE encountered operating system erro
r 3(The system cannot find the path specified.) while attempting to open or crea
te the physical file ‘E:\SQLDatabaseData\tempdb.mdf‘.
2014-09-18 15:51:36.89 spid10s     Error: 5123, Severity: 16, State: 1.
2014-09-18 15:51:36.89 spid10s     CREATE FILE encountered operating system erro
r 3(The system cannot find the path specified.) while attempting to open or crea
te the physical file ‘F:\SQLServerDatabaseLog\templog.ldf‘.
2014-09-18 15:51:36.90 spid10s     Error: 17204, Severity: 16, State: 1.
2014-09-18 15:51:36.90 spid10s     FCB::Open failed: Could not open file E:\SQLD
atabaseData\tempdb.mdf for file number 1.  OS error: 3(The system cannot find th
e path specified.).
2014-09-18 15:51:36.91 spid10s     Error: 5120, Severity: 16, State: 101.
2014-09-18 15:51:36.91 spid10s     Unable to open the physical file "E:\SQLDatab
aseData\tempdb.mdf". Operating system error 3: "3(The system cannot find the pat
h specified.)".
2014-09-18 15:51:36.91 spid10s     Error: 1802, Severity: 16, State: 4.
2014-09-18 15:51:36.91 spid10s     CREATE DATABASE failed. Some file names liste
d could not be created. Check related errors.
2014-09-18 15:51:36.91 spid10s     Could not create tempdb. You may not have eno
ugh 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.
2014-09-18 15:51:36.92 spid15s     The SQL Server Network Interface library succ
essfully deregistered the Service Principal Name (SPN) [ MSSQLSvc/SQL2014-1.SPS.
P51359 ] for the SQL Server service.

看來即使是強制啟動SQL, tempdb檔案所在的原始路徑還是需要存在的. 於是筆者給這台虛擬重新分配了兩個虛擬硬碟. 把相應路徑都建立好. SQL Server Instance終於可以強制啟動了.

後面照著文章所講的步驟就可以成功解決問題了.

 

附

========================

為方便將來尋找和防止原文的丟失, 我把重要的步驟列在這裡:

Following are the steps needed to add a new file to TEMPDB and then restart SQL Server.

1)   Open command prompt window #1 as ADMIN and go to the BINN directory where SQL Server is installed. This is typically in

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn

OR

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn

2) Then execute command like

Sqlservr.exe /f /c

3)   Then open one more command window #2 and if this is a default instance then open SQLCMD  using the following command

SQLCMD –S localhost –E

4)   This will open a SQL command prompt there where you can type the following commands

1> USE MASTER

2> GO

3> ALTER DATABASE tempdb MODIFY FILE

4> (NAME = tempdev, FILENAME = ‘C:\NEWPATH\datatempdb.mdf‘)

5> GO

6> quit

5)   Now go back to Command window #1 and hit CTRL C.It will ask if you want to stop the instance. Y/N. Enter Y

 

6)   Now start the SQL Server instance from configuration manager. You should see the new data file created and SQL Server started.

 

參考資料

========================

How to start SQL Server if you lost TEMPDB Data files?

http://blogs.xtivia.com/home/-/blogs/how-to-start-sql-server-if-you-lost-tempdb-data-files-

SQL Server Instance無法啟動了, 因為TempDB所在的分區沒有了, 怎麼辦?