-
Notifications
You must be signed in to change notification settings - Fork 1
/
sp_blitz.txt
4379 lines (4100 loc) · 182 KB
/
sp_blitz.txt
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
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
USE [master];
GO
IF OBJECT_ID('dbo.sp_Blitz') IS NOT NULL
DROP PROC dbo.sp_Blitz;
GO
CREATE PROCEDURE [dbo].[sp_Blitz]
@CheckUserDatabaseObjects TINYINT = 1 ,
@CheckProcedureCache TINYINT = 0 ,
@OutputType VARCHAR(20) = 'TABLE' ,
@OutputProcedureCache TINYINT = 0 ,
@CheckProcedureCacheFilter VARCHAR(10) = NULL ,
@CheckServerInfo TINYINT = 0 ,
@SkipChecksServer NVARCHAR(256) = NULL ,
@SkipChecksDatabase NVARCHAR(256) = NULL ,
@SkipChecksSchema NVARCHAR(256) = NULL ,
@SkipChecksTable NVARCHAR(256) = NULL ,
@IgnorePrioritiesBelow INT = NULL ,
@IgnorePrioritiesAbove INT = NULL ,
@OutputDatabaseName NVARCHAR(128) = NULL ,
@OutputSchemaName NVARCHAR(256) = NULL ,
@OutputTableName NVARCHAR(256) = NULL ,
@OutputXMLasNVARCHAR TINYINT = 0 ,
@EmailRecipients VARCHAR(MAX) = NULL ,
@EmailProfile sysname = NULL ,
@Help TINYINT = 0 ,
@Version INT = NULL OUTPUT,
@VersionDate DATETIME = NULL OUTPUT
AS
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT @Version = 33, @VersionDate = '20140120'
IF @Help = 1 PRINT '
/*
sp_Blitz (TM) v33 - Jan 20, 2014
(C) 2014, Brent Ozar Unlimited.
See http://BrentOzar.com/go/eula for the End User Licensing Agreement.
To learn more, visit http://www.BrentOzar.com/blitz where you can download
new versions for free, watch training videos on how it works, get more info on
the findings, and more. To contribute code and see your name in the change
log, email your improvements & checks to [email protected].
Known limitations of this version:
- No support for SQL Server 2000 or compatibility mode 80.
- If a database name has a question mark in it, some tests will fail. Gotta
love that unsupported sp_MSforeachdb.
- If you have offline databases, sp_Blitz fails the first time you run it,
but does work the second time. (Hoo, boy, this will be fun to debug.)
Unknown limitations of this version:
- None. (If we knew them, they would be known. Duh.)
Changes in v33 - January 20, 2014
- Bob Klimes fixed a bug that Russell Hart introduced in v32, hahaha. Check
59 was false-alarming on Agent jobs that actually had notifications.
Changes in v32 - January 19, 2014
- Russell Hart fixed a bug in check 59 (Agent jobs without notifications).
- Added @EmailRecipients and @EmailProfile parameters to send the results via
Database Mail. Assumes that database mail is already configured correctly.
Only sends the main results table, and it will not work well if you also
try to use @CheckProcedureCache. Execution plans will not render in email.
- Fixed a bug in checks 108 and 109 that showed poison waits even if they had
0ms of wait time since restart.
- Removed check 120 which warned about backups not using WITH CHECKSUM. We
fell out of love with WITH CHECKSUM - turns out nobody uses it.
- Added check 121 - Poison Wait Detected: Serializable Locking - looking for
waits with %LCK%R%. Happens when a query uses a combination of lock hints
that make the query serializable.
- Added check 122 - User-Created Statistics In Place. There is nothing wrong
with creating your own statistics, but it can cause an IO explosion when
statistics are updated.
- Added check 123 - Multiple Agent Jobs Starting Simultaneously. Ran into an
issue where dozens of jobs started at the exact same time every hour.
Changes in v31 - December 1, 2013
- Dick Baker, Ambrosetti Ltd (UK):
- Fixed typos in checks 107-109 that looked for the wrong CheckID when
skipping checks, plus improved performance while he was in there.
- Improved check 106 (default trace file) so that it will not error out
if the user does not have permissions on sys.traces.
- Christoph Muller-Spengler @cms4j added check 118 looking at the top queries
in the plan cache for key lookups.
- Philip Dietrich added check 119 for TDE certificates that have not been
backed up recently.
- Ricky Lively added @Help to print inline help. I love his approach to it.
- Added check 120 looking for databases that have not had a full backup using
the WITH CHECKSUM option in the last 30 days.
For prior changes, see http://www.BrentOzar.com/blitz/changelog/
Parameter explanations:
@CheckUserDatabaseObjects 1=review user databases for triggers, heaps, etc. Takes more time for more databases and objects.
@CheckServerInfo 1=show server info like CPUs, memory, virtualization
@CheckProcedureCache 1=top 20-50 resource-intensive cache plans and analyze them for common performance issues.
@OutputProcedureCache 1=output the top 20-50 resource-intensive plans even if they did not trigger an alarm
@CheckProcedureCacheFilter ''CPU'' | ''Reads'' | ''Duration'' | ''ExecCount''
@OutputType ''TABLE''=table | ''COUNT''=row with number found | ''SCHEMA''=version and field list
@IgnorePrioritiesBelow 100=ignore priorities below 100
@IgnorePrioritiesAbove 100=ignore priorities above 100
For the rest of the parameters, see http://www.brentozar.com/blitz/documentation for details.
*/'
ELSE IF @OutputType = 'SCHEMA'
BEGIN
SELECT @Version AS Version,
FieldList = '[Priority] TINYINT, [FindingsGroup] VARCHAR(50), [Finding] VARCHAR(200), [DatabaseName] NVARCHAR(128), [URL] VARCHAR(200), [Details] NVARCHAR(4000), [QueryPlan] NVARCHAR(MAX), [QueryPlanFiltered] NVARCHAR(MAX), [CheckID] INT'
END
ELSE /* IF @OutputType = 'SCHEMA' */
BEGIN
/*
We start by creating #BlitzResults. It's a temp table that will store all of
the results from our checks. Throughout the rest of this stored procedure,
we're running a series of checks looking for dangerous things inside the SQL
Server. When we find a problem, we insert rows into #BlitzResults. At the
end, we return these results to the end user.
#BlitzResults has a CheckID field, but there's no Check table. As we do
checks, we insert data into this table, and we manually put in the CheckID.
We (Brent Ozar Unlimited) maintain a list of the checks by ID#. You can
download that from http://www.BrentOzar.com/blitz/documentation/ - you'll
see why it can help shortly.
*/
DECLARE @StringToExecute NVARCHAR(4000)
,@curr_tracefilename NVARCHAR(500)
,@base_tracefilename NVARCHAR(500)
,@indx int
,@query_result_separator CHAR(1)
,@EmailSubject NVARCHAR(255)
,@EmailBody NVARCHAR(MAX)
,@EmailAttachmentFilename NVARCHAR(255);
IF OBJECT_ID('tempdb..#BlitzResults') IS NOT NULL
DROP TABLE #BlitzResults;
CREATE TABLE #BlitzResults
(
ID INT IDENTITY(1, 1) ,
CheckID INT ,
DatabaseName NVARCHAR(128) ,
Priority TINYINT ,
FindingsGroup VARCHAR(50) ,
Finding VARCHAR(200) ,
URL VARCHAR(200) ,
Details NVARCHAR(4000) ,
QueryPlan [XML] NULL ,
QueryPlanFiltered [NVARCHAR](MAX) NULL
);
/*
You can build your own table with a list of checks to skip. For example, you
might have some databases that you don't care about, or some checks you don't
want to run. Then, when you run sp_Blitz, you can specify these parameters:
@SkipChecksDatabase = 'DBAtools',
@SkipChecksSchema = 'dbo',
@SkipChecksTable = 'BlitzChecksToSkip'
Pass in the database, schema, and table that contains the list of checks you
want to skip. This part of the code checks those parameters, gets the list,
and then saves those in a temp table. As we run each check, we'll see if we
need to skip it.
Really anal-retentive users will note that the @SkipChecksServer parameter is
not used. YET. We added that parameter in so that we could avoid changing the
stored proc's surface area (interface) later.
*/
IF OBJECT_ID('tempdb..#SkipChecks') IS NOT NULL
DROP TABLE #SkipChecks;
CREATE TABLE #SkipChecks
(
DatabaseName NVARCHAR(128) ,
CheckID INT ,
ServerName NVARCHAR(128)
);
CREATE CLUSTERED INDEX IX_CheckID_DatabaseName ON #SkipChecks(CheckID, DatabaseName);
IF @SkipChecksTable IS NOT NULL
AND @SkipChecksSchema IS NOT NULL
AND @SkipChecksDatabase IS NOT NULL
BEGIN
SET @StringToExecute = 'INSERT INTO #SkipChecks(DatabaseName, CheckID, ServerName )
SELECT DISTINCT DatabaseName, CheckID, ServerName
FROM ' + QUOTENAME(@SkipChecksDatabase) + '.' + QUOTENAME(@SkipChecksSchema) + '.' + QUOTENAME(@SkipChecksTable)
+ ' WHERE ServerName IS NULL OR ServerName = SERVERPROPERTY(''ServerName'');'
EXEC(@StringToExecute)
END
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 106 )
AND (select convert(int,value_in_use) from sys.configurations where name = 'default trace enabled' ) = 1
BEGIN
select @curr_tracefilename = [path] from sys.traces where is_default = 1 ;
set @curr_tracefilename = reverse(@curr_tracefilename);
select @indx = patindex('%\%', @curr_tracefilename) ;
set @curr_tracefilename = reverse(@curr_tracefilename) ;
set @base_tracefilename = left( @curr_tracefilename,len(@curr_tracefilename) - @indx) + '\log.trc' ;
END
/*
That's the end of the SkipChecks stuff.
The next several tables are used by various checks later.
*/
IF OBJECT_ID('tempdb..#ConfigurationDefaults') IS NOT NULL
DROP TABLE #ConfigurationDefaults;
CREATE TABLE #ConfigurationDefaults
(
name NVARCHAR(128) ,
DefaultValue BIGINT,
CheckID INT
);
IF OBJECT_ID('tempdb..#DBCCs') IS NOT NULL
DROP TABLE #DBCCs;
CREATE TABLE #DBCCs
(
ID INT IDENTITY(1, 1)
PRIMARY KEY ,
ParentObject VARCHAR(255) ,
Object VARCHAR(255) ,
Field VARCHAR(255) ,
Value VARCHAR(255) ,
DbName NVARCHAR(128) NULL
)
IF OBJECT_ID('tempdb..#LogInfo2012') IS NOT NULL
DROP TABLE #LogInfo2012;
CREATE TABLE #LogInfo2012
(
recoveryunitid INT ,
FileID SMALLINT ,
FileSize BIGINT ,
StartOffset BIGINT ,
FSeqNo BIGINT ,
[Status] TINYINT ,
Parity TINYINT ,
CreateLSN NUMERIC(38)
);
IF OBJECT_ID('tempdb..#LogInfo') IS NOT NULL
DROP TABLE #LogInfo;
CREATE TABLE #LogInfo
(
FileID SMALLINT ,
FileSize BIGINT ,
StartOffset BIGINT ,
FSeqNo BIGINT ,
[Status] TINYINT ,
Parity TINYINT ,
CreateLSN NUMERIC(38)
);
IF OBJECT_ID('tempdb..#partdb') IS NOT NULL
DROP TABLE #partdb;
CREATE TABLE #partdb
(
dbname NVARCHAR(128) ,
objectname NVARCHAR(200) ,
type_desc NVARCHAR(128)
)
IF OBJECT_ID('tempdb..#TraceStatus') IS NOT NULL
DROP TABLE #TraceStatus;
CREATE TABLE #TraceStatus
(
TraceFlag VARCHAR(10) ,
status BIT ,
Global BIT ,
Session BIT
);
IF OBJECT_ID('tempdb..#driveInfo') IS NOT NULL
DROP TABLE #driveInfo;
CREATE TABLE #driveInfo
(
drive NVARCHAR ,
SIZE DECIMAL(18, 2)
)
IF OBJECT_ID('tempdb..#dm_exec_query_stats') IS NOT NULL
DROP TABLE #dm_exec_query_stats;
CREATE TABLE #dm_exec_query_stats
(
[id] [int] NOT NULL
IDENTITY(1, 1) ,
[sql_handle] [varbinary](64) NOT NULL ,
[statement_start_offset] [int] NOT NULL ,
[statement_end_offset] [int] NOT NULL ,
[plan_generation_num] [bigint] NOT NULL ,
[plan_handle] [varbinary](64) NOT NULL ,
[creation_time] [datetime] NOT NULL ,
[last_execution_time] [datetime] NOT NULL ,
[execution_count] [bigint] NOT NULL ,
[total_worker_time] [bigint] NOT NULL ,
[last_worker_time] [bigint] NOT NULL ,
[min_worker_time] [bigint] NOT NULL ,
[max_worker_time] [bigint] NOT NULL ,
[total_physical_reads] [bigint] NOT NULL ,
[last_physical_reads] [bigint] NOT NULL ,
[min_physical_reads] [bigint] NOT NULL ,
[max_physical_reads] [bigint] NOT NULL ,
[total_logical_writes] [bigint] NOT NULL ,
[last_logical_writes] [bigint] NOT NULL ,
[min_logical_writes] [bigint] NOT NULL ,
[max_logical_writes] [bigint] NOT NULL ,
[total_logical_reads] [bigint] NOT NULL ,
[last_logical_reads] [bigint] NOT NULL ,
[min_logical_reads] [bigint] NOT NULL ,
[max_logical_reads] [bigint] NOT NULL ,
[total_clr_time] [bigint] NOT NULL ,
[last_clr_time] [bigint] NOT NULL ,
[min_clr_time] [bigint] NOT NULL ,
[max_clr_time] [bigint] NOT NULL ,
[total_elapsed_time] [bigint] NOT NULL ,
[last_elapsed_time] [bigint] NOT NULL ,
[min_elapsed_time] [bigint] NOT NULL ,
[max_elapsed_time] [bigint] NOT NULL ,
[query_hash] [binary](8) NULL ,
[query_plan_hash] [binary](8) NULL ,
[query_plan] [xml] NULL ,
[query_plan_filtered] [nvarchar](MAX) NULL ,
[text] [nvarchar](MAX) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[text_filtered] [nvarchar](MAX) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
)
/* If we're outputting CSV, don't bother checking the plan cache because we cannot export plans. */
IF @OutputType = 'CSV'
SET @CheckProcedureCache = 0;
/* Sanitize our inputs */
SELECT
@OutputDatabaseName = QUOTENAME(@OutputDatabaseName),
@OutputSchemaName = QUOTENAME(@OutputSchemaName),
@OutputTableName = QUOTENAME(@OutputTableName)
/*
Whew! we're finally done with the setup, and we can start doing checks.
First, let's make sure we're actually supposed to do checks on this server.
The user could have passed in a SkipChecks table that specified to skip ALL
checks on this server, so let's check for that:
*/
IF ( ( SERVERPROPERTY('ServerName') NOT IN ( SELECT ServerName
FROM #SkipChecks
WHERE DatabaseName IS NULL
AND CheckID IS NULL ) )
OR ( @SkipChecksTable IS NULL )
)
BEGIN
/*
Our very first check! We'll put more comments in this one just to
explain exactly how it works. First, we check to see if we're
supposed to skip CheckID 1 (that's the check we're working on.)
*/
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 1 )
BEGIN
/*
Below, we check master.sys.databases looking for databases
that haven't had a backup in the last week. If we find any,
we insert them into #BlitzResults, the temp table that
tracks our server's problems. Note that if the check does
NOT find any problems, we don't save that. We're only
saving the problems, not the successful checks.
*/
INSERT INTO #BlitzResults
( CheckID ,
DatabaseName ,
Priority ,
FindingsGroup ,
Finding ,
URL ,
Details
)
SELECT 1 AS CheckID ,
d.[name] AS DatabaseName ,
1 AS Priority ,
'Backup' AS FindingsGroup ,
'Backups Not Performed Recently' AS Finding ,
'http://BrentOzar.com/go/nobak' AS URL ,
'Database ' + d.Name + ' last backed up: '
+ CAST(COALESCE(MAX(b.backup_finish_date),
' never ') AS VARCHAR(200)) 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') /*Backupset ran on current server */
WHERE d.database_id <> 2 /* Bonus points if you know what that means */
AND d.state <> 1 /* Not currently 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 d.name NOT IN ( SELECT DISTINCT
DatabaseName
FROM #SkipChecks
WHERE CheckID IS NULL )
/*
The above NOT IN filters out the databases we're not supposed to check.
*/
GROUP BY d.name
HAVING MAX(b.backup_finish_date) <= DATEADD(dd,
-7, GETDATE());
/*
And there you have it. The rest of this stored procedure works the same
way: it asks:
- Should I skip this check?
- If not, do I find problems?
- Insert the results into #BlitzResults
This particular check is just a little bit fancy - it also has a second
query below that checks for databases that have NEVER been backed up.
We use CheckID #1 for both of these just because they represent the same
problem - a database that needs a backup.
*/
INSERT INTO #BlitzResults
( CheckID ,
DatabaseName ,
Priority ,
FindingsGroup ,
Finding ,
URL ,
Details
)
SELECT 1 AS CheckID ,
d.name AS DatabaseName ,
1 AS Priority ,
'Backup' AS FindingsGroup ,
'Backups Not Performed Recently' AS Finding ,
'http://BrentOzar.com/go/nobak' AS URL ,
( 'Database ' + d.Name
+ ' never backed up.' ) AS Details
FROM master.sys.databases d
WHERE d.database_id <> 2 /* Bonus points if you know what that means */
AND d.state <> 1 /* Not currently 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 d.name NOT IN ( SELECT DISTINCT
DatabaseName
FROM #SkipChecks
WHERE CheckID IS NULL )
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 = 'D'
AND b.server_name = SERVERPROPERTY('ServerName') /*Backupset ran on current server */)
END
/*
And that's the end of CheckID #1.
CheckID #2 is a little simpler because it only involves one query, and it's
more typical for queries that people contribute. But keep reading, because
the next check gets more complex again.
*/
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 2 )
BEGIN
INSERT INTO #BlitzResults
( CheckID ,
DatabaseName ,
Priority ,
FindingsGroup ,
Finding ,
URL ,
Details
)
SELECT DISTINCT
2 AS CheckID ,
d.name AS DatabaseName ,
1 AS Priority ,
'Backup' AS FindingsGroup ,
'Full Recovery Mode w/o Log Backups' AS Finding ,
'http://BrentOzar.com/go/biglogs' AS URL ,
( 'Database ' + ( d.Name COLLATE database_default )
+ ' is in ' + d.recovery_model_desc
+ ' recovery mode but has not had a log backup in the last week.' ) AS Details
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 <> 1 /* Not currently 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 d.name NOT IN ( SELECT DISTINCT
DatabaseName
FROM #SkipChecks
WHERE CheckID IS NULL )
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,
-7, GETDATE()) );
END
/*
Next up, we've got CheckID 8. (These don't have to go in order.) This one
won't work on SQL Server 2005 because it relies on a new DMV that didn't
exist prior to SQL Server 2008. This means we have to check the SQL Server
version first, then build a dynamic string with the query we want to run:
*/
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 8 )
BEGIN
IF @@VERSION NOT LIKE '%Microsoft SQL Server 2000%'
AND @@VERSION NOT LIKE '%Microsoft SQL Server 2005%'
BEGIN
SET @StringToExecute = 'INSERT INTO #BlitzResults
(CheckID, Priority,
FindingsGroup,
Finding, URL,
Details)
SELECT 8 AS CheckID,
150 AS Priority,
''Security'' AS FindingsGroup,
''Server Audits Running'' AS Finding,
''http://BrentOzar.com/go/audits'' AS URL,
(''SQL Server built-in audit functionality is being used by server audit: '' + [name]) AS Details FROM sys.dm_server_audit_status'
EXECUTE(@StringToExecute)
END;
END
/*
But what if you need to run a query in every individual database?
Check out CheckID 99 below. Yes, it uses sp_MSforeachdb, and no,
we're not happy about that. sp_MSforeachdb is known to have a lot
of issues, like skipping databases sometimes. However, this is the
only built-in option that we have. If you're writing your own code
for database maintenance, consider Aaron Bertrand's alternative:
http://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/
We don't include that as part of sp_Blitz, of course, because
copying and distributing copyrighted code from others without their
written permission isn't a good idea.
*/
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 99 )
BEGIN
EXEC dbo.sp_MSforeachdb 'USE [?]; IF EXISTS (SELECT * FROM sys.tables WITH (NOLOCK) WHERE name = ''sysmergepublications'' ) IF EXISTS ( SELECT * FROM sysmergepublications WITH (NOLOCK) WHERE retention = 0) INSERT INTO #BlitzResults (CheckID, DatabaseName, Priority, FindingsGroup, Finding, URL, Details) SELECT DISTINCT 99, DB_NAME(), 110, ''Performance'', ''Infinite merge replication metadata retention period'', ''http://BrentOzar.com/go/merge'', (''The ['' + DB_NAME() + ''] database has merge replication metadata retention period set to infinite - this can be the case of significant performance issues.'')';
END
/*
Note that by using sp_MSforeachdb, we're running the query in all
databases. We're not checking #SkipChecks here for each database to
see if we should run the check in this database. That means we may
still run a skipped check if it involves sp_MSforeachdb. We just
don't output those results in the last step.
And that's the basic idea! You can read through the rest of the
checks if you like - some more exciting stuff happens closer to the
end of the stored proc, where we start doing things like checking
the plan cache, but those aren't as cleanly commented.
If you'd like to contribute your own check, use one of the check
formats shown above and email it to [email protected]. You don't
have to pick a CheckID or a link - we'll take care of that when we
test and publish the code. Thanks!
*/
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 93 )
BEGIN
INSERT INTO #BlitzResults
( CheckID ,
Priority ,
FindingsGroup ,
Finding ,
URL ,
Details
)
SELECT DISTINCT
93 AS CheckID ,
1 AS Priority ,
'Backup' AS FindingsGroup ,
'Backing Up to Same Drive Where Databases Reside' AS Finding ,
'http://BrentOzar.com/go/backup' AS URL ,
'Drive '
+ UPPER(LEFT(bmf.physical_device_name, 3))
+ ' houses both database files AND backups taken in the last two weeks. This represents a serious risk if that array fails.' Details
FROM msdb.dbo.backupmediafamily AS bmf
INNER JOIN msdb.dbo.backupset AS bs ON bmf.media_set_id = bs.media_set_id
AND bs.backup_start_date >= ( DATEADD(dd,
-14, GETDATE()) )
WHERE UPPER(LEFT(bmf.physical_device_name COLLATE SQL_Latin1_General_CP1_CI_AS, 3)) IN (
SELECT DISTINCT
UPPER(LEFT(mf.physical_name COLLATE SQL_Latin1_General_CP1_CI_AS, 3))
FROM sys.master_files AS mf )
END
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 119 )
AND EXISTS ( SELECT *
FROM sys.all_objects o
WHERE o.name = 'dm_database_encryption_keys' )
BEGIN
SET @StringToExecute = 'INSERT INTO #BlitzResults (CheckID, Priority, FindingsGroup, Finding, DatabaseName, URL, Details)
SELECT 119 AS CheckID,
1 AS Priority,
''Backup'' AS FindingsGroup,
''TDE Certificate Not Backed Up Recently'' AS Finding,
db_name(dek.database_id) AS DatabaseName,
''http://BrentOzar.com/go/tde'' AS URL,
''The certificate '' + c.name + '' is used to encrypt database '' + db_name(dek.database_id) + ''. Last backup date: '' + COALESCE(CAST(c.pvt_key_last_backup_date AS VARCHAR(100)), ''Never'') AS Details
FROM sys.certificates c INNER JOIN sys.dm_database_encryption_keys dek ON c.thumbprint = dek.encryptor_thumbprint
WHERE pvt_key_last_backup_date IS NULL OR pvt_key_last_backup_date <= DATEADD(dd, -30, GETDATE())';
EXECUTE(@StringToExecute);
END
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 3 )
BEGIN
INSERT INTO #BlitzResults
( CheckID ,
DatabaseName ,
Priority ,
FindingsGroup ,
Finding ,
URL ,
Details
)
SELECT TOP 1
3 AS CheckID ,
'msdb' ,
200 AS Priority ,
'Backup' AS FindingsGroup ,
'MSDB Backup History Not Purged' AS Finding ,
'http://BrentOzar.com/go/history' AS URL ,
( 'Database backup history retained back to '
+ CAST(bs.backup_start_date AS VARCHAR(20)) ) AS Details
FROM msdb.dbo.backupset bs
WHERE bs.backup_start_date <= DATEADD(dd, -60,
GETDATE())
ORDER BY backup_set_id ASC;
END
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 4 )
BEGIN
INSERT INTO #BlitzResults
( CheckID ,
Priority ,
FindingsGroup ,
Finding ,
URL ,
Details
)
SELECT 4 AS CheckID ,
10 AS Priority ,
'Security' AS FindingsGroup ,
'Sysadmins' AS Finding ,
'http://BrentOzar.com/go/sa' AS URL ,
( 'Login [' + l.name
+ '] is a sysadmin - meaning they can do absolutely anything in SQL Server, including dropping databases or hiding their tracks.' ) AS Details
FROM master.sys.syslogins l
WHERE l.sysadmin = 1
AND l.name <> SUSER_SNAME(0x01)
AND l.denylogin = 0;
END
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 5 )
BEGIN
INSERT INTO #BlitzResults
( CheckID ,
Priority ,
FindingsGroup ,
Finding ,
URL ,
Details
)
SELECT 5 AS CheckID ,
10 AS Priority ,
'Security' AS FindingsGroup ,
'Security Admins' AS Finding ,
'http://BrentOzar.com/go/sa' AS URL ,
( 'Login [' + l.name
+ '] is a security admin - meaning they can give themselves permission to do absolutely anything in SQL Server, including dropping databases or hiding their tracks.' ) AS Details
FROM master.sys.syslogins l
WHERE l.securityadmin = 1
AND l.name <> SUSER_SNAME(0x01)
AND l.denylogin = 0;
END
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 104 )
BEGIN
INSERT INTO #BlitzResults
( [CheckID] ,
[Priority] ,
[FindingsGroup] ,
[Finding] ,
[URL] ,
[Details]
)
SELECT 104 AS [CheckID] ,
10 AS [Priority] ,
'Security' AS [FindingsGroup] ,
'Login Can Control Server' AS [Finding] ,
'http://BrentOzar.com/go/sa' AS [URL] ,
'Login [' + pri.[name]
+ '] has the CONTROL SERVER permission - meaning they can do absolutely anything in SQL Server, including dropping databases or hiding their tracks.' AS [Details]
FROM sys.server_principals AS pri
WHERE pri.[principal_id] IN (
SELECT p.[grantee_principal_id]
FROM sys.server_permissions AS p
WHERE p.[state] IN ( 'G', 'W' )
AND p.[class] = 100
AND p.[type] = 'CL' )
AND pri.[name] NOT LIKE '##%##'
END
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 6 )
BEGIN
INSERT INTO #BlitzResults
( CheckID ,
Priority ,
FindingsGroup ,
Finding ,
URL ,
Details
)
SELECT 6 AS CheckID ,
200 AS Priority ,
'Security' AS FindingsGroup ,
'Jobs Owned By Users' AS Finding ,
'http://BrentOzar.com/go/owners' AS URL ,
( 'Job [' + j.name + '] is owned by ['
+ SUSER_SNAME(j.owner_sid)
+ '] - meaning if their login is disabled or not available due to Active Directory problems, the job will stop working.' ) AS Details
FROM msdb.dbo.sysjobs j
WHERE j.enabled = 1
AND SUSER_SNAME(j.owner_sid) <> SUSER_SNAME(0x01);
END
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 7 )
BEGIN
INSERT INTO #BlitzResults
( CheckID ,
Priority ,
FindingsGroup ,
Finding ,
URL ,
Details
)
SELECT 7 AS CheckID ,
10 AS Priority ,
'Security' AS FindingsGroup ,
'Stored Procedure Runs at Startup' AS Finding ,
'http://BrentOzar.com/go/startup' AS URL ,
( 'Stored procedure [master].['
+ r.SPECIFIC_SCHEMA + '].['
+ r.SPECIFIC_NAME
+ '] runs automatically when SQL Server starts up. Make sure you know exactly what this stored procedure is doing, because it could pose a security risk.' ) AS Details
FROM master.INFORMATION_SCHEMA.ROUTINES r
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),
'ExecIsStartup') = 1;
END
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 9 )
BEGIN
IF @@VERSION NOT LIKE '%Microsoft SQL Server 2000%'
BEGIN
SET @StringToExecute = 'INSERT INTO #BlitzResults
(CheckID,
Priority,
FindingsGroup,
Finding,
URL,
Details)
SELECT 9 AS CheckID,
200 AS Priority,
''Surface Area'' AS FindingsGroup,
''Endpoints Configured'' AS Finding,
''http://BrentOzar.com/go/endpoints/'' AS URL,
(''SQL Server endpoints are configured. These can be used for database mirroring or Service Broker, but if you do not need them, avoid leaving them enabled. Endpoint name: '' + [name]) AS Details FROM sys.endpoints WHERE type <> 2'
EXECUTE(@StringToExecute)
END;
END
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 10 )
BEGIN
IF @@VERSION NOT LIKE '%Microsoft SQL Server 2000%'
AND @@VERSION NOT LIKE '%Microsoft SQL Server 2005%'
BEGIN
SET @StringToExecute = 'INSERT INTO #BlitzResults
(CheckID,
Priority,
FindingsGroup,
Finding,
URL,
Details)
SELECT 10 AS CheckID,
100 AS Priority,
''Performance'' AS FindingsGroup,
''Resource Governor Enabled'' AS Finding,
''http://BrentOzar.com/go/rg'' AS URL,
(''Resource Governor is enabled. Queries may be throttled. Make sure you understand how the Classifier Function is configured.'') AS Details FROM sys.resource_governor_configuration WHERE is_enabled = 1'
EXECUTE(@StringToExecute)
END;
END
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 11 )
BEGIN
IF @@VERSION NOT LIKE '%Microsoft SQL Server 2000%'
BEGIN
SET @StringToExecute = 'INSERT INTO #BlitzResults
(CheckID,
Priority,
FindingsGroup,
Finding,
URL,
Details)
SELECT 11 AS CheckID,
100 AS Priority,
''Performance'' AS FindingsGroup,
''Server Triggers Enabled'' AS Finding,
''http://BrentOzar.com/go/logontriggers/'' AS URL,
(''Server Trigger ['' + [name] ++ ''] is enabled, so it runs every time someone logs in. Make sure you understand what that trigger is doing - the less work it does, the better.'') AS Details FROM sys.server_triggers WHERE is_disabled = 0 AND is_ms_shipped = 0'
EXECUTE(@StringToExecute)
END;
END
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 12 )
BEGIN
INSERT INTO #BlitzResults
( CheckID ,
DatabaseName ,
Priority ,
FindingsGroup ,
Finding ,
URL ,
Details
)
SELECT 12 AS CheckID ,
[name] AS DatabaseName ,
10 AS Priority ,
'Performance' AS FindingsGroup ,
'Auto-Close Enabled' AS Finding ,
'http://BrentOzar.com/go/autoclose' AS URL ,
( 'Database [' + [name]
+ '] has auto-close enabled. This setting can dramatically decrease performance.' ) AS Details
FROM sys.databases
WHERE is_auto_close_on = 1
AND name NOT IN ( SELECT DISTINCT
DatabaseName
FROM #SkipChecks )
END
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 13 )
BEGIN
INSERT INTO #BlitzResults
( CheckID ,
DatabaseName ,
Priority ,
FindingsGroup ,
Finding ,
URL ,
Details
)
SELECT 13 AS CheckID ,
[name] AS DatabaseName ,
10 AS Priority ,
'Performance' AS FindingsGroup ,
'Auto-Shrink Enabled' AS Finding ,
'http://BrentOzar.com/go/autoshrink' AS URL ,
( 'Database [' + [name]
+ '] has auto-shrink enabled. This setting can dramatically decrease performance.' ) AS Details
FROM sys.databases
WHERE is_auto_shrink_on = 1
AND name NOT IN ( SELECT DISTINCT
DatabaseName
FROM #SkipChecks );
END
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 14 )
BEGIN
IF @@VERSION NOT LIKE '%Microsoft SQL Server 2000%'
BEGIN
SET @StringToExecute = 'INSERT INTO #BlitzResults
(CheckID,
DatabaseName,
Priority,
FindingsGroup,
Finding,
URL,
Details)
SELECT 14 AS CheckID,
[name] as DatabaseName,
50 AS Priority,
''Reliability'' AS FindingsGroup,
''Page Verification Not Optimal'' AS Finding,
''http://BrentOzar.com/go/torn'' AS URL,
(''Database ['' + [name] + ''] has '' + [page_verify_option_desc] + '' for page verification. SQL Server may have a harder time recognizing and recovering from storage corruption. Consider using CHECKSUM instead.'') COLLATE database_default AS Details
FROM sys.databases
WHERE page_verify_option < 2
AND name <> ''tempdb''
and name not in (select distinct DatabaseName from #SkipChecks)'
EXECUTE(@StringToExecute)
END;
END
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 15 )
BEGIN
INSERT INTO #BlitzResults
( CheckID ,
DatabaseName ,
Priority ,
FindingsGroup ,
Finding ,
URL ,
Details
)
SELECT 15 AS CheckID ,
[name] AS DatabaseName ,
110 AS Priority ,
'Performance' AS FindingsGroup ,
'Auto-Create Stats Disabled' AS Finding ,
'http://BrentOzar.com/go/acs' AS URL ,
( 'Database [' + [name]
+ '] has auto-create-stats disabled. SQL Server uses statistics to build better execution plans, and without the ability to automatically create more, performance may suffer.' ) AS Details
FROM sys.databases
WHERE is_auto_create_stats_on = 0
AND name NOT IN ( SELECT DISTINCT
DatabaseName
FROM #SkipChecks )
END
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 16 )
BEGIN
INSERT INTO #BlitzResults
( CheckID ,
DatabaseName ,
Priority ,
FindingsGroup ,
Finding ,
URL ,
Details
)
SELECT 16 AS CheckID ,
[name] AS DatabaseName ,
110 AS Priority ,
'Performance' AS FindingsGroup ,