May 16, 2012 at 2:56 am
Hi,
In one of our server one drive is down which contains the ldf file of tempdb. But other drives are up which has master, model and msdb files. SQL server fails to start showing message tempdb ldf file is inaccessible where I can see other logs which says master database and other databases started successfully. This really looks strange to me. Any idea why this happen and remedy for this?
I checked the properties of SQL server services and I can see masterdb and error log files are in C drive.
Ryan
//All our dreams can come true, if we have the courage to pursue them//
May 16, 2012 at 3:00 am
I think you would have to start SQL in single_user or minimal config mode and then issue a number of alter database commands to alter where it thinks TempDB lives to a drive which is actually online, then restart SQL in multi user mode
http://msdn.microsoft.com/en-us/library/ms190737.aspx
You would need the -f or the -m switch
May 16, 2012 at 3:07 am
ive found the easiest way to do this is to mount a new drive with the same drive letter as the failed one.
then boot sql server and modify the tempdb location
then drop the newly mounted drive and put your failed drive back in place and repair it (if it can be repaired)
MVDBA
May 16, 2012 at 3:12 am
I would also look at your drive configuration after it and see if your current config / RAID levels are sufficient enough to support failure of hardware to hopefully mitigate the marjoirty of risk in failing hardware
May 16, 2012 at 3:14 am
Hi Anthony,
I have tried with single user mode but it does not work.
C:\Windows\system32>NET START MSSQLSERVER /m
The SQL Server (MSSQLSERVER) service is starting.
The SQL Server (MSSQLSERVER) service could not be started.
A service specific error occurred: 1814.
More help is available by typing NET HELPMSG 3547.
Hi Michael ,
I think that is good option to do it.
Ryan
//All our dreams can come true, if we have the courage to pursue them//
May 16, 2012 at 3:17 am
what about with the /f switch which should just start the MASTER database to which you should then be able to execute the alter database statements
http://msdn.microsoft.com/en-us/library/ms345408%28v=SQL.100%29.aspx
May 16, 2012 at 3:22 am
ryan - i beleive that is your only way - sql is trying to create the tempdb files when it starts and it can't, so it shuts down.
as far as i know you can only reconfigure the location of tempdb while sql server is running.
MVDBA
May 16, 2012 at 3:24 am
anthony.green (5/16/2012)
what about with the /f switch which should just start the MASTER database to which you should then be able to execute the alter database statementshttp://msdn.microsoft.com/en-us/library/ms345408%28v=SQL.100%29.aspx
that looks interesting
MVDBA
May 16, 2012 at 3:29 am
Hi Anthony
This works fine. Thanks a lot.
Ryan
//All our dreams can come true, if we have the courage to pursue them//
May 16, 2012 at 3:41 am
Not a problem, that got me out of a number of holes where our Junior DBA issued a modify file which had a space at the beginning of the file name like this "C:\sql\data\ templog.ldf", obviously you cant have a space as the first character so SQL wouldnt start up, so I knew it could be done, just couldnt remember if it would of worked with /m over /f.
Now the topic moves to how to do prevent this from happening again due to a drive failure, hot spares, bigger RAID arrays, different RAID levels.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply