Preventing Locking, Blocking and Deadlocks in the vCenter database

Published On: 2013-07-10By:

As our VMware environments become larger and larger with more and more hosts and guests more thought needs to be given to the vCenter database that is typically running within a SQL Server database.

With the vCenter database running within Microsoft SQL Server (which is the default) their will be lots of locking and blocking happening as the queries which the vCenter server runs aggregates the data into the summary tables.  The larger the environment the more data that needs to be aggregated every 5 minutes, hours, daily, etc.

Then problem here is that in order for these aggregations to run the source and destination tables have to be locked.  This is normal data integrity within the SQL Server database engine.

Thankfully there is a way to get out of this situation.  That is to enable a setting called Snapshot Isolation level for the vCenter database.  This setting changes the way that SQL Server handles concurrency by allowing people to write to the database while at the same time allowing people to read the old versions of the data pages therefor preventing locks.  The SQL Server does this by making a copy of the data page when it is being modified and putting that copy into the tempdb database.  Any user that attempts to run queries against the original page will instead be given the old version from the tempdb database.

If you’ve seen problems with the vCenter client locking up and not returning performance data when the aggregation jobs are running, this will make these problems go away.

Turning this feature on is pretty simple.  In SQL Server Management Studio simply right click on the vCenter database and find the “Allow Snapshot Isolation” setting on the options tab.  Change the setting from False to True and click OK (this is the AdventureWorks2012 database, but you’ll get the idea).


If you’d rather change the settings via T-SQL it’s done via the ALTER DATABASE command shown below.


Hopefully this will help fix some performance problems within the vCenter database.


Contact the Author | Contact DCAC


Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner   Denny Cherry & Associates Consulting LLC BBB Business Review    Microsoft MVP    Microsoft Certified Master VMWare vExpert
INC 5000 Award for 2020    American Business Awards People's Choice    American Business Awards Gold Award    American Business Awards Silver Award    FT Americas’ Fastest Growing Companies 2020   
Best Full-Service Cloud Technology Consulting Company       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers
Share via
Copy link