TempDB will not shrink

Question

TempDB will not shrink

Will SQL 4 Food

SSCrazy

Points: 2253
More actions
May 14, 2007 at 2:20 pm

#178515

My TempDB on production is normally pretty small, less than 1 GB. Today it's over 9.5GB. We're migrating a lot of data, but it's just going into staging tables on our main DB. There's only one data file for tempdb, id = 1.
I've run this from tempdb:
checkpoint
dbcc
It's the DBCC response that Page 1:1184916 could not be moved that has me at the end of my rope.
- I've checked for open cursors. I found several, and could kill the sessions associated withthem.
-I've checked for open transactions and find none.
My only thought at this point is that killing the sessions didn't remove the cursors. Other than that, any ideas?
Anyone?
Thanks in advance!
Will

But boss, why must the urgent always take precedence over the important?

Viewing 5 posts - 1 through 4 (of 4 total)

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

Louis Ventura Valued Member Points: 73 More actions · Answer 1

Hi,

You migth switch the recovery mode to simple before the shrinkfile cmd, and switch it back afterwards.... Hope this helps,

HellVice,

shahzadtariq SSC-Addicted Points: 424 More actions · Answer 2

Hi

In order to shrink database, you are using right command but the thing is tempdb uses some objects thats why there is a problems to shrink the tempdb. When you will restart the sql instance then tempdb will automatically be refreshed and it will occupy default size 8 MB if you have not changed it manually.

Will SQL 4 Food SSCrazy Points: 2253 More actions · Answer 3

Thanks for both replies! We resolved the problem with a restart of the DB Engine service during our maintenance window, and it did resolve the problem.

My bind is that the maintenance windows only come around once a quarter or so, and if this happens again I'd like to be able to solve it without restarting.

I believe the problem is that a cursor pinned a page around the 9GB mark after tempdb had grown due to a large sort operation. The shrink would have worked fine but that the cursor pinned that particular page. I know the cursors are declared through API by a JDBC driver. As such, they're unfortunately not named.

So how does one kill a cursor which has no name?!?

But boss, why must the urgent always take precedence over the important?

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

Heh... cursors... gotta love 'em.

How much disk space do you have... 300 gig? 500 gig? Why are you worried about a 9 or 10 gig TempDB? Shoot... we preset ours to 9 gig on boot!

Also, just an fyi because someone said something about it... TempDB is already set to SIMPLE... as you said, a haywire cursor caused it to grow. The Doctor says, "If it hurts when you do that, then stop doing that". I know, I know... not always possible...

You can cause JDBC connections to be "named" at the time they are created by including "app=putsomenamehere" in the appropriate spot in the connection string. "putsomenamehere" will show up in sp_Who2 and SysProcesses in the "Program_Name" column.

--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)