December 5, 2002 at 12:53 am
As we don't practice to shutdown SQL server (unless there is a system maintenance work needs to be carried out), tempdb database size will keep on growing until it reach to its maximum (hardisk space full). I am thinking to move the tempdb file to another disk drive, shutting down SQL server once a week and run a scheduler job to shrink the tempdb file size. I try not to set the maximum file size as this will stop the users job once it reach to its maximum. Is there a way to find out exactly which process/job is taking up tempdb space?
December 5, 2002 at 7:11 am
tempdb is just like any other data base except it is used by the system for work space. you can use the INFORMATION_SCHEMA views to look at tables and other items in the data base and from there you should be able to determine who's filling your space.
December 5, 2002 at 4:07 pm
You might also want to try using profiler to catch what is happening with the database. A few notes, Temp tables, order by and suqueries make inserts into that database for storage. Moving to it's own drive is always suggested if an option but for the database to gro so large you obviously have a process that requires temporary table storage needs (most commonly is order by on a large select).
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply