question

Shyam prasad avatar image
Shyam prasad asked

why sqlserver not up if tempdb is crashed..?

why sqlserver will not up if tempdb is crashed?
tempdb
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
What error message are you getting? What are you trying to do?
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
You might have answered your own question here - tempdb is a system database, if SQL cannot start it/open it/ access it, it would be unable to start the SQL service. I have never tried this (and don't have an environment at the minute to break), but presumably you are getting error log entries saying that tempdb is somewhat unavailable? Do you know why tempDB isn't available? Have you recently moved it to a location that no longer exists? Could the disk that holds tempdb be corrupted/damaged?
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tim avatar image
Tim answered
TEMPDB is recreated each time SQL Server Service is restarted. The only way I can think of that would prevent SQL Server from started due to tempdb is if you altered TEMPDB to move to another drive letter that doesn't exist, the location of TEMPDB became unavailable, or someone dropped the model database as it is needed to recreate TEMPDB as well. If you can post your exact error log we can more adequately assist you.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Blackhawk-17 avatar image
Blackhawk-17 answered
Check your errorlog, a text file under your instance's Log directory. See if it has any clues. In addition to the lost drive scenario supplied by @Kev Riley and @TRAD another possibility is that someone changed the ACLs on the file/folder/drive or, if you moved the location via a T-SQL command, perhaps it was to a location that the service did not have permissions to. If the SQL Server service doesn't have access to the file it can't open it. If it can't be opened SQL won't start. Depending on how your O/S was set up you may be able to check the Windows security event log. You may see access denied errors in there.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
Another option is if you've set the TempDB to be (re)created at a size that exceeds the free disk space on the drive. This is an unlikely scenario in a production environment, but could happen on a development machine... As others have said, though, without the exact error message, we can't really provide an accurate fix. Chances are that the error message will itself tell you what the problem is anyway...
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.