Archive | Microsoft SQL Server RSS feed for this section

VMware App Volumes Database – Annual Clean-up (Pruning old data)

22 Jul

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:

Select SQL Query on AV DB

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:

Delete SQL Query – AV DB

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

Tags: , , , ,

VMware Horizon Events Database – Annual Clean-up (purge old data)

14 May

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)

No. of older records in Events DB

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

Error during deletion “Log is full”

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:

Enteire deletion in batches of 10K rows

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.

Zero records found

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

Tags: , ,