All VM's showing as (orphaned) and vcenter cannot contact hosts even though they are running normally

Dear Experts

I have been trying to run some maintenance on the vcenter Database which has grown too large (10GB) on SQL Express.

I found that it was the VPX_TEXT_ARRAY that is over 6GB in size by running the script here:

http://www.vbrain.info/2014/08/19/vcenter-database-running-full

so I tried to run through this VMware KB:

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

I got to step 6 where you delete the records of which I had 1444345 rows to delete! It was at this point that SQL said that the database transaction log was full:

"The transaction log for the database 'VIM_VCDB' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

The database has approximately 160MB free space but when I launch vcenter it shows all VM's as orphaned and that it can't contact the host servers, even though the hosts and VM's are fine if I login to them directly in vsphere?

I need help in sorting the vcenter Database size and therfore hopefully restoring vcenter connection to the hosts.

I am not a SQL admin and have only been following the KB's articles

We have 40 VM's/replicas over three hosts
Assist-NetopaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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:
Are you sure Tasks and Events are not the largest tables ?
Assist-NetopaAuthor Commented:
Yes, I will post the query results when I am back at work. I am off sick just now

Thanks Andrew
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
ok.

I have a script, if handy, which will show the sizes of all the tables.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Assist-NetopaAuthor Commented:
Andrew

Thanks for your patience it's my first day back today after three days out of action. Hopefully you can help me reduce the large table size, presumably by doing something with the transaction logs to allow the vmware script to complete?

Alan
vcenter-DB-query.JPG
Query-table-size.jpg
vcenter-tlog-full.JPG
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
see my EE Question here, which has scripts to show the table sizes

http://www.experts-exchange.com/Software/VMWare/Q_28647805.html

and a script to purge the tables
Assist-NetopaAuthor Commented:
Will the second script also purge the data from the large VPX_TEXT_ARRAY table as that is 6GB plus. Also will that be able to run given the full transaction log message?
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
truncate table VPX_TASK
truncate table VPX_ENTITY_LAST_EVENT
truncate table VPX_EVENT
truncate table VPX_EVENT_ARG

these are the tables which are purged, you could modify the script.
Assist-NetopaAuthor Commented:
Excuse my lack of SQL scripting knowledge but to add the VPX_TEXT_ARRAY table would you simply add it to the list, or are there other lines required to account for that table?

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

I am concerned that the script won't run due to the transaction log full message, is there a way to deal with that issue? Perhaps then the VMware script will work for that table on it's own:

http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=2005333
Assist-NetopaAuthor Commented:
The solution was to set the transaction logs to unrestricted file growth while the vmware article script to prune the VPX_TEXT_ARRAY ran. The transaction logs had to grow to almost 10GB themselves brefore the script had completed.

The database now has 6GB free space

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:
I had suggested the correct article in the post the key was increasing the transaction log size
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.