Purging old events from the View Events database

Do you have a lot of non-persistent floating pool desktops that refresh on logoff?

Do you point View Administrator to a SQL Event Database to store historical events?

If so, I've found that the database can get quite big as it seems to basically keeps events forever.

There are VMware KB's for purging the vCenter database of old records, but nothing for View Events.
Then I found the following SQL query on the VMware Community boards:

https://communities.vmware.com/message/1881999

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())

and I changed it to the following for my needs:

delete from [ViewEvents].[dbo].[viewevent_data_historical] where EventID in (where EventID from [ViewEvents].[dbo].[viewevent_historical] where Time < DATEADD(day,-365,getdate()))

delete from [ViewEvents].[dbo].[viewevent_historical] where Time < DATEADD(day,-365,getdate())

Where [ViewEvents] is the name of my Events Database and since I have the table prefix set as "view" in View Administrator,  [viewevent_data_historical] and [viewevent_historical] are the names of the tables that need to be purged of old events.  If using the above, you'd need to modify those to your table names.  

Also note where I changed "-30" to "-365" which is where you specify how old the data has to be to be deleted.  I chose 365 days as a safe starting point because 1 year's worth of old Event data is more than I'll ever need, but it's still there if I need to review the past year for some reason.

Even keeping 1 year of data reduced my ViewEvents database size from almost 20GB to ~7GB once I performed a database shrink after running the query.  There were at least 2-3 extra years of accumulated old data.

One last thing, if you don't have a lot of disk space and this is the first time running the cleanup, change your logging of the database to Bulk-logging or you'll run out of drive space with all of the transaction logs before it completes.

I also ended up setting up the above query to run as a weekly maintenance cleanup job to keep the Event database size consistent over time.