The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
tempdb growing out of control
Posted: 10 Feb 09 8:36 AM
|
Hi everyone,
After new install of 7.2.2 running on SQL 2005 SP1 I noticed that tempdb growing out of control. After few days it's over 70GB and has consumed my entire hard drive. Shinking it DB got me back to 25GB but it keeps growing.
Please help!!
Alex
|
|
|
|
Re: tempdb growing out of control
Posted: 11 Feb 09 9:44 AM
|
Alex,
More information please. Is this an isolated instance? How many DB's in this instance? Do you have integrations? Are any of your Prod DB(s) growing proportionately? Are you using Transaction logs? Are you truncating logs? Do you have Maintenance, excessive indices, etc.
Any information along those lines may help us. 
Carla |
|
|
|
Re: tempdb growing out of control
Posted: 12 Feb 09 3:14 AM
|
| Set your recovery level to Simple. It sounds like it's Full logging - and you aren't doing a SQL Backup - in which case the log file will grow. |
|
|
|
Re: tempdb growing out of control
Posted: 12 Feb 09 7:08 AM
|
You are correct I do not backup tempdb, should I ? i never did, neither did the business partner when it was originally set up in ver 5 with sql 7
The recovery model is set to simple. I also see a setting for "Auto Shrink" that is set to "FALSE"
My SLX database is 3.5GB and I limited "tempdb" to 500MB, every once in a while a user would get an error stating that database 'tempdb' PRIMARY filegroup is full. Shrinking tempdb solve the problem until next time. |
|
|
|
Re: tempdb growing out of control
Posted: 12 Feb 09 7:45 AM
|
| No, you don't backup tempdb - the backup of the database should fix that (if it's SLX causing tempdb to grow). Is the recovery model of the SLX db set to simple (though) - not the recovery model for the tempdb ? |
|
|
|
Re: tempdb growing out of control
Posted: 12 Feb 09 7:54 AM
|
Carlo,
I will try to answer the questions, please keep in mind i'm no DBA
How many DB's in this instance?
-This is SQL2008 standard server and it only run SLX7.2DB plus any system databases that were installed as default install
Do you have integrations?
-The only integrations i can think of is Dynalink 7 that moved files from MAS4.2 to SLX
Are any of your Prod DB(s) growing proportionately?
-No.. the production DB is growing steady 2-5 megs per day depending on usage
Are you using Transaction logs?
-it doesn't ring a bell.. i have not done anything is SQL except restoring the DB and running sysdba script during installation
Are you truncating logs?
-not sure what this means..
Do you have Maintenance, excessive indices, etc.
-There are 2 maintenance plan that backup up production DB nightly and another job that delete old backups older than 5 days
thank you,
Alex
|
|
|
| |
|
Re: tempdb growing out of control
Posted: 12 Feb 09 8:03 AM
|
| Something else is happening - this suggests lots of small transactions that are been completed or very large sorts. How many users ? Essentially, you'll need to get someone in to analyse the database, its usage and functions as something is very wrong here. It shouldn't grow and not shrink back (but it will grow over time). You could always modify your maint. plans to shrink the tempdb database. |
|
|
|