Recently i came up with the issue which was causing the vCenter DB (SQL DB) to grow huge, after digging on the internet and spending some time, i was able to find the cause and the solution for my vCenter DB size.
I am making this for the easy future reference , incase if faced same issue again and this will also help the other VMware Admins who are also facing the similar issue in their environment.
Environment:
- Windows based vCenter 6.0 with external SQL DB
- SQL DB 2012 Standard
Issue: vCenter DB size has grown huge more than expected and need to reduce the vCenter DB size.
Analysis: VPX_HIST_STAT tables has grown huge and that includes the orphaned VPX_HIST_STAT tables.
This may be happen if you have upgraded the vCenter to the new build.
VMware KB Article Followed:
https://kb.vmware.com/s/article/2058670
https://kb.vmware.com/s/article/1036738
The Script mentioned below are present in the above mentioned KB article.
This is very very very important thing to be noted
Note : Before doing any changes to the database, Please ensure you take the backup of the database on which you are working and here you need to make sure you have taken the latest vCenter Database backup.
If you want to know how to take the SQL DB backup:
- Login to the SQL Management Studio
- Right Click on the vCenter Database –> Go to Tasks–> Backup
- Browse the path to where you want to save the backup and give the filename to the backup file with .bak at the end of the filename . For e.g. vcenterdbbackup.bak
Steps to be followed to reduce the SQL DB size:
Note: This SQL Query may take a long time to complete the query execution depending upon the number of orphaned VPX_HIST_STAT tables and VPX_HIST_STAT tables which has grown huge to be deleted.
For me it took around 4 hours to complete the query execution
1) Stop the vCenter Service
2) Login to the SQL DB server and run the below SQL Query on vCenter Database
Note :SQL Query Starts from” /*” and ends at “SET NOCOUNT OFF”You need to login to the SQL Management Studio, Select the vCenter Database and run the below mentioned SQL Query.
=====================================================================
/*
This script will delete data from VPX_HIST_STAT* tables which have no parent record in
corresponding VPX_SAMPLE_TIME* table.You are strongly advised to shut down the VirtualCenter server and make
a complete backup of your database before running this script.VirtualCenter Server must be stopped while this script is running.
Configurable parameters:
@delete_data – Set @delete_data to 1 to delete orphan rows.
In case of delete_data <> 1 the script will only count how many rows are orphaned
@delete_data must be 1 to actually delete rows.
@batch_size – Number of rows to delete per one transactionThe script may be executed via SQL Server Management Studio or from command line.
USER which executes the script must have privileges to delete data from vCenter Server database and
the user must have default schema equal to the vCenter Server database schema.*/
SET NOCOUNT ON
— Create temporary tables
— #ORPHAN_STATS – contains table name and time_id which should be deleted
— #DYNAMIC_SQL – contains sql scripts which be executed later
IF OBJECT_ID(‘tempdb..#ORPHAN_STATS’) IS NOT NULL
DROP TABLE #ORPHAN_STATS
GO
CREATE TABLE #ORPHAN_STATS (table_name VARCHAR(30) NOT NULL, time_id BIGINT NOT NULL, deleted_rows BIGINT)IF OBJECT_ID(‘tempdb..#DYNAMIC_SQL’) IS NOT NULL
DROP TABLE #DYNAMIC_SQL
GO
CREATE TABLE #DYNAMIC_SQL (orphan_sql varchar(2000) NOT NULL)DECLARE @delete_data BIT
DECLARE @batch_size INTEGER
DECLARE @affected_rows INTEGERprint ‘*********************************************************************’
print ‘* This script is for deleting data from VPX_HIST_STAT tables which *’
print ‘* have not master record in corresponding VPX_SAMPLE_TIME table. *’
print ‘*********************************************************************’SET @delete_data = 1
SET @batch_size = 10000IF @delete_data = 1
PRINT ‘*** DATA WILL BE DELETED ! ***’
ELSE
BEGIN
PRINT ‘* Currently script only counts how many records will be deleted. *’
PRINT ‘* set @delete_data to 1 in order to actually delete rows *’
END
PRINT ‘*********************************************************************’
— Populate #DYNAMIC_SQL with scripts for collect orphan rows
INSERT INTO #DYNAMIC_SQL
SELECT ‘SELECT ‘ + CHAR(39) + name + CHAR(39) + ‘, time_id, count(*) FROM ‘ + name + ‘ D WHERE NOT EXISTS (SELECT 1 FROM vpx_sample_time1 M WHERE D.TIME_ID = M.TIME_ID) GROUP BY time_id ‘ FROM sys.tables WHERE name like ‘vpx_hist_stat1%’
INSERT INTO #DYNAMIC_SQL
SELECT ‘SELECT ‘ + CHAR(39) + name + CHAR(39) + ‘, time_id, count(*) FROM ‘ + name + ‘ D WHERE NOT EXISTS (SELECT 1 FROM vpx_sample_time2 M WHERE D.TIME_ID = M.TIME_ID) GROUP BY time_id ‘ FROM sys.tables WHERE name like ‘vpx_hist_stat2%’
INSERT INTO #DYNAMIC_SQL
SELECT ‘SELECT ‘ + CHAR(39) + name + CHAR(39) + ‘, time_id, count(*) FROM ‘ + name + ‘ D WHERE NOT EXISTS (SELECT 1 FROM vpx_sample_time3 M WHERE D.TIME_ID = M.TIME_ID) GROUP BY time_id ‘ FROM sys.tables WHERE name like ‘vpx_hist_stat3%’
INSERT INTO #DYNAMIC_SQL
SELECT ‘SELECT ‘ + CHAR(39) + name + CHAR(39) + ‘, time_id, count(*) FROM ‘ + name + ‘ D WHERE NOT EXISTS (SELECT 1 FROM vpx_sample_time4 M WHERE D.TIME_ID = M.TIME_ID) GROUP BY time_id ‘ FROM sys.tables WHERE name like ‘vpx_hist_stat4%’DECLARE @l_orphan_sql VARCHAR(2000)
DECLARE c_exec_sql CURSOR FOR
SELECT orphan_sql FROM #DYNAMIC_SQLOPEN c_exec_sql
FETCH NEXT FROM c_exec_sql INTO @l_orphan_sqlSET @affected_rows = 0
–Populate #ORPHAN_STATS with table names, time_id and count of rows for delete
WHILE (@@FETCH_STATUS = 0)
BEGIN
INSERT INTO #ORPHAN_STATS
EXEC (@l_orphan_sql)
SET @affected_rows = @affected_rows+ @@ROWCOUNT
FETCH NEXT FROM c_exec_sql INTO @l_orphan_sql
END
CLOSE c_exec_sql
DEALLOCATE c_exec_sqlSELECT CAST(SUM(deleted_rows) AS VARCHAR) + ‘ rows will be deleted from table ‘ + table_name
FROM #ORPHAN_STATS
GROUP BY table_nameIF @affected_rows > 0
BEGIN
IF @DELETE_DATA = 1
BEGIN
DECLARE c_exec_sql CURSOR FOR
SELECT orphan_sql FROM #DYNAMIC_SQL
— Populate tamp table #DYNAMIC_SQL with scripts for DELETE
TRUNCATE TABLE #DYNAMIC_SQL
INSERT INTO #DYNAMIC_SQL
SELECT ‘DELETE TOP (‘ + cast (@batch_size as varchar) + ‘) FROM ‘ + table_name + ‘ WHERE time_id = ‘ + cast (time_id as varchar) FROM #ORPHAN_STATS
OPEN c_exec_sql
FETCH NEXT FROM c_exec_sql INTO @l_orphan_sql
WHILE (@@FETCH_STATUS = 0)
BEGIN
WHILE 1=1
BEGIN
BEGIN TRAN
EXEC (@l_orphan_sql)
SET @affected_rows = @@ROWCOUNT
COMMIT TRANPRINT ‘EXECUTED SQL: ‘ + @l_orphan_sql
PRINT ‘Affected rows: ‘ + cast( @affected_rows as varchar)
PRINT ‘——————‘IF @affected_rows < @batch_size BREAK
END
FETCH NEXT FROM c_exec_sql INTO @l_orphan_sql
ENDCLOSE c_exec_sql
DEALLOCATE c_exec_sql
END
END
ELSE
PRINT ‘There are not any orphan rows.’SET NOCOUNT OFF
Ohh so the query execution completed 🙂
Now here we are to the last step, we have to run one more query that will shrink the database as we have deleted VPX_HIST_STAT tables.
3) SQL query to Shrink the Database:
You need to login to the SQL Management Studio, Select the vCenter Database and run the below mentioned SQL Query.
SQL Query :
dbcc shrinkdatabase ( vcdbname , 20);
go
Note: When DBCC SHRINKDATABASE is specified with target_percent, the Database Engine calculates the target size to be thetarget_percent amount of space free in the file after shrinking.
And here we goooooo… You should have reduced the vCenter DB Size & create some free space in your drive and now you can start the vCenter service.
Enjoyyyyyy 🙂
Again Thanks to the VMware KB article to helping in resolving the issue 🙂
I would be more than happy to reply to your queries.
Rajesh Poojary
Where to find me 😉
Twitter : @RajjeshPoojary
Mail : Rajjesh.poojary@gmail.com