I’ve found that a lot of vCenter installs often have the data collection cranked up beyond what is actually needed. In most cases, the lowest level of collection is needed and doesn’t consume that much room provided you have a reasonable number of hosts/guests and a reasonable amount of disk space available.
First thing to check is the settings for the data retention in your vSphere Client. Click to Home and then look for “vCenter Server Settings” kind of in the middle of this screen shot:
Click it, and check the “Statistics” option first. In my case I have just the 5 minute intervals set to Level 2 and I keep them for one day. This lets me have more detailed counters for cpu and network which I sometimes find useful for debugging things, while anything older than a day is just Level 1. If disk is very critical, set it to Level 1 as well and just turn it on when you need it.
Keep in mind that you have to reduce the log level starting from the bottom because you can’t have the one day interval set to level 2 while the 2 hour interval is set to level 1, since the data would no longer be available. Start at the bottom and work up if you’re reducing them.
If you encounter any issues changing the settings, you may need to just click OK and come back in and try again; for some reason I received the error “the statistics level for an interval cannot be greater than its previous interval” when trying to reduce the 1-day level from 2 to 1, and that should of course never have occurred since I was reducing the longest interval below the previous interval, which was also 2. Clicking out and coming back in resolved it and let me change the setting.
If you have had your install running for longer than your longest “save for” time frame, and you’re not in a high growth environment, then chances are if you reduced some of the above, you can just let vCenter go about its normal business from this point forward as your disk space will not grow much more since the SQL databases will have already expanded to house what had been more data than what will be put into them in the future. If you really need to purge though because you’re dangerously low on disk space, here’s a kb article on how to do that:
Next thing to check is your “Database Retention Policy” for tasks and events. These don’t take a massive amount of space but they do take space never the less. I don’t know what’s typical or not but as a reference, I have one vCenter cluster with 10 hosts and about 300 guests and they generate about 800 MB of data in the SQL table VPX_EVENT_ARG every 180 days. So set these to whatever you need to keep and not more than that.
Now, assuming you’re really low on disk and you have made some changes to reduce usage, or had to go through the purge process I linked to above, next task is to shrink the actual SQL databases since ideally they won’t grow back to the size they were before after your adjustments to your settings. If you’re running vCenter on SQL Server standard or enterprise, fire up the Management Studio app. Right click on your vCenter database name and you can initiate a shrink task:
It will let you see how much room you stand to recover before you initiate it so you’ll know if it will be worthwhile or not. Here’s what it looks like for me actively shrinking a 37 GB database down by about 15 GB:
This 15 GB shrink took about 4 hours to run on what is a pretty fast server, so this is not a fast process, AND, you cannot run a shrink while a backup is running, nor can you initiate a backup while a shrink is running, so if your database is really huge, plan around that so it doesn’t mess your backups up.
Once shrinking is done, you will probably want to rebuild your indexes for good measure as they’re now likely heavily fragmented. Here’s a script I found that will let you rebuild all indexes on all databases in one shot. This is not something you should do if your vCenter database is on a shared MS SQL server, only do this if your SQL server is exclusively for vCenter, OR, if you have a database admin modify the script to only run on your vCenter database instead of all databases it finds. Fortunately this rebuild only took about 8 minutes after the 4 hour shrink completed.
DECLARE @Database VARCHAR(255)
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 90
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','msdb','tempdb','model','distribution')
ORDER BY 1
OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +
table_name + '']'' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES
WHERE table_type = ''BASE TABLE'''
-- create table cursor
EXEC (@cmd)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@@MICROSOFTVERSION / POWER(2, 24) >= 9)
BEGIN
-- SQL 2005 or higher command
SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' +CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@cmd)
END
ELSE
BEGIN
-- SQL 2000 command
DBCC DBREINDEX(@Table,' ',@fillfactor)
END
FETCH NEXT FROM TableCursor INTO @Table
END
CLOSE TableCursor
DEALLOCATE TableCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
I found the script at http://www.mssqltips.com/sqlservertip/1367/sql-server-script-to-rebuild-all-indexes-for-all-tables-and-all-databases/ but am reproducing it here in case that site ever goes away.
Following the recommendations on http://www.itsupportforum.net/topic/vmware-vcenter-database-is-huge/ I ran the vcenter roll up jobs in SQL to truncate the DB.
Probably your roll up jobs aren’t working if it’s got like this?