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.
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:
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:
- From Microsoft SQL Server Management studio
- Click databases to expand and select VIM_VCDB > Tables.
- 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.
- Modify event.maxAge to 30, and modify the event.maxAgeEnabled value to true.
- Modify task.maxAge to 30, and modify the task.maxAgeEnabled value to true.
- Next run a built-in stored procedure to shink the logs:
- Go to VIM_VCDB > Programmability > Stored Procedures.
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:
- As before connect using SQL Server Management Studio.
2. Right click on the VIM_VCDB database and New Query, when the white SQLQuery box opens type the below and click ! Execute
dbcc shrinkdatabase ( VIM_VCDB , 5 )
3. It’ll start running and when its finished you should see:
Check the size of the file again and you should be able to start the services.
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!
Sorry only just seen these, really glad you sorted your issues.
Thanks for sharing this. Fixed my problem.
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?
Excellent post – worked exactly as specified. Thanks!
Does this do the same?
https://kb.vmware.com/kb/2110031
YOU ROCK! Put up a fund me and I’ll be tossing you a little change. Saved me hours.
Thanks robd, worked a treat.
Just one thing to add, you can probably miss out the shrink DB step as now that you have made room in your database, SQL Server will not be trying to grow it beyond the 10GB, unless your rate of fill is greater than your trimming, which brings me to my question 🙂 Is there not a process that runs this SP? We can create our own of course.
Thanks for the explanation by incressing task.mxAage for 30 days it dose not decrease your database size instaed it incresse it in the future because by default it set to 15 days. so here the trick only shrinkdatabase. so do not change anything only run the following command dbcc shrinkdatabase ( VIM_VCDB , 5 ) it also reclaim some MBs.