tempdb Growing Alarmingly Fast!

linque

I upgraded to version 2.3.0.334 earlier today, and decided to purge my data after the installation completed. I've never purged the database before, and my data file for SQL Monitor was about 70GB. A few hours ago I changed all purge options to delete anything older than 3-months. My tempdb.ldf file grew from 2.5 MB to 33.9 GB within about a 3-hour period, and it's still growing. I actually ran out of drive space, and had to have the SAN admin add more space to my LUN to get SQL Server working again. I've changed my purge settings back to not purge data, but it's still growing. I have other databases on this machine, and if it keeps growing I'm going to to have to shut SQL Monitor down.

Help, please.

RBA

Hi,

SQL Monitor uses tempdb to keep a log of what it plans to delete before it actually does. This sacrifices disk space for an increase in the speed in which data can be purged.

If you've set SQL Monitor to 'do not purge' then, when it restarts, temp DB should stop growing. Unfortunately, temp db will not shrink without either a restart or user intervention.

How many months of data are there in your 70GB DB? If there's more than six, dropping down to a six-month purge window before going down to three would make the purge less aggressive.

I'm sorry to hear this has affected your other systems.

Regards,

linque

The database server was crawling when I got in this morning, and users were complaining about not being able to use apps that had databases on that server. I did receive an email from RG Support with a link about my issue, but it's too late now unfortunately.

www.red-gate.com/supportcenter/Content? ... %20Monitor

The database was created on 02/08/2011, and is currently monitoring 8 servers and 8 instances. I should have set a purge policy long ago to avoid all this. A reboot seems to have cleared things up. I also got more memory and 2 additional procs added to my server -- bonus!

I'm going to let it run for a bit, then revisit the purge properties later. I do have 1659MB of free space now (out of 72064 MB) on the SQL Monitor MDF file. I only had about 20MB free before the purge started.

P.S. I still love SQL Monitor!

RBA

Hi,

I can understand how annoying this interruption must be to your other users. Thanks for bearing with it.

In future versions we're thinking of cutting back the volume of data we collect by default, but in the copy your have right now you can edit RedGate.Response.Engine.Alerting.Base.Service.exe.settings.config and adjust the collection schedules for individual metrics (most default at 15 seconds, which if you pull back to 1 minute will x0.25 your DB growth). I've dumped a set of configs here: http://pastebin.com/80XfUUDX as an example, should the comments in the config file prove insufficient. Note that there might be a cosmetic impact on the UI if your make collection is too sparse, but the option is there.

Hope this helps,