Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DReneau
Starting Member
2 Posts |
 Posted - 2012-01-05 : 12:57:41
|
| We have multiple SQL2005 DBs that are used for our claims processing. Out environment is relatively small in comparison to other companies. We have about 125 users that access the SQL DBs on a regular basis. For most days of the week where there is not a check-run process, our TempDB database stays at about 3 to 5 GBs depending on reporting and Claims inloading processes. On the days where we have check runs, the TempDB will grow to 43+ GBs! I know that the check run process is the cause of the problem, but what can be done from a SQL maintenance standpoint to shrink the TempDB during production hours that won't impact the users?Danny ReneauE-mail:danny_reneau@med3000.com |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-01-05 : 13:03:58
|
quote:
but what can be done from a SQL maintenance standpoint to shrink the TempDB during production hours that won't impact the users?
Nothing. The red and blue parts I highlighted are mutually exclusive. Shrinking always has a performance hit (unless you're merely truncating the file). Unless you're totally out of disk space, don't shrink tempdb, it will just grow again. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2012-01-05 : 13:05:34
|
| "what can be done from a SQL maintenance standpoint to shrink the TempDB during production hours that won't impact the users?"Suggest you abandon that line of thought - shrinking TEMPDB with SQL running "live" is liable to corrupt user databases. TEMPDB will be recreated, next time SQL is re-stated, at its configured original size. (That doesn't always happen, and there is a process to manually shrink it safely, but that involves putting the SQL Service into single-user-mode - so that's a non-starter for a live-running system)Suggest you look at the Check Run process to see what in the SQL Code is causing the explosion of TEMPDB size. |
|
|
|
DReneau
Starting Member
2 Posts |
Posted - 2012-01-06 : 11:51:20
|
| Thank you both for your insight into this matter. I'm reviewing the Crystal Reports that are run during the check-run process to see if they are the culprit to the large TempDB growth.Regards, Danny Reneau |
|
|
|
|
|
|
TempDB File growing extremely large