SET NOCOUNT ON 

DECLARE @ErrorTag NVARCHAR(20) 
SET		@ErrorTag = '###ERROR###'

DECLARE @WarningTag NVARCHAR(20) 
SET		@WarningTag = '###WARNING###'

DECLARE @AdviceTag NVARCHAR(20) 
SET		@AdviceTag = '###ADVICE###'

DECLARE @HeaderTag NVARCHAR(20) 
SET		@HeaderTag = '###HEADER###'

DECLARE @DataTag NVARCHAR(20) 
SET		@DataTag = '###DATA###'

DECLARE @DescTag NVARCHAR(20) 
SET		@DescTag = '###DESCRIPTION###'

DECLARE @colSeperator NVARCHAR(20) 
SET		@colSeperator = ':,:'

DECLARE @SPNameWithWarningLimitCurFlag  INTEGER 
SET 	@SPNameWithWarningLimitCurFlag  = 0 

DECLARE @SPNameWithUpperLimitCurFlag  INTEGER 
SET 	@SPNameWithUpperLimitCurFlag  = 0 

DECLARE @SPCountWithWarningLimit 	INTEGER 
SET		@SPCountWithWarningLimit 		= 0

DECLARE @SPCountWithUpperLimit 	INTEGER 
SET		@SPCountWithUpperLimit		= 0

DECLARE @DDBCount 	INTEGER 
SET		@DDBCount			= 0

-- Max allowed primary records = 1 Billion
DECLARE @MaxAllowed 	BIGINT 
SET		@MaxAllowed		= 1000000000

-- Warning limit that will fail subclient association [>= 60% of @MaxAllowed]
DECLARE @warningLimit 	INTEGER 
SET		@warningLimit		= 60

-- Upper limit that will fail backups to DDB [>= 90% of @MaxAllowed]
DECLARE @upperLimit 	INTEGER 
SET		@upperLimit	= 90

DECLARE @errorCode  INTEGER 
SET		@errorCode	= 0

DECLARE @retCode  INTEGER 
SET		@retCode	= 0

DECLARE @SPName 	NVARCHAR(510) 
DECLARE @output		NVARCHAR(MAX)

IF OBJECT_ID('tempdb.dbo.#SPWithPrimaryCopyStore') IS NOT NULL 
	DROP TABLE #SPWithPrimaryCopyStore
	
CREATE TABLE #SPWithPrimaryCopyStore 
(
		archGrpCopyId		INTEGER,	
		archGrpId				INTEGER,
		archGroupName		NVARCHAR(510), 		
		SIDBStoreId			INTEGER,
		commCellId			INTEGER,	
		primaryRecCount	BIGINT	
)

CREATE INDEX Idx_SPWithPrimaryCopyStore_1 ON #SPWithPrimaryCopyStore (SIDBStoreId, commCellId)

PRINT @DescTag + ' Check for Deduplication DB primary records count'

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[IdxSIDBUsageHistory2]') and OBJECTPROPERTY(id, N'IsTable') = 1)
BEGIN
	--Get all active SIDB stores of primary copies except primary copy of GDSP
	INSERT INTO #SPWithPrimaryCopyStore
	SELECT DISTINCT AGC.id, AG.id, AG.name, AGC.SIDBStoreId, 2, 0
	FROM archGroupCopy AGC WITH (NOLOCK), archGroup AG WITH (NOLOCK)
	WHERE AGC.archGroupId = AG.id
	AND		AGC.id = AG.defaultCopy
	AND		AGC.SIDBStoreId > 0
	AND		AGC.flags & 268435456 = 0		--GDSP Primary Copy
	
	SELECT @errorCode	= @@ERROR
	IF	@errorCode != 0 GOTO CX_EXIT 
	
	;WITH SIDB_REC_CNT AS
	(
	   SELECT	UH.SIDBStoreId, UH.SubStoreId, UH.commCellId, UH.modifiedTime, UH.primaryEntries,
						ROW_NUMBER() OVER (PARTITION BY UH.SIDBStoreId, UH.SubStoreId, UH.commCellId ORDER BY UH.modifiedTime DESC) AS rowNumber
	   FROM IdxSIDBUsageHistory2 UH WITH (NOLOCK), #SPWithPrimaryCopyStore T
	   WHERE UH.SIDBStoreId = T.SIDBStoreId
	   AND	 UH.commCellId = T.commCellId
	)
	UPDATE #SPWithPrimaryCopyStore
	SET primaryRecCount = TT.primaryEntries
	FROM #SPWithPrimaryCopyStore T, (	SELECT SIDBStoreId, commCellId, MAX(primaryEntries) AS primaryEntries
																		FROM 	SIDB_REC_CNT
																		WHERE rowNumber = 1
																		GROUP BY SIDBStoreId, commCellId) TT
	WHERE T.SIDBStoreId = TT.SIDBStoreId
	AND		T.commCellId = TT.commCellId
	
	SELECT @errorCode	= @@ERROR
	IF	@errorCode != 0 GOTO CX_EXIT 
