TEMPDB issue in SQL server

Dear All

Yesterday I have received call from DU team that they are getting error of TEMPDB

“The log file for database ‘tempdb’ is full. Back up the transaction log for the database to free up some log space.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.”

Here i would like to mention one note that tempdb is the very sensitive so if we do shrinking of log file it may cause data currption.

so i have used a trick , i have taken backup of tempdb on live server downloaded it on local system and attached and change the database mode in Simple and then given following command in Local SQL query analyser

 

 

 

DBCC SHRINKDATABASE tempdb TRUNCATEONLY

 

 

I got a message

“Server: Msg 2501, Level 16, State 1, Line 1 Could not find table named ‘1525580473’. Check sysobjects. “
after this I have taken backup of this local Tempdb of whcih size was reduced drastically

Again uploaded on live server and attached by stoping SQL server and then restarted SQL server of live server .

After this i got confirmation from DU team that now itw working properly and tranasactions are getting processed fastly.

Thanks

Prashant Deshpande

Advertisements

Leave a comment

Filed under Database Server, Server Application, SQL Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s