Monday, July 24, 2017

http://www.stopdoingitwrong.co.uk/vmware-vcenter-server-5-1-database-is-full/

 

1. Truncate all performance data

1.1 Open SQL Management Studio and connect to the database.

1.2 Right click on the vCenter database (named VIM_VCDB by default) and click ‘New Query’.

VCSQLQue

Copy the following into the right hand pane and click ‘execute’.

    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

1.3 Start the vCenter Server Service.

2. Purge old data from the database

2.1 Connect to the database with SQL Server Management Studio.

2.2 Expand ‘databases’ –> ‘VIM_VCDB’ –> ‘Tables’ and locate ‘dbo.VPX_Parameter’, right click on it and click ‘Edit Top 200 Rows’.

 

2.3 Edit the rows ‘event.maxAge’ and ‘event.maxAgeEnabled’ as per below.

VCSQLtab

2.4 Navigate to ‘VIM_VCDB’ –> ‘Programmability’ –> ‘Stored Procedures’ and right click on ‘dbo.cleanup_events_tasks_proc’ then click ‘Execute Stored Procedure’. This will purge old task and event data from the database and may take some time.

VCSQLSto

2.5 Start the vCenter Server Service.

3. Shrink the SQL database

3.1 As before connect using SQL Server Management Studio.

3.2 Right click on the VIM_VCDB database and click properties. Under options set the ‘Reovery model’ to ‘Simple’ and click OK.

VCSQLRec

3.3 Right click on ‘VIM_VCDB’ and go to ‘Tasks’ –> ‘Shrink’ –> ‘Database’.

VCSQLShr

3.4 Click on OK to shrink the database.

3.5 Start the vCenter Server Service.