Sql-Server
移動 tempDB 後無法連接到我的 SQL 管理工作室
在這里處於相當糟糕的位置。今天下午在我的 tempDB 上我執行了查詢：
sp_helpfile找到我的 MDF 和 LOG 文件的位置。我的 MDF 在我的 C: 驅動器上，而我的 LOG 文件在 D: 驅動器上，因此我執行以下查詢以將此日誌文件從 D: 驅動器移回 C: 驅動器。（這可能不是我執行的查詢，因為我不記得它到底是什麼！）use master go Alter database tempdb modify file (name = templog, filename = 'C:[drivelocation]\tempdb.ldf') go執行後，我關閉了 SQL Server MS 2012 並重新打開以連接回我機器上的 SQL Server。嘗試登錄時，SQL MS 返回以下錯誤：
=================================== Cannot connect to machineName\SQL2012. =================================== A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider) ------------------------------ For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=2&LinkId=20476 ------------------------------ Error Number: 2 Severity: 20 State: 0 ------------------------------ Program Location: at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry) at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry) at System.Data.SqlClient.SqlConnection.Open() at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server) at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser() =================================== The system cannot find the file specified顯然，它似乎再也找不到文件了！我什至嘗試在服務管理器中啟動該服務，但這表明 Windows 無法啟動該服務，錯誤程式碼為 1814。即使重新啟動機器也無法使其正常工作。
無論如何我可以解決這個問題嗎？我一直在嘗試查看是否可以通過 CMD 將日誌文件位置再次更改回 D: 驅動器，但似乎找不到任何東西
編輯 我在嘗試啟動服務時在事件查看器中註意到了這個警告：
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.這並沒有加起來，因為 C: DRIVE 上至少有 250GB 可用空間
編輯 2
根據要求，這是我的錯誤日誌
2019-11-01 22:37:06.45 Server Microsoft SQL Server 2012 (SP4-GDR) (KB4057116) - 11.0.7462.6 (X64) Jan 5 2018 22:11:56 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 17763: ) 2019-11-01 22:37:06.45 Server (c) Microsoft Corporation. 2019-11-01 22:37:06.45 Server All rights reserved. 2019-11-01 22:37:06.45 Server Server process ID is 20460. 2019-11-01 22:37:06.45 Server System Manufacturer: 'Dell Inc.', System Model: 'Precision Tower 7910'. 2019-11-01 22:37:06.45 Server Authentication mode is MIXED. 2019-11-01 22:37:06.45 Server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Log\ERRORLOG'. 2019-11-01 22:37:06.45 Server The service account is 'MACHINENAMEREMOVED'. This is an informational message; no user action is required. 2019-11-01 22:37:06.45 Server Registry startup parameters: -d C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\master.mdf -e C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Log\ERRORLOG -l C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\mastlog.ldf 2019-11-01 22:37:06.45 Server Command Line Startup Parameters: -s "SQL2012" 2019-11-01 22:37:06.67 Server SQL Server detected 1 sockets with 10 cores per socket and 20 logical processors per socket, 20 total logical processors; using 20 logical processors based on SQL Server licensing. This is an informational message; no user action is required. 2019-11-01 22:37:06.67 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required. 2019-11-01 22:37:06.67 Server Detected 32690 MB of RAM. This is an informational message; no user action is required. 2019-11-01 22:37:06.67 Server Using locked pages in the memory manager. 2019-11-01 22:37:06.70 Server Large Page Allocated: 32MB 2019-11-01 22:37:06.91 Server This instance of SQL Server last reported using a process ID of 17484 at 01/11/2019 22:25:55 (local) 01/11/2019 22:25:55 (UTC). This is an informational message only; no user action is required. 2019-11-01 22:37:06.92 Server Node configuration: node 0: CPU mask: 0x00000000000fffff:0 Active CPU mask: 0x00000000000fffff:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required. 2019-11-01 22:37:07.00 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. 2019-11-01 22:37:07.00 Server Lock partitioning is enabled. This is an informational message only. No user action is required. 2019-11-01 22:37:07.00 Server Database Instant File Initialization: enabled. For security and performance considerations see the topic 'Database Instant File Initialization' in SQL Server Books Online. This is an informational message only. No user action is required. 2019-11-01 22:37:07.00 Server Software Usage Metrics is disabled. 2019-11-01 22:37:07.01 spid5s Starting up database 'master'. 2019-11-01 22:37:07.02 spid5s 9 transactions rolled forward in database 'master' (1:0). This is an informational message only. No user action is required. 2019-11-01 22:37:07.02 spid5s 0 transactions rolled back in database 'master' (1:0). This is an informational message only. No user action is required. 2019-11-01 22:37:07.05 Server CLR version v4.0.30319 loaded. 2019-11-01 22:37:07.11 spid5s Resource governor reconfiguration succeeded. 2019-11-01 22:37:07.11 spid5s SQL Server Audit is starting the audits. This is an informational message. No user action is required. 2019-11-01 22:37:07.11 spid5s SQL Server Audit has started the audits. This is an informational message. No user action is required. 2019-11-01 22:37:07.12 Server Common language runtime (CLR) functionality initialized using CLR version v4.0.30319 from C:\Windows\Microsoft.NET\Framework64\v4.0.30319\. 2019-11-01 22:37:07.14 spid5s SQL Trace ID 1 was started by login "sa". 2019-11-01 22:37:07.14 spid5s Server name is 'machine\SQL2012'. This is an informational message only. No user action is required. 2019-11-01 22:37:07.16 spid13s The certificate [Cert Hash(sha1) "3605E5C22E9EA14834F0C8069ED7EFE9437F6DA0"] was successfully loaded for encryption. 2019-11-01 22:37:07.16 spid5s Failed to verify Authenticode signature on DLL 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Binn\ftimport.dll'. 2019-11-01 22:37:07.16 spid5s Starting up database 'msdb'. 2019-11-01 22:37:07.16 spid9s Starting up database 'mssqlsystemresource'. 2019-11-01 22:37:07.16 spid13s Server is listening on [ 'any' <ipv6> 58026]. 2019-11-01 22:37:07.16 spid13s Server is listening on [ 'any' <ipv4> 58026]. 2019-11-01 22:37:07.16 spid13s Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\SQL2012 ]. 2019-11-01 22:37:07.16 spid13s Server named pipe provider is ready to accept connection on [ \\.\pipe\MSSQL$SQL2012\sql\query ]. 2019-11-01 22:37:07.16 Server Server is listening on [ ::1 <ipv6> 58027]. 2019-11-01 22:37:07.16 Server Server is listening on [ 127.0.0.1 <ipv4> 58027]. 2019-11-01 22:37:07.16 Server Dedicated admin connection support was established for listening locally on port 58027. 2019-11-01 22:37:07.16 spid9s The resource database build version is 11.00.7462. This is an informational message only. No user action is required. 2019-11-01 22:37:07.17 spid13s SQL Server is now ready for client connections. This is an informational message; no user action is required. 2019-11-01 22:37:07.17 Server SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. This is an informational message. No user action is required. 2019-11-01 22:37:07.18 spid9s Starting up database 'model'. 2019-11-01 22:37:07.19 spid5s 1 transactions rolled forward in database 'msdb' (4:0). This is an informational message only. No user action is required. 2019-11-01 22:37:07.19 spid5s 0 transactions rolled back in database 'msdb' (4:0). This is an informational message only. No user action is required. 2019-11-01 22:37:07.20 Server The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/BDTUK178AA.bluedoor.local:SQL2012 ] for the SQL Server service. Windows return code: 0x200b, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered. 2019-11-01 22:37:07.20 Server The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/machine ] for the SQL Server service. Windows return code: 0x200b, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered. 2019-11-01 22:37:07.20 spid9s Clearing tempdb database. 2019-11-01 22:37:07.23 spid9s Error: 5171, Severity: 16, State: 1. 2019-11-01 22:37:07.23 spid9s C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\tempdb.mdf is not a primary database file. 2019-11-01 22:37:07.23 spid9s Error: 1802, Severity: 16, State: 4. 2019-11-01 22:37:07.23 spid9s CREATE DATABASE failed. Some file names listed could not be created. Check related errors. 2019-11-01 22:37:07.23 spid9s 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. 2019-11-01 22:37:07.23 spid9s SQL Server shutdown has been initiated 2019-11-01 22:37:07.23 spid9s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
請注意您引用的錯誤消息的最後一部分：
.. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.如果您查看與 tempdb 創建相關的整個錯誤塊：
2019-11-01 22:37:07.20 spid9s Clearing tempdb database. 2019-11-01 22:37:07.23 spid9s Error: 5171, Severity: 16, State: 1. 2019-11-01 22:37:07.23 spid9s C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\tempdb.mdf is not a primary database file. 2019-11-01 22:37:07.23 spid9s Error: 1802, Severity: 16, State: 4. 2019-11-01 22:37:07.23 spid9s CREATE DATABASE failed. Some file names listed could not be created. Check related errors. 2019-11-01 22:37:07.23 spid9s 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.第一個錯誤似乎是問題所在：
...\tempdb.mdf is not a primary database file.您應該嘗試刪除該
tempdb.mdf文件，然後重新啟動 SQL Server 服務。當您在那裡時，檢查是否有tempdb.ldf文件並刪除該文件。如果 tempdb 數據文件不存在，SQL Server 將重新創建它們。