vCenter Database running full

I had this problem several times in the last few months. In a small environment the customer is using SQL 2008 R2 Express Edition for their vCenter database. When using SQL 2008 R2 Express you must be aware of the following limiations.
SQL 2008 R2 Express limitations (taken from here)

Maximum memory utilized (per instance of SQL Server Database Engine) 1GB (Buffer Pool Memory)
Maximum Compute Capacity Used by a Single Instance (SQL Server Database Engine) Limited to lesser of 1 Socket or 4 cores
Maximum relational Database size 10GB

Because the memory limit is only applicable for the buffer pool, the SQL process can take more than 1GB memory in total because not all memory allocations go via the buffer pool.
After a while the customer observes the following message in the Application event log:

The transaction log for database “<vCenter database name>” is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases.

This indicates that the SQL database is almost at it’s limit of 10GB. To find out why the database grows that much I used a SQL script I found here.

In SQL Server, the page size is 8 KB, or 128 pages per megabyte
*/
select
    a2.name [Table],
    replace(convert (nvarchar, convert(money, a1.rows), 1), '.00','') [Row Count],
    cast (a1.reserved/128.0/1024 as decimal(9,3)) [Reserved GB],
    case when a1.reserved = 0 then 0
    else cast ((100-(100.0*a1.used/a1.reserved)) as decimal(9,2)) end [Unused %]
from (
    select
        ps.object_id,
        sum (cast (ps.row_count as bigint)) rows,
        sum (cast (ps.reserved_page_count as bigint)) reserved,
        sum (cast (ps.used_page_count as bigint)) used
    from sys.dm_db_partition_stats ps
    inner join sys.all_objects a2
		on a2.object_id = ps.object_id
		and a2.type IN ('U')
    group by ps.object_id
    )  a1
left join sys.all_objects a2 ON a1.object_id = a2.object_id
where (a1.reserved/128) > 5
ORDER BY a1.rows DESC, a2.name

When running this statement against the vCenter database I got the following result:
sql_table_01
As you can see there are 2 tables which are quite big and has a lot of rows. This will happen when you leave the following with default settings:

If something in your environment creates a lot of tasks and events (e.g. using Nagios monitoring) then both tables will grow bigger and bigger. To avoid this activate task and events clean-up in the vCenter settings and set an appropriate retention time. My customer used 30 days for it.
But how can I shrink the current tables? You have 2 options for that.
1. Wait until the clean-up stored procedure runs
2. Run the clean-up stored procedure manually
If the situation is urgent, which means vCenter is down because the database is full, connect to the database server through SQL Server Management Studio and go to vCenterDatabaseProgrammability > Stored Procedures, right-click dbo.cleanup_events_tasks_proc and select Execute Stored Procedure.
It will take some time until the clean-up is finished, depending on the numbers of rows you have in both tables.
After the clean-up and executing the SQL script you will have a similar output:
sql_table_02
For more information visit the original KB article.

3 Responses

  1. Asela says:

    Thanks and  very use full
    Note: If you are using SQL Server 2008, right-click the dbo.VPX_PARAMETER table and click Edit Top 200 Rows.
    Modify event.maxAge to 30, and modify the event.maxAgeEnabled value to true.

    • Hi. I have already set this through vCenter. It’s per default 180 days and not enabled, so that’s the first thing I’m doing with a new installed vCenter. It’s also depending on which other software writes into tasks and events.

  2. Hello, I want to subscribe for this blog to obtain most up-to-date updates, thus where can i do it
    please help out.

Leave a Reply

Your email address will not be published. Required fields are marked *