IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[CardioLogHealthCheck]') AND type in (N'P', N'PC'))
DROP PROCEDURE CardioLogHealthCheck
GO
CREATE PROCEDURE CardioLogHealthCheck
AS 
DECLARE
	@Edition VARCHAR(50),
    @DedicatedServerStatus VARCHAR(200),
    @DedicatedServerCheck VARCHAR(200),
    @MinRam SQL_VARIANT,
    @MaxRam SQL_VARIANT,
    @MinRamFinal INT,
    @MaxRamFinal INT,
	@LogicalCPUCount INT,
	@PhysicalCPUCount INT,
    @RamTotal VARCHAR (200),
    @RecoveryModel SQL_VARIANT,
    @CardiologDisk VARCHAR(50),
    @DriveCDATA VARCHAR(50),
    @AvailableMBsCDATA DECIMAL,
    @TotalMBsCDATA DECIMAL,
    @RatioCDATA DECIMAL(20,2),
    @RatioLeftCDATA INT,
    @DriveCLOG VARCHAR(50),
    @AvailableMBsCLOG DECIMAL,
    @TotalMBsCLOG DECIMAL,
    @RatioCLOG DECIMAL(20,2),
    @RatioLeftCLOG INT,
    @DriveTEMPDB VARCHAR(50),
    @AvailableMBsTEMPDB DECIMAL,
    @TotalMBsTEMPDB DECIMAL,
    @RatioTEMPDB DECIMAL(20,2),
    @RatioLeftTEMPDB INT,
    @RowCount INT,
    @LatestDataCollected DATETIME



PRINT @@VERSION
	
SELECT @Edition = Value from tab_global_settings where name = 'SystemMode' and Category = 'CardioLog'
PRINT 'STATUS: INFO. CardioLog Edition:' + @Edition + '.'

DECLARE @NumberOfExtraDBsInInstance VARCHAR(50)
SELECT @NumberOfExtraDBsInInstance = COUNT(1) from sys.databases where name NOT IN ('master','tempdb','model','msdb','CardioLog')

--Checks if Dedicated Server
IF @NumberOfExtraDBsInInstance > 0

           SET @DedicatedServerStatus = 'STATUS: WARNING. This instance is not a dedicated server for Cardiolog. There are ' + @NumberOfExtraDBsInInstance + ' Extra DBs.'
   ELSE
           SET @DedicatedServerStatus = 'STATUS: OK. This is a dedicated server for Cardiolog.'

PRINT @DedicatedServerStatus


--Checks RAM availability
SELECT @MaxRam = value
FROM sys.configurations
WHERE name like '%max server memory%'
ORDER BY name OPTION (RECOMPILE);

SELECT @MinRam = value
FROM sys.configurations
WHERE name like '%min server memory%'
ORDER BY name OPTION (RECOMPILE);

SET @MinRamFinal = CAST(@MinRam AS INT)
SET @MaxRamFinal = CAST(@MaxRam AS INT)

IF @MinRamFinal/@MaxRamFinal < 1
    SET @RamTotal = 'STATUS: OK. Minimum RAM Allocated for this instance is ' + CAST(@MinRam AS VARCHAR(100)) + ' MB, While Maximum RAM Allocated for this instance is ' + CAST(@MaxRam AS VARCHAR(100)) + 'MB.'
ELSE
    SET @RamTotal = 'STATUS: WARNING. Minimum RAM = ' + CAST(@MinRam AS VARCHAR(100)) + ', Maximum RAM = ' + CAST(@MaxRam AS VARCHAR(100)) + '. Minimum RAM is higher then the minimum!'

PRINT @RamTotal


--Check CPU
SELECT @LogicalCPUCount = cpu_count, @PhysicalCPUCount = cpu_count/hyperthread_ratio FROM sys.dm_os_sys_info

PRINT 'STATUS: INFO. Number of physical CPU:' + CAST(@PhysicalCPUCount AS VARCHAR(50))
PRINT 'STATUS: INFO. Number of logical CPU:' + CAST(@LogicalCPUCount AS VARCHAR(50))

