SQL Server Heath Check Script

SELECT @@SERVERNAME as ServerName,@@ServiceName as [InstanceName],
case
when convert(varchar(100), SERVERPROPERTY(N'ProductVersion')) like '16.%' then 'SQL Server 2022'
when convert(varchar(100), SERVERPROPERTY(N'ProductVersion')) like '15.%' then 'SQL Server 2019'
when convert(varchar(100), SERVERPROPERTY(N'ProductVersion')) like '14.%' then 'SQL Server 2017'
when convert(varchar(100), SERVERPROPERTY(N'ProductVersion')) like '13.%' then 'SQL Server 2016'
when convert(varchar(100), SERVERPROPERTY(N'ProductVersion')) like '12.%' then 'SQL Server 2014'
when convert(varchar(100), SERVERPROPERTY(N'ProductVersion')) like '11.%' then 'SQL Server 2012'
when convert(varchar(100),SERVERPROPERTY(N'ProductVersion')) like '10.5%' then 'SQL Server 2008R2'
when convert(varchar(100),SERVERPROPERTY(N'ProductVersion')) like '10.0%' then 'SQL Server 2008'
when convert(varchar(100),SERVERPROPERTY(N'ProductVersion')) like '10.0%' then 'SQL Server 2008'
when convert(varchar(100),SERVERPROPERTY(N'ProductVersion')) like '9.0%' then 'SQL Server 2005'
else 'Not Found'
end as VersionName,
SERVERPROPERTY(N'ProductVersion') AS [Number],
SERVERPROPERTY('ProductLevel') AS SP,
CAST(SERVERPROPERTY(N'Edition') AS sysname) AS [Edition]

sqlserver_start_time FROM sys.dm_os_sys_info

***********1. Health check when apply Windows patch or SQL patch
--DB Status--


select
a.name,
a.state_desc,
case
When a.state = 0 then 'ONLINE'
when a.state = 1 then 'RESTORING'
when a.state = 2 then 'RECOVERING'
when a.state = 3 then 'RECOVERY_PENDING'
when a.state = 4 then 'SUSPECT'
when a.state = 5 then 'EMERGENCY'
when a.state = 6 then 'OFFLINE'
when a.state = 7 then 'COPYING - SQL AZURE'
when a.state = 10 then 'OFLINE_SECONDARY - SQL AZURE'
end
from sys.databases a where a.state<>0




--SQL Services Script--
DECLARE @ServiceStatus TABLE
(ServerName nvarchar(50)
,ServiceName nvarchar(50)
,StatusOfService nvarchar(20)
,StatusAsOn datetime)


INSERT INTO @ServiceStatus (StatusOfService)
EXEC master..xp_servicecontrol 'QueryState', 'MSSQL'
UPDATE @ServiceStatus
  SET ServerName=@@SERVERNAME
  ,ServiceName='MSSQL Server'
  ,StatusAsOn=GETDATE()
WHERE ServerName IS NULL


INSERT INTO @ServiceStatus (StatusOfService)
EXEC master..xp_servicecontrol 'QueryState', 'SQLAgent'
UPDATE @ServiceStatus
  SET ServerName=@@SERVERNAME
  ,ServiceName='SQL Server Agent'
  ,StatusAsOn=GETDATE()
WHERE ServerName IS NULL


INSERT INTO @ServiceStatus (StatusOfService)
EXEC master..xp_servicecontrol 'QueryState', 'SQLBrowser'
UPDATE @ServiceStatus
  SET ServerName=@@SERVERNAME
  ,ServiceName='SQL Server Browser'
  ,StatusAsOn=GETDATE()
WHERE ServerName IS NULL


SELECT * FROM @ServiceStatus where StatusOfService='Stopped.' and ServiceName!='SQL Server Browser'


--CDC Check
select name from msdb.dbo.sysjobs_view where name like '%cdc.%capture%'




--Always-ON
select database_id, synchronization_state_desc,synchronization_state,synchronization_health,synchronization_health_desc from sys.dm_hadr_database_replica_states


--Always-ON ----
SELECT RCS.replica_server_name,ARS.role_desc
  FROM master.sys.availability_groups_cluster AS AGC  INNER JOIN
             master.sys.dm_hadr_availability_replica_cluster_states AS RCS ON RCS.group_id = AGC.group_id INNER JOIN
             master.sys.dm_hadr_availability_replica_states AS ARS ON ARS.replica_id = RCS.replica_id    INNER JOIN
             master.sys.availability_group_listeners AS AGL ON AGL.group_id = ARS.group_id


--Replication:
SELECT  activity.start_execution_date, job.name, category.name AS Job_Category
,job.originating_server,ROW_NUMBER() OVER (ORDER BY job.name) AS RowID
FROM.msdb.dbo.sysjobs_view AS job INNER JOIN
             .msdb.dbo.sysjobactivity AS activity ON job.job_id = activity.job_id INNER JOIN
             .msdb.dbo.syscategories AS category ON job.category_id = category.category_id
WHERE     (activity.start_execution_date >= GETDATE()-02
  AND (activity.stop_execution_date IS NULL)
   AND job.category_id IN (10, 13))


***********************2. Daily Health Check*****************

----------------1. Database Status----------------

SELECT name,
DATABASEPROPERTYEX(name, 'Recovery'),
DATABASEPROPERTYEX(name, 'Status')
FROM master.dbo.sysdatabases
ORDER BY 1

