Часто сталкиваешься с проблемой что база данных VC постоянно растёт.. Как с этим бороться ?
Первое что необходимо сделать если вы не делаете бэкап transaction log - переставить базу в режим SIMPLE.
Так же для очистки базы существует очень хороший скрипт:
/*
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 = 1
-- 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
