Today I had some issues with my vCenter(5.0.x). The database was not working and even vCenter services were running no connections was possible to the DB(SQL 2008 Enterprise SP1). VMware: vCenter not starting – VCDB database ‘PRIMARY’ filegroup is full
After checking the logs(vpx) from vCenter I found some errors
[SQL Server]Could not allocate space for object ‘dbo.VPX_HOST_VM_CONFIG_OPTION’.’PK_VPX_HOST_VM_CONFIG_OPTION’ in database ‘VCDB’ because the ‘PRIMARY’ filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.”
Note: Logs of vCenter are located at \ProgramData\VMware\VMware VirtualCenter\Logs
Check also KB 1021804 for vCenter logs location.
This means all connections to DB were dropped and that the ‘PRIMARY’ filegroup is full and it consumes all my disk space where DBs were allocated(in this case was d:)
I had lot of old trash information in the DB, need to purge most of the information and also shrink the DB to fix this issues. Also my vCenter DB was growing to much(was 35Gb).
To clean the DB we need to run some SQL scripts.
These Script can be found in VMware KB 1025914
Using SQL Management Studio connect to your DB Server(local or remote).
Note: Before running the script check if you have the right DB connected(standard DB name for vCenter is VCDB or VIM_VCDB)
Before you run the script you can change the days of data you want to keep. The standard is 180 days, in my case I just want the last 30 days, the rest was to delete.
Change here: SET @CUTOFF_DATE = GETUTCDATE()-30 (number of days that you want to have data in your DB)
Stop all vCenter services
Create a full backup of your DB
Run the script with the flag @DELETE_DATA = 0 (this is the default). This only count the rows on the DB, will not delete any data.
After checking all the information that was collected in the previous step, just change the @DELETE_DATA to 1 and run the script to delete the data.
These task can take very long time. It depends on the size of your DB and the amount of information that you have. My task was around 3h.
After you purge all old data, you need to shrink your vCenter DB. In you SQL Management Studio go to Object Explorer and right click in your DB(this case VCDB), tasks and then Shrink database.
After this you can restart you vCenter services and check if everything is working smoothly.
Hope this can help.
Note: Share this article, if you think is worth sharing.