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 @MANameCurFlag  INTEGER 
SET 		@MANameCurFlag  = 0 

DECLARE @MACount 		INTEGER 
SET			@MACount		= 0

DECLARE @errorCode  INTEGER 
SET			@errorCode	= 0

DECLARE @retCode  INTEGER 
SET			@retCode	= 0

DECLARE @HostId 	INTEGER 
DECLARE @MAName 	NVARCHAR(510) 
DECLARE @output		NVARCHAR(MAX)

DECLARE @MAWithMultipleDDBsOnSameVolume TABLE
(
		HostId		INTEGER,
		MAName		NVARCHAR(510) 
		PRIMARY KEY (HostId)
)

PRINT @DescTag + ' Check for MediaAgents with multiple Deduplication databases (DDBs) on the same volume'

INSERT INTO @MAWithMultipleDDBsOnSameVolume
SELECT DISTINCT AC.id, AC.name
FROM APP_Client AC WITH (NOLOCK), 
	(SELECT	ClientId, RootPath
	FROM	(SELECT	P.ClientId, (CASE WHEN CHARINDEX('/', P.Path, 1) = 1 THEN SUBSTRING(P.Path, 1, CHARINDEX('/', P.Path, 2)) ELSE SUBSTRING(P.Path, 1, 3) END) RootPath
			FROM	IdxSIDBSubStore S WITH (NOLOCK), IdxAccessPath P WITH (NOLOCK)
			WHERE	S.IdxAccessPathId = P.IdxAccessPathId
					AND S.SIDBStoreID != 0
					AND S.IdxAccessPathId != 0
					AND S.SealedTime = 0
					AND EXISTS (SELECT id FROM archGroupCopy AGC WITH (NOLOCK) WHERE S.SIDBStoreId = AGC.SIDBStoreId)) T
	GROUP BY ClientId, RootPath
	HAVING COUNT(*) > 1) T
WHERE	AC.id = T.ClientId

SELECT @errorCode	= @@ERROR, @MACount = @@ROWCOUNT
IF	@errorCode != 0 GOTO CX_EXIT 

--If No Media Agents hosting more than one active DDB on the same File system volume
IF @MACount = 0 
BEGIN 
	SET @retCode = 0
	PRINT 'No MediaAgents hosting more than one active DDB on the same File System volume.'
	GOTO CX_EXIT
END 

--Print Error/Warning
SET @retCode = 1
SET @output = @WarningTag + ' The following (' + CAST(@MACount AS VARCHAR(10)) + ') MediaAgents might be hosting more than one active DDB on the same File System volume. This might cause severe performance issues with deduplication-based backups and data aging operations.'
SET @output = @output + CHAR(10)

PRINT @output

--Print Header
PRINT @HeaderTag + ' Media Agents'
PRINT '----------------------------'
	
DECLARE	MANameCur CURSOR STATIC FOR 
SELECT HostId, MAName
FROM @MAWithMultipleDDBsOnSameVolume

SET	@errorCode	= @@ERROR 
IF	@errorCode != 0 GOTO CX_EXIT 

SET @MANameCurFlag = 1 
OPEN MANameCur 

SET	@errorCode	= @@ERROR 
IF	@errorCode != 0 GOTO CX_EXIT 

FETCH NEXT 
FROM  MANameCur 
INTO  @HostId, @MAName 

SET	@errorCode	= @@ERROR 
IF	@errorCode != 0 GOTO CX_EXIT 

WHILE @@FETCH_STATUS = 0 
BEGIN 
	PRINT @DataTag + ' ' + @MAName

	FETCH NEXT 
	FROM  MANameCur 
	INTO  @HostId, @MAName 

	SET	@errorCode	=	@@ERROR 
	IF	@errorCode != 0 GOTO CX_EXIT 
END 

--Print Advice
SET @output = CHAR(10) + @AdviceTag + ' If the DDBs are already on different volumes, ignore this warning. Otherwise, move the DDBs to separate volumes.'
SET @output = @output + CHAR(10)

PRINT @output

CX_EXIT: 

IF @MANameCurFlag = 1 
BEGIN 
	CLOSE	    MANameCur 
	DEALLOCATE	MANameCur 
END

IF @errorCode != 0
	SELECT 2
ELSE
	SELECT @retCode

RETURN