DBA

SQL server daily health check DBA script

Advertisements

The following script will help to do a quick health check of SQL estate that you manage. To check all SQL server health check, you need to create Registered or Centralized server.

I usually automated everything using my AutoMon script package that will trigger an email from CMS server. But, some small clients and where I do not have long term scope, I use Registered Server and will run various script than the health check like – DB list, Login list & agent job list etc.

 

-- 1) Disk Free Space Check

XP_FIXEDDRIVES

-- 2) DB Online Status (Including SQL 2000)

SELECT NAME,DATABASEPROPERTYEX (NAME,'STATUS') AS STATUS, GETDATE() as [Today_Date]
FROM MASTER.DBO.SYSDATABASES WHERE DATABASEPROPERTYEX (NAME,'STATUS') NOT IN ('ONLINE')
go
WITH DB_STATUS AS
(
SELECT NAME,DB_STATE = CASE
WHEN STATUS = 67584 THEN 'Restricted User'
WHEN STATUS = 69632 THEN 'Single User'
WHEN STATUS = 98560 THEN 'Emergency Mode'
WHEN STATUS = 128 THEN 'Recovering'
ELSE 'Unchecked' END, GETDATE() as [Today_Date]
FROM MASTER.DBO.SYSDATABASES
)
SELECT * FROM DB_STATUS WHERE DB_STATE NOT IN ('Unchecked')

-- 3) Failled Job check

DECLARE @Date CHAR(8);
SET @Date= CONVERT(CHAR(8), DATEADD(HH,-24,GETDATE()),112)

SELECT  
    @@SERVERNAME AS [Server Name],  
    SUBSTRING(T2.name,1,140) AS [SQL Job Name],
    T1.step_name AS [Step Name],
    --msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',
    COUNT(*) AS TotalFailures,
    CAST(MIN(CONVERT(DATETIME,CAST(run_date AS CHAR(8)),101)) AS CHAR(11)) AS [MinFailure Date],
    CAST(MAX(CONVERT(DATETIME,CAST(run_date AS CHAR(8)),101)) AS CHAR(11)) AS [MaxFailure Date],
    GETDATE() as [Today_Date],
    --MAX(msdb.dbo.agent_datetime(T1.run_date, T1.run_time)) AS 'RunDateTime',
    MIN(T1.run_duration) StepDuration,
    CASE MIN(T1.run_status)
    WHEN 0 THEN 'Failed'
    WHEN 1 THEN 'Succeeded'
    WHEN 2 THEN 'Retry'
    WHEN 3 THEN 'Cancelled'
    WHEN 4 THEN 'In Progress'
    END AS ExecutionStatus,
    MAX(T1.message) AS [Error Message]
FROM
msdb..sysjobhistory T1 INNER JOIN msdb..sysjobs T2 ON T1.job_id = T2.job_id
WHERE
T1.run_status NOT IN (1,2,4)
AND T1.step_id != 0
AND run_date >= @Date
--AND step_name NOT LIKE '%%'
GROUP BY 
  T1.step_name,
  T2.name

-- 4) Always ON check

SELECT name, role_desc, operational_state_desc, recovery_health_desc,
synchronization_health_desc, Getdate() as [Today_Date]
FROM SYS.DM_HADR_AVAILABILITY_REPLICA_CLUSTER_STATES A 
JOIN SYS.DM_HADR_AVAILABILITY_REPLICA_STATES B ON A.group_id=B.group_id
JOIN SYS.AVAILABILITY_GROUPS_CLUSTER C ON B.group_id =C.group_id
WHERE B.synchronization_health_desc NOT IN ('HEALTHY')

-- Logshipping & DB Mirroring

-- 5) Long running process
-- Long running process based on last login time
select db_name(dbid) as DBName , Cmd, Status as [Run_Status],datediff(minute,login_time,getdate()) as [Runtime_minute]
,spid,blocked,lastwaittype,waittime,hostname,program_name,loginame,login_time from sysprocesses
where spid > 51 and status not in ('background','sleeping')
and datediff(minute,login_time,getdate()) >5 -- more than 5 minutes
--and cmd not in ('waitfor','awaiting command')
--and not in dbid (1,2,3,4)
group by dbid,Cmd, Status ,login_time,spid,blocked,lastwaittype,waittime,hostname,program_name,loginame,login_time

 

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

Leave a Reply

Your email address will not be published. Required fields are marked *

3 + 6 =