THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

Error: 5123, Severity: 16, State: 1 when moving TempDB

I have seen the problem for this post twice in the forums in the last month and both times the way it was fixed was not documented as supported method, though in a desperate "production down" scenario I probably would have done the same thing to get my server back online. The biggest problem in both of the posts is that they are not backing up their system databases, which would make me also suspect that they are not backing up their user databases, or validating their backups.

Moved TempDB but forgot to add the name of the MDF and LDF at the end of the file path

move tempdb file location to a different drive - New Problem and Solution

To move tempdb is a fairly simple task, that can very easily be done incorrectly, which will cause the SQL server to not start up the next time it is restarted, which is generally immediately to put the file moves for tempdb into place. To start off with get the file path information for the current configuration of tempdb, you will need this to fall back to if you have a problem:

SELECT name, physical_name 
FROM sys.database_files

On my laptop which runs SQL 2005 Developer Edition, SQL 2005 Express, SQL 2008 Developer Edition, and SQL 2008 Express, this outputs the following for my SQL 2005 Developer Edition instance:

name physical_name
tempdev c:\program files\microsoft sql server\mssql.2\mssql\data\tempdb.mdf
templog c:\program files\microsoft sql server\mssql.2\mssql\data\tempdb.ldf

To move the files, you simply use ALTER DATABASE as follows:

ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev'FILENAME = 'c:\program files\microsoft sql server\mssql.2\mssql\sqldata\tempdb.mdf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'templog'FILENAME = 'c:\program files\microsoft sql server\mssql.2\mssql\sqldata\tempdb.ldf')

To break my instance I am going to omit the file names and only provide the path, which is what was done in both of the posts that inspired this tread:

ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev'FILENAME = 'c:\program files\microsoft sql server\mssql.2\mssql\sqldata\')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'templog'FILENAME = 'c:\program files\microsoft sql server\mssql.2\mssql\sqldata\')

This will output the following result:

The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.

You can rerun the above query to validate the change occured, but it won't take effect until you restart the service, so I went ahead and restarted my SQL Instance and it fails as expected with the following error in the error log:

Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86)
Mar 23 2007 16:28:52
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

(c) 2005 Microsoft Corporation.
All rights reserved.
Server process ID is 12548.
Authentication mode is MIXED.
Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\LOG\ERRORLOG'.
This instance of SQL Server last reported using a process ID of 6184 at 1/6/2009 9:04:39 PM (local) 1/7/2009 2:04:39 AM (UTC). This is an informational message only; no user action is required.
Registry startup parameters:
-d C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\master.mdf
-e C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\LOG\ERRORLOG
-l C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\mastlog.ldf
-g 512
SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
Detected 2 CPUs. This is an informational message; no user action is required.
Set AWE Enabled to 1 in the configuration parameters to allow use of more memory.
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.
The time stamp counter of CPU on scheduler id 1 is not synchronized with other CPUs.
Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
The Microsoft Distributed Transaction Coordinator (MS DTC) service could not be contacted. If you would like distributed transaction functionality, please start this service.
Database mirroring has been enabled on this instance of SQL Server.
Starting up database 'master'.
Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
CHECKDB for database 'master' finished without errors on 2008-05-10 01:30:00.483 (local time). This is an informational message only; no user action is required.
SQL Trace ID 1 was started by login "sa".
Starting up database 'mssqlsystemresource'.
The resource database build version is 9.00.3042. This is an informational message only. No user action is required.
Server name is 'LT-JKEHAYIAS'. This is an informational message only. No user action is required.
Starting up database 'model'.
CHECKDB for database 'model' finished without errors on 2008-05-10 01:30:19.403 (local time). This is an informational message only; no user action is required.
Clearing tempdb database.
Error: 5123, Severity: 16, State: 1.
CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file 'c:\program files\microsoft sql server\mssql.2\mssql\data\'.
Error: 17207, Severity: 16, State: 1.
FCB::Open: Operating system error 3(The system cannot find the path specified.) occurred while creating or opening file 'c:\program files\microsoft sql server\mssql.2\mssql\data\'. Diagnose and correct the operating system error, and retry the operation.
Error: 17204, Severity: 16, State: 1.
FCB::Open failed: Could not open file c:\program files\microsoft sql server\mssql.2\mssql\data\ for file number 1. OS error: 3(The system cannot find the path specified.).
Error: 5120, Severity: 16, State: 101.
Unable to open the physical file "c:\program files\microsoft sql server\mssql.2\mssql\data\". Operating system error 3: "3(The system cannot find the path specified.)".
Error: 1802, Severity: 16, State: 4.
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
Error: 5123, Severity: 16, State: 1.
CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file 'c:\program files\microsoft sql server\mssql.2\mssql\data\'.
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.
SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

I bold underlined the important information from the log file for why SQL Server failed to start. So now that we can't get into SQL Server how do we fix it. Well it isn't all that difficult to do, but you have to drop to the command prompt to do it. First open the command prompt by running cmd in the Run box:

Then change directories to the Binn directory under your SQL Instances path:

Then run sqlservr with the -c and -f startup parameters which will start SQL Server in minimal configuration mode.

image 

NOTE: Do not use any other startup parameters or Trace Flags as these can cause SQL to try to create tempdb from the settings that are wrong and again fail to start.

When you start SQL Server from the command prompt it will spool the log information out to the command prompt screen. When it shows Recovery is complete the SQL Server Instance is running in single user mode and can be connected to through SSMS or sqlcmd.

 image

Once you connect you can then run the correct ALTER DATABASE scripts to fix the tempdb path.

ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev'FILENAME = 'c:\program files\microsoft sql server\mssql.2\mssql\data\tempdb.mdf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'templog'FILENAME = 'c:\program files\microsoft sql server\mssql.2\mssql\data\tempdb.ldf')

Once this has been run, you can close the SQL Server Instance running in the command prompt by pressing Ctrl+C with the window active.  Then restart the SQL Service from the Services.msc snapin or the Computer Management Console and you should be back in business.

Published Tuesday, January 06, 2009 8:15 AM by Jonathan Kehayias
Filed under:

Comments

 

The Rambling DBA: Jonathan Kehayias said:

This is a somewhat continuation post from my post Error: 5123, Severity: 16, State: 1 when moving TempDB

January 22, 2009 1:09 PM
 

Vandya said:

Hi,

I am testing a disaster situation on SQL 2005 when TempDB is not available due to the loss of the drive it resides on. SQL cannot be started at this point. Basically, I was doing the same steps that you described in your posting.

So I tried to start in the single-user mode using "net start MSSQLSERVER -c -f" command. The SQL started successfully,  I managed to open SSMS and tried to run the ALTER statements to move tempDB to the available location. At this point I get an error message "Error: 18461, Severity: 14, State: 1. Login failed for user 'AdminAccountName'. Reason: Server is in single user mode. Only one administrator can connect at this time. Object Explorer is closed at this point. And I am an administrator on this machine.

I would appreciate any response.

March 20, 2009 4:07 PM
 

Tyrel said:

Just in case anyone gets the same error

"Error: 18461, Severity: 14, State: 1. Login failed for user 'AdminAccountName'. Reason: Server is in single user mode. Only one administrator can connect at this time. Object Explorer is closed at this point. And I am an administrator on this machine.

Shut down all you SQL services on than the browser service, then run the command again.

March 26, 2010 1:17 PM
Anonymous comments are disabled

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement