VMware vCenter Server 5.1 Database is Full

If your vCenter Server Service won’t start and you are seeing event IDs 1105 and 1827 then your database is full. You are probably running the database in a SQL Express instance where the licensing limits the size of databases.

VCSQLEvent1

VCSQLEvent2

Here are a few things you can do to make the database smaller. These are taken from the following VMWare KBA’s http://kb.vmware.com/selfservice/microsites/search.do?cmd=displayKC&docType=kc&docTypeID=DT_KB_1_1&externalId=1025914 and http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1007453

You can run any or all of these as required to free up as much space as you deem neccessary. I would leave option 2 until last as it does clear down old event and task data, although hopefully you won’t be needing these older events.

If the vCenter Server Service is running then stop it before you carry out any of these and also ensure you have a good backup.

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.

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s