------------------2. Services Info----------------

exec master.dbo.xp_servicecontrol 'QUERYSTATE', 'MSSQLServer'
exec master.dbo.xp_servicecontrol 'QUERYSTATE', 'SQLServerAgent'

------------------3. space info----------------

xp_fixeddrives

------------------4. Log file size----------------

dbcc sqlperf ('Logspace') - some database needs permission -DB access issue.

------------------5. disk space Data & log ----------------

create table #logsize
(Dbname varchar(200),dbstatus varchar(50),Recovery_Model varchar(40) default ('NA'), Log_File_Size_MB decimal(20,2)default (0),
log_Space_Used_MB decimal(20,2)default (0),log_Free_Space_MB decimal(20,2)default (0))
go

insert into #logsize(Dbname,dbstatus,recovery_model,Log_File_Size_MB,log_Space_Used_MB,log_Free_Space_MB)
exec sp_msforeachdb
'use [?];
select DB_NAME() AS DbName,
CONVERT(varchar(20),DatabasePropertyEx(''?'',''Status'')) ,
CONVERT(varchar(20),DatabasePropertyEx(''?'',''Recovery'')),
sum(size)/128.0 AS Log_File_Size_MB,
sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as log_Space_Used_MB,
SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS log_Free_Space_MB
from sysfiles where groupid=0 group by groupid
'
go

create table #dbsize
(Dbname varchar(200), file_Size_MB decimal(20,2)default (0),Space_Used_MB decimal(20,2)default (0),
Free_Space_MB decimal(20,2) default (0))
go

insert into #dbsize(Dbname,file_Size_MB,Space_Used_MB,Free_Space_MB)
exec sp_msforeachdb
'use [?];
select DB_NAME() AS DbName,

sum(size)/128.0 AS File_Size_MB,
sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as Space_Used_MB,
SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS Free_Space_MB
from sysfiles where groupid<>0 group by groupid '

go

select
d.Dbname,l.dbstatus,l.recovery_model,
(file_size_mb + log_file_size_mb) as DBsize,
d.file_Size_MB,d.Space_Used_MB,d.Free_Space_MB,
l.Log_File_Size_MB,log_Space_Used_MB,l.log_Free_Space_MB
from #dbsize d join #logsize l
on d.Dbname=l.Dbname




------------------6. Backup Job History----------------

SET NOCOUNT ON
GO
SET quoted_identifier OFF
DECLARE @dbname AS VARCHAR(80)
DECLARE @msgdb AS VARCHAR(100)
DECLARE @dbbkpname AS VARCHAR(80)
DECLARE @dypart1 AS VARCHAR(2)
DECLARE @dypart2 AS VARCHAR(3)
DECLARE @dypart3 AS VARCHAR(4)
DECLARE @currentdate AS VARCHAR(10)
DECLARE @server_name AS VARCHAR(30)
SELECT @server_name = @@servername
SELECT @dypart1 = DATEPART(dd,GETDATE())
SELECT @dypart2 = DATENAME(mm,GETDATE())
SELECT @dypart3 = DATEPART(yy,GETDATE())
SELECT @currentdate= @dypart1 + @dypart2 + @dypart3
PRINT "#####################################################################"
PRINT "# SERVERNAME : "+ @server_name + " DATE : "+ @currentdate +"#"
PRINT "#####################################################################"
PRINT "DatabaseName Full Diff TranLog"
PRINT "##########################################################################################################################################"
SELECT SUBSTRING(s.name,1,50) AS 'DATABASE Name',
b.backup_start_date AS 'Full DB Backup Status',
c.backup_start_date AS 'Differential DB Backup Status',
d.backup_start_date AS 'Transaction Log Backup Status'
FROM MASTER..sysdatabases s
LEFT OUTER JOIN msdb..backupset b
ON s.name = b.database_name
AND b.backup_start_date =
(SELECT MAX(backup_start_date)AS 'Full DB Backup Status'
FROM msdb..backupset
WHERE database_name = b.database_name
AND TYPE = 'D') -- full database backups only, not log backups
LEFT OUTER JOIN msdb..backupset c
ON s.name = c.database_name
AND c.backup_start_date =
(SELECT MAX(backup_start_date)'Differential DB Backup Status'
FROM msdb..backupset
WHERE database_name = c.database_name
AND TYPE = 'I')
LEFT OUTER JOIN msdb..backupset d
ON s.name = d.database_name
AND d.backup_start_date =
(SELECT MAX(backup_start_date)'Transaction Log Backup Status'
FROM msdb..backupset
WHERE database_name = d.database_name
AND TYPE = 'L')
WHERE s.name <>'tempdb'
ORDER BY s.name
-----

use msdb
go

-- D = Full, I = Differential and L = Log.
-- There are other types of backups too but those are the primary ones.
SELECT backupset.database_name,
MAX(CASE WHEN backupset.type = 'D' THEN backupset.backup_finish_date ELSE NULL END) AS LastFullBackup,
MAX(CASE WHEN backupset.type = 'I' THEN backupset.backup_finish_date ELSE NULL END) AS LastDifferential,
MAX(CASE WHEN backupset.type = 'L' THEN backupset.backup_finish_date ELSE NULL END) AS LastLog
FROM backupset
GROUP BY backupset.database_name
ORDER BY backupset.database_name DESC