SQL Monitor Archive - NOT PURGE; Wheres The Feature

SQL Server performance monitoring and alerting

Moderators: eddie davis, priyasinha, Adam, chriskelly, Chris Lambrou, Chris Spencer

SQL Monitor Archive - NOT PURGE; Wheres The Feature

Postby EdCarden » Fri Apr 13, 2012 5:17 pm

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?
EdCarden
 
Posts: 90
Joined: Tue Nov 25, 2008 6:26 pm

Postby peter.peart » Mon Apr 16, 2012 10:12 am

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
Peter Peart
Red Gate Software Ltd
+44 (0)870 160 0037 ext. 8569
1 866 RED GATE ext. 8569
peter.peart
Site Admin
 
Posts: 362
Joined: Tue Sep 02, 2008 9:58 am
Location: Top floor, RG towers with the cool kids

Postby EdCarden » Tue Apr 17, 2012 7:23 pm

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?
EdCarden
 
Posts: 90
Joined: Tue Nov 25, 2008 6:26 pm

Postby peter.peart » Wed Apr 18, 2012 1:48 pm

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.
Peter Peart
Red Gate Software Ltd
+44 (0)870 160 0037 ext. 8569
1 866 RED GATE ext. 8569
peter.peart
Site Admin
 
Posts: 362
Joined: Tue Sep 02, 2008 9:58 am
Location: Top floor, RG towers with the cool kids

Database Compression

Postby sean.alexander@ontario.ca » Tue May 01, 2012 9:17 pm

I enabled Database Compression on the tables which greatly reduced the size of the database with very little penalty.
sean.alexander@ontario.ca
 
Posts: 6
Joined: Wed Mar 28, 2012 5:54 pm

Postby Chris Spencer » Wed May 02, 2012 10:26 am

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
Chris Spencer
Test Engineer
Red Gate
Chris Spencer
 
Posts: 301
Joined: Tue Aug 29, 2006 9:09 am
Location: Red Gate - Cambridge

Re:

Postby EdCarden » Tue Jul 03, 2012 6:05 pm

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
EdCarden
 
Posts: 90
Joined: Tue Nov 25, 2008 6:26 pm


Return to SQL Monitor 3

Who is online

Users browsing this forum: No registered users and 0 guests