Checking vCenter DB Table sizes

Sometimes you can experience issues with the vCenter Database getting too large. If you want to check the table sizes to confirm this, please do the following.

• Open SQL Management Studio
• Select vCenter DB name
• Execute the following query to get Table sizes

Create Table #Temp(Name sysname, rows int, reserved varchar(100), data varchar(100), index_size varchar(100), unused varchar(100))

exec sp_msforeachtable ‘Insert Into #Temp Exec sp_spaceused ”?”, ”true”’

Select * From #Temp

Drop Table #Temp

Purge Scripts

There are a set of purge scripts where you can set how much historical data you keep and it will remove everything else from the DB.

Take a look at this KB, it runs through the process and has a link to the scripts:

http://kb.vmware.com/kb/1025914

One thing I will say is that the scripts can take a long time to run, If the size of your DB is quite large, it could take quite a while to complete. I would kick it off and let it run over the weekend if it hasn’t completed by the end of the day. Also the scripts are perfectly safe but I would take a backup of the DB before doing anything just for peace of mind

DB Maintenance

You should run a shrink on the DB to remove empty space and a regular backup is a good idea as MS SQL uses this to do maintenance on the DB. If you check out the Microsoft documentation for your version of MS SQL there should be detail and recommendation for setting that up

Useful Links

Defragmenting VirtualCenter performance data indexes on a Microsoft SQL database:

http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1003990

Unable to get an exclusive access to the vCenter Server repository:

http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1006369