SQL Server Performance Forum – Threads Archive

TEMP DB CORRUPT

How to start SQL server when Tempdb is corrupt? krishna chaitanya.s
project engineer-SQLDBA

I know only one way i.e. to restart the machine or restart SQL Services manually to create tempdb again if its corrupt.

Try to start in sinlge-user mode first and you must try to check what are the causes for that TEMPDB corruption.
Check under SQL error log and event viewer log for more information. Make sure TEMPDB is resized as per your requirement, as when you start it will resize to its previous size. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Satya,
I have tried to change the TEMPDB size into its maximum+1 GB size to get the benifit of performance.
But when the service restarts, it comes it 9 MB (8MB for Data and 1MB for Log). Is there any way to maintain a fixed size for my TEMPDB, which should not resize into its default? -Johnson

IT is a default setting in SQL server to rest it to original size when services are restarted, you must take care of TEMPDB when you do a restart next time. Set the original size of the tempdb database files to a reasonable size to avoid the files from automatically expanding as more space is needed. If the tempdb database expands too frequently, performance can be affected.
Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Hi Satya,
Even i am interested in knowing how to change the default setting so that everytime SQL services gets started it should have default size of 1GB.

http://www.sql-server-performance.com/tempdb.asp
http://www.aspfaq.com/show.asp?id=2446
http://www.windowsitpro.com/Article/ArticleID/45154/45154.html
http://www.windowsitpro.com/SQLServer/Article/ArticleID/39158/39158.html Fyi. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I started sql server in sinlge-user mode.
I checked the errorlog for info…some disk error is there…i have to check if any hardware problems r there.. thnx for ur suggestions…
krishna chaitanya.s
project engineer-SQLDBA

Then better to move the TEMPDB from that troubled drive and make sure to rectify all the disk problems by checking with vendor. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>