May 24, 2012 at 1:18 am
Tempdb .ldf file is growing in production since 15 days...
Nearly my Drive is going to full..
Suggest me what to do ?
We can't increase the Drive size.., Only option to shink..
But I don't to Restart the Server.
Thanks in advance.
May 24, 2012 at 2:47 am
No need to restart the instance to shrink the file.
Have you tried dbcc shrinkfile?
Also run dbcc opentran to see whether there is any open transaction.
May 24, 2012 at 4:59 am
Suresh B. (5/24/2012)
No need to restart the instance to shrink the file.Have you tried dbcc shrinkfile?
Also run dbcc opentran to see whether there is any open transaction.
that's correct
you can shrink the log file with the shrinkfile command
use tempd
go
dbcc shrinkfile(2,1) - there are certain recommendations around shrinking tempdb (mainly the data file) while it is in use though
i suspect though that the issue you have is that auto checkpointing has stopped
can you run the following
DBCC SQLPERF(LOGSPACE)
and post the tempdb results here
if you have a large log file in tempdb and it is a high % usage then try the following to re-start the autocheckpointing
use tempdb
go
checkpoint
MVDBA
May 24, 2012 at 10:30 am
Thank you,
Result of DBCC SQLPERF(LOGSPACE) :
tempdb
Log size in MB: 4523.992
Log space used (%): 3.018456
I know It is not hues ,, But it was usually around 1 GB. and my drive don't have space.
I am trying to shrink. but it is not shrinking.
May 24, 2012 at 10:55 am
You must have had something that made it grow large. Some transaction that created a lot of logging in tempdb. It's possible that you need that much log for your peak usage of tempdb.
Try shrinkfile on the log, as Michael suggested.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply