November 10, 2008 at 11:06 pm
I am having a hard time finding out which process if any is filling the tempdb which goes over 40GB.
Ideally the tempdb log should truncate by its own since there are no open transactions or anything visible seems to be running using tempdb or holding locks in tempdb.
Manually running checkpoint in tempdb clears the logfile.
Any help would be greatly appreciated.
Thanks
H Manzer
November 11, 2008 at 4:17 am
First thing you should try to find out is which query\process is causing the growth. Most likely some process which stores a lot of data in temporary tables g.e. a ETL process could be the culprit. If you have no idea at all, shrinkt the tempdb to a "normal" size and setup an alert which fires as soon as the tempdb grows above a certain threshold. As a reaction to the alert execute a job which executes sp_who or query sysprocesses to see what's active.
The reason that the tempdb is not shrinking once the space is no longer used is simply that autoshrink is not enabled (and you should keep it like that). If a process needs that kind of space to the database will only grow again. Having the space already reseverd, is easier and faster.
So first find the reason for the growth and when analyze if that process can be tuned in a way it uses less space in tempdb. If that's not possible you probably have to accept that the tempdb is rather large.
[font="Verdana"]Markus Bohse[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply