VCenter database option to maintain disk space usage on SQL Server

/*
	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

Select allOpen in new window

DBCC SHRINKDATABASE ( DatabaseName , 5);
GO

Select allOpen in new window

DBCC SHRINKDATABASE ( DatabaseName , 5);
GO