VCenter database option to maintain disk space usage on SQL Server

Hi,

I've just found out that in one of my VCenter server, the setting to truncate the old content is not enabled:

VCDB screenshot
So I wonder what's the impact to the SQL Server or to the VCenter VM in particular when I put the checkbox there ?

Note: My Windows VCenter VM contains the SQL Server 2008 which is running low on disk space.

So before I run / execute the SQL T-SQL code in http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1025914

I just wonder how is the impact ?
LVL 9
Senior IT System EngineerIT ProfessionalAsked:
Who is Participating?
 
Uni KittyCommented:
The script you posted has a comment that it's for version 4.x. I'm guessing you have version 5 or higher. If that is the case, then no, do not use that script.

Yes you can manually shrink the database.

As far as impact, please make sure that you back up the database and expect some downtime of vCenter. Sometimes the purge process can take a long time, and vCenter will be inaccessible.

It's ideal if you have a sql DBA on your team to advise you as well.
0
 
Senior IT System EngineerIT ProfessionalAuthor Commented:
one more thing, supposed I execute the below SQL script:

/*
	VCDB_table_cleanup_MSSQL_V4.X.sql,v 4.0 2010/08/12 

	This script will delete data from designated tables in the VirtualCenter DB 
	for vc4.x versions.

	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.

	Please see USER CONFIGURABLE PARAMETERS section below for options.

	In particular, you must set @DELETE_DATA = 1 in order to actually delete rows; 
	this is a safety precaution.

	Directions: open this file with one of the following and execute:
		SQL Query Analyzer (SQL Server 2000) or
		SQL Server Management Studio (SQL Server 2005)
		SQL Server Management Studio (SQL Server 2008)

	Connect using the same DB login that VirtualCenter uses.

	The transaction log may fill up during this procedure if sufficient space
	is not available.  Monitor the transaction log size and usage with this command:

	dbcc sqlperf (logspace)

*/


IF OBJECT_ID('tempdb..#CLEANUP_VCDB') IS NOT NULL
	DROP TABLE #CLEANUP_VCDB
GO

SET NOCOUNT ON

DECLARE @VCUSER NVARCHAR(60)
DECLARE @VCUSERID INT
DECLARE @BATCH_SIZE INT
DECLARE @CUTOFF_DATE SMALLDATETIME
DECLARE @CUTOFF_DATE_S NVARCHAR(60)
DECLARE @DELETE_DATA BIT
DECLARE @CNT INT
DECLARE @TOT INT
DECLARE @SQL NVARCHAR(900)
DECLARE @FROM_VAL NVARCHAR(60)
DECLARE @WHERE_VAL NVARCHAR(900)


-- ######### USER CONFIGURABLE PARAMETERS ######################## 
-- 0 = COUNT ONLY; 1 = DELETE ROWS
SET @DELETE_DATA = 0

-- Use one of these methods to specifiy the data cutoff date
SET @CUTOFF_DATE = GETUTCDATE()-180
--SET @CUTOFF_DATE = '2007/01/01'

-- Number of rows to delete per transaction
SET @BATCH_SIZE = 10000

-- ######### END USER CONFIGURABLE PARAMETERS #################### 

-- PERFORM SOME ERROR CHECKING TO ENSURE DB ACCESS
SELECT @VCUSERID = UID FROM SYSOBJECTS
WHERE NAME = 'VPX_VERSION'

IF @@ROWCOUNT < 1
BEGIN
	PRINT 'The ' + DB_NAME() + ' database does not appear to contain VirtualCenter tables.'
	PRINT 'Please ensure you are connected to the correct database.'
	RETURN -- stop execution of this script
END

SELECT @VCUSER = NAME FROM SYSUSERS
WHERE UID = @VCUSERID

PRINT 'VirtualCenter database user: ' + @VCUSER
PRINT 'Logged in user: ' + CURRENT_USER

