VCenter Service cannot stay started - 'VIM_VCDB' primary filegroup is full

SQL Express has a 10GB maximum limit imposed by the software license.

Can you check the size of your DB, is it 10GB or near 10GB.

I've covered and answered this question many times, vCenter 5.5 and ESXi 5.5 are now very noisy, and SQL databases fill up very quickly.

How many hosts and VMs do you have ?


okay here goes...

Check the following tables...in the database VIM_VCDB

There are two scripts here, the first one will show the row and total space used in the table.

SELECT [Table Name],
(SELECT rows FROM sysindexes s WHERE s.indid < 2 AND s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space used (MB)] FROM 
                (
                SELECT  QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id)) AS [Table Name],
                                CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM(i.reserved)) * (SELECT low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E')) / 1024.)/1024.)) AS [Total space used (MB)]
                FROM    sysindexes i (NOLOCK)
                                                INNER JOIN
                                sysobjects o (NOLOCK)
                                                ON
                                i.id = o.id AND
                                ((o.type IN ('U', 'S')) OR o.type = 'U') AND
                                (OBJECTPROPERTY(i.id, 'IsMSShipped') = 0)
                WHERE indid IN (0, 1, 255)
                GROUP BY           QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
                
                ) as a
ORDER BY            [Total space used (MB)] DESC 

Select allOpen in new window

the second script here, will remove Tasks and Events from the Db

alter table VPX_EVENT_ARG drop constraint FK_VPX_EVENT_ARG_REF_EVENT, FK_VPX_EVENT_ARG_REF_ENTITY 
alter table VPX_ENTITY_LAST_EVENT drop constraint FK_VPX_LAST_EVENT_EVENT

truncate table VPX_TASK
truncate table VPX_ENTITY_LAST_EVENT
truncate table VPX_EVENT
truncate table VPX_EVENT_ARG

alter table VPX_EVENT_ARG add
constraint FK_VPX_EVENT_ARG_REF_EVENT foreign key(EVENT_ID) references VPX_EVENT (EVENT_ID) on delete cascade, 
constraint FK_VPX_EVENT_ARG_REF_ENTITY foreign key (OBJ_TYPE) references VPX_OBJECT_TYPE (ID)

alter table VPX_ENTITY_LAST_EVENT add
constraint FK_VPX_LAST_EVENT_EVENT foreign key(LAST_EVENT_ID) references VPX_EVENT (EVENT_ID) on delete cascade

Select allOpen in new window

You run these scripts "as is", and your responsibility.

I would run the first script and check, if the tasks and events are large... and these are the tables causing the excessive space issue.

Andrew

Thanks for your quick response, to answer your questions first:

I have 34 VM's spread over three hosts in the infrastructure including Veeam replica's at DR.
The Vcenter DB is 10,486,016KB in size so it is at the limit reported in the event logs

My questions:

How do your two scripts vary from the guidance in the VMWare article shown below

-----------VMWARE text----------------------------

To purge the data in the VPX_EVENT table:
Connect to Servername\SQL Database and log in with the appropriate credentials.
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.

Note: To improve the time of the data cleanup, run the preceding steps in several intervals. To do this, ensure to keep the default value of event.maxAge and task.maxAge and perform step 6 to run the cleanup. Then, reduce the event.maxAge and task.maxAge value by 60 and run the cleanup. Repeat the steps until the value is reached to 30 for the final cleanup process.

Run the built-in stored procedure:

Go to VIM_VCDB > Programmability > Stored Procedures.
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.

When this has successfully completed, close SQL Management Studio and start the VMware Virtual Center Server service.

---------End of VMWare text------------

I do not even have SQL Management studio Manager on the vcenter VM only the program group shown in the attached screenshot.

I have never run scripts in SQL before so I think I need the Query Analyser to run them?I am sorry my SQL skills are so limited.

How can I stop this happening again?
vmware-prog-group.JPG

Our scripts, first show the size of tables, so you can see what tables are the largest.

the second script will then purchase all the events and tasks, this is usually the table which causes the large database.

Sounds Good, how does that differ from the VMware advice?

Also given my lack of SQL knowledge if I run the scripts I think I am missing the components on the server to do so ie no Query analyser or SQL management studio. I did the default installation of vCenter yet it didn't install these components?

The issue you have is 3 hosts and 34 VMs, causes alot of noise, increase in database growth, that SQL Express is no longer suitable, because of the 10GB database maximum, hence why VMware are pushing the Linux Appliance, because it does not cost you in terms of licensing.

You will need to install SQL Management Studio for SQL Express.

VMware advice, purges a defined number of days old, this may not help you.

First thing, is to install Management Studio, and check in the events and tasks tables are the tables involved in the reason for max database size being reached.

After a bit of research on the correct management studio I have that installed. I know it's asking a lot but could you help me run your scripts against the vcenter DB.

