2

In quite a bad position here. This afternoon on my tempDB I ran the query: sp_helpfile to locate where my MDF and LOG file were. My MDF was on my C: drive and my LOG file was on a D: drive so I've run the following query to move this Log file from the D: drive back to C: drive. (This may not have been exactly the query I run as I don't remember what it was precisely!)

use master

go

Alter database tempdb modify file (name = templog, filename = 'C:[drivelocation]\tempdb.ldf')

go

After running, I closed down SQL Server MS 2012 and reopened to connect back to my SQL Server which is on my machine. When tried to log-in, SQL MS returns the following error:

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

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

Evidently, it seems it can't find the file no more! I've even tried starting the service in service manager but this states Windows can't start the service with an error code of 1814. Even a reboot of the machine fails to get it working.

Is there anyway in which I can fix this? I've been trying to see if I can change the log file location again back to the D: drive through CMD but can't seem to find anything

EDIT I've noticed this warning in event viewer when trying to start the service:

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.

That doesn't add up as there's at least 250GB free on the C: DRIVE

Edit 2

As requested, here is my error log

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.
Improve this question
4
  • Do you know how big the tempDB log file was before the restart? This How to Start SQL Server without TempDB might help.
    – SqlWorldWide
    Commented Nov 1, 2019 at 23:43
  • No more then 3MB. I've been trying to run those cases but I come across another error "Your SQL Server Installation is either corrupt or has been tampered with (Error getting instance ID from name). Please uninstall then re-run setup to correct this problem
    – Aiden
    Commented Nov 1, 2019 at 23:51
  • 1
    Have you checked the windows event log? And have you ensured that the account SQL Server runs under has write permissions in the destination you specified? And, just to confirm - you DID NOT move the existing tempdb files to that new location, did you?
    – Laughing Vergil
    Commented Nov 1, 2019 at 23:57
  • Check that the service account has permissions to the file. Just because you can see it, doesn't mean SQL server can.
    – James
    Commented Nov 3, 2019 at 14:52

2 Answers 2

Reset to default
5

Note the very last part of the error message you referenced:

.. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.

If you look at the entire chunk of errors related to tempdb creation:

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.

The first error seems like it may be the problem:

...\tempdb.mdf is not a primary database file.

You should try deleting that tempdb.mdf file, and restart the SQL Server service. While you're in there, check to see if there is a tempdb.ldf file and delete that one, too. SQL Server will recreate the tempdb data files if they do not already exist.

Improve this answer
1

Use the errorlog file instead of the eventlog, It is easier to see the chain of errors from the first one and to subsequent errors that resulted from that. Post the relevant ones here if you need to after reading below.

Tempdb is created according to your sys.master_files. This is the template for your tempdb regarding the files. If your sys.master_files specify something which isn't possible (like creating a file where there isn't space enough or a location that doesn't exist), then SQL server might not start. I think that there is some safety mechanism like if there are two or more of the same kind of file (data/log) and at least one can be created that SQL server will start - not 100% of the details here, though.

If tempdb can't be created as specified at startup, then you have to rectify that. Possibly by creating the folder, repairing the disk, adding diskspace etc. Or change what is in sys.master_files.

The thing is how to get SQL Server started in the first place. This is the trick: start your SQL Server using the -f switch. Among other things, it will created a "minimal tempdb" where your master.mdf is.

This might get you up an running so you can connect using sqlcmd.exe and fix your sys.master_files. As for managing your tempdb, I have some info blogged here: http://sqlblog.karaszi.com/managing-tempdb/.

Improve this answer
4
  • I've updated the question with my error log
    – Aiden
    Commented Nov 3, 2019 at 13:34
  • I've also posted some new steps I've tried with no luck too
    – Aiden
    Commented Nov 3, 2019 at 13:37
  • This is why it is so important to have as much info as possible. See AMtwo's reply. The errorlog makes it clear that there exists an mdf file for tempdb, but it is something else than a proper tempdb mdf file. Probably some other file was created or renamed/copied into this name. Hence the refusal to start. Just delete the tempdb files that might exist and SQL server will gladly create them at startup. As specified in sys.master_files (but you don't have to worry about that aspect if your SQL server starts fine by deleting the existing tempdb files).
    – Tibor Karaszi
    Commented Nov 3, 2019 at 16:31
  • I ended up getting it up by reallocating the files through CMD back to their D drive location and that got it working! I reckon deleting the files would have worked too so once I have this production database restored I'll give that a test. Thanks for your help
    – Aiden
    Commented Nov 3, 2019 at 22:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.