Impact of Local Database Files on Usability

DBA_Dave

My company has offices in the States, India, and Pakistan. We have DBA's in the overseas offices who need to use this GUI to monitor the backups. The .DAT files are proving to be quite a problem because they are just too large.

We have limited bandwidth to the Subcontinent and this is keeping our people there from being able to install and use the GUI.

You are pulling historical data out of msdb..sysjobhistory that has nothing to do with your master..sqlbackup jobs and that is bloating the SQLite database files you place in %APPDATA%. You really should create your own entry in msdb..syscategories and select out only those histories that pertain to this product.

We do a lot of replication and have many scheduled jobs so we cannot afford to purge that data just to limit the size of your data files. That data is needed to troubleshoot and monitor all the jobs, not just the backup jobs.

Also, the location of the .DAT fies leaves much to be desired. Placing them in a user profile causes a lot of problems at logon, and it effectively prevents the use of roaming profiles by making them too large to replicate and load.

We need the ability to select a location for those files, and there is no reason to have a complete set of files for every user. They should be a shared resource and they should be in a user-selectable directory.

I have been able to keep those files in check using SQLite:

sqlite> select max(entry_id) from activityhistory;
max(entry_id) = 4474
sqlite> select min(entry_id) from activityhistory;
min(entry_id) = 0

then dividing the range returned by 2 and deleting all activityhistory and log table records where entry_id is lower than that value. If I could figure out what you are doing to create the INTEGER_DATETIME values I could delete by date, but so far I have not been able to figure it out. I know where the decimal goes and that the four digits to the left of it are seconds, but assuming that the remainder of the digits are seconds overflows any known date type.

It would be very nice of you to share that information...

Paring down the historical data was simple enough to do that you should be able to incorporate this into your GUI with a minimum of effort.

You should also include the ability to run the vacuum command on those databases to shrink the file sizes down to a reasonable level. I have been doing this by hand to keep my files small.

Other than this nitpicking, I really like the product. The architecture is beautiful. I love the way you plug in your DLL as an XP and call it without needing the wrapper SP's COM requires. Very ingenious.

Ben Pinchen

I understand your issue with the the amount of data that SQL Backup pulls in from the msdb database. As you know there is currently no way to filter this at the moment. The only work around presently is to make sure that the job history in the msdb is kept to a minimum. I know that this isn't ideal and is unworkable in some scenarios but unfortunately there’s, currently, no way to change this.

I will raise a feature request concerning this issue and pass on your suggestions to the developers.

Your comment about where we place the data files is a valid point and something that we will look into.

DBA_Dave

There is a very simple way to do it. It's called a where clause. ;-)

1. Create an entry in msdb..syscategories for your backup jobs.

USE msdb
EXEC sp_add_category 'JOB', 'LOCAL', 'red-gate backup job'

2. Select only those jobs, steps, and histories:

select * from msdb..sysjobhistory sjh
join msdb..sysjobs sj on sjh.job_id = sj.job_id
join msdb..syscategories sc on sc.category_id = sj.category_id
where sc.name = 'red-gate backup job'

select * from msdb..sysjobsteps sjs
join msdb..sysjobs sj on sjs.job_id = sj.job_id
join MSDB..syscategories sc on sc.category_id = sj.category_id
where sc.name = 'red-gate backup job'

select * from msdb..sysjobs sj
join msdb..syscategories sc on sc.category_id = sj.category_id
where sc.name = 'red-gate backup job'

It would also be nice of you to select only data newer than the local data.