Red Gate forums :: View topic - SQL Monitor Archive - NOT PURGE; Wheres The Feature
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Monitor 3
SQL Monitor 3 forum

SQL Monitor Archive - NOT PURGE; Wheres The Feature

Search in SQL Monitor 3 forum
Post new topic   Reply to topic
Jump to:  
Author Message
EdCarden



Joined: 25 Nov 2008
Posts: 88

PostPosted: Fri Apr 13, 2012 5:17 pm    Post subject: SQL Monitor Archive - NOT PURGE; Wheres The Feature Reply with quote

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?
Back to top
View user's profile Send private message
peter.peart
Site Admin


Joined: 02 Sep 2008
Posts: 362
Location: Top floor, RG towers with the cool kids

PostPosted: Mon Apr 16, 2012 10:12 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
EdCarden



Joined: 25 Nov 2008
Posts: 88

PostPosted: Tue Apr 17, 2012 7:23 pm    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
peter.peart
Site Admin


Joined: 02 Sep 2008
Posts: 362
Location: Top floor, RG towers with the cool kids

PostPosted: Wed Apr 18, 2012 1:48 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
sean.alexander@ontario.ca



Joined: 28 Mar 2012
Posts: 6

PostPosted: Tue May 01, 2012 9:17 pm    Post subject: Database Compression Reply with quote

I enabled Database Compression on the tables which greatly reduced the size of the database with very little penalty.
Back to top
View user's profile Send private message
Chris Spencer



Joined: 29 Aug 2006
Posts: 301
Location: Red Gate - Cambridge

PostPosted: Wed May 02, 2012 10:26 am    Post subject: Reply with quote

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/viewtopic.php?t=12585

Regards
Chris
_________________
Chris Spencer
Test Engineer
Red Gate
Back to top
View user's profile Send private message
EdCarden



Joined: 25 Nov 2008
Posts: 88

PostPosted: Tue Jul 03, 2012 6:05 pm    Post subject: Re: Reply with quote

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
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group