SQL Monitor Archive - NOT PURGE; Wheres The Feature

EdCarden

I imagine the answer is "there is none" but I'm not going to asusme that.

Where in SQL Monitor do I go to archive data instead of purging data?

peter.peart

Thanks for your post. If you go to Configuration > Purging you should see a number of drop downs for Machine Data, SQL Data and Alert Data.

If you click on the drop downs, you will see that you can select to not purge data at all.

There isn't however an archive functionality. As my colleague said via e-mail, you can backup and restore elsewhere, but that's about it I'm afraid.

Pete

EdCarden

Pete,

What about this. I checked my backup copies of the SQL Monitor DB and found that between the date when we first setup SQL Monitor and started capturing data (2011/08/07) and when I did a full backup using SQL Server (we normally do backups via DPM) on 2011/09/22 the DB grew to such that the uncompressed backup resulted in a 2.1GB file.

The backup I performed just before upgrading to version 3 of SQL Monior resulted in a 132 GB uncompressed backup file.

So between 2011/08/07 and 2011/09/22 (about 1.5 months or 6 weeks) we accumilated 2GB of data. Over the next 6.5 months the DB grew such that the backup increased by 128GB.

During that time we did turn on the Trace feature in SQL Monitor but only for a few minutes and only a handful of times if tat many.

I'm trying to determine if the accelarted growth in the monitoring DB came about because of an increase in the number of alerts or if its something else. I don't have any other backups to comapre with so I don;t know at what point the DB started to grow by a lot more then it had been. Do you know if there's a way using the data captured by SQL monitor to find out when this increased growth started?

peter.peart

Thanks for your reply. With the backend being a database, there's nothing to stop you from looking at the tables and running count queries based on alert time to ascertain how many alerts you had during a given period. We don't however provide schematics of the schema though I'm afraid as it's subject to change with each version we release (and has a couple of times with V2 and again with V3).

If you're not purging anything at all, and you're monitoring a decent number of servers which also have a decent number of alerts being generated, 128GB in 6 months isn't inconceivable.

sean.alexander@ontario.ca

I enabled Database Compression on the tables which greatly reduced the size of the database with very little penalty.

Chris Spencer

An alternative to native table compression is Redgate's SQL Storage Compress of course (90% vs 12% when I tried out compression options)
http://www.red-gate.com/MessageBoard/vi ... hp?t=12585

Regards
Chris

EdCarden

peter.peart wrote:

Thanks for your reply. With the backend being a database, there's nothing to stop you from looking at the tables and running count queries based on alert time to ascertain how many alerts you had during a given period. We don't however provide schematics of the schema though I'm afraid as it's subject to change with each version we release (and has a couple of times with V2 and again with V3).

If you're not purging anything at all, and you're monitoring a decent number of servers which also have a decent number of alerts being generated, 128GB in 6 months isn't inconceivable.

Because of the complex schema (which looks to be non-normalized) I'm hesistant to even try and guess at what rows in whcih tables can be safely purged or archived. I don't want to purge this information as it is valuebale data that will one day prove very beneficial for us once Redgate realizes the value in this data that SQL Monitor captures. For us its already greatly asissted in determing our hardware needs for a new reaplacement server for the server that hosts the SQL Server instance we are monitoring.

Assesing new hardware needs was easy as the IT Admin was able to do this by reviewing the info in the Analysis tab for the last X months worth of info cpatured in Analysis.

The other reason for not purging this data is the value in being able to review it and isolate trends such as which LRQ's (Long Running Queries) are most common and therefore in need of first attention when it next comes time upgrade/change reports and/or anything else that uses the T-SQL that raises these LRQ's alerts. We can't do this now because theres no realistic way to get that information from SQL monitor aside from asking for it one alert at a time using the SQL Monitor Web interface. A set of user-friendly views similiar to SQL Servers DMV's (Data Mgt Views) is whats needed and once that happens, all of this Alert data we've been saving instead of purging is going to be very valuable.

Thanks