IF (@VCUSER <> CURRENT_USER) AND (@VCUSER <> 'dbo')
BEGIN
	PRINT '***************************************************************'
	PRINT 'You do not appear to be logged in as the VirtualCenter DB user.'
	PRINT 'Please log in as ''' + @VCUSER + ''' to execute this SQL script.'
	PRINT '***************************************************************'
	RETURN
END

-- DONE WITH ERROR CHECKING

-- CONVERT THIS DATE TO A STRING WITH QUOTES FOR EFFICIENCY LATER
SET @CUTOFF_DATE_S = '''' + CONVERT(NVARCHAR, @CUTOFF_DATE, 111) + ''''
PRINT 'Cutoff date: ' + @CUTOFF_DATE_S

PRINT 'Batch size: ' + CONVERT(NVARCHAR, @BATCH_SIZE)

PRINT CONVERT(NVARCHAR, getdate(), 120) + ' starting...' 

CREATE TABLE #CLEANUP_VCDB (VPXTABLE NVARCHAR(40), CRITERIA NVARCHAR(250), INITIAL_CNT INT, DELETE_CNT INT)

IF ((SELECT VER_ID FROM VPX_VERSION)< 4) 
	BEGIN
		INSERT #CLEANUP_VCDB VALUES ('VPX_HIST_STAT', 'SAMPLE_ID IN (SELECT ID FROM VPX_SAMPLE WHERE SAMPLE_TIME < ' + @CUTOFF_DATE_S + ')', 0, 0 )
		INSERT #CLEANUP_VCDB VALUES ('VPX_SAMPLE', 'SAMPLE_TIME < ' + @CUTOFF_DATE_S, 0, 0 )
	END
ELSE
	BEGIN
		INSERT #CLEANUP_VCDB VALUES ('VPX_HIST_STAT1', 'TIME_ID IN (SELECT TIME_ID FROM VPX_SAMPLE_TIME1 WHERE SAMPLE_TIME < ' + @CUTOFF_DATE_S + ')', 0, 0 )
		INSERT #CLEANUP_VCDB VALUES ('VPX_SAMPLE_TIME1', 'SAMPLE_TIME < ' + @CUTOFF_DATE_S, 0, 0 )
		INSERT #CLEANUP_VCDB VALUES ('VPX_HIST_STAT2', 'TIME_ID IN (SELECT TIME_ID FROM VPX_SAMPLE_TIME2 WHERE SAMPLE_TIME < ' + @CUTOFF_DATE_S + ')', 0, 0 )
		INSERT #CLEANUP_VCDB VALUES ('VPX_SAMPLE_TIME2', 'SAMPLE_TIME < ' + @CUTOFF_DATE_S, 0, 0 )
		INSERT #CLEANUP_VCDB VALUES ('VPX_HIST_STAT3', 'TIME_ID IN (SELECT TIME_ID FROM VPX_SAMPLE_TIME3 WHERE SAMPLE_TIME < ' + @CUTOFF_DATE_S + ')', 0, 0 )
		INSERT #CLEANUP_VCDB VALUES ('VPX_SAMPLE_TIME3', 'SAMPLE_TIME < ' + @CUTOFF_DATE_S, 0, 0 )
		INSERT #CLEANUP_VCDB VALUES ('VPX_HIST_STAT4', 'TIME_ID IN (SELECT TIME_ID FROM VPX_SAMPLE_TIME4 WHERE SAMPLE_TIME < ' + @CUTOFF_DATE_S + ')', 0, 0 )
		INSERT #CLEANUP_VCDB VALUES ('VPX_SAMPLE_TIME4', 'SAMPLE_TIME < ' + @CUTOFF_DATE_S, 0, 0 )
	END

DECLARE curse CURSOR FOR
SELECT VPXTABLE, CRITERIA FROM #CLEANUP_VCDB

OPEN curse
FETCH NEXT FROM curse INTO @FROM_VAL, @WHERE_VAL

WHILE (@@FETCH_STATUS = 0)
BEGIN
	SET @SQL = 'SELECT @CNT= COUNT(1) FROM ' + @FROM_VAL + ' WHERE ' + @WHERE_VAL
	-- this is required to get result into a variable
	EXEC sp_executesql @SQL, N'@CNT INT OUTPUT', @CNT = @CNT OUTPUT

	SET @SQL = 'UPDATE #CLEANUP_VCDB SET INITIAL_CNT = ' + CONVERT(NVARCHAR, @CNT) + ' WHERE CURRENT OF curse ' 
	EXEC(@SQL)

	IF (@CNT = 0)
	BEGIN
		PRINT CONVERT(NVARCHAR, getdate(), 120) + ' ' + @FROM_VAL + ': no matching rows to delete.'
		FETCH NEXT FROM curse INTO @FROM_VAL, @WHERE_VAL
		CONTINUE
	END

	PRINT CONVERT(NVARCHAR, getdate(), 120) + ' ' + @FROM_VAL + ': will attempt to delete ' + CONVERT(NVARCHAR, @CNT) + ' rows.'

	IF @DELETE_DATA = 1
	BEGIN
		SET @CNT = 0
		SET @TOT = 0

		SET ROWCOUNT @BATCH_SIZE

		WHILE 1=1
		BEGIN
			BEGIN TRAN
			SET @SQL = 'DELETE FROM ' + @FROM_VAL + ' WHERE ' + @WHERE_VAL
			EXEC(@SQL)

			SET @CNT = @@ROWCOUNT
			SET @TOT = @TOT + @CNT

			COMMIT TRAN
			
			IF @CNT < @BATCH_SIZE BREAK
			PRINT CONVERT(NVARCHAR, getdate(), 120) + ' completed ' + CONVERT(nvarchar, @TOT) + ' rows...'
		END --ROW BATCH LOOP

		SET ROWCOUNT 0
		PRINT CONVERT(NVARCHAR, getdate(), 120) + ' ' + @FROM_VAL + ': deleted ' + CONVERT(nvarchar, @TOT) + ' total rows.'
		
		SET @SQL = 'UPDATE #CLEANUP_VCDB SET DELETE_CNT = ' + CONVERT(NVARCHAR, @TOT) + ' WHERE CURRENT OF curse ' 
		EXEC(@SQL)

	END -- DELETE DATA SECTION
	ELSE
	BEGIN
		PRINT CONVERT(NVARCHAR, getdate(), 120) + ' This is a test run, no data was deleted.'
	END

	FETCH NEXT FROM curse INTO @FROM_VAL, @WHERE_VAL
END -- END CURSOR LOOP

CLOSE curse
DEALLOCATE curse

IF @DELETE_DATA = 1
BEGIN
	PRINT ' '
	PRINT '****************** SUMMARY *******************' 

	DECLARE curse CURSOR FOR
	SELECT VPXTABLE, INITIAL_CNT, DELETE_CNT FROM #CLEANUP_VCDB

	DECLARE @INITIAL_VAL INT, @DELETE_VAL INT

	OPEN curse
	FETCH NEXT FROM curse INTO @FROM_VAL, @INITIAL_VAL, @DELETE_VAL

	WHILE (@@FETCH_STATUS = 0)
	BEGIN
		IF (@INITIAL_VAL <> @DELETE_VAL)
		BEGIN
			PRINT 'Potential problem: attempted to delete ' + CONVERT(NVARCHAR, @INITIAL_VAL) 
			+ ' rows, but only ' + CONVERT(NVARCHAR, @DELETE_VAL) + ' deleted.'
		END
		ELSE
		BEGIN
			PRINT 'Deleted ' + CONVERT(NVARCHAR, @DELETE_VAL) 
			+ ' rows from ' + CONVERT(NVARCHAR, @FROM_VAL) + ' table.'
		END

	FETCH NEXT FROM curse INTO @FROM_VAL, @INITIAL_VAL, @DELETE_VAL
	END

	CLOSE curse
	DEALLOCATE curse

END --DELETE DATA CHECK

DROP TABLE #CLEANUP_VCDB

Open in new window



Do I need to manually shrink the SQL Database with this article: http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1036738

DBCC SHRINKDATABASE ( DatabaseName , 5);
GO

Open in new window

0
 
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
What version of VMware vSphere are you using ?

How many VMs, and How many hosts do you have ?

This is the full version of the database, e.g. not SQL Express

Usually the largest tables in the SQL database, which contain lots of noise are the Tasks and Events.

and these can be safely purged and deleted.

We have developed and written scripts to show the sizes of tables in the database. Let me know if you want to try them.
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.

 
Senior IT System EngineerIT ProfessionalAuthor Commented:
I'm using VCenter 5.1 U1
I got 150+ VMs running on 12 ESXi hosts on a Blade servers.

Andrew, thanks for the offer, I've found the script in this page: kb.vmware.com/selfservice/search.do?cmd=displayKC&docType=kc&docTypeID=DT_KB_1_1&externalId=2110031

So do I need to shrink it manually using:

DBCC SHRINKDATABASE ( DatabaseName , 5);
GO
2110031-MS-SQL-task-event-stat.sql
0
 
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
If you are using the full version of SQL, I would not bother doing anything with your database.

Do you want to keep these events and tasks, or remove them?

If you want to manually shrink, and regain space, there is no issue, but make sure you have backup before you run any script.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I recommend you to not shrink the database.
Just turn on those retention policies. Should have some impact on the first time you run it but then should run smoothly on the next times.
0
 
Senior IT System EngineerIT ProfessionalAuthor Commented:
@Andrew: I'd like to remove them.

@Vitor: when is the performance going to be impacted ? is it directly after putting the checkboxes ?
0
 
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
I thought I was suffering "deja vu", I posted here scripts, you can run, which show the size of tasks and events, and a script which removes them all.

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

This will reduce the size of the database.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Shrinking database will impact the performance for sure. Will create locks and will fragment data pages so will impact future queries unless you perform a full reindex immediately after shrinking the database (expect more locks during this operation). I never recommend a database shrink unless you are running out of space.
0
 
Senior IT System EngineerIT ProfessionalAuthor Commented:
Yes, I'm running out of disk space in the VCenter D: drive.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Then perform the database maintenance task during non working hours, since you'll create locks and that will impact on everything.
Don't forget to run reindex job immediately after the shrink command.^
Those are two heavy operations so you may expect large minutes or few hours to have it done, depending on the database size, free memory and disk speed.
0
 
gheistCommented:
Other place it grows is configured under logging options, where you need to expire logs...
0
 
Senior IT System EngineerIT ProfessionalAuthor Commented:
@Gheist: do you mean on the logging levels section ?
0
 
gheistCommented:
Also
0
 
Senior IT System EngineerIT ProfessionalAuthor Commented:
Thanks !
0
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.

All Courses

From novice to tech pro — start learning today.