SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,691 questions
Lost Drive with TempDB
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(32, 86%, 13%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ECM%3C/text%3E%3C/svg%3E)
Cindy Mikeworth
21
Reputation points
During a storm, we lost power and our RAID-5 drive where we housed our TempDB failed and is unrecoverable. We have since replaced the drives, reconfigured the RAID-5, and assigned the same logical drive letter. We have also created the folder where the original TempDB was saved.
SQL Server service is not starting and SSMS is not launching, and there are so many errors - pretty much all indicating the TempDB is not there - that I'm not sure where to go from here.
The drive has 836 GB free. All our MDB files are 76 GB. Our LDBs total 140 GB.
ERROR WHEN LAUNCHING SSMS:
===================================
Cannot connect to TASSQL3.
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, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 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
EVENT VIEWER APPLICATION LOG
FCB::Open failed: Could not open file F:\SQLTemp\tempdb.mdf for file number 1. OS error: 3(The system cannot find the path specified.).
Event ID 17204
CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file 'F:\SQLTemp\tempdb.mdf'.
Event ID 5123
ERRORLOG CONTENTS
2021-11-03 13:56:30.03 Server Microsoft SQL Server 2014 (SP3-GDR) (KB4532095) - 12.0.6118.4 (X64)
Dec 12 2019 21:46:15
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: )
2021-11-03 13:56:30.03 Server UTC adjustment: -4:00
2021-11-03 13:56:30.03 Server (c) Microsoft Corporation.
2021-11-03 13:56:30.03 Server All rights reserved.
2021-11-03 13:56:30.03 Server Server process ID is 1564.
2021-11-03 13:56:30.03 Server System Manufacturer: 'Dell Inc.', System Model: 'PowerEdge R730'.
2021-11-03 13:56:30.03 Server Authentication mode is MIXED.
2021-11-03 13:56:30.03 Server Logging SQL Server messages in file 'C:\SQL2014\MSSQL12.MSSQLSERVER\MSSQL\Log\ERRORLOG'.
2021-11-03 13:56:30.03 Server The service account is 'TAS-IN\SQLAdmin'. This is an informational message; no user action is required.
2021-11-03 13:56:30.03 Server Registry startup parameters:
-d C:\SQL2014\MSSQL12.MSSQLSERVER\MSSQL\DATA\master.mdf
-e C:\SQL2014\MSSQL12.MSSQLSERVER\MSSQL\Log\ERRORLOG
-l C:\SQL2014\MSSQL12.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
2021-11-03 13:56:30.03 Server Command Line Startup Parameters:
-s "MSSQLSERVER"
2021-11-03 13:56:30.24 Server SQL Server detected 2 sockets with 6 cores per socket and 12 logical processors per socket, 24 total logical processors; using 24 logical processors based on SQL Server licensing. This is an informational message; no user action is required.
2021-11-03 13:56:30.24 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2021-11-03 13:56:30.24 Server Detected 32668 MB of RAM. This is an informational message; no user action is required.
2021-11-03 13:56:30.24 Server Using conventional memory in the memory manager.
2021-11-03 13:56:30.32 Server Default collation: SQL_Latin1_General_CP1_CI_AS (us_english 1033)
2021-11-03 13:56:30.35 Server The maximum number of dedicated administrator connections for this instance is '1'
2021-11-03 13:56:30.35 Server This instance of SQL Server last reported using a process ID of 2368 at 11/3/2021 9:52:17 AM (local) 11/3/2021 1:52:17 PM (UTC). This is an informational message only; no user action is required.
2021-11-03 13:56:30.35 Server Node configuration: node 0: CPU mask: 0x0000000000000fff:0 Active CPU mask: 0x0000000000000fff:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2021-11-03 13:56:30.35 Server Node configuration: node 1: CPU mask: 0x0000000000fff000:0 Active CPU mask: 0x0000000000fff000:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2021-11-03 13:56:30.39 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.
2021-11-03 13:56:30.39 Server Lock partitioning is enabled. This is an informational message only. No user action is required.
2021-11-03 13:56:30.39 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.
2021-11-03 13:56:30.40 spid9s Starting up database 'master'.
2021-11-03 13:56:30.43 spid9s 23 transactions rolled forward in database 'master' (1:0). This is an informational message only. No user action is required.
2021-11-03 13:56:30.43 spid9s 0 transactions rolled back in database 'master' (1:0). This is an informational message only. No user action is required.
2021-11-03 13:56:30.46 Server CLR version v4.0.30319 loaded.
2021-11-03 13:56:30.51 Server Common language runtime (CLR) functionality initialized using CLR version v4.0.30319 from C:\Windows\Microsoft.NET\Framework64\v4.0.30319.
2021-11-03 13:56:30.52 spid9s CHECKDB for database 'master' finished without errors on 2021-10-23 01:00:05.177 (local time). This is an informational message only; no user action is required.
2021-11-03 13:56:30.52 spid9s Resource governor reconfiguration succeeded.
2021-11-03 13:56:30.52 spid9s SQL Server Audit is starting the audits. This is an informational message. No user action is required.
2021-11-03 13:56:30.53 spid9s SQL Server Audit has started the audits. This is an informational message. No user action is required.
2021-11-03 13:56:30.55 spid9s SQL Trace ID 1 was started by login "sa".
2021-11-03 13:56:30.56 spid9s Server name is 'TASSQL3'. This is an informational message only. No user action is required.
2021-11-03 13:56:30.58 spid16s A self-generated certificate was successfully loaded for encryption.
2021-11-03 13:56:30.58 Server Software Usage Metrics is disabled.
2021-11-03 13:56:30.58 spid16s Server is listening on [ 'any' <ipv6> 1433].
2021-11-03 13:56:30.58 spid16s Server is listening on [ 'any' <ipv4> 1433].
2021-11-03 13:56:30.58 spid16s Server local connection provider is ready to accept connection on [ \.\pipe\SQLLocal\MSSQLSERVER ].
2021-11-03 13:56:30.58 spid16s Server local connection provider is ready to accept connection on [ \.\pipe\sql\query ].
2021-11-03 13:56:30.58 Server Server is listening on [ ::1 <ipv6> 1434].
2021-11-03 13:56:30.58 Server Server is listening on [ 127.0.0.1 <ipv4> 1434].
2021-11-03 13:56:30.58 Server Dedicated admin connection support was established for listening locally on port 1434.
2021-11-03 13:56:30.58 spid16s SQL Server is now ready for client connections. This is an informational message; no user action is required.
2021-11-03 13:56:30.59 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.
2021-11-03 13:56:30.60 Server The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/TASSQL3.tas-in.com ] for the SQL Server service.
2021-11-03 13:56:30.60 Server The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/TASSQL3.tas-in.com:1433 ] for the SQL Server service.
2021-11-03 13:56:30.76 spid21s A new instance of the full-text filter daemon host process has been successfully started.
2021-11-03 13:56:30.78 spid30s Starting up database 'ReportServer'.
2021-11-03 13:56:30.78 spid25s Starting up database 'msdb'.
2021-11-03 13:56:30.78 spid35s Starting up database 'HIST2'.
2021-11-03 13:56:30.78 spid32s Starting up database 'DYNAMICS'.
2021-11-03 13:56:30.78 spid39s Starting up database 'INC'.
2021-11-03 13:56:30.78 spid33s Starting up database 'DEMO_MSCRM'.
2021-11-03 13:56:30.78 spid34s Starting up database 'HIST'.
2021-11-03 13:56:30.78 spid12s Starting up database 'mssqlsystemresource'.
2021-11-03 13:56:30.78 spid26s Starting up database 'TotalAccountingSolutions_MSCRM'.
2021-11-03 13:56:30.79 spid28s Starting up database 'CRMDEMO_MSCRM'.
2021-11-03 13:56:30.79 spid42s Starting up database 'TEST'.
2021-11-03 13:56:30.79 spid38s Starting up database 'ManagementReporter'.
2021-11-03 13:56:30.79 spid27s Starting up database 'ReportServerTempDB'.
2021-11-03 13:56:30.79 spid36s Starting up database 'TWO'.
2021-11-03 13:56:30.79 spid40s Starting up database 'VPA'.
2021-11-03 13:56:30.79 spid41s Starting up database 'TEMP'.
2021-11-03 13:56:30.79 spid31s Starting up database 'MSCRM_CONFIG'.
2021-11-03 13:56:30.79 spid29s Starting up database 'SANDBOX_MSCRM'.
2021-11-03 13:56:30.79 spid37s Starting up database 'TAS'.
2021-11-03 13:56:30.82 spid12s The resource database build version is 12.00.6118. This is an informational message only. No user action is required.
2021-11-03 13:56:30.91 spid12s Starting up database 'model'.
2021-11-03 13:56:30.97 spid12s CHECKDB for database 'model' finished without errors on 2021-10-23 01:00:07.937 (local time). This is an informational message only; no user action is required.
2021-11-03 13:56:30.97 spid12s Clearing tempdb database.
2021-11-03 13:56:30.97 spid12s Error: 5123, Severity: 16, State: 1.
2021-11-03 13:56:30.97 spid12s CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file 'F:\SQLTemp\tempdb.mdf'.
2021-11-03 13:56:30.98 spid12s Error: 17204, Severity: 16, State: 1.
2021-11-03 13:56:30.98 spid12s FCB::Open failed: Could not open file F:\SQLTemp\tempdb.mdf for file number 1. OS error: 3(The system cannot find the path specified.).
2021-11-03 13:56:30.98 spid12s Error: 5120, Severity: 16, State: 101.
2021-11-03 13:56:30.98 spid12s Unable to open the physical file "F:\SQLTemp\tempdb.mdf". Operating system error 3: "3(The system cannot find the path specified.)".
2021-11-03 13:56:30.98 spid12s Error: 1802, Severity: 16, State: 4.
2021-11-03 13:56:30.98 spid12s CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
2021-11-03 13:56:30.98 spid12s 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.
2021-11-03 13:56:30.98 spid12s SQL Server shutdown has been initiated
2021-11-03 13:56:30.98 spid12s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
2021-11-03 13:56:30.98 Logon Error: 18456, Severity: 14, State: 38.
2021-11-03 13:56:30.98 Logon Login failed for user 'TAS-IN\CRMService1'. Reason: Failed to open the explicitly specified database 'MSCRM_CONFIG'. [CLIENT: 192.168.0.206]
2021-11-03 13:56:30.98 Logon Error: 18456, Severity: 14, State: 38.
2021-11-03 13:56:30.98 Logon Login failed for user 'TAS-IN\CRMService1'. Reason: Failed to open the explicitly specified database 'MSCRM_CONFIG'. [CLIENT: 192.168.0.206]
2021-11-03 13:56:30.98 Logon Error: 18456, Severity: 14, State: 38.
2021-11-03 13:56:30.98 Logon Login failed for user 'TAS-IN\SQLAdmin'. Reason: Failed to open the explicitly specified database 'ReportServer'. [CLIENT: 169.254.60.229]
2021-11-03 13:56:30.98 spid16s The SQL Server Network Interface library successfully deregistered the Service Principal Name (SPN) [ MSSQLSvc/TASSQL3.tas-in.com ] for the SQL Server service.
2021-11-03 13:56:30.98 spid16s The SQL Server Network Interface library successfully deregistered the Service Principal Name (SPN) [ MSSQLSvc/TASSQL3.tas-in.com:1433 ] for the SQL Server service.
Accepted answer
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(67.2, 1%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETP%3C/text%3E%3C/svg%3E)
Tom Phillips 17,716 Reputation points2021-11-03T19:13:19.287+00:00 -
Cindy Mikeworth 21 Reputation points
2021-11-04T00:42:25.323+00:00 Thx so much for the assist @Tom Phillips !
Your link got me where I needed to be. Assorted challenges and discoveries along the way.
For those coming after me that may like some tips:
- I installed to an atypical folder structure, so had to move to the containing folders for the commands. Required some of my ancient DOS skills.
- Then when I tried to run the startup command, I kept getting errors for SQL services running under domain accounts other than my login (because only one admin user login was allowed). Not my immediate path, but I would recommend shutting down other servers to take those out of the picture and change the accounts on your SQL Server to your login account.
- My final syntax: sqlservr -c -f -T3608 -T4022 -sMSSQLSERVER -m"SQLCMD"
- Then it wasn't terribly clear when the service was running outside that it stopped executing lines. I couldn't interact with the focus. I ended up opening a second command prompt (don't close the first). The temp databases were created without pause.
Good Luck!
Sign in to comment -
1 additional answer
Sort by: Most helpful
-
Erland Sommarskog 100.9K Reputation points MVP2021-11-03T22:08:45.5+00:00 Unable to open the physical file "F:\SQLTemp\tempdb.mdf". Operating system error 3: "3(The system cannot find the path specified.)".
So I assume that there is an F: drive. But did you create the SQLTemp folder?
The error you get in SSMS is due to the fact that SQL Server does not start. (Or rather starts, but gives up then tempdb can be created.)