Can't start SQL Server 2008 - weird path in the log

Question

I can't start it and the log file reads:

 CREATE FILE encountered operating system error 21(failed to retrieve text for 
 this error. Reason: 15100) while attempting to open or create the physical file 
 'I:\MSSQL \TempDB\tempdb.mdf'.`

Server installed by default to
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER

Why does it try to create temp DB on the I: drive? how can I change it?

Answer 1

You could also try using the Trace Flag /T3608 and /f switch to start in minimal configuration with only the master database.

Some examples in the below links.

http://msdn.microsoft.com/en-us/library/ms345408.aspx

http://support.microsoft.com/kb/224071

Answer 2

Temporarily create an I:, and create folders on there to let it start. Then go into the properties of tempdb (using Management Studio) and change the file locations.

Answer 3

system error 21 means "The device is not ready". Look for I/O related errors in the System Event Log for the I drive.

Answer 4

It sounds like your initial install of mssql server was done to the I drive. I hope you didn't wipe it out... fixing it can be difficult. Here are few things to try...

1) If you don't have an I drive anymore, create one temporarily using subst:

subst i: c:\some-path-you-like

& try and start the server. Once you can get the server started, you can re-map the tempdb to a different directory by doing this:

use master
go
Alter database tempdb modify file (name = tempdev, filename = 'C:\Sqldata\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'C:\Sqldata\templog.ldf')
Go

After this, you should be good-to-go. You can then remove the subst'd drive by doing this:

subst i: /D

It's also possible that your master database & log and error log are still on the I drive as well, and those can also be moved... but a little differently. You'll need to modify the mssql service startup-parameters to include 3 additional options:

-d is the fully qualified path for the master database data file.
-e is the fully qualified path for the error log file.
-l is the fully qualified path for the master database log`

i.e. -l c:\wherever\mastlog.ldf etc...