IF @LogicalCPUCount < 4
	PRINT 'STATUS: WARNING. Number of Cores is issuficiant!!'  + '.'

IF @Edition = 'Enterprise'
	BEGIN
		IF @LogicalCPUCount < 8
			PRINT 'STATUS: WARNING. Number of Cores is under the minimum requirment for Enterprise edition'  + '.'
		ELSE IF @LogicalCPUCount < 16
			PRINT 'STATUS: WARNING. Number of Cores is under the recommended requirment for Enterprise edition'  + '.'
	END
ELSE IF @Edition = 'Professional'
	IF @LogicalCPUCount < 8
		PRINT 'STATUS: WARNING. Number of Cores is under the recommended requirment for Professional edition'  + '.'

--Checks Recovery Model
SELECT @RecoveryModel = DATABASEPROPERTYEX([name],'recovery')
FROM sys.databases
WHERE name = 'Cardiolog'

IF @RecoveryModel = 'SIMPLE'
    PRINT 'STATUS: OK. The recovery model of Cardiolog database is ' + CAST(@RecoveryModel AS VARCHAR(100)) + '.'
ELSE 
    PRINT 'STATUS: WARNING. The recovery models for the Cardiolog database is ' + CAST(@RecoveryModel AS VARCHAR(100)) + ', Please change the recovery model of the CardioLog database to SIMPLE. '


--Checks Drive of Cardiolog DATA
SELECT 
    @DriveCDATA          =      s.volume_mount_point ,
    @AvailableMBsCDATA   =      CAST(s.available_bytes / 1048576.0 as decimal(20,2)),
    @TotalMBsCDATA       =      CAST(s.total_bytes / 1048576.0 as decimal(20,2)),
    @RatioCDATA          =      CAST((CAST(s.available_bytes / 1048576.0 as decimal(20,2)) / CAST(s.total_bytes / 1048576.0 as decimal(20,2))) AS decimal(20,2))
FROM 
sys.master_files f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) s
INNER JOIN sys.sysdatabases d
ON d.dbid = f.database_id
WHERE d.name = 'Cardiolog' 
AND type_desc = 'ROWS'

SET @RatioCDATA = @RatioCDATA * 100

SET @RatioLeftCDATA = (100 - @RatioCDATA)

PRINT 'STATUS: INFO. Total DB Data disk size: ' + CAST(@TotalMBsCDATA as VARCHAR(50))

--Checks Drive of Cardiolog LOG
SELECT 
    @DriveCLOG           =      s.volume_mount_point ,
    @AvailableMBsCLOG    =      CAST(s.available_bytes / 1048576.0 as decimal(20,2)),
    @TotalMBsCLOG        =      CAST(s.total_bytes / 1048576.0 as decimal(20,2)),
    @RatioCLOG           =      CAST((CAST(s.available_bytes / 1048576.0 as decimal(20,2)) / CAST(s.total_bytes / 1048576.0 as decimal(20,2))) AS decimal(20,2))
FROM 
sys.master_files f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) s
INNER JOIN sys.sysdatabases d
ON d.dbid = f.database_id
WHERE d.name = 'Cardiolog' 
AND type_desc = 'LOG'

SET @RatioCLOG = @RatioCLOG * 100

SET @RatioLeftCLOG = (100 - @RatioCLOG)

