The VMware App Volumes database archival tables grow constantly, and it can quickly outgrow the database size. To prevent this growth, it is advisable to perform regular pruning of the database. In our scenario, we prefer to perform this activity annually as we have sufficient space to accommodate the growth. There are mainly three tables that need pruning. (log_records, snapvol_timeseries and system_messages)
VMware has a detailed knowledge base article decribing Pruning the VMware App Volumes SQL database (2132454). However, if you are trying to delete many records at one time, you will get a full transaction log error. The below procedure will help you overcome this challenge.
Query to identify the entries older than 60 days:
SELECT * FROM log_records WHERE created_at < DATEADD(day, -60, GETDATE());
SELECT * FROM snapvol_timeseries WHERE created_at < DATEADD(day, -60, GETDATE());
SELECT * FROM system_messages WHERE created_at < DATEADD(day, -60, GETDATE());
The output of the query will be as follows:
The challenge is if you ran the Delete query mention in the VMware KB it would work 100%. However, while deleting all the rows, the transaction log will run out of space. If you follow my procedure, the transaction log will still run out of space, but the amount of transaction logs flush you need to perform is minimized to a great extend.
This query will delete the rows in the batches of 10,000, allowing some room to perform the clean-up and no need to flush the transaction logs at every run. (In our scenario for 8 million records, I had to flush the transaction log twice.)
DECLARE @continue INT
DECLARE @rowcount INT
SET @continue = 1
WHILE @continue = 1
BEGIN
PRINT GETDATE()
SET ROWCOUNT 10000
BEGIN TRANSACTION
DELETE FROM log_records WHERE created_at < DATEADD(day, -60, GETDATE());
DELETE FROM snapvol_timeseries WHERE created_at < DATEADD(day, -60, GETDATE());
DELETE FROM system_messages WHERE created_at < DATEADD(day, -60, GETDATE());
SET @rowcount = @@rowcount
COMMIT
PRINT GETDATE()
IF @rowcount = 0
BEGIN
SET @continue = 0
END
END
The output after the clean-up:
I hope you will find this SQL query helpful to perform App Volumes 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?
You can also follow the procedure to clean-up Horizon Events Database – VMware Horizon Events Database – Annual Clean-up (purge old data) | AskAresh
Thanks,
Aresh Sarkari
