shrink tempdb DATA file without killing jobs

Ive got a job that is suddenly making tempdb DATA file grow by 50 gigs in
3-4 hours. When the drive starts to run out of room Im forced to do a DBCC
SHRINKFILE on the DATA file, but that also kills the job with the message

"Executed as user: domain\Administrator. Could not allocate new page for
database 'TEMPDB'. There are no more pages available in filegroup DEFAULT.
Space can be created by dropping objects, adding additional files, or
allowing file growth. [SQLSTATE 42000] (Error 1101).  The step failed."

I had the data file set to grow at 10% and this happened. I changed it to 50 MB and it happened. I now changed it to 10 MB and will see if it happens again.

To get me through the weekend, is there a way I can shrink the file without
killing the job while it's running? I tried leaving 500 MB in it last time
but that didnt help.

sql2k sp3
TIA, ChrisR
LVL 3
rserajiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JesterTooCommented:
I believe the answer to your question is "No... it can't be shrunk without killing the job(s) using it.".  Are you certain the job is running correctly... in other words, is it reasonable for it to produce this amount of tempdb data?.

It's normally accepted as good practice to not shrink tempdb and to keep its size at least as large as its average size in order to reduce the time-consuming allocation extensions when it fills up.  I usually keep it at 150% of the largest size it normally needs and I set it's growth factor to a fixed size that represents 25-33% of that.  Placing tempdb on it's own disk volume (non-raid5) is also a good idea if you can.

HTH,
Lynn

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rserajiAuthor Commented:
No, Id rather it not take all this space... but was just looking for a quick fin until Monday.
JesterTooCommented:
Can the job be broken up into pieces such that it would require less space per piece?
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

rserajiAuthor Commented:
Not sure about breaking up the job. Does anyone know if I add another data file to the Primary Filegroup for tempdb on another drive, will they both fill up equally, at the same rate?
oleggoldCommented:
the simplest way to go around the problem is to:
1.shrink other tablespaces to achive space
2.create an additional temporary tablespace with enough space to have the job finished.
3.when the job finished
a.promote the new temporary tablespace  to be default
b. drop the old temporary tablespace  to finally solve the problem
jrb1senior developerCommented:
No comment has been added to this question in more than 21 days, so it is now classified as abandoned..
I will leave the following recommendation for this question in the Cleanup topic area:
   Accept: JesterToo {http:#14403947}, oleggold {http:#14412917}
   
Any objections should be posted here in the next 4 days. After that time, the question will be closed.

John Bush
EE Cleanup Volunteer
jrb1senior developerCommented:
Make that split:

No comment has been added to this question in more than 21 days, so it is now classified as abandoned..
I will leave the following recommendation for this question in the Cleanup topic area:
   Split: JesterToo {http:#14403947}, oleggold {http:#14412917}
   
Any objections should be posted here in the next 4 days. After that time, the question will be closed.

John Bush
EE Cleanup Volunteer
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.