Archive

Archive for the ‘Trace flag’ Category

Could not find database ID 2, name ‘tempdb’. The database may be offline

September 14, 2013 Leave a comment

 

 

 

one of my SQL server .. has been logging in ERRORS related to Tempdb been unavailable during Startup and after few seconds everything

 

seems to be working fine .. i assumed that its related to drives holding the TempDB was going offline or it was not accessible when the SQL

 

was coming online .. below is the error message 

 

ERROR :

 

013-09-10 06:36:22.850 spid55 Could not find database ID 2, name ‘tempdb’. The database may be offline. Wait a few minutes and try again. 2013-09-10 06:36:22.980 spid9s Starting up database ‘tempdb’.

 

After some time the SQL would recover normally .. without any furthur errors or similar messages 

 

Solution :

 

i figured out that this is a known issue and does occur in SQL 2000 and 2005 (could also be a possiblity in higher versions too)

 

below is what is causing this 

 

Application like  Biztalk or web Farm keep trying to connect to SQL every second while the sql is starting up .. 

 

and they try to Create objects in tempdb .. from the very go … as TEMPDB is been refreshed and not accessible  above mentioned messages

 

are logged in SQL error log .. (which can lead to DBA’s looking for reason from drive prespective)

 

–> i have used TRACE FLAG 3614 to cause the SSNETLIB.dll (SQL server network library) to be loaded only after successful recovery of

 

system databases and hance above messages are not logged in SQL error log .. 

 

refer : http://support.microsoft.com/kb/834798

 

 

 

Advertisements