Hello!!! Yes we can definitely start SQL service without Temp DB. Suppose you installed SQL server and specified the location of Temp DB. Now somebody stop the SQL services and changed the path of temp db at windows level with something that is not the actual path of temp db stored in the master database. Then what we will do. Will we be able to start the SQL service and change the location of temp db again?
Answer is YES. We can definitely do this. How!!!
We need to start SQL service in minimal configuration and single user mode. How we can do this!!! Here it is:
How to start SQL service with minimal configuration and in single user mode
Start SQL server configuration manager -> Go to SQL service -> Right click -> Go to Properties -> Click on Advanced Tab -> Scroll down and Go to Start up parameters. Enter –f and –m option just at starting. Something like as follows:
-f;-m;-dC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
Click on Apply -> Go to Service tab and start the service now. You can see it will be started successfully. Now you can connect to the SQL server.
Connect to SQL and issue following command to change alter the path of temp db with the correct value. Here is the SQL command to alter the path (change the path accordingly):
USE master
GoALTER DATABASE tempdb
MODIFY FILE (NAME = 'tempdev', FILENAME = 'F:\MSSQL\Data\tempdb.mdf');
Go
ALTER DATABASE tempdb
MODIFY FILE (NAME = 'templog', FILENAME = 'L:\MSSQL\Log\templog.ldf');
Go
Cool!!! Now go ahead and remove the –f and –m option from the start up parameters and start the SQL service again.
Now you are back in action. Just go and meet the person who actually changed the path of temp db after stopping the SQL service (I know no body does that but we need to be ready for all the situations).
Hope this helpful!!!!
Hi Ranjeet,
ReplyDeleteHope you are doing good. Good artical that I have learned how to start SQL Server with minimal configuration & single user mode.
But I sincerely did not understood why do you want to start the SQL Server with minimal configuration & single user mode just because someone has stopped the SQL Server and deleted or moved the tempDB files.
If anyone has did so I don't think we should have an issue because whenever the SQL Server is started it will create the new Data File & Log File for TempDB on the location defined in Master DB.
So there is no need to get your SQL Server in Single user mode rather just start the SQL Server Instance.
Please clear if I am wrong.
Thanks,
Vikas B Sahu.
Hi Sahu,
DeleteYou are absolutely correct if the location defined in the master db exists in the server. Think about the scenario when somebody removed (or hardware crash) the location which is defined in master database for tempdb. That time you will not be able to start SQL instance. The scenario which I mentioned is when somebody removes the tempdb location or it is gone due to hardware crash etc.
Yeah.. had similar issue on one of the production server. Thanks for your help.
Deletehttp://vikasbsahu.blogspot.in/
Can we start the SQL server without any other system databases apart from TEMP DB?
ReplyDeleteYes you can. You can restart the SQL without MSDB and MODEL but not without MASTER. Without master database SQL cannot be restarted. May be you need to use -T3608 startup parameter to start the SQL without MSDB and MODEL. This is something I have not tested yet but I will try to test this soon.
ReplyDelete