VMware vCenter Server 5.5 Database is Full

Posted by robd on May 27, 2015
SQL, vmware, vSphere

So today a vCenter Server Service wouldnt start Java was going mental and we kept seeing event IDs 1105 and 1827.  So what this means is our SQL Express instance reached the limits the size of databases i.e. 10GB on SQL Express 2008 R2.

Error_event1

Error_event

You can confirm this by looking at the size of the VIM_VCDB.MDF file in c:\program files\Microsoft SQL Server\MSSQL10_50.VIM_SQLEXP\MSSQL\Data, if its 10GB then your in trouble.

So the first thing to do is fire up Microsoft SQL Server Management studio and run a standard report against the VIM_VCDB by right clicking the DB > Reports > Standard Reports > Disk Usage by Top Tables:

Error1

From here you can see which tables are causing the problems, in my case its dbo.VPX_EVENT_ARG which is massive.

So from here you need to purge the above table:

  1. From Microsoft SQL Server Management studio
  2. Click databases to expand and select VIM_VCDB > Tables.
  3. Right-click the dbo.VPX_PARAMETER table and select Open.Note

    : If you are using SQL Server 2008, right-click the dbo.VPX_PARAMETER table and click Edit Top 200 Rows.
    Error3

  4. Modify event.maxAge to 30, and modify the event.maxAgeEnabled value to true.
  5. Modify task.maxAge to 30, and modify the task.maxAgeEnabled value to true.

Error5.5

Error5

  1. Next run a built-in stored procedure to shink the logs:
  2. Go to VIM_VCDB > Programmability > Stored Procedures.

Error6

3. Right-click dbo.cleanup_events_tasks_proc and select 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.

Now the records have been purged you need to shrink the DB:

  1. As before connect using SQL Server Management Studio.

ExecuteScript

2. Right click on the VIM_VCDB database and New Query, when the white SQLQuery box opens type the below and click ! Execute

3. It’ll start running and when its finished you should see:

ExecuteScript2

Check the size of the file again and you should be able to start the services.

 

Tags: , , , ,

9 Comments to VMware vCenter Server 5.5 Database is Full

  • Thank you very much for sharing your solution. We had exactly the same problem and solved it thants to your post.
    Went from +16M records in events table to 197000
    The cleanup_events_tasks_proc sp execution lasted for 35 minutes, and the shrink for 5. Database file reduced from 10GB to 1.5GB

    • What services need to be stopped when running this procedure? We are having this problem and when attempting to run through it I am hitting a “deadlock” on the cleanup, and the shrink command also finished with an error. The result was that the size of the files did not change. I did notice when looking at the VPX_PARAMETER table that the data did change and was smaller, so something was happening, but it never fully completed. I have stopped ALL vmware related services and am running the procedure and shrink again to see if that helps.

      • Ok so stopping all vmware related services allowed me to complete the steps above, with the exception of getting transaction log full errors. I increased the size of the log and both the stored procedure and db shirnk processes finished. The transaction log file itself it now smaller but the actual db file is still at the 10GB limit. How do I get this file to shrink as well?

        • I was able to get the DB to shrink by right-clicking the DB -> Tasks -> Shrink -> Database. After about 10 minutes it completed and the 10GB file was down to 1.5GB. Phew!

  • Hi,
    great tips right there.

    But,after running dbo.cleanup_events_tasks_proc,
    my database did not shrink.

    Any tips on this specific issue of mine?

Leave a Reply

Your email address will not be published.

Copy Protected by Chetan's WP-Copyprotect.