PRINT 'STATUS: INFO. Total DB Log disk size: ' + CAST(@TotalMBsCLOG as VARCHAR(50))

    IF @DriveCDATA = @DriveCLOG AND @RatioLeftCDATA < 40
        PRINT 'STATUS: WARNING. Cardiolog Data and Log are on the same disk and have less than 40% disk space left. Drive: ' + @DriveCLOG + '. Total space on disk: ' + CAST(@TotalMBsCLOG AS VARCHAR(100)) + '. Available space on disk :' + CAST(@AvailableMBsCLOG AS VARCHAR(100)) + '. Disk availablity: ' + CAST(@RatioLeftCLOG AS VARCHAR(30)) + '%.'
    ELSE IF @DriveCDATA = @DriveCLOG
        PRINT 'STATUS: WARNING. Cardiolog Data and Log are on the same disk. Drive: ' + @DriveCLOG + '. Total space on disk: ' + CAST(@TotalMBsCLOG AS VARCHAR(100)) + '. Available space on disk :' + CAST(@AvailableMBsCLOG AS VARCHAR(100)) + '. Disk availablity: ' + CAST(@RatioLeftCLOG AS VARCHAR(30)) + '%.'
    ELSE
        BEGIN
            IF @RatioLeftCDATA < 30
                PRINT 'STATUS: WARNING. Cardiolog Data Disk has less than 30% storage left. Drive: ' + @DriveCDATA + '. Total space on disk: ' + CAST(@TotalMBsCDATA AS VARCHAR(100)) + '. Available space on disk :' + CAST(@AvailableMBsCDATA AS VARCHAR(100)) + '. Disk availablity: ' + CAST(@RatioLeftCDATA AS VARCHAR(30)) + '%.'
            ELSE 
                PRINT 'STATUS: OK. Cardiolog Data Drive: ' + @DriveCDATA + '. Total space on disk: ' + CAST(@TotalMBsCDATA AS VARCHAR(100)) + '. Available space on disk :' + CAST(@AvailableMBsCDATA AS VARCHAR(100)) + '. Disk availablity: ' + CAST(@RatioLeftCDATA AS VARCHAR(30)) + '%.'

            IF @RatioLeftCLOG < 30
                PRINT 'STATUS: WARNING. Cardiolog Log Disk has less than 30% storage left. Drive: : ' + @DriveCLOG + '. Total space on disk: ' + CAST(@TotalMBsCLOG AS VARCHAR(100)) + '. Available space on disk :' + CAST(@AvailableMBsCLOG AS VARCHAR(100)) + '. Disk availablity: ' + CAST(@RatioLeftCLOG AS VARCHAR(30)) + '%.'
            ELSE 
                PRINT 'STATUS: OK. Cardiolog Log Disk: ' + @DriveCLOG + '. Total space on disk: ' + CAST(@TotalMBsCLOG AS VARCHAR(100)) + '. Available space on disk :' + CAST(@AvailableMBsCLOG AS VARCHAR(100)) + '. Disk availablity: ' + CAST(@RatioLeftCLOG AS VARCHAR(30)) + '%.'
        END


--Checks Drive of TEMPDB
SELECT DISTINCT 
    @DriveTEMPDB             =      s.volume_mount_point ,
    @AvailableMBsTEMPDB      =      CAST(s.available_bytes / 1048576.0 as decimal(20,2)),
    @TotalMBsTEMPDB          =      CAST(s.total_bytes / 1048576.0 as decimal(20,2)),
    @RatioTEMPDB             =      CAST((CAST(s.available_bytes / 1048576.0 as decimal(20,2)) / CAST(s.total_bytes / 1048576.0 as decimal(20,2))) AS decimal(20,2))
FROM 
sys.master_files f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) s
INNER JOIN sys.sysdatabases d
ON d.dbid = f.database_id
WHERE d.name = 'TempDB' 

SET @RatioTEMPDB = @RatioTEMPDB * 100

SET @RatioLeftTEMPDB = (100 - @RatioTEMPDB)

IF @RatioLeftTEMPDB < 50
    PRINT 'STATUS: WARNING. TEMPDB Disk has less than 50% storage left. Drive: : ' + @DriveTEMPDB + '. Total space on disk: ' + CAST(@TotalMBsTEMPDB AS VARCHAR(100)) + '. Available space on disk :' + CAST(@AvailableMBsTEMPDB AS VARCHAR(100)) + '. Disk availablity: ' + CAST(@RatioLeftTEMPDB AS VARCHAR(30)) + '%.'
