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

Hi

I cannot login to our vcenter server due to the Database being full. I get the following errors on the Server:

Log Name:      Application
Source:        MSSQL$VIM_SQLEXP
Date:          17/06/2015 16:03:45
Event ID:      1827
Task Category: Server
Level:         Error
Keywords:      Classic
User:          SYSTEM
Computer:      
Description:
CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 10240 MB per database.

Event ID:      1105
Task Category: Server
Level:         Error
Keywords:      Classic
User:          SYSTEM
Computer:      
Description:
Could not allocate space for object 'dbo.VPX_UPTIME' in database 'VIM_VCDB' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.


My SQL skills are very basic and I would appreciate guidance on the solution. From my research it appears as if I need to run pruning scripts rather than simply allowing autogrowth for example. I have found these articles which seem to describe what to do:

http://oxfordsbsguy.com/2015/01/26/vcenter-server-stopped-event-id-1827-1105/

http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1025914

I am running Vcenter 5.5 so I think this is the correct procedure rather than other which are for older versions of ESXi.

If I follow the steps in the resolution section is there a way to warn of DB size or prevent this happening again without migrating to the full version of SQL.
Assist-NetopaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
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 

Open 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

Open 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.
0
Assist-NetopaAuthor Commented:
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
0
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Assist-NetopaAuthor Commented:
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?
0
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
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.
0
Assist-NetopaAuthor Commented:
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
0
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
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.
0
Assist-NetopaAuthor Commented:
Awesome, I will carry this out in the morning as I will have to reboot the VM first.

Thanks for your help today
0
Assist-NetopaAuthor Commented:
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
0
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
is the MDF file size greater than 10GB, because those values do not look large.
0
Assist-NetopaAuthor Commented:
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
0
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
db needs shrinking, and compacting....
0
Assist-NetopaAuthor Commented:
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
0
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
Yes, got for it.
0
Assist-NetopaAuthor Commented:
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
0
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
I would restore your database, or VM, and test the vCenter Service before any changes, to check vCenter Server.
0
Assist-NetopaAuthor Commented:
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?
0
Assist-NetopaAuthor Commented:
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
0
Assist-NetopaAuthor Commented:
Andrew

After the VM restored it had to be re-activated and re-joined to the domain as it had lost it's trust relationship. When I went to shrink it again it said it only had 90MB of free space.

After I ran the VMware stored procedure it said it had 4.4GB free space.

The service restarted ok and I am now back in vCenter
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Assist-NetopaAuthor Commented:
Awarding Andrew points for his time to help and patience :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VMware

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.