tempdb reporting Error: 9002 log full

Question

Post reply

tempdb reporting Error: 9002 log full

Viewing 4 posts - 16 through 18 (of 18 total)

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

SanjayAttray SSChampion Points: 13157 More actions · Answer 1

first of all, don't shrink it unless you have to because space needed on the drive by something else. Tempdb has grown to this size because it needed to at some point. so unless there was a one off or runaway job that caused this, this is the size (roughly) you should set tempdb to (as discussed by others above).

At a quite time you can shrink the file via query analyzer:

dbcc shrinkfile(tempdev,size in MB)

shrink it to slightly smaller than you really want then use alter database command to set final size, and it will be this size on reboot.

I know when tempdb size has grown as its a process which we perform once in a 3 months. I needed the space back because of space issue on disk and the process which takes this much space will again be executed after 3 months. I tried to shrink it will every possible manner except restarting sqlservice, but failed.

If I couldn't shrink it in another couple of days then my last choice would be restart services, which will get tempdb to minimal size.

SQL DBA.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

SanjayAttray (9/10/2008)

I know when tempdb size has grown as its a process which we perform once in a 3 months. I needed the space back because of space issue on disk and the process which takes this much space will again be executed after 3 months.

You may want to consider rewriting the quarterly task to be a wee bit more effecient. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions