Database Retention policy

[MrVmware9423]

Dear Team,

In the below snap check box is not checked for "task retained for" and "Events Retained for". what does it mean it will retain task for 180 days or indefinitely.

 

regards

Mr VMware

[vThinkBeyondVM]

If you set to 180 days by selecting these check boxes.. It is mean that vCenter database will keep tasks & events entries for 180 days for your reference. Specially you need to use this if you want to limit your database space usage.

Also if you do not use these options, these entries will be there all the time.

Message was edited by: Vikas

[Zade]

This means if you tick the "Tasks and Events" retained for that your vcenter will remove any tasks/events older than 180 days from the database.

You can change this if you like to a number which is nice for you, this will also reduce some of the size of your database, I'm currently working on a database reduction, there is also a Stored Procedure you can run on the vCenter database to cleanse this data from the database.

If you haven’t tweaked the “Database Retention Policy” settings under the vCenter Server Settings, it is a good idea to do this also.

• Under vCenter database > Tables > Right-Click the dbo.VPX_PARAMETER table and click Open.
• Modify event.maxAge to 30 (or another reasonable value), and modify the event.maxAge.Enabled value to true.
• Modify task.maxAge to 30, and modify the task.maxAgeEnabled value to true.

  It is recommended that you jump down from 180 days in increments or you will fill up the Transaction Log , you can then run the below procedure and repeat the above to changing the days in increments.

Steps:

1. Run stored procedure below
2. Run transaction log backup
3. Shrink database

See below for full instructions on steps

Step 6

Run the built-in stored procedure:

• Navigate to Programmability > Stored Procedures.
• Right-click dbo.cleanup_events_tasks_proc and click Execture Stored Procedure.

Script below:

USE [Vmtest_vcenterdb]

GO

DECLARE                @return_value int

EXEC       @return_value = [dbo].[cleanup_events_tasks_proc]

SELECT    'Return Value' = @return_value

GO

• This purges the data from the vpx_event, vpx_event_arg, and vpx_task tables based on the date specified for maxAge.
• The last action is to shrink the database files. Right-click the vCenter database > Tasks > Shrink > Database

Note: You can run the below script on the database if shrink is taking its time to run through to check the progress:

SELECT

                    percent_complete,

                    start_time,

                    status,

                    command,

                    estimated_completion_time,

                    cpu_time,

                    total_elapsed_time

FROM

                    sys.dm_exec_requests

WHERE

                    command = 'DbccFilesCompact

[MrVmware9423]

Thanks Zade,

U mean to say if the option is unchecked then it will stoe data indefinitely.

regards

Mr VMware

[Zade]

Yeah if not ticked it'll just hold onto all event/task data in DB, also if you do check the retention boxes, it is worth checking in the VPX.Event table in the vCenter database by listing top rows to see if the creation time of the task matches the amount of days you have set as your retention. As it may not delete that historical database, you may need to do it manually... with the above stored procedure.

[Zade · Accepted solution]

Here is the full plan I put together for my project im working on to reduce our vcenter DB size:

vCenter Database Reduction

Checklist before starting

1. Full database backup of vCenter database
2. vCenter service MUST be STOPPED

Reducing the size of the vCenter Server database

Please note that running the below scripts on the vCenter database will clear ALL historical data, please make sure you have a FULL verified backup of the database.

Step 1

• Make sure you have a FULL BACKUP of vCenter DATABASE!!!
• Make sure that the VMWare Virtual Center service is stopped.

Step 2

The largest tables in the vCenter database are the table named:

1. VPX_HIST_STAT [1-4] tables and corresponding
2. VPX_SAMPLE_TIME [1-4] tables.

  Truncate these tables by executing the follow commands: 

• Truncate table VPX_HIST_STAT1_n  (1-4)
• Truncate table VPX_SAMPLE_TIME1 (1-4)
• Truncate table VPX_TEMPTABLE0
• Truncate table VPX_TEMPTABLE1
• Truncate table VPX_TEMPTABLE2

Step 3

1. Log into the vCenter server database using SQL management Studio
2. Copy and paste the contents of the SQL_truncate_5.x.sql script into SQL Management studio
3. Make sure the correct database is selected
4. Execute the script to delete the data

Step 4

Run the VCDB_Purge_MSSQL script now, it should finish immediately, telling you, it did not find any records to process.

Step 5

If you haven’t tweaked the “Database Retention Policy” settings under the vCenter Server Settings, it is a good idea to do this also.

• Under vCenter database > Tables > Right-Click the dbo.VPX_PARAMETER table and click Open.
• Modify event.maxAge to 30 (or another reasonable value), and modify the event.maxAge.Enabled value to true.
• Modify task.maxAge to 30, and modify the task.maxAgeEnabled value to true.

  It is recommended that you jump down from 180 days in increments or you will fill up the Transaction Log , you can then run the below procedure and repeat the above to changing the days in increments. 

Steps:

1. Run stored procedure below
2. Run transaction log backup
3. Shrink trans database

See below for full instructions on steps

Step 6

Run the built-in stored procedure:

• Navigate to Programmability > Stored Procedures.
• Right-click dbo.cleanup_events_tasks_proc and click Execture Stored Procedure.

Script below:

USE [Vmtest_vcenterdb]

GO

DECLARE                @return_value int

EXEC       @return_value = [dbo].[cleanup_events_tasks_proc]

SELECT    'Return Value' = @return_value

GO

• This purges the data from the vpx_event, vpx_event_arg, and vpx_task tables based on the date specified for maxAge.
• The last action is to shrink the database files. Right-click the vCenter database > Tasks > Shrink > Database

Note: You can run the below script on the database if shrink is taking its time to run through to check the progress:

SELECT

                    percent_complete,

                    start_time,

                    status,

                    command,

                    estimated_completion_time,

                    cpu_time,

                    total_elapsed_time

FROM

                    sys.dm_exec_requests

WHERE

                    command = 'DbccFilesCompact'

Step 7

Run the rollup scripts using these steps:

• Using SQL Management studio, connect to the SQL database for vCenter Server.
• Navigate to SQL Server Agent > Jobs
• Select the individual rollup jobs, right-click and select Start Job at Step.

Step 8

Start-up VMWare Virtual Center Server service and perform some health checks on the system.

Back out plan

1. Restore backup file over existing database and remap users

[MrVmware9423]

Thanks Zade for detailed explanation.

In step2 it will delete complete data from tables, what if from last 6 month I want current month data and need to remove last 5 month data then what to do ??

regards

Mr Vmware

[Zade]

See the below, the VPX HIST table are tables for performance data, this clears all performance data from the database, which will then build backup as you use vcenter, if you do not want to clear all performance data (when you click on the performance tab in vcenter for CPU, memory etc) see below.

The stored procedure for VPX.EVENTS AND TASK retention will clear only information for tasks and events, see below if you wish to keep some performance data, I chose not to as this will build up over time anyway.

VMware KB: Reducing the size of the vCenter Server database when the rollup scripts take a long time...

Warning: This procedure erases all historical data. If you want to retain some historical performance data instead of deleting all of it, see Purging old data from the database used by vCenter Server (1025914) or Purging old data from the database used by VirtualCenter 2.x (1000125).

[Zade]

Please bare in mind it is not good to shrink the database over and over (transaction log is fine), so when doing the incremential steps, do the below:

1. Change event/task parameter
2. Run stored procedure as above
3. Run full backup
4. Run Trans backup
5. Shrink TRANS log, and not database

Repeat steps until you get to the number you are happy with for event/task, and THEN run your Shrink on the DB at the end. (I would recommend dropping in increments of 20/30 to stop your transaction log from filling up)