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 |
|
CanadaDBA
583 Posts |
 Posted - 2007-04-19 : 08:45:42
|
| What a DBA should do if tempdb gets too big? Is recycling SQL Server the only solution?Canada DBA |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2007-04-19 : 08:58:37
|
| If a DBA doesn't know what to do when tempdb gets too big, they shouldn't be calling themselves a DBA.Mike"oh, that monkey is going to pay" |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-04-19 : 09:44:43
|
| How big? Data file or log file? Any open transaction in it? Did you enable row versioning? |
|
|
|
CanadaDBA
583 Posts |
|
|
CanadaDBA
583 Posts |
Posted - 2007-04-19 : 10:23:45
|
Another DBA had bounced the SQL Server before I got to the office. I am looking for a better way. Based on him the data file was 120GB and Log 40 GB. He didn't check for any open transactions. quote:
Originally posted by rmiao
How big? Data file or log file? Any open transaction in it? Did you enable row versioning?
Canada DBA |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-04-19 : 17:02:44
|
| I saw this when have long running process, can shrink it once killed the process. |
|
|
|
pareshmotiwala
Constraint Violating Yak Guru
323 Posts |
Posted - 2007-04-20 : 08:55:46
|
| You are right CanadaDBA, Mike should have used better tone. I hope the administrators are taking note. As RMiao suggested there must be a rogue transaction(s). What I suggest you do is to stop the sql server in the off hours and restart it. Run a profiler to trap what queries started at what time. You may have to baby sit it for a while. Example: does it happen at a fixed time. If so, check the activity monitor to see if any user nonsense is going on. You may have to work with the users or developers to rectify this.Just restarting the sql server is not always the right thing to do.RegardsParesh MotiwalaBoston, USA |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-04-20 : 09:18:04
|
"I am looking for a better way"Shrinking TEMPDB has the potential for some fairly horrific consequences, see:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Shrinking+TEMPDBPersonally I would bounce SQL Service (there are subtle ways to do this, and the less subtle push-red-switch; the second is more of a power-fail-recovery-test than a TEMPDB-log-shrinker!)But you DO need a procedure for rebooting SQL Server, so follow that (and if its missing you need to write one).Likely things to include are:Warning messages on web sites ("Server going down, you have 5 minutes to exit the store")Then mount holding pages on web sitesDisable all jobsWait for all jobs to finishDisconnect remote access so that remote procedures can't be in the middle of stuff ...... or make sure they are built robustly and will "resume" in a tidy fashion... (we have a "next maintenance window start date/time" value stored in each database, and such procedures check that and don't start if they are likely to overrun that time)Set Databases to DBO-onlyfor certain types of maintenance we also change the AutoStart on SQL Service to DISABLED, so that it doesn't come to life [after reboot] until we are ready.Then after reboot some reversing procedures to that - set databases to MULTI_USER, Re-enable jobs, Remove holding pages from web sites, set next-scheduled-downtime to 01-Jan-2999  , etc.Kristen |
|
|
|
|
|
|
|
|
Tempdb is full