TEMPDB cannot Shrink

Question

Post reply

TEMPDB cannot Shrink

RCC-JAXDBA

Mr or Mrs. 500

Points: 557
More actions
November 7, 2008 at 8:31 am

#195987

We have an active OLTP application that has many jobs that run at night to import and extract data and on occasion we have filled up our 100GB partition that houses tempdb. Often times I issue DBCC SHRINKDATABASE(tempdb) and it will not shrink and responds with something like "Cannot shrink file 2 as all logical log files are in use". I have been researching and have not found a way to see what active SPID is using tempdb or a way to really get around this problem except for waiting 15 minutes and trying again.
Does anyone have any information that would help me identify why tempdb cannot be shrunk and see the exact reason why

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

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

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 1

Hmm not seen that before. Can you shutdown server it will rebuild tempdb with correct sizes.

Or if you can shrink in smaller bits.

RCC-JAXDBA Mr or Mrs. 500 Points: 557 More actions · Answer 2

Yes, boucning SQL will solve the problem, and I can use sp_lock to see what has locks in tempdb at that point in time but that can change byt he time I run the query and do anything about it. TEMPDB is a black box to me for administering, but it still needs DBA administration so I am trying to see if anyone has any insight in to the Black Box of tempdb

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 3

Ok so if you bounce server it goes back down to say 1024 in size.

Then you run the query and voila your back up to 100 GIG

What is this query doing is it stuck in a loop.

RCC-JAXDBA Mr or Mrs. 500 Points: 557 More actions · Answer 4

It is actually an 8 hours job that runs every night to generate a mini DW, along with index maintenance jobs, extract and load jobs that all run over night. This is a 24/7 production environment so bouncing SQL is not an option every day

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 5

Ok so it is a job that is in house that you created and by the time it ends it is 100 GIG in tempdb.

You may have to issue some additional backup logs in your steps.

What is the mode of the database FULL or SIMPLE.

IF full are you doing BACKUPS of LOG every 15 minutes.

If Simple then i would but in shrink tempdb every so many steps through it..

or even do force CHECKPOINT in the procedure too.

You really need to determine where it is filling up on tempdb ...perhaps some additional

history logging at each step of your process.

Or run it on a difference database where it is in SIMPLE MODE then the log won't fill up as SIMPLE Mode deletes this as it goes along.

Mark Shvarts 1 SSCertifiable Points: 5702 More actions · Answer 6

You can query sys.dm_db_session_space_usage system view.

It will show you number of pages allocated per each SPID in tempdb. Going from there, you can get the largest SPIDs are doing by executing DBCC inputbuffer or some other DMVs.