/****************************************************************************************************
Function: dbo._as_Health_Overview_DBChecks
Purpose: Collect database-level health checks (D1 - D14) separately.
Author: Asamco BV - Alex
Date: 25-01-2025
****************************************************************************************************/
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER FUNCTION dbo._as_Health_Overview_DBChecks
(
@checkDate DATE
)
RETURNS @tblDBHealth TABLE
(
ErrorType NVARCHAR(150), -- e.g. 'Critical', 'High', 'Warning', 'OK'
Module NVARCHAR(20), -- e.g. 'DB', 'AR', 'AP', etc
HealthMessage NVARCHAR(MAX),
PossibleImpact NVARCHAR(MAX),
NoOfIssues INT,
DetailQuery NVARCHAR(MAX),
DetailData NVARCHAR(MAX),
LinkToResolve NVARCHAR(MAX),
IsInfoOnly BIT DEFAULT 0
)
AS
BEGIN
/*Usage:
select * from dbo._as_Health_Overview_DBChecks(null)
*/
/***************************************************************************************
D1. Missing indices, more than X impact
(Threshold ~ 500 for avg_total_user_cost. Adjust as needed.)
***************************************************************************************/
;WITH dD1 AS
(
SELECT
ObjectName = OBJECT_NAME(mid.object_id),
IndexGroupHandle = mig.index_group_handle,
AvgTotalUserCost = migs.avg_total_user_cost,
AvgUserImpact = migs.avg_user_impact,
EqualityColumns = mid.equality_columns,
InequalityColumns = mid.inequality_columns,
IncludedColumns = mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost > 5000
AND mid.database_id = DB_ID() -- If you only want checks for current DB
)
INSERT INTO @tblDBHealth
(
ErrorType, Module, HealthMessage, PossibleImpact,
NoOfIssues, DetailQuery, DetailData, LinkToResolve, IsInfoOnly
)
SELECT
CASE WHEN EXISTS (SELECT * FROM dD1) THEN 'WARNING' ELSE 'OK' END AS ErrorType,
'DB' AS Module,
CASE WHEN EXISTS (SELECT * FROM dD1)
THEN CONCAT('Missing indexes (cost>5000). ', (SELECT COUNT(*) FROM dD1), ' potential index(es).')
ELSE 'OK. No significant missing indexes found.'
END AS HealthMessage,
CASE WHEN EXISTS (SELECT * FROM dD1)
THEN 'Impact: Potential performance degradation due to missing indexes.'
ELSE ''
END AS PossibleImpact,
COALESCE((SELECT COUNT(*) FROM dD1), 0) AS NoOfIssues,
N'
SELECT
OBJECT_NAME(mid.object_id) AS TableName,
migs.avg_total_user_cost AS AvgTotalCost,
migs.avg_user_impact AS AvgImpact,
mid.equality_columns,
mid.inequality_columns,
mid.included_columns
FROM sys.dm_db_missing_index_group_stats migs
INNER JOIN sys.dm_db_missing_index_groups mig ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost > 5000
AND mid.database_id = DB_ID()
' AS DetailQuery,
(SELECT STRING_AGG(ObjectName, ', ')
FROM (SELECT DISTINCT TOP 5 ObjectName FROM dD1) AS x) AS DetailData,
NULL AS LinkToResolve,
CASE WHEN EXISTS (SELECT * FROM dD1) THEN 0 ELSE 1 END AS IsInfoOnly;
/***************************************************************************************
D2. Fragmented indices (>30%).
***************************************************************************************/
;WITH dD2 AS
(
SELECT
DBName = DB_NAME(ps.database_id),
SchemaName = OBJECT_SCHEMA_NAME(ps.object_id, ps.database_id),
TableName = OBJECT_NAME(ps.object_id, ps.database_id),
IndexName = i.name,
ps.index_id,
FragmentPct = ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ps
INNER JOIN sys.indexes i
ON ps.object_id = i.object_id
AND ps.index_id = i.index_id
WHERE ps.avg_fragmentation_in_percent > 30
AND i.name IS NOT NULL
AND i.is_disabled = 0
AND i.index_id > 0
)
INSERT INTO @tblDBHealth
(
ErrorType, Module, HealthMessage, PossibleImpact,
NoOfIssues, DetailQuery, DetailData, LinkToResolve, IsInfoOnly
)
SELECT
CASE WHEN EXISTS (SELECT * FROM dD2) THEN 'WARNING' ELSE 'OK' END,
'DB',
CASE WHEN EXISTS (SELECT * FROM dD2)
THEN CONCAT('High index fragmentation (>30%). ', (SELECT COUNT(*) FROM dD2), ' index(es).')
ELSE 'OK. No significant fragmentation.'
END,
CASE WHEN EXISTS (SELECT * FROM dD2)
THEN 'Impact: Poor query performance, heavy IO usage.'
ELSE ''
END,
COALESCE((SELECT COUNT(*) FROM dD2), 0),
N'
SELECT
DB_NAME(ps.database_id) AS DBName,
OBJECT_SCHEMA_NAME(ps.object_id) AS SchemaName,
OBJECT_NAME(ps.object_id) AS TableName,
i.name AS IndexName,
ps.avg_fragmentation_in_percent AS Fragmentation
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ''LIMITED'') ps
INNER JOIN sys.indexes i
ON ps.object_id = i.object_id
AND ps.index_id = i.index_id
WHERE ps.avg_fragmentation_in_percent > 30
AND i.name IS NOT NULL
AND i.is_disabled = 0
AND i.index_id > 0
',
(SELECT STRING_AGG(TableName + '.' + IndexName, ', ')
FROM (SELECT TOP 5 TableName, IndexName FROM dD2 ORDER BY FragmentPct DESC) t),
NULL,
CASE WHEN EXISTS (SELECT * FROM dD2) THEN 0 ELSE 1 END;
/***************************************************************************************
D3. Free space if Express Edition (simple check ~10GB limit).
***************************************************************************************/
IF CAST(SERVERPROPERTY('Edition') as nvarchar(1000)) NOT LIKE '%Express%'
BEGIN
INSERT INTO @tblDBHealth
(
ErrorType, Module, HealthMessage, PossibleImpact,
NoOfIssues, DetailQuery, DetailData, LinkToResolve, IsInfoOnly
)
SELECT
'OK',
'DB',
'Not Express Edition. Free space limit check skipped.',
'',
0,
NULL,
NULL,
NULL,
1;
END
ELSE
BEGIN
;WITH dD3 AS
(
SELECT
DBName = DB_NAME(),
FileName = name,
FileSizeMB = (size * 8) / 1024.0,
MaxSizeMB = CASE WHEN max_size = -1 THEN 10240
ELSE (max_size * 8) / 1024.0 END,
UsedSpaceMB= ((size - FILEPROPERTY(name, 'SpaceUsed')) * 8) / 1024.0,
FreeSpaceMB= (FILEPROPERTY(name, 'SpaceUsed') * 8) / 1024.0
FROM sys.database_files
WHERE type_desc = 'ROWS'
)
INSERT INTO @tblDBHealth
(
ErrorType, Module, HealthMessage, PossibleImpact,
NoOfIssues, DetailQuery, DetailData, LinkToResolve, IsInfoOnly
)
SELECT
CASE WHEN (SELECT TOP 1 (MaxSizeMB - FileSizeMB)
FROM dD3 ORDER BY FileSizeMB DESC) < 1000
THEN 'ERROR'
ELSE 'OK'
END,
'DB',
CASE WHEN (SELECT TOP 1 (MaxSizeMB - FileSizeMB)
FROM dD3 ORDER BY FileSizeMB DESC) < 1000
THEN 'Express Edition nearing 10GB limit. <1GB free.'
ELSE 'OK. Sufficient free space.'
END,
CASE WHEN (SELECT TOP 1 (MaxSizeMB - FileSizeMB)
FROM dD3 ORDER BY FileSizeMB DESC) < 1000
THEN 'Impact: DB may run out of space soon, insert/update failures.'
ELSE ''
END,
CASE WHEN EXISTS
(
SELECT 1
FROM dD3
WHERE (MaxSizeMB - FileSizeMB) < 1000
)
THEN 1
ELSE 0
END,
N'
SELECT
name AS FileName,
(size*8)/1024.0 AS FileSizeMB,
CASE WHEN max_size=-1 THEN 10240
ELSE (max_size*8)/1024.0 END AS MaxSizeMB,
((size - FILEPROPERTY(name, ''SpaceUsed''))*8)/1024.0 AS UsedSpaceMB,
(FILEPROPERTY(name, ''SpaceUsed'')*8)/1024.0 AS FreeSpaceMB
FROM sys.database_files
WHERE type_desc = ''ROWS''
',
(SELECT STRING_AGG(FileName + ': '
+ CONVERT(VARCHAR(10),(MaxSizeMB - FileSizeMB)) + 'MB free', '; ')
FROM dD3),
NULL,
CASE WHEN (SELECT TOP 1 (MaxSizeMB - FileSizeMB) FROM dD3 ORDER BY FileSizeMB DESC) < 1000
THEN 0
ELSE 1
END;
END
/***************************************************************************************
D4. Placeholder
***************************************************************************************/
INSERT INTO @tblDBHealth
(
ErrorType, Module, HealthMessage, PossibleImpact,
NoOfIssues, DetailQuery, DetailData, LinkToResolve, IsInfoOnly
)
SELECT
'OK',
'DB',
'D4 placeholder: No additional checks implemented here.',
'',
0,
NULL,
NULL,
NULL,
1;
/***************************************************************************************
D5. Last Successful FULL Backup (example: older than 1 day => WARNING)
***************************************************************************************/
;WITH LastFullBackup AS
(
SELECT
dbname = bs.database_name,
last_backup_date = MAX(bs.backup_finish_date)
FROM msdb.dbo.backupset bs
WHERE bs.type = 'D' -- D = Full backup
AND bs.database_name = DB_NAME()
GROUP BY bs.database_name
)
INSERT INTO @tblDBHealth
SELECT
CASE
WHEN EXISTS(SELECT * FROM LastFullBackup
WHERE last_backup_date < DATEADD(DAY, -1, GETDATE()))
THEN 'WARNING'
ELSE 'OK'
END,
'DB',
CASE
WHEN EXISTS(SELECT * FROM LastFullBackup
WHERE last_backup_date < DATEADD(DAY, -1, GETDATE()))
THEN 'No full backup in the last 24 hours.'
ELSE 'OK. Recent full backup found.'
END,
CASE
WHEN EXISTS(SELECT * FROM LastFullBackup
WHERE last_backup_date < DATEADD(DAY, -1, GETDATE()))
THEN 'Impact: Potential data loss if a crash occurs.'
ELSE ''
END,
/* If there is no row at all for the DB, consider that even worse => Insert logic or
treat as "no backups exist"? For demonstration, let's do basic. */
CASE
WHEN NOT EXISTS(SELECT * FROM LastFullBackup)
THEN 1 -- no backup at all
WHEN EXISTS(SELECT * FROM LastFullBackup
WHERE last_backup_date < DATEADD(DAY, -1, GETDATE()))
THEN 1
ELSE 0
END,
N'
SELECT TOP 1
database_name,
backup_finish_date
FROM msdb.dbo.backupset
WHERE type = ''D''
AND database_name = DB_NAME()
ORDER BY backup_finish_date DESC
',
(SELECT CONVERT(NVARCHAR(30), last_backup_date, 120)
FROM LastFullBackup),
NULL,
CASE
WHEN NOT EXISTS(SELECT * FROM LastFullBackup) THEN 0
WHEN EXISTS(SELECT * FROM LastFullBackup
WHERE last_backup_date < DATEADD(DAY, -1, GETDATE())) THEN 0
ELSE 1
END;
/***************************************************************************************
D6. DBCC CheckDB / Consistency Check (using msdb..suspect_pages as a simple indicator)
***************************************************************************************/
;WITH suspect AS
(
SELECT sp.*
FROM msdb.dbo.suspect_pages sp
WHERE sp.database_id = DB_ID()
AND sp.event_type IN (1,2,3)
/* 1=bad page, 2=checksum error, 3=tear, etc. See MS docs for others. */
)
INSERT INTO @tblDBHealth
SELECT
CASE WHEN EXISTS(SELECT * FROM suspect) THEN 'ERROR' ELSE 'OK' END,
'DB',
CASE WHEN EXISTS(SELECT * FROM suspect)
THEN 'Possible corruption detected in suspect_pages.'
ELSE 'OK. No pages marked suspect.'
END,
CASE WHEN EXISTS(SELECT * FROM suspect)
THEN 'Impact: Potential data corruption/integrity issues.'
ELSE ''
END,
COALESCE((SELECT COUNT(*) FROM suspect), 0),
N'
SELECT *
FROM msdb.dbo.suspect_pages
WHERE database_id = DB_ID()
AND event_type IN (1,2,3)
',
NULL,
NULL,
CASE WHEN EXISTS(SELECT * FROM suspect) THEN 0 ELSE 1 END;
/***************************************************************************************
D7. Transaction Log Usage (>80%)
***************************************************************************************/
;WITH logUse AS
(
SELECT
DBName = DB_NAME(database_id),
total_log_mb = total_log_size_in_bytes / 1024,
used_log_mb = used_log_space_in_bytes / 1024,
used_pct = CASE WHEN (total_log_size_in_bytes / 1024)=0 THEN 0
ELSE ((used_log_space_in_bytes / 1024) * 100.0 / (total_log_size_in_bytes / 1024))
END
FROM sys.dm_db_log_space_usage
WHERE database_id = DB_ID()
)
INSERT INTO @tblDBHealth
SELECT
CASE WHEN EXISTS(SELECT * FROM logUse WHERE used_pct > 80)
THEN 'WARNING'
ELSE 'OK'
END,
'DB',
CASE WHEN EXISTS(SELECT * FROM logUse WHERE used_pct > 80)
THEN 'Transaction log is >80% used.'
ELSE 'OK. Log usage within normal range.'
END,
CASE WHEN EXISTS(SELECT * FROM logUse WHERE used_pct > 80)
THEN 'Impact: Risk of running out of log space, blocking, or forced autogrowth.'
ELSE ''
END,
CASE WHEN EXISTS(SELECT * FROM logUse WHERE used_pct > 80)
THEN 1
ELSE 0
END,
N'
SELECT
DB_NAME(database_id) AS DBName,
total_log_size_mb,
used_log_space_mb,
(used_log_space_mb*100.0/total_log_size_mb) AS used_pct
FROM sys.dm_db_log_space_usage
WHERE database_id = DB_ID()
',
(SELECT CONCAT(used_pct,'%') FROM logUse),
NULL,
CASE WHEN EXISTS(SELECT * FROM logUse WHERE used_pct > 80) THEN 0 ELSE 1 END;
/***************************************************************************************
D8. Long-Running Transactions (>60 minutes)
***************************************************************************************/
;WITH longTrans AS
(
SELECT s.session_id,
s.login_time,
DATEDIFF(MINUTE, s.login_time, GETDATE()) AS MinutesActive
FROM sys.dm_exec_sessions s
WHERE s.is_user_process = 1
AND s.database_id = DB_ID()
AND DATEDIFF(MINUTE, s.login_time, GETDATE()) > 60
)
INSERT INTO @tblDBHealth
SELECT
CASE WHEN EXISTS(SELECT * FROM longTrans) THEN 'WARNING' ELSE 'OK' END,
'DB',
CASE WHEN EXISTS(SELECT * FROM longTrans)
THEN CONCAT('Long-running transactions (>60 min). Found: ', (SELECT COUNT(*) FROM longTrans), '.')
ELSE 'OK. No user transactions older than 60 min.'
END,
CASE WHEN EXISTS(SELECT * FROM longTrans)
THEN 'Impact: Potential blocking, locks, or delays.'
ELSE ''
END,
COALESCE((SELECT COUNT(*) FROM longTrans), 0),
N'
SELECT
s.session_id,
s.login_time,
DATEDIFF(MINUTE, s.login_time, GETDATE()) AS MinutesActive
FROM sys.dm_exec_sessions s
WHERE s.is_user_process = 1
AND s.database_id = DB_ID()
AND DATEDIFF(MINUTE, s.login_time, GETDATE()) > 60
',
NULL,
NULL,
CASE WHEN EXISTS(SELECT * FROM longTrans) THEN 0 ELSE 1 END;
/***************************************************************************************
D9. TempDB Usage (simple check if current DB is tempdb; otherwise skip or check usage).
For demonstration, let's check if 'tempdb' usage > 80% allocated vs. its total size.
***************************************************************************************/
IF DB_NAME() = 'tempdb'
BEGIN
;WITH TempDBFiles AS
(
SELECT
FileID = file_id,
FileName = name,
SizeMB = CAST((size*8)/1024.0 AS DECIMAL(10,2)) ,
MaxSizeMB = CASE WHEN max_size=-1 THEN 0 -- or unlimited
ELSE (max_size*8)/1024.0 END ,
UsedMB = CAST(FILEPROPERTY(name, 'SpaceUsed')*8/1024.0 AS DECIMAL(10,2))
FROM sys.database_files
WHERE type_desc = 'ROWS'
)
INSERT INTO @tblDBHealth
SELECT
CASE WHEN (SELECT SUM(UsedMB)*100.0/SUM(SizeMB) FROM TempDBFiles) > 80
THEN 'WARNING'
ELSE 'OK'
END,
'DB',
CASE WHEN (SELECT SUM(UsedMB)*100.0/SUM(SizeMB) FROM TempDBFiles) > 80
THEN 'TempDB usage >80% allocated.'
ELSE 'OK. TempDB usage is within normal range.'
END,
CASE WHEN (SELECT SUM(UsedMB)*100.0/SUM(SizeMB) FROM TempDBFiles) > 80
THEN 'Impact: Potential out-of-space issues for temp objects, queries, sorts.'
ELSE ''
END,
CASE WHEN (SELECT SUM(UsedMB)*100.0/SUM(SizeMB) FROM TempDBFiles) > 80
THEN 1
ELSE 0
END,
N'
SELECT
name AS FileName,
(size*8)/1024.0 AS SizeMB,
FILEPROPERTY(name, ''SpaceUsed'')*8/1024.0 AS UsedMB
FROM sys.database_files
WHERE type_desc = ''ROWS''
',
NULL,
NULL,
CASE WHEN (SELECT SUM(UsedMB)*100.0/SUM(SizeMB) FROM TempDBFiles) > 80 THEN 0 ELSE 1 END;
END
ELSE
BEGIN
-- Not tempdb, just add an info row (or omit entirely).
INSERT INTO @tblDBHealth
SELECT
'OK',
'DB',
'TempDB usage check skipped (current DB is not tempdb).',
'',
0,
NULL,
NULL,
NULL,
1;
END
/***************************************************************************************
D10. VLF Count (Virtual Log Files). If >1000 => WARNING
***************************************************************************************/
/* For SQL Server 2019 or above: sys.dm_db_log_info(DB_ID())
For older versions: DBCC LOGINFO() approach (which is more cumbersome). */
;WITH vlf AS
(
SELECT COUNT(*) AS VLFCount
FROM sys.dm_db_log_info(DB_ID())
)
INSERT INTO @tblDBHealth
SELECT
CASE WHEN (SELECT VLFCount FROM vlf) > 1000 THEN 'WARNING' ELSE 'OK' END,
'DB',
CASE WHEN (SELECT VLFCount FROM vlf) > 1000
THEN CONCAT('High VLF count (', (SELECT VLFCount FROM vlf), ').')
ELSE 'OK. Acceptable VLF count.'
END,
CASE WHEN (SELECT VLFCount FROM vlf) > 1000
THEN 'Impact: Slower recovery times, potential performance degradation.'
ELSE ''
END,
(SELECT VLFCount FROM vlf),
N'
SELECT DB_NAME() AS DBName, COUNT(*) AS VLFCount
FROM sys.dm_db_log_info(DB_ID())
',
NULL,
NULL,
CASE WHEN (SELECT VLFCount FROM vlf) > 1000 THEN 0 ELSE 1 END;
/***************************************************************************************
D11. High CPU queries (simplified).
E.g. if any query has total_worker_time > 100,000,000 (~100s CPU) => WARNING
***************************************************************************************/
;WITH topCPU AS
(
SELECT
qs.plan_handle,
qs.total_worker_time AS TotalCPU,
qs.execution_count AS ExecCount,
qs.total_worker_time / qs.execution_count AS AvgCPU,
SUBSTRING(
qt.TEXT,
(qs.statement_start_offset/2)+1,
(
CASE
WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.TEXT)) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset
)/2
) AS SqlText --select top 10 *
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
WHERE qt.dbid = DB_ID()
)
INSERT INTO @tblDBHealth
SELECT
CASE WHEN EXISTS (
SELECT 1 FROM topCPU WHERE TotalCPU > 100000000 -- ~100 CPU seconds
)
THEN 'WARNING' ELSE 'OK' END,
'DB',
CASE WHEN EXISTS (
SELECT 1 FROM topCPU WHERE TotalCPU > 100000000
)
THEN 'One or more queries used >100s total CPU time.'
ELSE 'OK. No extremely high CPU queries found.'
END,
CASE WHEN EXISTS (
SELECT 1 FROM topCPU WHERE TotalCPU > 100000000
)
THEN 'Impact: Potential performance bottleneck from CPU-heavy queries.'
ELSE ''
END,
CASE WHEN EXISTS (
SELECT 1 FROM topCPU WHERE TotalCPU > 100000000
)
THEN 1 ELSE 0 END,
N'
SELECT TOP 5
qs.total_worker_time AS TotalCPU,
qs.execution_count AS ExecCount,
(qs.total_worker_time / qs.execution_count) AS AvgCPU,
SUBSTRING(qt.TEXT,
(qs.statement_start_offset/2)+1,
(CASE WHEN qs.statement_end_offset=-1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.TEXT))*2
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2
) AS SqlText
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_worker_time DESC
',
NULL,
NULL,
CASE WHEN EXISTS (
SELECT 1 FROM topCPU WHERE TotalCPU > 100000000
) THEN 0 ELSE 1 END;
/***************************************************************************************
D12. Collation mismatch vs. DB collation (simple example)
***************************************************************************************/
;WITH colMismatch AS
(
SELECT
t.name AS TableName,
c.name AS ColumnName,
c.collation_name,
DBLevelColl = DATABASEPROPERTYEX(DB_NAME(), 'Collation')
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
WHERE c.collation_name IS NOT NULL
AND c.collation_name <> cast(DATABASEPROPERTYEX(DB_NAME(), 'Collation') as nvarchar(100))
)
INSERT INTO @tblDBHealth
SELECT
CASE WHEN EXISTS (SELECT * FROM colMismatch) THEN 'WARNING' ELSE 'OK' END,
'DB',
CASE WHEN EXISTS (SELECT * FROM colMismatch)
THEN CONCAT('Collation mismatches found. ', (SELECT COUNT(*) FROM colMismatch), ' columns differ.')
ELSE 'OK. No collation mismatches vs. DB collation.'
END,
CASE WHEN EXISTS (SELECT * FROM colMismatch)
THEN 'Impact: Potential errors or performance issues with string comparisons.'
ELSE ''
END,
COALESCE((SELECT COUNT(*) FROM colMismatch), 0),
N'
SELECT t.name AS TableName,
c.name AS ColumnName,
c.collation_name,
DATABASEPROPERTYEX(DB_NAME(), ''Collation'') AS DBLevelColl
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
WHERE c.collation_name IS NOT NULL
AND c.collation_name <> cast(DATABASEPROPERTYEX(DB_NAME(), ''Collation'') as nvarchar(100))
',
NULL,
NULL,
CASE WHEN EXISTS (SELECT * FROM colMismatch) THEN 0 ELSE 1 END;
/***************************************************************************************
D13. Database compatibility level
For example, if DB is < 150 on a SQL 2019 instance => Warning
***************************************************************************************/
DECLARE @SQLProductVersion nvarchar(1000) = CAST(SERVERPROPERTY('ProductVersion') as nvarchar(1000))
INSERT INTO @tblDBHealth
SELECT
CASE WHEN (d.compatibility_level < 150 AND @SQLProductVersion LIKE '15%')
THEN 'WARNING'
WHEN (d.compatibility_level < 160 AND @SQLProductVersion LIKE '16%')
THEN 'WARNING'
ELSE 'OK'
END,
'DB',
CASE WHEN (d.compatibility_level < 150 AND @SQLProductVersion LIKE '15%')
THEN CONCAT('DB Compatibility level is ', CONVERT(VARCHAR(5), d.compatibility_level),
', recommended 150 for SQL 2019.')
WHEN (d.compatibility_level < 160 AND @SQLProductVersion LIKE '16%')
THEN CONCAT('DB Compatibility level is ', CONVERT(VARCHAR(5), d.compatibility_level),
', recommended 160 for SQL 2022.')
ELSE 'OK. Compatibility level is at or above recommended.'
END,
CASE WHEN (d.compatibility_level < 150 AND @SQLProductVersion LIKE '15%')
OR (d.compatibility_level < 160 AND @SQLProductVersion LIKE '16%')
THEN 'Impact: Missing potential performance improvements, T-SQL features.'
ELSE ''
END,
CASE WHEN (d.compatibility_level < 150 AND @SQLProductVersion LIKE '15%')
OR (d.compatibility_level < 160 AND @SQLProductVersion LIKE '16%')
THEN 1
ELSE 0
END,
N'
SELECT name, compatibility_level
FROM sys.databases
WHERE database_id = DB_ID()
',
CONCAT('Current level: ', d.compatibility_level),
NULL,
CASE WHEN (d.compatibility_level < 150 AND @SQLProductVersion LIKE '15%')
OR (d.compatibility_level < 160 AND @SQLProductVersion LIKE '16%')
THEN 0
ELSE 1
END
FROM sys.databases d
WHERE d.database_id = DB_ID();
/***************************************************************************************
D14. Critical Errors / Alerts (Placeholder)
Real implementation might parse SQL Error Log or Extended Events.
***************************************************************************************/
INSERT INTO @tblDBHealth
(
ErrorType, Module, HealthMessage, PossibleImpact,
NoOfIssues, DetailQuery, DetailData, LinkToResolve, IsInfoOnly
)
SELECT
'OK',
'DB',
'D14 placeholder: No check implemented for critical errors/alerts in logs.',
'',
0,
NULL,
NULL,
NULL,
1;
RETURN;
END
GO
Overview
Content Tools