VMWare vCenter SQL Express Database Full
Last Updated: 8/6/16My 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.
