--Check logs for errors/corruption
--CRITICAL ERRORS ON SQL LOGS
EXEC SP_Readerrorlog 0,1,'A significant part of sql server memory has been paged out';
EXEC SP_ReadErrorlog 0,1,'SQL Server has encountered';
EXEC SP_ReadErrorlog 0,1,'SQL Server is starting at high priority base';
EXEC SP_Readerrorlog 0,1,'Deadlock encountered';
EXEC SP_Readerrorlog 0,1,'deadlock-list';
EXEC SP_Readerrorlog 0,1,'fail';
EXEC SP_Readerrorlog 0,1,'inaccessible';
EXEC sp_readerrorlog 0,1,'Mirroring';
EXEC sp_readerrorlog 0,1,'Virtual Log Files';
EXEC sp_readerrorlog 0,1,"error"
--Validate server authentication mode is mixed
select @@servername,
case SERVERPROPERTY('IsIntegratedSecurityOnly')
when 1 then 'Windows Authentication'
when 0 then 'Windows and SQL Server Authentication'
end as AuthMode
--Other health checks
--PACKET ERRORS
SELECT @@SERVERNAME AS 'SERVER', @@PACK_SENT AS 'Packets Sent',@@PACKET_ERRORS AS 'Packet Errors';
--BLOCKED PROCESSES
select @@SERVERNAME as InstanceName,Count(*) as NoBlocked from sys.sysprocesses
where blocked <> 0
-- DISK IO LATENCY
select * from sys.dm_io_pending_io_requests where io_pending_ms_ticks > 10;
--MEMORY OOM MESSAGES (RING_BUFFERS)
select record
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_OOM'
--Validate all databases are online and accessible
select
@@servername, name, user_access_desc, is_read_only,
state_desc, recovery_model_desc, page_verify_option_desc
from master.sys.databases
--Validate that the database instances are connected
select @@servername, * from sys.dm_db_mirroring_connections
--Validate that mirroring sessions are synchronized, check timeout value
select
@@servername, db_name(database_id), database_id, mirroring_state_desc, mirroring_role_desc,
mirroring_safety_level_desc, mirroring_connection_timeout
from sys.database_mirroring where mirroring_guid is not null
--To Find failed Jobs fort the past 24 hours
SELECT DISTINCT T1.server AS [Server Name],
T1.step_id AS [Step_id],
T1.step_name AS [Step Name],
SUBSTRING(T2.name,1,140) AS [SQL Job Name],
msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',
CAST(CONVERT(DATETIME,CAST(run_date AS CHAR(8)),101) AS CHAR(11)) AS [Failure Date],
msdb.dbo.agent_datetime(T1.run_date, T1.run_time) AS 'RunDateTime',
T1.run_duration StepDuration,
CASE 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,
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 >= CONVERT(CHAR(8), (SELECT DATEADD (DAY,(-1), GETDATE())), 112)
--WAIT STATS
--LOOK FOR TOP WAITS
SELECT TOP 10 @@SERVERNAME as Instance,
[Wait type] = wait_type,
[Wait time (s)] = wait_time_ms / 1000,
[% waiting] = CONVERT(DECIMAL(12,2), wait_time_ms * 100.0
/ SUM(wait_time_ms) OVER())
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%'
ORDER BY wait_time_ms DESC;
DBCC SQLPERF('sys.dm_os_wait_stats', clear)
--VLF COUNT
--variables to hold each 'iteration'
declare @query varchar(100)
declare @dbname sysname
declare @vlfs int
--table variable used to 'loop' over databases
declare @databases table (dbname sysname)
insert into @databases
--only choose online databases
select name from sys.databases where state = 0
--table variable to hold results
declare @vlfcounts table
(dbname sysname,
vlfcount int)
--table variable to capture DBCC loginfo output
--changes in the output of DBCC loginfo from SQL2012 mean we have to determine the version
declare @MajorVersion tinyint
set @MajorVersion = LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))-1)
if @MajorVersion < 11 -- pre-SQL2012
begin
declare @dbccloginfo table
(
fileid tinyint,
file_size bigint,
start_offset bigint,
fseqno int,
[status] tinyint,
parity tinyint,
create_lsn numeric(25,0)
)
while exists(select top 1 dbname from @databases)
begin
set @dbname = (select top 1 dbname from @databases)
set @query = 'dbcc loginfo (' + '''' + @dbname + ''') '
insert into @dbccloginfo
exec (@query)
set @vlfs = @@rowcount
insert @vlfcounts
values(@dbname, @vlfs)
delete from @databases where dbname = @dbname
end --while
end
else
begin
declare @dbccloginfo2012 table
(
RecoveryUnitId int,
fileid tinyint,
file_size bigint,
start_offset bigint,
fseqno int,
[status] tinyint,
parity tinyint,
create_lsn numeric(25,0)
)
while exists(select top 1 dbname from @databases)
begin
set @dbname = (select top 1 dbname from @databases)
set @query = 'dbcc loginfo (' + '''' + @dbname + ''') '
insert into @dbccloginfo2012
exec (@query)
set @vlfs = @@rowcount
insert @vlfcounts
values(@dbname, @vlfs)
delete from @databases where dbname = @dbname
end --while
end
--output the full list
select dbname, vlfcount
from @vlfcounts
order by dbname
