VMware vCenter Server 5.1 Database is Full

vmware

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.

13 thoughts on “VMware vCenter Server 5.1 Database is Full

  1. Hi,
    Many thanks for the excellent write up and willingness to share,i followed the steps in the article it works like charm.
    Please keep it up .
    Cheers!!!

  2. Thanks so much for this! I found VMware’s KB article(s) about this, and this is much easier to digest and just as helpful. As others have mentioned #2 was what actually freed an appreciable amount of space up, with the added bonus of preventing these events from filling up the DB again.

  3. Rene Skriver says:

    Thank you sir. This got my vCenter server up and running again, after many other failed attempts.

    What differs in this guide from the others I’ve tried is the dbo.cleanup_events_tasks_proc, so I believe this is the golden egg.

    • Hi,

      Yes works fine on vCenter 5.0, 5.1, 5.5 not sure on earlier than that but 4.1 is unsupported now anyway.

      Thanks for reading.

  4. Dmitriy Dvoryanchuk says:

    The last step didn’t free up the space for me, I had to manually execute the query:

    DBCC SHRINKDATABASE ( VIM_VCDB , 5);

    Thanks for the excellent writeup!

  5. Thx for the dbo.cleanup_events_tasks_proc hint, I wasn’t able to release space with any other steps, only manually execute dbo.cleanup_events_tasks_proc was releasing 8 GB!

  6. Chris Smallwood says:

    I really appreciated this write up. I have been struggling with this for months. Nothing that I have tried up until this point has helped much at all. This article freed up almost 50% of the DB!!! Thank you!!

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>