The below query will help us to know the SQL server health report. if we want multiple servers. we can create a job this in CMS.
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
EXEC MASTER..xp_fixeddrives
SELECT DISTINCT dovs.logical_volume_name AS LogicalName,
dovs.volume_mount_point AS Drive,
CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceInMB
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
ORDER BY FreeSpaceInMB ASC
GO
------------------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
DROP TABLE #dbsize
DROP TABLE #logsize
No comments:
Post a Comment