Upgrade to SQL Backup v5 chewing up disk space

rgiesbrecht

I just tried upgrading my v4.6 monitoring workstation to v5 and am finding that when I run the GUI interface and it loads up the history it is consuming all available disk space on the workstation. When I fired up the GUI interface I started with 4.5 GB of free disk space. Any idea what is going on?

James Moore

Hi there,

This is probably due to the size of your backup history, can I suggest you take a look at running sp_delete_backuphistory (http://msdn2.microsoft.com/en-us/library/ms188328.aspx) with an appropraite value and additionally configure SQL Backup (in the options dialog) to do this as well - IE keep all backup history for just 30 days - keeping an excessive amount of backup history will slow down both Native and SQL Backup backups as well as wasting disk space on your server as updating the MSDB tables starts to take a long time and Microsoft do not provide a good set of Indexes over these tables.

- James

rgiesbrecht

How is this behaviour different between SQL Backup 4 and SQL Backup 5. Nothing has changed about the servers being monitored, just the monitoring software.

Scott Shaw

I encountered the same issue. I downloaded version 5 and installed it onto a Windows XP workstation. The SQL Backup service was using almost 500 MB of memory and pegging my CPU between 65% and 100%. It made the gui and my workstation unusable.

Could there be a database registration limitation? I have about 200 servers imported into the GUI, though only one is running RedGate software.

Please help.

martin

I have the same issue.

25 Servers imported and 10 of them has SQLBackup running on it.

The CPU will hoover around 65% with a huge cut into memory.

We run with 14 months of backup history.

Regards,
Martin

Scott Shaw

I followed the steps above and reduced the backup history to 1 month and deleted all the old backup history for all the servers.

The RedGate software is still showing a years history and taking up all the CPU. Where is the application finding this information if I deleted it from the servers?

Can someone address these issues?

Thanks,
Scott

James Moore

Scott,

For various reasons we cache various data on your local disk, this is under (in windows xp) c:\documents and settings\<user name>\Local Settings\Application Data\Red Gate\SQL Backup\ there will be one or more sub directories under here containing a file called localdatacache.dat and serveral other numbered files, make sure you close the UI then delete these and you should see the correct history appear in the UI.

For those who are stuggling with this issue please clear these files, check how much backup history you have on your server and get rid of any you dont need (the UI should be quite happy to handle serveral million records).

Register the server then wait for data to appear on the timeline for that server, I would expect to see reasonably high CPU/IO usage before data appears on the timeline for the server, this will take a few minutes if there is a large amount of backup history, once data is on the timeline you should not see this sort of performance hit again - it only happens when you initially register the server.

Let me know if you get continued poor performance after the initial hit or if the UI is unusable. We have spent a lot of time trying to make sure that SQL Backup 5 scales well so I would be interested to hear from anyone who has performance problems.

- James

Scott Shaw

James,

Thanks for the response. After removing the .dat files the response was much better. Some of the .dat files were almost 500 MB!

I'm running a 3 Ghz Pentium 4 XP Pro workstation. I imported roughly 200 servers with over a thousand databases. The servers are distributed throughout the mid-West. It took about an hour and 1/2 to get information on all the servers. CPU was spiking but never brought my machine to a standstill. I have backup history set to 1 month and removed any history older than April 30.

We take trans logs every hour (a few every 15 minutes) and do daily backups. The largest .dat file I have now is at 79 MB. Hope this helps.

Thanks,
Scott

ghassett

Hi - I am having a problem which may be similar and may not be... when I launch SQL Backup, my CPU meter spikes to nearly 100% immediately, as soon as the splash screen shows. It never comes back down.

I am connected to three servers, and a total of about 12 databases. None have extensive backup histories.

Also, there is no directory

c:\documents and settings\<user name>\Local Settings\Application Data\Red Gate\SQL Backupon my machine (nor anything similar to it anywhere in the "Documents and settings" tree).

Any ideas?

Thanks

Greg

DBA_Dave

It doesn't download only backup histories; look at the "jobs" tab on the GUI, it downloads ALL the job histories in MSDB.

I have been nagging them about this behavior as I have servers in NYC, Los Angeles, Pakistan, and India. Their over-selection of data makes the product essentially useless other than for servers in our HQ. The overseas admins don't have a chance of launching the GUI against our servers, and we can't launch it against their servers. It never finishes loading.

http://www.red-gate.com/MessageBoard/vi ... php?t=5564

Seems kind of pointless to have those time zone tabs since the product can only be used on a LAN.

Bryant

I ran into this problem at the beginning of the week and followed the advice to clear out the SQL Backup cache files. They were using over 480 MB before I deleted them all. However, even after deleting them SQL Backup was bogging my PC down with high CPU utilization and a bit more memory usage than I would like. It just so happens that I did this yesterday and I start my disk defrag utility before I leave work (almost) every Thursday. When I came in this morning and the drives had been defragged, SQL Backup was back to it's normal behavior without taxing the CPU and memory reserves.

I don't know if that helps anyone very much. If I have the problem again I will try defragging before I delete the cache files to see if that helps.

Bryant

I can confirm that defragmenting the hard drive that contains the cache files takes care of the SQL Backup 5 startup performance issue I was having. SQL Backup appeared having trouble connecting to servers and gathering information this morning so I closed SQL Backup and ran my defrag utility. Once it finished, I opened SQL Backup and it's working fine now.

It sounds like the caching algorithms do not tolerate disk fragmentation very well. To be honest, I am not really interested in looking back much more than a few hours (maybe 12) unless I need to do a restore. Perhaps there needs to be an option that allows the user to specify the time period to keep the cached data around and have any older data purged.