移动tempDB后无法连接到我的
在这里处境很糟糕。今天下午，在我的tempDB上，我运行了查询：sp_helpfile来定位我的MDF和日志文件所在的位置。我的MDF在我的C:驱动器上，我的日志文件在D:驱动器上，所以我运行了以下查询，将这个日志文件从D:驱动器移回C:驱动器。(这可能不是我所运行的查询，因为我不记得它到底是什么！)
use master
go
Alter database tempdb modify file (name = templog, filename = 'C:[drivelocation]\tempdb.ldf')
go运行后，我关闭了Server 2012并重新打开以连接到我的计算机上的Server。尝试登录时，SQL MS返回以下错误：
===================================无法连接到机器名\SQL2012 2012。===================================建立到Server连接时发生的与网络相关的或特定于实例的错误.找不到或无法访问服务器。验证实例名是否正确，以及Server是否配置为允许远程连接。(提供程序:命名管道提供者，错误: 40 -无法打开到Server的连接) (.Net SqlClient数据提供程序)单击：http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=2&LinkId=20476在System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options，DbConnectionPoolKey poolKey，Object poolGroupProviderInfo，DbConnectionPool池，DbConnection owningConnection，DbConnectionOptions userOptions)在System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection，DbConnectionPoolGroup poolGroup，DbConnectionPoolGroup userOptions)在System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection，#en3 20#1重试，en23#)在en3#，，(在System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1重试)在Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci，IServerType服务器上的System.Data.SqlClient.SqlConnection.Open()在Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser() ===================================上系统找不到指定的文件
显然，它似乎找不到文件了！我甚至尝试过在服务管理器中启动服务，但这表明Windows不能以1814的错误代码启动服务。即使重新启动这台机器也不能使它正常工作。
我有办法解决这个问题吗？我一直试图看看是否可以将日志文件的位置再次更改为D:驱动器通过CMD，但似乎找不到任何东西
编辑我注意到在尝试启动服务时事件查看器中有此警告：
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:驱动器上至少有250 on的空闲
Edit 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 (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' 58026].
2019-11-01 22:37:07.16 spid13s Server is listening on [ 'any' 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 58027].
2019-11-01 22:37:07.16 Server Server is listening on [ 127.0.0.1 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.回答 2
Database Administration用户
发布于 2019-11-03 14:13:56
请注意您引用的错误消息的最后一部分：
.. 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文件，并重新启动Server服务。当您在那里时，请检查是否有tempdb.ldf文件，并删除该文件。如果tempdb数据文件不存在，Server将重新创建这些文件。
Database Administration用户
发布于 2019-11-02 17:41:48
使用错误日志文件而不是事件日志，可以更容易地看到第一个错误的链以及由此导致的随后的错误。如果您需要阅读下面的文章，请在这里张贴相关的内容。
Tempdb是根据sys.master_files创建的。这是有关文件的tempdb模板。如果您的sys.master_files指定了一些不可能的内容(比如创建一个没有足够空间的文件或不存在的位置)，那么server可能不会启动。我认为存在一些安全机制，比如如果有两个或多个相同类型的文件(数据/日志)，并且至少可以创建一个SQL server启动的文件--但是这里的细节不是100%。
如果在启动时不能按指定的方式创建tempdb，那么您必须纠正这个问题。可能是通过创建文件夹、修复磁盘、添加磁盘空间等方式，或者更改sys.master_files中的内容。
首先，问题是如何启动Server。这就是诀窍:使用start开关实现Server的-f。除其他外，它将创建一个master.mdf所在的“最小的tempdb”。
这可能会使您启动运行，这样您就可以使用sqlcmd.exe连接并修复您的sys.master_files。至于管理你的tempdb，我在这里有一些信息：http://sqlblog.karaszi.com/managing-tempdb/。
https://dba.stackexchange.com/questions/252407
复制相似问题
腾讯云开发者
