after 1 wk online, tempdb=39gig in size why???

[jerryk]

Greetings,

Last week, our ERP vendor converted our db from Access(200meg) to SQLSvr 2000.

Today, our tempdb is 39gig, and the disk it's on is full.

I've done some reading and the "autogrow" feature may be on.

May I turn it off?

Given how large tempdb got in a week, can I expect performance hits if I limit it to say, 5gig?

 

[SQLSister]

You would be better off discussing with your vendor what processes could be happening to cause this.

 

[jerryk]

I'll do that, thanks.

If I do decide to limit the db to something like 20meg, what happens when the tempdb hits that size?

does it halt? crash?

Thanks again for your help.

 

[SQLSister]

whatever function it is trying to do would fail because it couldn't put the data in the tempdb database

I personally would never turn of autogrow on temdb.

If you want to worry about the size, set up an alert to inform you whenever it gets to a specific size, so that you can monitor the growth and shrink it if it gets too big.

Really your more urgent problem is to figure out what caused it to grow like that because I would not expect tempdb to grow like that by normal processing of data in a database the size you described.

 

[fluteplr]

I would have to go with SQL Sister,
Whoever converted your database program munged something up very badly. There is no reason why you should be seeing that kind of temp db sizes.

Call the vendor, hop up and down, scream whatever but get them to fix it.

 

[SQLBill]

I have a database that's grown to 142+GB in 5 months. It is populated strictly by Bulk Inserts which are done frequently throughout the day. The MOST my TEMPDB has ever grown to is 6 GB. You really need to find out what's going on with your system.

Also, a quick suggestion...one way to 'shrink'/clear out the TEMPDB is to stop the services, wait a few seconds, and then start them again.

-SQLBill

 

[jerryk]

Thanks for all your help.

Interestingly, I shut down the server to put it in my rack.

When it came up, the tempdb was still 30gig. Instantly.

Shouldn't the reboot have cleared it out the same way stopping/starting the service should have?

I've got a feeling I'm about to become an SqlSvr expert real quick.

-Jerry

 

[SQLBill]

Two things happen (that I know of) to cause TEMPDB to be a certain size.

1. TEMPDB is 'assigned' that size. In Enterprise Manager, right-click on TEMPDB and select properties. Go to the Data tab and look at what size is assigned.

2. TEMPDB 'grows' to that size. This is due to transactions occuring.

Stopping/restarting the services, etc. only work for #2. TEMPDB will ALWAYS be the size assigned until the size is changed manually.

Your vendor MAY have set the size to 39GB so it wouldn't have to keep 'autogrowing' while the data was being transfered from ACCESS.

-SQLBill