END
ELSE
BEGIN
	SET @retCode = 0
	
	PRINT 'Deduplication DB primary records count information is not available. Skipping the check.'
	GOTO CX_EXIT
END
	
IF EXISTS (SELECT * FROM #SPWithPrimaryCopyStore WHERE primaryRecCount >= (@MaxAllowed * @warningLimit / 100))
BEGIN
	SET @retCode = 1

	--Print Warning
	SET @output = @WarningTag + ' In order to achieve optimal performance for backups and disk pruning, it is advisable to adhere to the prescribed scale limits. '
	SET @output = @output + 'If size of the Deduplication DB exceeds the specifications, there may be delays in pruning, resulting in low disk space on disk libraries '
	SET @output = @output + 'as well as performance penalties during backup. Version 10 has automated checks to detect this condition and warn you in timely fashion.'
	SET @output = @output + CHAR(10)
	SET @output = @output + 'http://documentation.commvault.com/commvault/release_10_0_0/books_online_1/english_us/features/dedup_disk/faq.htm'
	SET @output = @output + CHAR(10)
	
	PRINT @output		
END	
ELSE
BEGIN
	SET @retCode = 0
	
	PRINT 'No Deduplication DB found with primary records count greater than 60 percent size of recommended capacity.'
	GOTO CX_EXIT
END

--GET DDB Count and SP Count with primary records exceeding @warningLimit of @MaxAllowed
SELECT @DDBCount = COUNT(DISTINCT SIDBStoreId), @SPCountWithWarningLimit = COUNT(DISTINCT archGrpId)
FROM #SPWithPrimaryCopyStore
WHERE primaryRecCount >= (@MaxAllowed * @warningLimit / 100)
AND		primaryRecCount < (@MaxAllowed * @upperLimit / 100)

SELECT @errorCode	= @@ERROR
IF	@errorCode != 0 GOTO CX_EXIT 
	
--If Deduplication DB found that reached the @warningLimit on Primary Records
IF @SPCountWithWarningLimit > 0 
BEGIN
	--Print Warning
	SET @output = @WarningTag + ' Currently there are [' + CAST(@DDBCount AS VARCHAR(10)) + '] Deduplication DBs at more than 60 percent size of recommended capacity. '
	SET @output = @output + 'After the upgrade, addition of new subclients will be prevented for [' + CAST(@SPCountWithWarningLimit AS VARCHAR(10)) + '] Storage Policies below '
	SET @output = @output + 'that use these specific Deduplication DBs, to prevent the above situation. '
	SET @output = @output + CHAR(10) + 'Note that the current subclients that point to all of these Storage Policies will continue to perform backups and pruning using the current Deduplication DB.'
	SET @output = @output + CHAR(10)

	PRINT @output
	
	--Print Header
	PRINT @HeaderTag + ' Storage Policies'
	PRINT '-------------------------------'
		
	DECLARE	SPNameWithWarningLimitCur CURSOR STATIC FOR 
	SELECT DISTINCT archGroupName
	FROM #SPWithPrimaryCopyStore
	WHERE primaryRecCount >= (@MaxAllowed * @warningLimit / 100)
	AND		primaryRecCount < (@MaxAllowed * @upperLimit / 100)
	
	SET	@errorCode	= @@ERROR 
	IF	@errorCode != 0 GOTO CX_EXIT 
	
	SET @SPNameWithWarningLimitCurFlag = 1 
	OPEN SPNameWithWarningLimitCur 
	
	SET	@errorCode	= @@ERROR 
	IF	@errorCode != 0 GOTO CX_EXIT 
	
	FETCH NEXT 
	FROM  SPNameWithWarningLimitCur 
	INTO  @SPName 
	
	SET	@errorCode	= @@ERROR 
	IF	@errorCode != 0 GOTO CX_EXIT 
	
	WHILE @@FETCH_STATUS = 0 
	BEGIN 
		PRINT @DataTag + ' ' + @SPName
	
		FETCH NEXT 
		FROM  SPNameWithWarningLimitCur 
		INTO  @SPName
	
		SET	@errorCode	=	@@ERROR 
		IF	@errorCode != 0 GOTO CX_EXIT 
	END 
	
	--Print Advice
	SET @output = CHAR(10) + @AdviceTag + ' In order to add new subclients for backups, you will have to create new Storage Policies with new Deduplication DBs - '
	SET @output = @output + 'Please refer to Deduplication Building Block Guide in the product Documentation for more details on the configuration of Deduplication DBs for best performance.'
	SET @output = @output + CHAR(10)

	PRINT @output	
END

--GET DDB Count and SP Count with primary records >= @upperLimit of @MaxAllowed
SELECT @DDBCount = COUNT(DISTINCT SIDBStoreId), @SPCountWithUpperLimit = COUNT(DISTINCT archGrpId)
FROM #SPWithPrimaryCopyStore
WHERE primaryRecCount >= (@MaxAllowed * @upperLimit / 100) 

SELECT @errorCode	= @@ERROR
IF	@errorCode != 0 GOTO CX_EXIT 
	
--If Deduplication DB found that reached the @upperLimit on Primary Records
IF @SPCountWithUpperLimit > 0 
BEGIN
	--Print Warning
	SET @output = @WarningTag + ' Currently there are [' + CAST(@DDBCount AS VARCHAR(10)) + '] Deduplication DBs at size more than the recommended capacity. '
	SET @output = @output + 'After the upgrade, all backups will be prevented for [' + CAST(@SPCountWithUpperLimit AS VARCHAR(10)) + '] Storage Policies below and only pruning will be allowed. '
	SET @output = @output + 'Once pruning reduces the size of the Deduplication DBs, new backups will be allowed on these Storage policies. '
	SET @output = @output + CHAR(10)

	PRINT @output

	--Print Header
	PRINT @HeaderTag + ' Storage Policies'
	PRINT '-------------------------------'
		
	DECLARE	SPNameWithUpperLimitCur CURSOR STATIC FOR 
	SELECT DISTINCT archGroupName
	FROM #SPWithPrimaryCopyStore
	WHERE primaryRecCount >= (@MaxAllowed * @upperLimit / 100) 
		
	SET	@errorCode	= @@ERROR 
	IF	@errorCode != 0 GOTO CX_EXIT 
	
	SET @SPNameWithUpperLimitCurFlag = 1 
	OPEN SPNameWithUpperLimitCur 
	
	SET	@errorCode	= @@ERROR 
	IF	@errorCode != 0 GOTO CX_EXIT 
	
	FETCH NEXT 
	FROM  SPNameWithUpperLimitCur 
	INTO  @SPName 
	
	SET	@errorCode	= @@ERROR 
	IF	@errorCode != 0 GOTO CX_EXIT 
	
	WHILE @@FETCH_STATUS = 0 
	BEGIN 
		PRINT @DataTag + ' ' + @SPName
	
		FETCH NEXT 
		FROM  SPNameWithUpperLimitCur 
		INTO  @SPName
	
		SET	@errorCode	=	@@ERROR 
		IF	@errorCode != 0 GOTO CX_EXIT 
	END 
	
	--Print Advice
	SET @output = CHAR(10) + @AdviceTag + ' Please create new Storage Policies with new Deduplication DBs and re-associate some of the subclients from the affected Storage Policies – '
	SET @output = @output + 'Please refer to Deduplication Building Block Guide in the product Documentation for more details on the configuration of Deduplication DBs for best performance.'
	SET @output = @output + CHAR(10)

	PRINT @output	
END

CX_EXIT: 

IF @SPNameWithWarningLimitCurFlag = 1 
BEGIN 
	CLOSE	    	SPNameWithWarningLimitCur 
	DEALLOCATE	SPNameWithWarningLimitCur 
END

IF @SPNameWithUpperLimitCurFlag = 1 
BEGIN 
	CLOSE	    	SPNameWithUpperLimitCur 
	DEALLOCATE	SPNameWithUpperLimitCur 
END

IF OBJECT_ID('tempdb.dbo.#SPWithPrimaryCopyStore') IS NOT NULL 
	DROP TABLE #SPWithPrimaryCopyStore
	
IF @errorCode != 0
	SELECT 2
ELSE
	SELECT @retCode

RETURN