Welcome to MSDN Blogs Sign in | Join | Help

Moving the tempdb to unknown location

One of my friends called me for a problem at a customer site. The problem simple is that the customer wanted to move the tempdb in SQL Server 2005 to a new location but because of a typo the new path of the file is pointing to a folder not a file. Something like this "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\"

The alter statement would be something like this

alter database tempdb

modify file (name=tempdev, fileName='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\')

The result of this statement would be

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

So next time SQL Server tries to access the tempdb it will fail and the problem comes worse if you restarted the SQL Server, the Server won't start and this message will be logged in the Application Event log

FCB::Open: Operating system error 3(error not found) occurred while creating or opening file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\'. Diagnose and correct the operating system error, and retry the operation.

So what would be the solution of something like this?

I've tried this scenario and worked, I thought it would be better if I published it so if someone faced this situation (although, it's very rare situation) it would help.

  • First we need to start SQL Server with minimum configuration in console mode; go the SQL Server binary folder (commonly in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn)
  • Open a console window and run this command

    sqlservr.exe –c –f –m

So this starts SQL Server in console mode (not a service) and with minimum configuration and also with single user mode. You should see some messages like you see when you open the error log file

  • Open another console window and run this command

    Sqlcmd /A

    This will connect to the default instance on the machine using windows authentication though Dedicated Administrator Connection (DAC)

    After logging you can alter the tempdb to add the path of the correct file; like this.

alter database tempdb modify file (name=tempdev, fileName='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ tempdb.mdf')

Now return back to the first console window where you started the SQL Server and press Ctrl+C this will shutdown the server

Start SQL Server normally and the problem is now solved J

Published Friday, July 11, 2008 1:29 AM by Mohamed Sharaf
Filed under:

Comments

Thursday, July 10, 2008 8:00 PM by Pregnant Man » Moving the tempdb to unknown location

# Pregnant Man » Moving the tempdb to unknown location

PingBack from http://wordnew.acne-reveiw.info/?p=7393

Tuesday, January 13, 2009 9:25 AM by David Paskiet

# re: Moving the tempdb to unknown location

I have a named instance and I cannot get this towork.  Any suggestions?

Thursday, May 28, 2009 1:33 AM by harvest316

# re: Moving the tempdb to unknown location

Thanks Mo.  This worked!

(((Very big hug)))

You saved my bacon, buddy.

I tried moving my tempdb to a ramdisk, but MSSQL doesnt like tempdbs on non-local non-NTFS drives.

These are the kind of errors I was having:

2009-05-28 14:45:43.65 spid9s      Error: 5177, Severity: 16, State: 1.

2009-05-28 14:45:43.65 spid9s      An unexpected error occurred while checking the sector size for file 'R:\tempdb.mdf'. Move the file to a local NTFS volume, where the sector size can be retrieved. Check the SQL Server error log for more information.

2009-05-28 14:45:44.49 spid9s      Error: 1802, Severity: 16, State: 4.

2009-05-28 14:45:44.49 spid9s      CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

2009-05-28 14:45:44.49 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.

2009-05-28 14:45:44.49 spid9s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

2009-05-28 15:14:21.62 spid9s      Error: 823, Severity: 24, State: 6.

2009-05-28 15:14:21.62 spid9s      The operating system returned error 38(error not found) to SQL Server during a read at offset 0000000000000000 in file 'R:\tempdb.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

And in the System Event Log:

The SQL Server (MSSQLSERVER) service terminated with service-specific error 1814 (0x716).

Hope these error messages help others to find this page. :)

Anonymous comments are disabled
 
Page view tracker