Today we have a post about potential database issues from Matt Scott, who is a tech support engineer in our Broomfield, Colorado office, currently focusing on content creation. This post deals with SQL database operations and so it is recommended you be familiar with database concepts and have adequate backups before proceeding.
In larger VirtualCenter installations you might notice the VPX_EVENT_ARG and VPX_EVENT tables can become very large. For example, in one test environment I created with two hosts I saw over 10,000 rows in the VPX_EVENT Table alone.
In SQL 2008 it is not always easy to find the information for certain tables. If you open the tables in your VirtualCenter database and Right Click > Select Top 100 Rows it returns exactly 1000 rows. A more accurate way to see the advanced properties is go down to the bottom of that list and select properties for the table. In the Storage Page you will see the exact number of rows.
Back to the question at hand: why do these tables get so large?
In many cases it is simply because of the volume hosts and Virtual machines in an environment. But in some cases we see a custom script or third party management software installed on the service console aka VCenter. Some examples of these are:
- Dell Open Manager.
- Microsoft System Center – Operations Manager (Listed as OPSMGR is Services.msc).
- IBM Director.
- HP Insight Manager.
- Esxpress backup software being installed in the COS.
When excessive database growth in these tables occurs we recommend the following course of action:
- Backup your VCenter Database. VMware recommends a good backup strategy for your production database.
- Run the following command in SQL Server Management Studio to find out exactly what is going on in your database.
SQL Server Management Studio > Login > SQL or Windows Authentication based on your setup > New Query > Change the database to your VCenter Database (We see in many cases including myself from time to time it defaults to the (master) database.
use vcdb (Or the name of your VCenter Databases)
CREATE TABLE #TemptableforspaceUsed
(name SYSNAME,
rows bigINT,
reserved VARCHAR(20),
data VARCHAR(20),
index_size VARCHAR(20),
unused VARCHAR(20))
GO
INSERT #TemptableforspaceUsed
EXEC sp_MSforeachtable ‘sp_spaceused "?"’
select *
from #TemptableforspaceUsed
drop table #TemptableforspaceUsed
This query will return Tables, Rows, reserved, data, index_size, unused. This query itself is quite useful for looking at all 222 Tables/Rows (as of VC 4.1.x)
Another handy query is:
use vcdb ——> (Or the name of your VCenter Databases)
go
exec sp_tables ‘%’, ‘%’, ‘vcdb’, "’table’"
go
This lists all of the Tables in the Database utilized by VCenter.
You can now clean the event tables.
use vcdb (Or the name of your VCenter Databases)
truncate table vpx_event_arg;
delete from vpx_event;
Note: As always its good practice to run these during a Maintenance window or when VirtualCenter Services can be brought down.
And of course it is Production Data so a good backup is recommended as stated earlier.
There are foreign keys inside VPX_EVENT, so you cannot truncate the data inside with the standard command:
"truncate table dbo.vpx_event" – It will throw an error similar to the following:
Msg 4712, Level 16, State 1, Line 1
Cannot truncate table ‘dbo.VPX_EVENT’ because it is being referenced by a FOREIGN KEY constraint.
In many cases I have seen large ‘dbo.VPX_EVENT’ and ‘dbo.VPX_EVENT_ARG’ tables cause the VCenter service to fail and/or not start. That is why this cleanup procedure is useful in addition to turning off the third party services.
One final note. The previous DELETE command will dynamically save a copy of the change real-time, so you may notice an increase in transaction log growth relative to the amount of data you have at the current time until the cleanup finishes.
Comments