Lesson of the day
Jan. 8th, 2009 09:47 pm![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png){kind=small}
johnpalmerIf, for some reason, your SQL Server is not starting, due to an inability to create TempDB on a particular disk, here's what you should do.
Start SQL Server in minimal mode.
That's (from a command prompt):
sqlservr.exe -f
(Note: this is "minimal configuration mode", which includes putting the server in single user mode. -m is the switch for ordinary single user mode. But -f only allows one user to connect, so make sure that no one else even contemplates attaching to it. Use of the LART or, for hard cases, a cutlass, can help here.)
Then, open a new command window, connect via SQLCMD.EXE and key in
ALTER DATABASE TEMPDB MODIFY FILE(NAME=TEMPDEV,filename='(a path and filename)')
GO
ALTER DATABASE TEMPDB MODIFY FILE(NAME=TEMPLOG,filename='(a path and filename)')
GO
-- the default filenames are tempdb.mdf and templog.ldf. Use them, or make sure everyone knows
-- you didn't, and knows what you used. It's easier just to use the defaults.
This will set SQL to create TEMPDB on the specified disk.
Cycle SQL. Everything should be fine... or, at least, TempDB should now be set to be created in a location where it can be created.
Don't do anything else.
(Unless, of course, you know of another method of setting the tempdb location safely. In which case, sure, you can do that, but you probably know darn well why I'm saying "don't do anything else".)
Start SQL Server in minimal mode.
That's (from a command prompt):
sqlservr.exe -f
(Note: this is "minimal configuration mode", which includes putting the server in single user mode. -m is the switch for ordinary single user mode. But -f only allows one user to connect, so make sure that no one else even contemplates attaching to it. Use of the LART or, for hard cases, a cutlass, can help here.)
Then, open a new command window, connect via SQLCMD.EXE and key in
ALTER DATABASE TEMPDB MODIFY FILE(NAME=TEMPDEV,filename='(a path and filename)')
GO
ALTER DATABASE TEMPDB MODIFY FILE(NAME=TEMPLOG,filename='(a path and filename)')
GO
-- the default filenames are tempdb.mdf and templog.ldf. Use them, or make sure everyone knows
-- you didn't, and knows what you used. It's easier just to use the defaults.
This will set SQL to create TEMPDB on the specified disk.
Cycle SQL. Everything should be fine... or, at least, TempDB should now be set to be created in a location where it can be created.
Don't do anything else.
(Unless, of course, you know of another method of setting the tempdb location safely. In which case, sure, you can do that, but you probably know darn well why I'm saying "don't do anything else".)