Do  I do the following:

Save your script texts as a prune1.sql and prune2.sql files in from a text editor like notepad
Stop the VMWare VirtualCenter Server Service, or all VMWare servers?
In SMSS open the prune1.sql script and ensure the VIM_VCDB is slected
Execute the script
Examine the results

then execute prune2.sql as above and examine the amount of space recovered in the .mdf file?

Then restart the Vcenter server service/s and try to login to Vcenter

You can run the scripts with vCenter Server running, but best stop the service.

Make sure you have a full backup of your vCenter Server before any changes to db. (not a snapshot!).

the first script just displays the size, the second does the purge.

1. Run Management Studio.

2. Connect to db engine.

3. In the Left hand pane, expand Databases, and select VIM_VCDM

4. In the Right pane, select New Query

5. Paste in contents of script 1. from EE post above

6. Click Execute.

7. this will execute, and show you a table of tables, and their sizes.

8. Repeat with script 2. (this will purge)

9. Then repeat with script 1. again.

Awesome, I will carry this out in the morning as I will have to reboot the VM first.

Thanks for your help today

Andrew

I have run the scripts with the VirtualCenter Service disabled because it was in the starting state after the reboot.

I have posted the results from the queries but the prune script didn't seem to do anything to the database? The last modified date on the mdf is the date it reached it's limit.

Shall I try the VMware advice and see if that makes a difference?
results2.jpg
results1.JPG

is the MDF file size greater than 10GB, because those values do not look large.

Yes, I have atatched a snip of the databses files. I thought it was strange to that the largest table was only 1GB. It didn't seem to ad up to 10GB or anywhere near it.
VIM-VCDB-size.JPG

db needs shrinking, and compacting....

Shall I just select the defaults. That look like it will shrink it be quite a margin.

I really appreciate your assistance with this
shrink-DB.JPG

Yes, got for it.

The Database files have been shrunk and have actually reduced in size as per screenshot. Unfortunately the Vcenter server service gets stuck on starting > terminates then tries to re-start again, even after a reboot of the VM.

Just get this:

Log Name:      Application
Source:        VMware VirtualCenter Server
Date:          26/06/2015 11:01:28
Event ID:      1000
Task Category: None
Level:         Information
Keywords:      Classic
User:          N/A
Computer:      
Description:
The description for Event ID 1000 from source VMware VirtualCenter Server cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.

If the event originated on another computer, the display information had to be saved with the event.

The following information was included with the event:

Starting VMware VirtualCenter 5.5.0 build-1891313

the message resource is present but the message is not found in the string/message table


and this in the system log:

Log Name:      System
Source:        Service Control Manager
Date:          26/06/2015 10:49:29
Event ID:      7031
Task Category: None
Level:         Error
Keywords:      Classic
User:          N/A
Computer:      
Description:
The VMware VirtualCenter Server service terminated unexpectedly.  It has done this 3 time(s).  The following corrective action will be taken in 0 milliseconds: Restart the service.
VIM-VCDB-size-after-shrink.JPG

I got this from the vpdx.log:

2015-06-26T10:58:26.245+01:00 [05232 warning 'Libs'] Failed to report event. Reason: The interface is unknown
2015-06-26T10:58:26.416+01:00 [05424 warning 'Default'] [JrnlFilePersistenceProvider] Got EOF while reading file
2015-06-26T10:58:26.432+01:00 [05424 warning 'Default'] [JrnlFilePersistenceProvider] Got EOF while reading file
2015-06-26T10:58:26.432+01:00 [05424 warning 'Default'] [JrnlFilePersistenceProvider] Got EOF while reading file
2015-06-26T10:58:26.432+01:00 [05424 warning 'Default'] [JrnlFilePersistenceProvider] Got EOF while reading file
2015-06-26T10:58:26.447+01:00 [05424 warning 'Default'] [JrnlFilePersistenceProvider] Got EOF while reading file
2015-06-26T10:58:26.447+01:00 [05424 warning 'Default'] [JrnlFilePersistenceProvider] Got EOF while reading file
2015-06-26T10:58:26.447+01:00 [05424 warning 'Default'] [JrnlFilePersistenceProvider] Got EOF while reading file
2015-06-26T10:58:26.447+01:00 [05424 warning 'Default'] [JrnlFilePersistenceProvider] Got EOF while reading file


If I restore the database/VM to before the database was full what effect will that have on the infrastructure if/when I manage to login to Vcenter?

Andrew

Enitire VM Restore is underway under further guidance from Veeam... It will be to the point just before the Database is full, should I :

1) Check Vcenter works
2) Re-install SMSS which will have been wiped out by restore
3) Stop the vcenter service gracefully
4) Backup the database manually
4) Shrink the DB again
5) Try to start the vcenter service after second shrink operation

Awarding Andrew points for his time to help and patience :)