VMware Horizon doesn’t restrict the growth of the historical tables in the Horizon Events database. VMware has a detailed knowledge base article with describes in details Purging old data from the View Events Database (2150309). However, there is a catch if you are trying to delete many records at one time, you will get transaction log full error. The below procedure will help you overcome the challenge. In our scenario, we purge the records once every year.
use HZNLOG
select count(*) from [dbo].[POD1_event_data_historical] where EventID in (select EventID from [dbo].[POD1_event_historical] where Time < '2021-01-31 00:00:00.000')
select count(*) from [dbo].[POD1_event_historical] where Time < '2021-01-31 00:00:00.000'
In the above example HZNLOG is the name of the database. POD1 is the prefix of the Horizon Events Database (Check in Horizon Admin console) and 2021-01-31 is the YYYY-MM-DD format (Show me all records before 31st Jan 2021)
If we used the delete tables mentioned within the knowledge base article, we get the following error “The transaction log for database ‘HZNLOG’ is full due to ‘LOG_BACKUP”. Of course, the number of records in our case we are trying to delete is relatively high(Millions).
You can shorten the above query for approx. 30 or 15 days, but still in our scenario, one would have to run the delete query more than 15 times to perform the annual clean-up. After searching around, I came across a blog post – Deleting millions of records from a table without blowing the transaction log (A big thank you Merill for sharing his knowledge) I tweaked it for my usecase of Horizon Events DB clean-up and, in a single query within 20 mins I could perform a yearly clean-up without any fuss of transaction log getting full. Essentially this performs the clean-up in a batch size of 10,000 row counts.
DECLARE @continue INT
DECLARE @rowcount INT
SET @continue = 1
WHILE @continue = 1
BEGIN
PRINT GETDATE()
SET ROWCOUNT 10000
BEGIN TRANSACTION
delete from [dbo].[POD1_event_data_historical] where EventID in (select EventID from [dbo].[POD1_event_historical] where Time < '2021-01-31 00:00:00.000')
delete from [dbo].[POD1_event_historical] where Time < '2021-01-31 00:00:00.000'
SET @rowcount = @@rowcount
COMMIT
PRINT GETDATE()
IF @rowcount = 0
BEGIN
SET @continue = 0
END
END
The ouput will look something like below:
After running the above deletion query, now re-run the select query to see if records exist before 31st Jan 2021, and now we have 0 records.
I hope you will find this SQL query helpful to perform Horizon Events Database clean-up in a jiffy. My request if you further enhance the query or make it more creative, I hope you can share it back with me?
Thanks,
Aresh Sarkari
