Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

snapshot of window and spaceI am developing a log analyzer tool and After queering it and generating reports Tempdb is getting full very quickly .

Is there any solution to clean the tempdb file in microsoft sql server 2008 on a timely basis other than restarting the server ?

Thank you so much in Advance

share|improve this question
You could analyse it and see what's being added to it, and truncating it regularly? – Preet Sangha Jun 13 '12 at 14:44
Are you using temp tables in your queries? Are you batching the queries properly? – David Manheim Jun 13 '12 at 16:54

migrated from stackoverflow.com Jun 14 '12 at 4:07

3 Answers

up vote 3 down vote

Tempdb is growing like this because something is using it. No attempt to fix, clean or manipulate tempdb is going to work as long as this is going on.

To address this problem, you need to find out what is causing it to grow like this and then fix that problem first. This alone may resolve the whole issue and remove your need to do anything else with tempdb. In my experience, the most likely cause for something like this is an out-of-control Cross Join query (though there are lots of other possibilities).

share|improve this answer
2  
In addition it may just not be sized appropriately to the work loaded being demanded. – StarShip3000 Jul 14 '12 at 16:02
up vote 0 down vote

We recently faced the same issue. I got a marvellous solution over here : Cleaning tempdb
This will definitely help you. All the best !!

share|improve this answer
Hi Thanks for your reply but this did not work . The query runs successfully but did not see any changes . I even tried using 1 hh instead of 12 – Rishi Ranka Jun 13 '12 at 15:02
Probable reason for this can be that either you do not have the permission to drop tempdb objects or the objects are currently in use by some session (as you have reduced old objects' age from 12 to 1 hr). – Saurabh Jun 13 '12 at 15:11
up vote -2 down vote

If you have a nightly maintenance window for the DB the simplest method is to change your database from FULL logging to SIMPLE and then back to FULL again.

All other methods, e.g. http://support.microsoft.com/kb/307487 are overly complicated but you may be forced to consider them nonetheless.

share|improve this answer
6  
tempdb is always simple. And this is generally a bad idea because of backup chain issues getting broken... – gbn Jun 14 '12 at 14:35
Yes, you will need to do backups after changing the logging. – Jimbo Jun 15 '12 at 12:08
We have full backup every week, diffs every morning and log backups every 15 minutes. After flipping the logging from full to simple and then back again we do the diff. All restores work correctly and we never have issues with large temp DBs... – Jimbo Jun 15 '12 at 12:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.