In order to limit the growth of a vCenter Server database, you can use the database retention policy settings to discard unneeded data regarding tasks and events after a specified period of time. This is important to understand because one of two outcomes can occur pretty easily over time….each causing major problems at the most inconvenient of times:
- Though the hard drive on which the database resides can have plenty of free space available, if you are using SQL Express for your vCenter database, the database size limit of 4GB (SQL Express 2008 and earlier) or 10GB (SQL Express 2008R2 and later) may be reached.
- Though you may be using full SQL and have no database size limit, a hard drive typically doesn’t though you can take steps to increase hard drive space available to your applications pretty easily in a virtual world.
Personally, I have seen these issues in both the largest and the smallest of environments (as few as 12 VMs), its simply a matter of time depending on how the database retention policy settings are configured.
Here are the typical database retention policy settings I see in a vCenter 5.x environment:
Notice that the Tasks and Events retained for options are set to 180 BUT are unchecked. In this case, items will not be purged from the database and so, the database will continue to grow.
At this point, I’m going to pretend that one of two things has happened:
- the vCenter server is hosed because the SQL express database has reached it capacity
- the vCenter server is hosed because the SQL database is HUGE since it has 5 years of old task and event data and you want to delete it. Maybe you’ve added space to the vCenter VM (its running on a VM right?) to get the vCenter service running but you really want to get rid of that data….
Though the most likely cause for database growth will be the dbo.VPX_EVENT, dbo.VPX_EVENT_ARG, and dbo.VPX_HIST_STATx tables, you can see the sizes of specific tables within the database by looking at the Disk Usage by Table SQL Report as shown below:
As you can see, on my vCenter server, the dbo.VPX_HIST_STATx tables are consuming the bulk of the space. There are (4) tables which represent daily, weekly, monthly, and yearly statistics. To remove all of the data contained within the dbo.VPX_HIST_STAT1 table, execute the following Query using SQL Management Studio:
- truncate table VPX_HIST_STAT1
If you refresh the table report, you will see that the VPX_HIST_STAT1 table has indeed been truncated:
Honestly, and I admit this to my shame….I had the idea that truncating this data would “shrink” the size of my database file, the actual MDB file itself, but I have not seen that to be the case. What I have seen, by using the sp_spaceused query, is that though the database_size remains constant, the unallocated space of the database increases as I truncate the tables:
Finally, now that the vCenter server service has started and VMs are working once again, take the opportunity to set the database retention policy settings in such a way as to meet any retention requirements your company may have while also avoiding the situation described above in the future.