ELSE 
    PRINT 'TempDB Disk: ' + @DriveTEMPDB + '. Total space on disk: ' + CAST(@TotalMBsTEMPDB AS VARCHAR(100)) + '. Available space on disk :' + CAST(@AvailableMBsTEMPDB AS VARCHAR(100)) + '. Disk availablity: ' + CAST(@RatioLeftTEMPDB AS VARCHAR(100))


DECLARE @IndexName VARCHAR(50)

--Fragmantation check
IF EXISTS (SELECT ind.name AS IndexName, indexstats.avg_fragmentation_in_percent 
          FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats 
          INNER JOIN sys.indexes ind  
          ON ind.object_id = indexstats.object_id 
          AND ind.index_id = indexstats.index_id 
          WHERE indexstats.avg_fragmentation_in_percent > 30 
          AND ind.name IS NOT NULL 
          )
BEGIN 
    PRINT 'STATUS: WARNING. The are fragmented indexes over 30% on the Cardiolog Database. Here is the list of them:'

    DECLARE @FragRate VARCHAR(256)

    DECLARE FragTest CURSOR 
    FOR
    SELECT ind.name AS IndexName, indexstats.avg_fragmentation_in_percent 
    FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats 
    INNER JOIN sys.indexes ind  
    ON ind.object_id = indexstats.object_id 
    AND ind.index_id = indexstats.index_id 
    WHERE indexstats.avg_fragmentation_in_percent > 30 
    AND ind.name IS NOT NULL 
    ORDER BY indexstats.avg_fragmentation_in_percent DESC

    OPEN FragTest   
    FETCH NEXT FROM FragTest INTO @indexName, @FragRate   

    WHILE @@FETCH_STATUS = 0   
    BEGIN   
           PRINT '  The index ' + @INDEXNAME + ', is ' + @FragRate + '% fragmented.'
           FETCH NEXT FROM FragTest INTO @indexName, @FragRate 
    END   

    CLOSE FragTest   
    DEALLOCATE FragTest

    PRINT 'STATUS: TODO. Defragment any index above 30% fragmentation.'
END
ELSE 
    PRINT 'STATUS: OK. There are no indexes fragmented over 30% on the Cardiolog database.'

SELECT @RowCount = COUNT(1) from tab_sharepoint_tree (nolock)

PRINT 'STATUS: INFO. Amount of tree items in tab_sharepoint_tree is ' + CAST(@RowCount AS VARCHAR(30)) + '.'

SELECT @RowCount = COUNT(1) from tab_sharepoint_tree (nolock)
WHERE IsDeleted = 1

PRINT 'STATUS: INFO. Amount of deleted tree items in tab_sharepoint_tree is ' + CAST(@RowCount AS VARCHAR(30)) + '.'

SELECT @RowCount = count(1) from tab_sharepoint_tree (nolock)
WHERE IsDeleted = 1 and DeletionDate < GETDATE() - 365

PRINT 'STATUS: INFO. Amount of deleted tree items in tab_sharepoint_tree that are deleted for more then a year is ' + CAST(@RowCount AS VARCHAR(30)) + '.'

SELECT @RowCount = count(1) from tab_users (nolock)
WHERE IsDeleted = 0

PRINT 'STATUS: INFO. Amount of users in tab_users that are not deleted is ' + CAST(@RowCount AS VARCHAR(30)) + '.'

--Count rows in table
SELECT @RowCount = COUNT(EventLogId)
FROM tab_event_log with(nolock)

PRINT 'STATUS: INFO. Amount of events collected in tab_event_log is ' + CAST(@RowCount AS VARCHAR(30)) + '.'

--Last time tracked (If there is tracking)
SELECT TOP 1 @LatestDataCollected = timestamp 
FROM tab_temp_event_log with(nolock)
ORDER BY timestamp desc

PRINT 'STATUS: INFO. The last event collected was on the ' + CAST(@LatestDataCollected AS VARCHAR(50)) + '.'