Wrong TempDB path

Question

Post reply

Wrong TempDB path

eloco1

SSC Veteran

Points: 274
More actions
August 1, 2008 at 10:43 am

#130431

Hi all,
I just made a huge error when moving the tempDB log file. I forgot to write the filename, this is what I wrote:
...
MODIFY FILE (NAME = templog, FILENAME = 'J:\MSSQL2005\Data');
...
The right syntax would be:
MODIFY FILE (NAME = templog, FILENAME = 'J:\MSSQL2005\Data\templog.ldf');
The problem is that SQL server reported a success and I restarted the service. Now the SQL service won't start and the eventlog reports something like: "CREATE FILE encountered operating system error 5(error not found) while attempting to open or create the physical file 'J:\MSSQL2005\Data'."
This is the default instance on a multi instanced failover cluster.
Anyone got an idea on how to fix this problem?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

TheRedneckDBA SSChampion Points: 14001 More actions · Answer 1

Can't you specify that in a startup parameter if you need to?

The Redneck DBA

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 2

Did you remove the old files for tempdb?

There is one post recently. See the link below. May it help.

http://www.sqlservercentral.com/Forums/Topic526254-146-1.aspx

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Try starting the server in minimal config. From the command prompt

sqlservr.exe -f

I think that may allow the service to start. I know there's one switch that allows SQL to start without any of the system databases other than master.

If it works, then you can try an alter database on tempDB.

No promises.

If you still have the tempDB files, the other option is to move them to the directory J:\MSSQL2005 and rename the log file to just data

It may require renaming or moving the directory Data it it exists.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 4

I think Gail is talking about trace flag -T3608. Try restarting sql server using -c -T3608 flag. This might allow you to change tempdb files location.

Manu

Perry Whittle SSC Guru Points: 233913 More actions · Answer 5

sounds like these guys had the problem as you, give it a shot

http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=2968469&SiteID=1

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

eloco1 SSC Veteran Points: 274 More actions · Answer 6

Thanks guys,

I started the service with "–s -f -c -T3609" parameters and then I could run queries. But now I have problems actually moving the templog file.

I ran the following:

USE MASTER;

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = templog, FILENAME = 'J:\MSSQL2005\Data\templog.ldf')

GO

And got this error message:

Msg 5121, Level 16, State 1, Line 1

The path specified by "J:\MSSQL2005\Data\templog.ldf" is not in a valid directory.

The directory "J:\MSSQL2005\Data" is valid, it's where the datafile resides.

Any ideas?

---

Think I got it now, ntfs permissions on the directory.. The service starts up normally now and the tempdb files are in the correct directory.

Thanks again for helping me out.

Chris Harrod SSC Veteran Points: 290 More actions · Answer 7

GilaMonster (8/1/2008)
Try starting the server in minimal config. From the command prompt
sqlservr.exe -f
I think that may allow the service to start. I know there's one switch that allows SQL to start without any of the system databases other than master.
If it works, then you can try an alter database on tempDB.
No promises.
If you still have the tempDB files, the other option is to move them to the directory J:\MSSQL2005 and rename the log file to just data
It may require renaming or moving the directory Data it it exists.

This got me out of trouble. Thanks heaps.

Use sqlservr.exe -f and then run the alter database properly with actual filenames for the tempdb.

murthy.sai SSC Rookie Points: 33 More actions · Answer 8

1.Start sql server in single user mode -

Net Start MSSQLServer /mSQLCMD /f/t3608

2.To be in single user mode sqlcmd -S. –E

3.Run the commands

Alter database tempdb modify file (name = tempdev, filename = 'E:\SQLDATA\DEFAULT\tempdb.mdf')

Alter database tempdb modify file (name = templog, filename = 'E:\SQLDATA\DEFAULT\Templog.ldf')

4.Stop sql server

Net stop mssqlserver

5. Start SQL Server normally