Database

Exchange Database Size and Limits

In the dream time hours of the night one of the exchange data-stores went offline.  The steps to resolve the issue were put in place and handled swiftly by the administrator on duty; but what was the cause.

After some digging I arrive at the reason why the data-store went offline.  Found in the Windows Application event ID 9630 the database had reached the logical side equal to the physical size of the data base and that being 250GB.  Thus the database size had exceed the size limit of 250GB

Exchange then dismounted the database preventing further access.   My take on this is that Microsoft expects users with large dB’s to have multiple storage servers.  Well I am balling with a budget here so that is not happening anytime soon.

So how does one get around this?

Well simple; You follow the steps here on Microsoft Technet.  And the procedure in below:

  1. Start Registry Editor (regedit).
  2. Locate the following registry subkey:HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesMSExchangeIS<SERVER NAME>Private-<database GUID>
    Note
    You can get the GUID of a database by running the following command in the Exchange Management Shell: Get-MailboxDatabase -Identity "<server name><storage group name><database name>" | Format-Table Name, GUID
  3. If the Database Size Limit in GB DWORD exists for the subkey, change its value to the desired size in gigabytes.
  4. If the Database Size Limit in GB DWORD does not exist for the subkey, create a new DWORD with that name, and then set its value to the desired size in gigabytes.

Purging old data from the database used by VirtualCenter 2.x (a repost)

VirtualCenter stores tasks, event, and performance data in the VirtualCenter database. Over time, data collection results in growth of the database files and a mechanism is needed to shrink these files.

VirtualCenter 2.x does not have a feature to purge or shrink old records from the database. For more information on shrinking databases after running the scripts provided in this article, see:

Link 1: http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1000125

Link 2: http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1025914

 

How to Shrink a Database (http://msdn.microsoft.com/en-us/library/ms189035.aspx) & (http://msdn.microsoft.com/en-us/library/ms189080.aspx)

 

VIM_VCDB database ‘PRIMARY’ filegroup is full (part 2)

After cleaning up your Virtual Center Database to get us back and running I came across the following

vCenter Server 4.x has a Database Retention Policy setting that allows you to specify when vCenter Server tasks and events should be deleted. Since this setting does not affect performance data records it is still possible to purge or shrink old records from the database using the scripts attached to this article. To access the Database Retention Policy setting in the vSphere Client, click Administration > vCenter Server Settings > Database Retention Policy.

By |How-To, Software, Technical|Comments Off on VIM_VCDB database ‘PRIMARY’ filegroup is full (part 2)

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

By |How-To, Technical|Comments Off on VIM_VCDB database ‘PRIMARY’ filegroup is full