Categories
How-To Technical

VIM_VCDB database ‘PRIMARY’ filegroup is full

Additional Errors:  CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 4096 MB per database.

Cause:  The 4 GB limit is a limit of SQL Server Express 2005.   If you needed to grow your database beyond that size, you would want to upgrade to SQL Server 2005 Workgroup / Standard or Enterprise.  If you want to continue using SQL Express, then you’ll need to purge data from the database

 

Why does this happen, and how to prevent it

Virtual Center stores tasks, event, and performance data in the Virtual Center database. Over time, this data needs to be removed.  There are two variations of this solution, one for each of the supported vCenter database platform.

How to fix

– Make a full database backup

– Stop all Virtual Center Server services

Run the following script below on the Virtual Center Database

/*

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