-- Script will check if there is enough space available for database upgrade
-- Space available should be greater than or equal to size of the database

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 @TempLogFiles TABLE (PhysicalName nvarchar(max), DiskFreeSpace bigint, IsFreeSpaceEnough integer, DriveLetter nvarchar(1))
DECLARE @TempDriveInfo TABLE (Drive nvarchar(1), FreeSpace integer) 
DECLARE	@driveLetters nvarchar(256) 
SET		@driveLetters = ''
DECLARE	@totalReqDiskSpaceKB FLOAT
DECLARE	@databaseName nvarchar(256) 
SET		@databaseName = DB_NAME()

DECLARE @errorCode INTEGER 
SET		@errorCode = 0


PRINT @DescTag + ' Check for available Disk Space on CommServe'

-- Get the Required disk space
--SELECT @totalReqDiskSpaceKB =	(3 * (ISNULL(SUM(size_on_disk_bytes), 0)))/1024.0/1024.0, @totalReqDiskUsedSpaceMB = (3 * 8 * (ISNULL(SUM(mf.size), 0)))/1024.0
--FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS divfs
--	JOIN sys.master_files AS mf ON mf.database_id = divfs.database_id
--	AND mf.file_id = divfs.file_id
--	AND mf.database_id = DB_ID(@databaseName)


SELECT  @totalReqDiskSpaceKB = 3 * (SUM((a1.reserved + ISNULL(a4.reserved,0))* 8) - SUM((CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8))
FROM
	(SELECT
		ps.object_id,
		SUM (
			CASE
				WHEN (ps.index_id < 2) THEN row_count
				ELSE 0
			END
			) AS [rows],
		SUM (ps.reserved_page_count) AS reserved,
		SUM (
			CASE
				WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
				ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
			END
			) AS data,
		SUM (ps.used_page_count) AS used
	FROM sys.dm_db_partition_stats ps
	GROUP BY ps.object_id) AS a1
	LEFT OUTER JOIN
	(SELECT
		it.parent_id,
		SUM(ps.reserved_page_count) AS reserved,
		SUM(ps.used_page_count) AS used
	FROM sys.dm_db_partition_stats ps
		INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
	WHERE (it.internal_type = 202 OR it.internal_type = 204)
	GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
	INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id )
	INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.type <> N'S' and a2.type <> N'IT'

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

-- Get the Log file information
INSERT INTO @TempLogFiles
SELECT	mf.physical_name, 0, 2, ''
FROM sys.master_files AS mf
WHERE mf.database_id = DB_ID(@databaseName)
	AND mf.type_desc = 'LOG'

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

-- Get the Disk Space information
INSERT INTO @TempDriveInfo
EXEC master.dbo.xp_fixeddrives

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

UPDATE	@TempLogFiles
SET		DiskFreeSpace = D.FreeSpace, IsFreeSpaceEnough = (CASE WHEN (D.FreeSpace < (@totalReqDiskSpaceKB/1024)) THEN 0 ELSE 1 END),
		DriveLetter = D.Drive
FROM	@TempLogFiles L, @TempDriveInfo D
WHERE	SUBSTRING(L.PhysicalName, 1, 1) = D.Drive

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

-- SELECT DISTINCT @driveLetters = @driveLetters + ' ' + ISNULL(DriveLetter, '') + ':\'  from @TempLogFiles WHERE IsFreeSpaceEnough = 0

SELECT @driveLetters = ISNULL(SUBSTRING((SELECT	DISTINCT ',' + ISNULL(DriveLetter, '') + ':\' FROM @TempLogFiles  WHERE IsFreeSpaceEnough = 0 FOR XML PATH('')), 2, 99999999), '')

CX_EXIT: 
	IF @errorCode != 0 
	BEGIN
		PRINT @ErrorTag + ' Failed to check free space on the disk.'
		SELECT 2
	END
	ELSE IF (EXISTS (SELECT * FROM @TempLogFiles WHERE IsFreeSpaceEnough = 0))
	BEGIN
		PRINT @ErrorTag + ' Space available on disk[' + @driveLetters +'] where log files of database [' + @databaseName + '] exist is less than 3 times the size of database.' 
		PRINT @AdviceTag + ' You must have at least ' + dbo.getSizeString(@totalReqDiskSpaceKB*1024) + ' free space available on disk[' + @driveLetters +'].'
		SELECT 2
	END
	ELSE IF (EXISTS (SELECT * FROM @TempLogFiles WHERE IsFreeSpaceEnough = 2))
	BEGIN
		PRINT @WarningTag + ' Unable to check available space on the disk where log files exist.'
		SELECT 1
	END
	ELSE
	BEGIN
		-- PRINT 'There is enough disk space available for upgrade to complete.'
		SELECT 0
	END
			
	RETURN