Purging old events from the "historical" databases

[griese]

Hello,

We are looking at purging some of the old events in our View historical databases.  We've got floating pools that refresh on user logoff so the databases are getting pretty big (>30GB).  I've seen KB 1025914, but that is referencing the vCenter Tasks and Events.  Does anyone have any experience doing it with the event_data_historical (and event_historical) databases?

Thanks,

Troy

[LindseyJ · Accepted solution]

I'm posting this because I hade the same issue. My event database turned out to be HUGE. Since we decided we didn't need the old data I decided to find a way to purge some of the data.

The sql query I used is crude but it worked. I am by no means anything close to a sql query person .

delete from [View-Event].[dbo].[v_event_data_historical]  where EventID in (select EventID from [View-Event].[dbo].[v_event_historical] where Time < DATEADD(day,-30,getdate())) 
delete from [View-Event].[dbo].[v_event_historical] where Time < DATEADD(day,-30,getdate())

Our database is called View-Event and our table prefixes are v_ so you will need to modify it to your settings.

the

Time < DATEADD(day,-30,getdate()

is what i use to get specify anything older then 30 days. You will want to modify this to match your retention desires.

I created a sql job to run this monthly to keep my database size in check.

I'm sure there are better ways to do this but this is the best I could come up with in the 20 minutes of time I had to work on this.

Justin

[griese]

Thanks for the reply!  I'll have to give it a shot and let you know how it goes!

Troy

[griese]

You're commands were great to start with.  My event_data_historical table has over 141 Million rows in it so it took a long time to parse through the Time field and find those older than the time specified. I anticipate using your syntax after I prune it down.  Since the table is chronological (*see correction below), I cleaned mine up based on the TOP entries so it didn't have to do any comparisons of the actual data.  Here's a snapshot of the code I did used.

TIP: set your database to Bulk-logging before doing this.

In case anyone is interested in the time it took to run this.  It took 1m 15sec to delete 200,000 entries from the historical table and 1,105,283 entries from the data_historical table.  It got me back about 100MB of space.

My database is named viewEvents.  My prefix is VE.

/***This sets the database***/
USE viewEvents
/***These display the DB and table sizes prior to any deleting***/
EXEC sp_spaceused
EXEC sp_spaceused N'[viewEvents].[dbo].[VE_event_historical]'
EXEC sp_spaceused N'[viewEvents].[dbo].[VE_event_data_historical]'
/***This displays the top 100 EventIDs and the time they occured***/
/***This is so you can see what range of dates you would be deleting***/
SELECT TOP (100) [EventID],[Time] FROM [viewEvents].[dbo].[VE_event_historical]
/***This displays the data_historical rows to be deleted based on the top 100 EventIDs prior to deleting them***/
/***This is to verify the data you are to be deleting***/
SELECT * FROM [viewEvents].[dbo].[VE_event_data_historical] WHERE EventID in (SELECT TOP (100) [EventID] FROM [viewEvents].[dbo].[VE_event_historical])
/***This actually deletes the data_historical table rows***/
DELETE FROM [viewEvents].[dbo].[VE_event_data_historical] WHERE EventID in (SELECT TOP (100) [EventID FROM [viewEvents].[dbo].[VE_event_historical])
/***This actually deletes the historical table rows***/ 
DELETE TOP (100) FROM [viewEvents].[dbo].[VE_event_historical]
/***These display the table and db sizes post deletion for comparison***/
EXEC sp_spaceused N'[viewEvents].[dbo].[VE_event_data_historical]'
EXEC sp_spaceused N'[viewEvents].[dbo].[VE_event_historical]'
EXEC sp_spaceused

I still need to shrink the database down after I'm done, but hopefully it will be a little less unwieldy.

I hope this helps!

Troy

*CORRECTION

The event_historical table appears to be roughly chronological so be warned that if you use the TOP parameter to determine the oldest records then you may not really be getting the oldest records.  It worked well enough for me to get my database down to managable size so it served my purpose.

[LindseyJ]

I'm not sure how long it took to clean up my databses. I know it took some time but when that and the shrink were done my database went from over 3GB to around 300MB. Hopefully scheduling the monthly cleaning will keep the database under contol.

Justin

[griese]

We're using floating pools with refresh on log off in a dynamic healthcare environment.  Our viewEvents database is about 38GB.  We've been in production use since March of this year.  I think we see something like 62,000 events a day.  Hence the 140+ million rows.

[LindseyJ]

Yeah we use floating pools that refresh on logoff in student labs in the school district. We get quite a few logins through out the day but we are currently only deployed in 5 schools with plans to expand. We have 4 high schools and their feeder schools so once this thing in in full swing I fully expect our database would get out of control in a fairly short amount of time.

Have you noticed if any other databases get out of control with all the refreshes? Does the composer and vcenter databases stay in check?

Justin

[griese]

The Composer DB is minimal (<20MB), but vCenter is getting big.  Currently at 18GB.  Fortunately that has some managment fuctions from within vCenter though.

Administration --> vCenter Server Settings... --> Database Retention Policy.

We'll probably have to be turning those on too! 

Troy

[mayjono]

Thanks for posting this mate