Urgent help for tempdb

Question

Urgent help for tempdb

subbkarth

SSC-Addicted

Points: 448
More actions
June 30, 2011 at 12:47 am

#253649

Hi all,
In one of my production server faces a problem of one issue.
Here is the issue
The log file for database tempdb is full. Back up the transaction log for the database to free up some log space.
according to my exp, 1) restart the server
2) shrinking the log
but thses 2 methods require downtime. without downtime any other ways
I already check the log file size -------> almost full
but free space in mdf file

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

Your message is a bit confusing. You can’t backup the tempdb’s log, and there is no reason to do so. TempDB’s recovery model is simple and the server clears the log records that are not needed automatically.

Between the 2 ways that you wrote about (Shrinking the log and restarting the server), restarting the server causes downtime. Shrinking the log doesn’t cause downtime, but if the error message is that you don’t have free space in the log, then I don’t see how shrinking it will give you more space. I also wouldn’t restart the server. Although restarting the server makes many problems disappear, it is not a magic solution for all problems. I would try to increase the log’s size, and would also try to check what process is using the tempDB

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

subbkarth SSC-Addicted Points: 448 More actions · Answer 2

Thanks for ur reply

what u said is good for me, any other ways

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 3

DBCC SHRINKDATABASE

Visit this link

http://support.microsoft.com/kb/307487

My Blog
www.aureus-salah.com

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 4

Add another file to the log on tempdb on a different drive. Then you should get a log truncation at the next checkpoint and then you can shrink the file in place. It might take a checkpoint or two until the log clears enough for shrinking. I'd figure out what filled the log on tempdb, because it will happen again.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

calvo SSChampion Points: 12930 More actions · Answer 5

Syed Jahanzaib Bin hassan (6/30/2011)

DBCC SHRINKDATABASE

Visit this link

http://support.microsoft.com/kb/307487%5B/quote%5D

don't listen to him ^

What Mr. Fritchey suggests is best if your problem is that you are running out of disk space on the disk which the log file resides.

______________________________________________________________________________________________
Forum posting etiquette.[/url] Get your answers faster.

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 6

If you still have enough space on the disk where the log file of the tempdb is located, I will make the log file bigger(Adi's suggestion), but if that disk does not have enough free space I will advice you to follow Grant's instructions. Good luck!

My blog