Articles > Virtualization
Printer Friendly Version
Views: 19261

VMWare vCenter SQL Express Database Full

Last Updated: 8/6/16

My environment:
VMware vCenter 5.1 (build 880146) running on Windows 2008 R2. (Previously upgraded from older version of vCenter)
Microsoft SQL Express 2005 Instance [vCenter] (TCP 53440)
Microsoft SQL Express 2008 R2 instance [Single Sign-On] (TCP 63668)


Problem:
vCenter SQL Express Database mdf file reach 4GB (the limit for a SQL express database). I found that the vCenter tasks and events were not being pruned at 180 days as configured.


Solution:
VMware KB: Purging old data from the database used by vCenter Server http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1025914


How I did it:
I tried to install SQL express, but kept having problems. A link to the correct version is below, but I ended up using the SQL studio installed on my PC. I connected remotely to SQL express. I created a firewall rule to allow TCP 53440. Then I connected with SQL studio from my PC like this:

vCenter-Hostname\SQLEXP_VIM,52775   OR  vCenter-Hostname\SQLEXP_VIM,62668
Authentication: Windows Authentication

Or you can download sql express management studio and run it from vCenter host:
Download SQL Management Studio 2005 Express (64-bit)
http://www.microsoft.com/en-us/download/details.aspx?id=8961

Note: Stop the vCenter service before continuing!





Backup DB before actions:
use vim_vcdb
checkpoint
backup database vim_vcdb
to disk='c:\temp\vCenter-db-vim_vcdb-before-db-shrink.bak'
with init

Check the status of an executing SQL task:
select * FROM [master].[sys].[dm_exec_requests]

Method 1: run cleanup from GUI
Connect to Servername\SQL Database and log in with the appropriate credentials.
Expand databases > VIM_VCDB > Tables.
Right-click the dbo.VPX_PARAMETER table and click Open.
Modify event.maxAge to 30, and modify the event.maxAgeEnabled value to true.
Modify task.maxAge to 30, and modify the task.maxAgeEnabled value to true.
Run the built-in stored procedure:

    Navigate to VIM_VCDB > Programmability > Stored Procedures.
    Right-click dbo.cleanup_events_tasks_proc and click Execute Stored Procedure.

    This purges the data from the vpx_event, vpx_event_arg, and vpx_task tables based on the date specified for maxAge.

    When this has successfully completed, close SQL Management Studio and start the VMware Virtual Center Server service.

Method 2: run cleanup from query window
EXECUTE [VIM_VCDB].[dbo].[cleanup_events_tasks_proc]
go


Then....

Shrink the DB:
dbcc shrinkdatabase('VIM_VCDB');

Check DB for corruption:
dbcc checkdb('VIM_VCDB');


Backup DB After Actions:
use vim_vcdb
checkpoint
backup database vim_vcdb
to disk='c:\temp\vCenter-db-vim_vcdb-after-db-shrink.bak'
with init

Check the status of an executing SQL task:
select * FROM [master].[sys].[dm_exec_requests]


Restart the Server or start the vCenter services, then you are done.





Keywords: vmware vcenter mdf database file full and service crashes