How to create new tempDB is tempDB is currepted or marked as suspect [ All Languages » SQL Server »  Server Operations] Total Hit ( 3147) Rate this article:     Poor     Excellent   Submit Your Question/Comment about this article Rating

The following steps explain how you can create a new tempdb database. With this workaround, you are able to successfully start the SQL Server service. If they exist, rename the current Tempdb.mdf and Templog.ldf files. If the files do not exist, that is one possible reason for the suspect status, the files are missing. Start SQL Server from a command prompt by using the following command: Click here to copy the following block sqlservr -c -f -T3608 -T4022 For SQL Server 2000 named instances you have to add the additional -s parameter. On a SQL Server 7.0 cluster, you must first run this statement: Click here to copy the following block set _CLUSTER_NETWORK_NAME=YourSQLVirtualName This will allow SQL Server to start from a command prompt. Note Make sure that the command prompt window remains open after SQL Server starts. Closing the command prompt window terminates the SQL Server process. Connect to the server by using Query Analyzer, and then use the following stored procedure to reset the status of the tempdb database. Click here to copy the following block exec master..sp_resetstatus Tempdb Shut down the server by pressing CTRL+C in the command prompt window. Restart the SQL Server service. This creates a new set of tempdb database files, and recovers the tempdb database.

Related Article(s)

Submitted By : Nayan Patel  (Member Since : 5/26/2004 12:23:06 PM)
	Job Description : He is the moderator of this site and currently working as an independent consultant. He works with VB.net/ASP.net, SQL Server and other MS technologies. He is MCSD.net, MCDBA and MCSE. In his free time he likes to watch funny movies and doing oil painting.
View all (893) submissions by this author  (Birth Date : 7/14/1981 )