Truncate vcenter SQL Server database before the size reach 10G limit

Jephe Wu - http://linuxtechres.blogspot.com

Environment: SQL server 2008 Express edition on vcenter server, no SSMS installed by default
Objective:  learn how to truncate sql server database size in SQL Server Management Studio so that it won't exceed 10G limit, otherwise, database engine service will stop after 10G.


Steps:

1.  check the current database size
run the following query in new query window in SSMSE

exec sp_spaceused

2. stop Vmware VirtualCenter Service

Just click on Vmware VirtualCenter Server, then stop it, then it will also stop some other services due to dependency.

3. Stop SQL Server (VIM_SQLEXP) service from sql server configuration manager 

4. Backup data and log files for database VIM_SQLEXP somewhere else
Must make sure database service is stopped first then take backup.

5. Install SSMS 
Install locally on the same server or copy 2 database files over to another server which has SSMS installed.

refer to http://blogs.msdn.com/b/bethmassi/archive/2011/02/18/step-by-step-installing-sql-server-management-studio-2008-express-after-visual-studio-2010.aspx for installation

During installation of SSMS, choose 'Perform a new installation of SQL Server 2008' as follows:

Use SSMSE to connect to local Vcenter SQL express database

6. type in 'localhost' to connect

if error is showing it's unable to connect to database, go to SQL server configuration manager, SQL server network configuration, Protocols for VIM_SQLEXP,

enable IP1 and IP3 (changed 'Enabled' from no to yes), also, give tcp Port from null to 1433

If you copied data .mdf and log .ldf files to another server which has SSMS installed, then attach database then choose those 2 copied files

When you attach database to another full edition of SQL server,  SQL server must be the same version as original vcenter database 

7.  Check tables disk usage
Right click on database VIM_VCDB, reports, standard reports, disk usage by table to check table disk usage, normally, VPX_EVENT table should be very big.

8. Change event.maxage and task.maxAge to 7 days if it's not set yet. You may also set to 30 days as per suggested by Vmware KB

go to dbo.VPX.PARAMETER table to edit event.maxAge to 7 days, and also for task.maxAge.

9. To truncate the data in the VPX_EVENT table:

• 1. Connect to SQL Database and log in with the appropriate credentials.
  2. Expand databases ,VIM_VCDB ,Tables.
  3. Right-click the dbo.VPX_PARAMETER table and click Open.
  4. Modify event.maxAge to 7, and modify the event.maxAgeEnabled value to true.
  5. Modify task.maxAge to 7, and modify the task.maxAgeEnabled value to true.
  6. 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.

Note: this step might take around one hour time, be patient, do not touch anything once click

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

10. truncate all performance tables - optional

right click on VIM_VCDB database, new query, then paste the following code to truncate all performance tables.
refer to http://kb.vmware.com/selfservice/search.do?cmd=displayKC&docType=kc&docTypeID=DT_KB_1_1&externalId=1007453

Declare @current_table varchar(100) declare @sqlstatement nvarchar(4000) --move declare cursor into sql to be executed set @sqlstatement = 'Declare table_cursor CURSOR FOR SELECT name FROM  sys.tables where name like ''VPX_HI%'' or name like ''VPX_SAMPLE%''' exec sp_executesql @sqlstatement OPEN table_cursor FETCH NEXT FROM table_cursor INTO @current_table WHILE @@FETCH_STATUS = 0 BEGIN set @sqlstatement = 'truncate table ' + @current_table exec sp_executesql @sqlstatement FETCH NEXT FROM table_cursor INTO @current_table END CLOSE table_cursor DEALLOCATE table_cursor

11. Shrink database size
right click database, tasks, Shrink - database

12. References

Purging old data from the database used by VMware vCenter Server 4.x and 5.x(1025914) - http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1025914