-
Notifications
You must be signed in to change notification settings - Fork 46
/
Copy pathfirstByte (working).sql
269 lines (242 loc) · 9.71 KB
/
firstByte (working).sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
-------------------------------------------------------------
---- DBA Hound First Byte
-- Works only for SQL 2008+
-------------------------------------------------------------
DECLARE @Results TABLE
(severity varchar(15) null
,Category varchar(50) null
,MostRecent datetime2(0) null
,Issue varchar(max) null
,Details varchar(max) null
);
-------------------------------------------------------------
--- Severity 'Critical'
-------------------------------------------------------------
------------------ Disaster Recovery / Backups -------------
-- This should return a row for every database, if there are no rows then
-- backups have been failing or never done
--------------------------------------------------------------
INSERT INTO @Results(severity,Category,MostRecent,Issue,Details)
SELECT
'(1) Critical'
,Category = 'Backup Gap'
,MostRecent = MAX(b.backup_finish_date)
,'Backups out of date for ' + d.name + N' ' AS Issue
,'Database last had a full backup: ' + COALESCE(CAST(MAX(b.backup_finish_date) AS VARCHAR(25)),'never') AS Details
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.backupset b ON d.name COLLATE SQL_Latin1_General_CP1_CI_AS = b.database_name COLLATE SQL_Latin1_General_CP1_CI_AS
AND b.type = 'D'
AND b.server_name = SERVERPROPERTY('ServerName')
WHERE d.database_id <> 2
AND d.state NOT IN(1, 6, 10)
AND d.is_in_standby = 0
AND d.source_database_id IS NULL
GROUP BY d.name
HAVING MAX(b.backup_finish_date) <= DATEADD(dd, -7, sysdatetime()) OR MAX(b.backup_finish_date) IS NULL;
INSERT INTO @Results(severity,Category,Issue,Details)
SELECT DISTINCT
'(1) Critical'
,Category = 'Backup Gap'
,[Issue] = 'Database '+ d.name + N' is in FULL Recovery mode w/o a recent Log Backup'
,Details = ( 'The log file has not been backed up for at least two days, is ' + CAST(CAST((SELECT ((SUM([mf].[size]) * 8.) / 1024.) FROM sys.[master_files] AS [mf] WHERE [mf].[database_id] = d.[database_id] AND [mf].[type_desc] = 'LOG') AS DECIMAL(18,2)) AS VARCHAR) + 'MB and growing unchecked' )
FROM master.sys.databases d
WHERE d.recovery_model IN ( 1, 2 )
AND d.database_id NOT IN ( 2, 3 )
AND d.source_database_id IS NULL
AND d.state NOT IN(1, 6, 10) /* Not currently offline or restoring, like log shipping databases */
AND d.is_in_standby = 0 /* Not a log shipping target database */
AND d.source_database_id IS NULL /* Excludes database snapshots */
AND NOT EXISTS ( SELECT * FROM msdb.dbo.backupset b
WHERE d.name COLLATE SQL_Latin1_General_CP1_CI_AS = b.database_name COLLATE SQL_Latin1_General_CP1_CI_AS
AND b.type = 'L'
AND b.backup_finish_date >= DATEADD(dd,-2, sysdatetime()) );
insert into @Results(severity,Category,Issue,Details)
SELECT
'(1) Critical' ,
'Database Performance' ,
'Auto Closed Enabled on a database',
( 'Database [' + [name]
+ '] has auto-close enabled. This setting can dramatically decrease performance.' ) AS Details
FROM sys.databases d
WHERE is_auto_close_on = 1
union All
SELECT
'(1) Critical',
'Database Performance' ,
'Auto-Shrink Enabled on a database' ,
( 'Database [' + [name]
+ '] has auto-shrink enabled. This setting can dramatically decrease performance.' ) AS Details
FROM sys.databases
WHERE is_auto_shrink_on = 1
if (select value from sys.configurations c where c.name='optimize for ad hoc workloads') <> 1
BEGIN
INSERT INTO @Results(severity,Category,Issue,Details)
SELECT
'(2) High'
,'Server Performance'
,'Optimze for Ad Hoc Workload is not enabled'
,'SQL Server typicall performas better when ' + CHAR(39) + 'Optimze for Ad Hoc Workloads' + CHAR(39) +' is enabled'
END
if(SELECT COUNT(*)
FROM tempdb.sys.database_files
WHERE type_desc = 'ROWS') = 1
BEGIN
INSERT INTO @Results(Severity,Category,Issue,Details)
SELECT
'(2) High'
,'Server Performance'
,'TempDB has only 1 data file'
,'SQL Server typically performs better when the TempDB has more than one Data file, perhaps up to the same # of data files as logical processors (' + cast(cpu_count as varchar(3)) + '), up to 8.'
FROM sys.dm_os_sys_info
END
IF ( SELECT COUNT (distinct [size])
FROM tempdb.sys.database_files
WHERE type_desc = 'ROWS'
) <> 1
BEGIN
INSERT INTO @Results(severity,Category,Issue,Details)
SELECT
'(2) High'
,'Server Performance'
,'TempDB File Size'
,'TempDB data files are not configured with the same size.'
END
--------------- MAX Memory Setting ------------------
DECLARE @MaxMemorySetting bigint
DECLARE @PhysicalMemory bigint
SELECT @PhysicalMemory = (SELECT m.total_physical_memory_kb/1024 from sys.dm_os_sys_memory m)
SELECT @MaxMemorySetting=(SELECT CAST(value_in_use as BIGINT) FROM sys.configurations WHERE name = 'max server memory (MB)')
if @MaxMemorySetting=2147483647
insert into @Results(severity,Category,Issue,Details)
select
'(2) High'
,'Server Performance'
,'MAX Memory Setting is uncapped (default) and should be changed'
,'Based on available physical memory of ' + cast(@PhysicalMemory as varchar(20)) + ' MB'
+ ' this server should have a maximum setting of ' + cast(cast(@PhysicalMemory*.90 as decimal(6,0)) as varchar(10)) + ' MB'
-------------------- SQL Server Error Log ---------------------------------------------
DECLARE @LogError TABLE
(LogDate datetime2(0),ProcessInfo varchar(50),Details varchar(max), severity varchar(1000) null, Category varchar(1000) null)
INSERT INTO @LogError (LogDate, ProcessInfo, Details)
exec sp_readerrorlog 0,1,'Severity'
INSERT INTO @LogError (LogDate, ProcessInfo, Details)
exec sp_readerrorlog 0,1,'Error'
DELETE A
FROM @LogError A
where a.Details LIKE '%CHECKDB%'
DELETE A
FROM @LogError A
where a.Details like '%registry%'
DELETE A
FROM @LogError A
where a.Details LIKE '%Logging SQL Server messages in file%'
/*
select * from sys.messages m
inner join sys.syslanguages l on l.msglangid= m.language_id
and l.langid = @@LANGID
WHERE message_id = 18456
*/
UPDATE @LogError
SET Severity = CASE WHEN (ProcessInfo like '%Backup%'
or Details like '%Backup%'
or Details like '%18210%'
or Details like '%18204%'
or Details like '%3041%' )
and LogDate >= dateadd(day, -7, getdate())
THEN '(2) High'
WHEN (ProcessInfo like '%Backup%'
or Details like '%Backup%'
or Details like '%18210%'
or Details like '%18204%'
or Details like '%3041%' )
and LogDate < dateadd(day, -7, getdate())
THEN '(3) Medium'
WHEN ProcessInfo like '%Logon%'
or Details like '%Severity: 20%'
THEN '(4) Low'
WHEN Details like '%Severity: 16%'
THEN '(3) Medium'
ELSE '(2) High'
END
, Category = CASE WHEN (Details like '%Backup%'
or Details like '%18210%'
or Details like '%18204%'
or Details like '%3041%')
and LogDate >= dateadd(day, -7, getdate())
THEN 'Recent Backup Failure'
WHEN
(Details like '%Backup%'
or Details like '%18210%'
or Details like '%18204%'
or Details like '%3041%')
and LogDate < dateadd(day, -7, getdate())
THEN 'Past Backup Failure'
WHEN Details like '%Logon%' or ProcessInfo = 'Logon'
THEN 'Logon Failure'
ELSE 'SQL Error Log'
END
, ProcessInfo = CASE WHEN ProcessInfo like 'spid%' THEN '' ELSE ProcessInfo END --filter out spids which will create dup entries
, Details = Left(Details, CASE WHEN (CHARINDEX(':\', Details)) = 0 THEN 200 ELSE (CHARINDEX(':\', Details)) END)
INSERT INTO @Results(severity,Category,MostRecent,Issue,Details)
select
Severity = Severity
, Category = Category
, MostRecent = convert(varchar(30), MAX(LogDate))
, Issue = 'Errors in the SQL Server Log'
, Details = cast(count(Details) as varchar(10)) + ' errors of ' + ProcessInfo + Details + '... '
from @LogError r
GROUP BY Severity, Category, ProcessInfo, Details
-------------------- Page Life Expectancy -----------------------------------------
DECLARE @PLE int
SELECT @PLE = (
SELECT
cntr_value AS [Page Life Expectancy]
FROM sys.dm_os_performance_counters WITH (NOLOCK)
WHERE [object_name] LIKE N'%Buffer Node%' -- Handles named instances
AND counter_name = N'Page life expectancy')
if @PLE < 300
BEGIN
INSERT INTO @Results(severity,Category,MostRecent,Issue,Details)
SELECT
'(3) Medium'
,'Server Performance'
, SYSDATETIME()
,'Page Life Expectancy (PLE)'
,'The current PLE is ' + cast(@PLE as varchar(10)) + ' which is below the "standard" of 300. This could be an indication of low memory'
END
----------------------------------------------------------------------
if exists (select * from msdb.dbo.suspect_pages )
begin
INSERT INTO @Results(severity,Category,Issue,Details)
select
'(1) Critical'
,'Database Performance'
,'There appears to be a corrupted page in ' + db_name(sp.database_id)
,'Please execute DBCC CHECKDB to determine the problem and possible result'
FROM msdb.dbo.suspect_pages sp
INNER JOIN master.sys.databases db ON sp.database_id = db.database_id
WHERE sp.last_update_date >= DATEADD(dd, -30, sysdatetime())
end
-------
if (
SELECT
COUNT(*)
FROM msdb.dbo.sysalerts
WHERE severity >= 19 and severity <= 25) < 6 --OK if 20 is not in place
BEGIN
INSERT INTO @Results(Severity,Category,Issue,Details)
SELECT
'(3) Medium'
,'Monitoring'
,'Some SQL Alerts are missing'
,'Please review the SQL Agent Alerts. Some high severity errors are not sending alerts.'
END
if (select count(*) from @Results) > 0
BEGIN
select * FROM @Results r ORDER BY severity asc, MostRecent asc, Issue asc, Details asc
END
ELSE
BEGIN
SELECT 'There are no critical findings with this SQL Server' as [Congratulations!]
END
-----------------------------------------------