One of the aspects at times overlooked is the growing size of the databases for longer periods. Of course leaving the default settings and not paying attention to or not including mantainance tasks will grow the database size to a point where it is difficult to Backup or even try to reduce the size of both data and log files. The vCenter Databse from this point of view makes no exception. The purpose of this article to show the steps on how to shrink vCenter Database.
We have been all there: trying to recover a database grown to a point it is taking all free space in the Data and Log volumes. When this happens there is a number of symptoms we can easily detect. From errors and warnings in the event or log viewer to applications failing to connect to their pertinent databases just because the Data disk is full and cannot be expanded.
For this particular instance the steps cover how to shrink vCenter database sitting on a SQL Cluster.
The steps are really easy and it is just a matter to perform the following tasks:
- stop the VMware vCenter Service
- change the VPX Parameters
- run the SQL Stored Procedure to purge old data
- restore the VPX Parameters (optional step)
- shrink vCenter Database Data and Log files
- start the VMware vCenter Service
- check vCenter Database settings
It is a good idea (read best practice) to include the VMware vCenter Database in the mantainance task procedures already in place for other databases. Which includes at least regular Full backups and Transaction logs, checking Indexes, checking Tables size and purging old data. Of course the option to shrink vCenter Database is a result of further optimizing the free space for both the Data and Log Database files. Ideally we can run these tasks using a thrid party application to manage the databases (like Management Studio in case of SQL databases) or make sure at least the Transaction Logs are backed up (read truncated) avoiding for these ones to grow forever.
So let’s take a look on the steps to shrink vCenter database in VMware.
Steps to shrink vCenter database in VMware 6.5
Before starting with the procedure to shrink vCenter Database let’s make sure we have an application and crash consistent Backup of both Database and SQL Servers!
In this first step we are going to stop the VMware vCenter service to make sure no read and write operations are running against the vCenter database. To accomplish this we can use the Services Manager in Windows and stop the VMware vCenter Service. In this case I’m using a VMware vCenter 6.5 and such service is not visible from the Windows Services Manager console.
For this reason what we can do instead is to tuse the command line as per screenshot below. Using either the Windows Command prompt or PowerShell we navigate to the
%ProgramFiles%\VMware\vCenter\Bin
In my case the vCenter is installed on the “E:\” drive.
There’s a number of binaries we can use for vCenter Management and in this instance we’ll use the “service-control”. Like in the screenshot below let’s run
“service-control –list”
to get an idea of all services we can control.
The list of VMware services is quite extensive. The one we are after is “vpxd” service.
If we issue the command
“service-control –list | select-string vpx”
will return just the services maching this name. Also a confirmation of the VMware service name to stop.
At this point we are ready to stop the VMware vCenter Service with a simple command like:
“service-control –stop vpxd”
This operation will take a few seconds to complete so let’s wait for the final message.
Now that the VMware vCenter Service is not running we can edit the default VPX Parameters. All we have to do is to open the VMware vCenter database with SQL Management Studio or similar an browse to the dbo.VPX_Parameter table.
From here let’s select to show the top 1000 Rows.
SQL Management Studio will now show the value of the default VPX parameters. Before starting to shrink vCenter Database we need to purge old data. In particular there are 4 Rows we can use to achieve this:
- event.maxAge
- event.maxAgeEnabled
- task.maxAge
- task.maxAgeEnabled
These Rows define in days for how long we want to retain data for All Events and Tasks. And of course if we want to enable this configuration.
By default the values are set to 30 days. Changing to a longer period will inevitably grow the vCenter database very large. So we can start reducing this to maybe 15 days or 7 and work into stages.
The bigger the database the longer it is going to take purge such data.
Likewise when we scroll down let’s also make sure to change the values for task.maxAge in days and task.maxAgeEnabled to true.
The next step is to run the Stored Procedure built-in to vCenter Database which will run and purge stale data.
From the vCenter database let’s navigate to Programmability > Stored Procedures > dbo.cleanup_events_tasks_proc.
Let’s do a right click and chose to excute the Stored Procedure.
The wizard will also offer the option to review the settings in a script which will look something similar to this one.
From the wizard let’s click to proceed. The bottom of that window will show the current status. Depending the amount of data it might take a long time to complete. From this perspective it might be a good idea to work this in smaller chunks.
For example we can lower the value from 30 days to 15 and eventually repeat the same steps setting the value to 7. This ways we can remove the same amount of old data but in different chunks.
Also another important consideration to make is when running such operations all changes will be automatically logged in the Transaction Log files and kept there until flushed with the next occurrence of a SQL Transaction Log Backup. In other words the vCenter database log file will grow.
So the option to shrink vCenter data base also implies the fact we have to take care of both Data and Log files as we cover this with the next screenshots.
We are now ready to start to shrink vCenter Database. All we have to do is a right click on the vCenter Database and choose Tasks > Shrink > Files.
From here we can check the actual size of both the Data and Log files.
Data file is currently using 13 GB of space allocated but after the Stored Procedure to clean up old data only 3 GB are effectively used. That Stored Procedure freed up to 76% within the Data file. Not bad at all!
What we can do is to go with the first option to “release” unused space and click on Script button to generate one. This way we can check and execute the script from the Management Studio console.
What this command is doing is to “consolidate” all space inside the Data file.
As a next step we can now repeat the same operation to shrink vCenter database file choosing to “Reorganize” the space within the SQL data file and also set a physical size for the Data file. This way the Operating System can claim back the free space.
If we now re-open the shrink option we can see the new size for the database data file.
Let’s repeat the same steps for the vCenter database Log file.
As an addtional step we can also leave the values to the default ones for 30 days.
Once completed we can go back to PowerShell prompt on the VMware vCenter Server and start the VPX service with
“service-control –start vpxd”
At this point let’s login to the vCenter Console and configure general settings and browse to the Database section.
In the Database section let’s make sure the values are set to “Level 1”.
What’s interesting is this Database size calculator wich offers an estimate of the vCenter Database size based on the number of physical VMware Hosts and Virtual machines. Simple and immediate.
And this concludes this quick article on how to shrink vCenter database. Although this particular instance is based on VMware vCenter 6.5 in reality it is coming from an upgrade from an existing vCenter 6.0 environment with lots of history already.
Add Comment