Skip to content

Instantly share code, notes, and snippets.

@BrentOzar

BrentOzar/sp_Blitz_as_script.sql

Created Dec 2, 2019
Star
Embed
What would you like to do?
Learn more about clone URLs
Download ZIP
Long query without the create proc
Raw
sp_Blitz_as_script.sql
DECLARE
@Help TINYINT = 0 ,
@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 ,
@OutputServerName NVARCHAR(256) = NULL ,
@OutputDatabaseName NVARCHAR(256) = NULL ,
@OutputSchemaName NVARCHAR(256) = NULL ,
@OutputTableName NVARCHAR(256) = NULL ,
@OutputXMLasNVARCHAR TINYINT = 0 ,
@EmailRecipients VARCHAR(MAX) = NULL ,
@EmailProfile sysname = NULL ,
@SummaryMode TINYINT = 0 ,
@BringThePain TINYINT = 0 ,
@UsualDBOwner sysname = NULL ,
@SkipBlockingChecks TINYINT = 1 ,
@Debug TINYINT = 0 ,
@Version VARCHAR(30) = NULL ,
@VersionDate DATETIME = NULL ,
@VersionCheckMode BIT = 0;
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT @Version = '7.9', @VersionDate = '20191024';
SET @OutputType = UPPER(@OutputType);
IF(@VersionCheckMode = 1)
BEGIN
RETURN;
END;
IF @Help = 1 PRINT '
/*
sp_Blitz from http://FirstResponderKit.org
This script checks the health of your SQL Server and gives you a prioritized
to-do list of the most urgent things you should consider fixing.
To learn more, visit http://FirstResponderKit.org where you can download new
versions for free, watch training videos on how it works, get more info on
the findings, contribute your own code, and more.
Known limitations of this version:
- Only Microsoft-supported versions of SQL Server. Sorry, 2005 and 2000.
- 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.)
- @OutputServerName will output QueryPlans as NVARCHAR(MAX) since Microsoft
has refused to support XML columns in Linked Server queries. The bug is now
16 years old! *~ \o/ ~*
Unknown limitations of this version:
- None. (If we knew them, they would be known. Duh.)
Changes - for the full list of improvements and fixes in this version, see:
https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/
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 | ''MARKDOWN''=bulleted list | ''SCHEMA''=version and field list | ''XML'' =table output as XML | ''NONE'' = none
@IgnorePrioritiesBelow 50=ignore priorities below 50
@IgnorePrioritiesAbove 50=ignore priorities above 50
For the rest of the parameters, see https://www.BrentOzar.com/blitz/documentation for details.
MIT License
Copyright for portions of sp_Blitz are held by Microsoft as part of project
tigertoolbox and are provided under the MIT license:
https://github.com/Microsoft/tigertoolbox
All other copyright for sp_Blitz are held by Brent Ozar Unlimited, 2019.
Copyright (c) 2019 Brent Ozar Unlimited
Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.
*/';
ELSE IF @OutputType = 'SCHEMA'
BEGIN
SELECT 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;/* IF @OutputType = 'SCHEMA' */
ELSE
BEGIN
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)
,@ProductVersion NVARCHAR(128)
,@ProductVersionMajor DECIMAL(10,2)
,@ProductVersionMinor DECIMAL(10,2)
,@CurrentName NVARCHAR(128)
,@CurrentDefaultValue NVARCHAR(200)
,@CurrentCheckID INT
,@CurrentPriority INT
,@CurrentFinding VARCHAR(200)
,@CurrentURL VARCHAR(200)
,@CurrentDetails NVARCHAR(4000)
,@MsSinceWaitsCleared DECIMAL(38,0)
,@CpuMsSinceWaitsCleared DECIMAL(38,0)
,@ResultText NVARCHAR(MAX)
,@crlf NVARCHAR(2)
,@Processors int
,@NUMANodes int
,@MinServerMemory bigint
,@MaxServerMemory bigint
,@ColumnStoreIndexesInUse bit
,@TraceFileIssue bit
-- Flag for Windows OS to help with Linux support
,@IsWindowsOperatingSystem BIT
,@DaysUptime NUMERIC(23,2)
/* For First Responder Kit consistency check:*/
,@spBlitzFullName VARCHAR(1024)
,@BlitzIsOutdatedComparedToOthers BIT
,@tsql NVARCHAR(MAX)
,@VersionCheckModeExistsTSQL NVARCHAR(MAX)
,@BlitzProcDbName VARCHAR(256)
,@ExecRet INT
,@InnerExecRet INT
,@TmpCnt INT
,@PreviousComponentName VARCHAR(256)
,@PreviousComponentFullPath VARCHAR(1024)
,@CurrentStatementId INT
,@CurrentComponentSchema VARCHAR(256)
,@CurrentComponentName VARCHAR(256)
,@CurrentComponentType VARCHAR(256)
,@CurrentComponentVersionDate DATETIME2
,@CurrentComponentFullName VARCHAR(1024)
,@CurrentComponentMandatory BIT
,@MaximumVersionDate DATETIME
,@StatementCheckName VARCHAR(256)
,@StatementOutputsCounter BIT
,@OutputCounterExpectedValue INT
,@StatementOutputsExecRet BIT
,@StatementOutputsDateTime BIT
,@CurrentComponentMandatoryCheckOK BIT
,@CurrentComponentVersionCheckModeOK BIT
,@canExitLoop BIT
,@frkIsConsistent BIT
/* End of declarations for First Responder Kit consistency check:*/
;
SET @crlf = NCHAR(13) + NCHAR(10);
SET @ResultText = 'sp_Blitz Results: ' + @crlf;
/* Last startup */
SELECT @DaysUptime = CAST(DATEDIFF(HOUR, create_date, GETDATE()) / 24. AS NUMERIC(23, 2))
FROM sys.databases
WHERE database_id = 2;
IF @DaysUptime = 0
SET @DaysUptime = .01;
/*
--TOURSTOP01--
See https://www.BrentOzar.com/go/blitztour for a guided tour.
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.
For a list of checks, visit http://FirstResponderKit.org.
*/
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
);
IF OBJECT_ID('tempdb..#TemporaryDatabaseResults') IS NOT NULL
DROP TABLE #TemporaryDatabaseResults;
CREATE TABLE #TemporaryDatabaseResults
(
DatabaseName NVARCHAR(128) ,
Finding NVARCHAR(128)
);
/* First Responder Kit consistency (temporary tables) */
IF(OBJECT_ID('tempdb..#FRKObjects') IS NOT NULL)
BEGIN
EXEC sp_executesql N'DROP TABLE #FRKObjects;';
END;
-- this one represents FRK objects
CREATE TABLE #FRKObjects (
DatabaseName VARCHAR(256) NOT NULL,
ObjectSchemaName VARCHAR(256) NULL,
ObjectName VARCHAR(256) NOT NULL,
ObjectType VARCHAR(256) NOT NULL,
MandatoryComponent BIT NOT NULL
);
IF(OBJECT_ID('tempdb..#StatementsToRun4FRKVersionCheck') IS NOT NULL)
BEGIN
EXEC sp_executesql N'DROP TABLE #StatementsToRun4FRKVersionCheck;';
END;
-- This one will contain the statements to be executed
-- order: 1- Mandatory, 2- VersionCheckMode, 3- VersionCheck
CREATE TABLE #StatementsToRun4FRKVersionCheck (
StatementId INT IDENTITY(1,1),
CheckName VARCHAR(256),
SubjectName VARCHAR(256),
SubjectFullPath VARCHAR(1024),
StatementText NVARCHAR(MAX),
StatementOutputsCounter BIT,
OutputCounterExpectedValue INT,
StatementOutputsExecRet BIT,
StatementOutputsDateTime BIT
);
/* End of First Responder Kit consistency (temporary tables) */
/*
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.
*/
/* --TOURSTOP07-- */
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
IF @Debug IN (1, 2) RAISERROR('Inserting SkipChecks', 0, 1) WITH NOWAIT;
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'') OPTION (RECOMPILE);';
EXEC(@StringToExecute);
END;
-- Flag for Windows OS to help with Linux support
IF EXISTS ( SELECT 1
FROM sys.all_objects
WHERE name = 'dm_os_host_info' )
BEGIN
SELECT @IsWindowsOperatingSystem = CASE WHEN host_platform = 'Windows' THEN 1 ELSE 0 END FROM sys.dm_os_host_info ;
END;
ELSE
BEGIN
SELECT @IsWindowsOperatingSystem = 1 ;
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);
-- Set the trace file path separator based on underlying OS
IF (@IsWindowsOperatingSystem = 1) AND @curr_tracefilename IS NOT NULL
BEGIN
select @indx = patindex('%\%', @curr_tracefilename) ;
set @curr_tracefilename = reverse(@curr_tracefilename) ;
set @base_tracefilename = left( @curr_tracefilename,len(@curr_tracefilename) - @indx) + '\log.trc' ;
END;
ELSE
BEGIN
select @indx = patindex('%/%', @curr_tracefilename) ;
set @curr_tracefilename = reverse(@curr_tracefilename) ;
set @base_tracefilename = left( @curr_tracefilename,len(@curr_tracefilename) - @indx) + '/log.trc' ;
END;
END;
/* If the server has any databases on Antiques Roadshow, skip the checks that would break due to CTEs. */
IF @CheckUserDatabaseObjects = 1 AND EXISTS(SELECT * FROM sys.databases WHERE compatibility_level < 90)
BEGIN
SET @CheckUserDatabaseObjects = 0;
PRINT 'Databases with compatibility level < 90 found, so setting @CheckUserDatabaseObjects = 0.';
PRINT 'The database-level checks rely on CTEs, which are not supported in SQL 2000 compat level databases.';
PRINT 'Get with the cool kids and switch to a current compatibility level, Grandpa. To find the problems, run:';
PRINT 'SELECT * FROM sys.databases WHERE compatibility_level < 90;';
INSERT INTO #BlitzResults
( CheckID ,
Priority ,
FindingsGroup ,
Finding ,
URL ,
Details
)
SELECT 204 AS CheckID ,
0 AS Priority ,
'Informational' AS FindingsGroup ,
'@CheckUserDatabaseObjects Disabled' AS Finding ,
'https://www.BrentOzar.com/blitz/' AS URL ,
'Since you have databases with compatibility_level < 90, we can''t run @CheckUserDatabaseObjects = 1. To find them: SELECT * FROM sys.databases WHERE compatibility_level < 90' AS Details;
END;
/* --TOURSTOP08-- */
/* If the server is Amazon RDS, skip checks that it doesn't allow */
IF LEFT(CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS VARCHAR(8000)), 8) = 'EC2AMAZ-'
AND LEFT(CAST(SERVERPROPERTY('MachineName') AS VARCHAR(8000)), 8) = 'EC2AMAZ-'
AND LEFT(CAST(SERVERPROPERTY('ServerName') AS VARCHAR(8000)), 8) = 'EC2AMAZ-'
AND db_id('rdsadmin') IS NOT NULL
AND EXISTS(SELECT * FROM master.sys.all_objects WHERE name IN ('rds_startup_tasks', 'rds_help_revlogin', 'rds_hexadecimal', 'rds_failover_tracking', 'rds_database_tracking', 'rds_track_change'))
BEGIN
INSERT INTO #SkipChecks (CheckID) VALUES (6);
INSERT INTO #SkipChecks (CheckID) VALUES (29);
INSERT INTO #SkipChecks (CheckID) VALUES (30);
INSERT INTO #SkipChecks (CheckID) VALUES (31);
INSERT INTO #SkipChecks (CheckID) VALUES (40); /* TempDB only has one data file */
INSERT INTO #SkipChecks (CheckID) VALUES (57);
INSERT INTO #SkipChecks (CheckID) VALUES (59);
INSERT INTO #SkipChecks (CheckID) VALUES (61);
INSERT INTO #SkipChecks (CheckID) VALUES (62);
INSERT INTO #SkipChecks (CheckID) VALUES (68);
INSERT INTO #SkipChecks (CheckID) VALUES (69);
INSERT INTO #SkipChecks (CheckID) VALUES (73);
INSERT INTO #SkipChecks (CheckID) VALUES (79);
INSERT INTO #SkipChecks (CheckID) VALUES (92);
INSERT INTO #SkipChecks (CheckID) VALUES (94);
INSERT INTO #SkipChecks (CheckID) VALUES (96);
INSERT INTO #SkipChecks (CheckID) VALUES (98);
INSERT INTO #SkipChecks (CheckID) VALUES (100); /* Remote DAC disabled */
INSERT INTO #SkipChecks (CheckID) VALUES (123);
INSERT INTO #SkipChecks (CheckID) VALUES (177);
INSERT INTO #SkipChecks (CheckID) VALUES (180); /* 180/181 are maintenance plans */
INSERT INTO #SkipChecks (CheckID) VALUES (181);
INSERT INTO #SkipChecks (CheckID) VALUES (184); /* xp_readerrorlog checking for IFI */
INSERT INTO #SkipChecks (CheckID) VALUES (211); /* xp_regread checking for power saving */
INSERT INTO #SkipChecks (CheckID) VALUES (212); /* xp_regread */
INSERT INTO #SkipChecks (CheckID) VALUES (219);
INSERT INTO #BlitzResults
( CheckID ,
Priority ,
FindingsGroup ,
Finding ,
URL ,
Details
)
SELECT 223 AS CheckID ,
0 AS Priority ,
'Informational' AS FindingsGroup ,
'Some Checks Skipped' AS Finding ,
'https://aws.amazon.com/rds/sqlserver/' AS URL ,
'Amazon RDS detected, so we skipped some checks that are not currently possible, relevant, or practical there.' AS Details;
END; /* Amazon RDS skipped checks */
/* If the server is ExpressEdition, skip checks that it doesn't allow */
IF CAST(SERVERPROPERTY('Edition') AS NVARCHAR(1000)) LIKE N'%Express%'
BEGIN
INSERT INTO #SkipChecks (CheckID) VALUES (30); /* Alerts not configured */
INSERT INTO #SkipChecks (CheckID) VALUES (31); /* Operators not configured */
INSERT INTO #SkipChecks (CheckID) VALUES (61); /* Agent alerts 19-25 */
INSERT INTO #SkipChecks (CheckID) VALUES (73); /* Failsafe operator */
INSERT INTO #SkipChecks (CheckID) VALUES (96); /* Agent alerts for corruption */
INSERT INTO #BlitzResults
( CheckID ,
Priority ,
FindingsGroup ,
Finding ,
URL ,
Details
)
SELECT 223 AS CheckID ,
0 AS Priority ,
'Informational' AS FindingsGroup ,
'Some Checks Skipped' AS Finding ,
'https://stackoverflow.com/questions/1169634/limitations-of-sql-server-express' AS URL ,
'Express Edition detected, so we skipped some checks that are not currently possible, relevant, or practical there.' AS Details;
END; /* Express Edition skipped checks */
/* If the server is an Azure Managed Instance, skip checks that it doesn't allow */
IF SERVERPROPERTY('EngineEdition') = 8
BEGIN
INSERT INTO #SkipChecks (CheckID) VALUES (1); /* Full backups - because of the MI GUID name bug mentioned here: https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/1481 */
INSERT INTO #SkipChecks (CheckID) VALUES (2); /* Log backups - because of the MI GUID name bug mentioned here: https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/1481 */
INSERT INTO #SkipChecks (CheckID) VALUES (6); /* Security - Jobs Owned By Users per https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/1919 */
INSERT INTO #SkipChecks (CheckID) VALUES (21); /* Informational - Database Encrypted per https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/1919 */
INSERT INTO #SkipChecks (CheckID) VALUES (24); /* File Configuration - System Database on C Drive per https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/1919 */
INSERT INTO #SkipChecks (CheckID) VALUES (50); /* Max Server Memory Set Too High - because they max it out */
INSERT INTO #SkipChecks (CheckID) VALUES (55); /* Security - Database Owner <> sa per https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/1919 */
INSERT INTO #SkipChecks (CheckID) VALUES (74); /* TraceFlag On - because Azure Managed Instances go wild and crazy with the trace flags */
INSERT INTO #SkipChecks (CheckID) VALUES (97); /* Unusual SQL Server Edition */
INSERT INTO #SkipChecks (CheckID) VALUES (100); /* Remote DAC disabled - but it's working anyway, details here: https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/1481 */
INSERT INTO #SkipChecks (CheckID) VALUES (186); /* MSDB Backup History Purged Too Frequently */
INSERT INTO #SkipChecks (CheckID) VALUES (199); /* Default trace, details here: https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/1481 */
INSERT INTO #SkipChecks (CheckID) VALUES (211); /*Power Plan */
INSERT INTO #SkipChecks (CheckID, DatabaseName) VALUES (80, 'master'); /* Max file size set */
INSERT INTO #SkipChecks (CheckID, DatabaseName) VALUES (80, 'model'); /* Max file size set */
INSERT INTO #SkipChecks (CheckID, DatabaseName) VALUES (80, 'msdb'); /* Max file size set */
INSERT INTO #SkipChecks (CheckID, DatabaseName) VALUES (80, 'tempdb'); /* Max file size set */
INSERT INTO #BlitzResults
( CheckID ,
Priority ,
FindingsGroup ,
Finding ,
URL ,
Details
)
SELECT 223 AS CheckID ,
0 AS Priority ,
'Informational' AS FindingsGroup ,
'Some Checks Skipped' AS Finding ,
'https://docs.microsoft.com/en-us/azure/sql-database/sql-database-managed-instance-index' AS URL ,
'Managed Instance detected, so we skipped some checks that are not currently possible, relevant, or practical there.' AS Details;
END; /* Azure Managed Instance skipped checks */
/*
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..#Recompile') IS NOT NULL
DROP TABLE #Recompile;
CREATE TABLE #Recompile(
DBName varchar(200),
ProcName varchar(300),
RecompileFlag varchar(1),
SPSchema varchar(50)
);
IF OBJECT_ID('tempdb..#DatabaseDefaults') IS NOT NULL
DROP TABLE #DatabaseDefaults;
CREATE TABLE #DatabaseDefaults
(
name NVARCHAR(128) ,
DefaultValue NVARCHAR(200),
CheckID INT,
Priority INT,
Finding VARCHAR(200),
URL VARCHAR(200),
Details NVARCHAR(4000)
);
IF OBJECT_ID('tempdb..#DatabaseScopedConfigurationDefaults') IS NOT NULL
DROP TABLE #DatabaseScopedConfigurationDefaults;
CREATE TABLE #DatabaseScopedConfigurationDefaults
(ID INT IDENTITY(1,1), configuration_id INT, [name] NVARCHAR(60), default_value sql_variant, default_value_for_secondary sql_variant, 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 OBJECT_ID('tempdb..#ErrorLog') IS NOT NULL
DROP TABLE #ErrorLog;
CREATE TABLE #ErrorLog
(
LogDate DATETIME ,
ProcessInfo NVARCHAR(20) ,
[Text] NVARCHAR(1000)
);
IF OBJECT_ID('tempdb..#fnTraceGettable') IS NOT NULL
DROP TABLE #fnTraceGettable;
CREATE TABLE #fnTraceGettable
(
TextData NVARCHAR(4000) ,
DatabaseName NVARCHAR(256) ,
EventClass INT ,
Severity INT ,
StartTime DATETIME ,
EndTime DATETIME ,
Duration BIGINT ,
NTUserName NVARCHAR(256) ,
NTDomainName NVARCHAR(256) ,
HostName NVARCHAR(256) ,
ApplicationName NVARCHAR(256) ,
LoginName NVARCHAR(256) ,
DBUserName NVARCHAR(256)
);
IF OBJECT_ID('tempdb..#Instances') IS NOT NULL
DROP TABLE #Instances;
CREATE TABLE #Instances
(
Instance_Number NVARCHAR(MAX) ,
Instance_Name NVARCHAR(MAX) ,
Data_Field NVARCHAR(MAX)
);
IF OBJECT_ID('tempdb..#IgnorableWaits') IS NOT NULL
DROP TABLE #IgnorableWaits;
CREATE TABLE #IgnorableWaits (wait_type NVARCHAR(60));
INSERT INTO #IgnorableWaits VALUES ('BROKER_EVENTHANDLER');
INSERT INTO #IgnorableWaits VALUES ('BROKER_RECEIVE_WAITFOR');
INSERT INTO #IgnorableWaits VALUES ('BROKER_TASK_STOP');
INSERT INTO #IgnorableWaits VALUES ('BROKER_TO_FLUSH');
INSERT INTO #IgnorableWaits VALUES ('BROKER_TRANSMITTER');
INSERT INTO #IgnorableWaits VALUES ('CHECKPOINT_QUEUE');
INSERT INTO #IgnorableWaits VALUES ('CLR_AUTO_EVENT');
INSERT INTO #IgnorableWaits VALUES ('CLR_MANUAL_EVENT');
INSERT INTO #IgnorableWaits VALUES ('CLR_SEMAPHORE');
INSERT INTO #IgnorableWaits VALUES ('DBMIRROR_DBM_EVENT');
INSERT INTO #IgnorableWaits VALUES ('DBMIRROR_DBM_MUTEX');
INSERT INTO #IgnorableWaits VALUES ('DBMIRROR_EVENTS_QUEUE');
INSERT INTO #IgnorableWaits VALUES ('DBMIRROR_WORKER_QUEUE');
INSERT INTO #IgnorableWaits VALUES ('DBMIRRORING_CMD');
INSERT INTO #IgnorableWaits VALUES ('DIRTY_PAGE_POLL');
INSERT INTO #IgnorableWaits VALUES ('DISPATCHER_QUEUE_SEMAPHORE');
INSERT INTO #IgnorableWaits VALUES ('FT_IFTS_SCHEDULER_IDLE_WAIT');
INSERT INTO #IgnorableWaits VALUES ('FT_IFTSHC_MUTEX');
INSERT INTO #IgnorableWaits VALUES ('HADR_CLUSAPI_CALL');
INSERT INTO #IgnorableWaits VALUES ('HADR_FABRIC_CALLBACK');
INSERT INTO #IgnorableWaits VALUES ('HADR_FILESTREAM_IOMGR_IOCOMPLETION');
INSERT INTO #IgnorableWaits VALUES ('HADR_LOGCAPTURE_WAIT');
INSERT INTO #IgnorableWaits VALUES ('HADR_NOTIFICATION_DEQUEUE');
INSERT INTO #IgnorableWaits VALUES ('HADR_TIMER_TASK');
INSERT INTO #IgnorableWaits VALUES ('HADR_WORK_QUEUE');
INSERT INTO #IgnorableWaits VALUES ('LAZYWRITER_SLEEP');
INSERT INTO #IgnorableWaits VALUES ('LOGMGR_QUEUE');
INSERT INTO #IgnorableWaits VALUES ('ONDEMAND_TASK_QUEUE');
INSERT INTO #IgnorableWaits VALUES ('PARALLEL_REDO_DRAIN_WORKER');
INSERT INTO #IgnorableWaits VALUES ('PARALLEL_REDO_LOG_CACHE');
INSERT INTO #IgnorableWaits VALUES ('PARALLEL_REDO_TRAN_LIST');
INSERT INTO #IgnorableWaits VALUES ('PARALLEL_REDO_WORKER_SYNC');
INSERT INTO #IgnorableWaits VALUES ('PARALLEL_REDO_WORKER_WAIT_WORK');
INSERT INTO #IgnorableWaits VALUES ('PREEMPTIVE_HADR_LEASE_MECHANISM');
INSERT INTO #IgnorableWaits VALUES ('PREEMPTIVE_SP_SERVER_DIAGNOSTICS');
INSERT INTO #IgnorableWaits VALUES ('QDS_ASYNC_QUEUE');
INSERT INTO #IgnorableWaits VALUES ('QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP');
INSERT INTO #IgnorableWaits VALUES ('QDS_PERSIST_TASK_MAIN_LOOP_SLEEP');
INSERT INTO #IgnorableWaits VALUES ('QDS_SHUTDOWN_QUEUE');
INSERT INTO #IgnorableWaits VALUES ('REDO_THREAD_PENDING_WORK');
INSERT INTO #IgnorableWaits VALUES ('REQUEST_FOR_DEADLOCK_SEARCH');
INSERT INTO #IgnorableWaits VALUES ('SLEEP_SYSTEMTASK');
INSERT INTO #IgnorableWaits VALUES ('SLEEP_TASK');
INSERT INTO #IgnorableWaits VALUES ('SOS_WORK_DISPATCHER');
INSERT INTO #IgnorableWaits VALUES ('SP_SERVER_DIAGNOSTICS_SLEEP');
INSERT INTO #IgnorableWaits VALUES ('SQLTRACE_BUFFER_FLUSH');
INSERT INTO #IgnorableWaits VALUES ('SQLTRACE_INCREMENTAL_FLUSH_SLEEP');
INSERT INTO #IgnorableWaits VALUES ('UCS_SESSION_REGISTRATION');
INSERT INTO #IgnorableWaits VALUES ('WAIT_XTP_OFFLINE_CKPT_NEW_LOG');
INSERT INTO #IgnorableWaits VALUES ('WAITFOR');
INSERT INTO #IgnorableWaits VALUES ('XE_DISPATCHER_WAIT');
INSERT INTO #IgnorableWaits VALUES ('XE_LIVE_TARGET_TVF');
INSERT INTO #IgnorableWaits VALUES ('XE_TIMER_EVENT');
IF @Debug IN (1, 2) RAISERROR('Setting @MsSinceWaitsCleared', 0, 1) WITH NOWAIT;
SELECT @MsSinceWaitsCleared = DATEDIFF(MINUTE, create_date, CURRENT_TIMESTAMP) * 60000.0
FROM sys.databases
WHERE name = 'tempdb';
/* Have they cleared wait stats? Using a 10% fudge factor */
IF @MsSinceWaitsCleared * .9 > (SELECT MAX(wait_time_ms) FROM sys.dm_os_wait_stats WHERE wait_type IN ('SP_SERVER_DIAGNOSTICS_SLEEP', 'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', 'REQUEST_FOR_DEADLOCK_SEARCH', 'HADR_FILESTREAM_IOMGR_IOCOMPLETION', 'LAZYWRITER_SLEEP', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', 'DIRTY_PAGE_POLL', 'LOGMGR_QUEUE'))
BEGIN
IF @Debug IN (1, 2) RAISERROR('Running CheckId [%d].', 0, 1, 185) WITH NOWAIT;
SET @MsSinceWaitsCleared = (SELECT MAX(wait_time_ms) FROM sys.dm_os_wait_stats WHERE wait_type IN ('SP_SERVER_DIAGNOSTICS_SLEEP', 'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', 'REQUEST_FOR_DEADLOCK_SEARCH', 'HADR_FILESTREAM_IOMGR_IOCOMPLETION', 'LAZYWRITER_SLEEP', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', 'DIRTY_PAGE_POLL', 'LOGMGR_QUEUE'));
IF @MsSinceWaitsCleared = 0 SET @MsSinceWaitsCleared = 1;
INSERT INTO #BlitzResults
( CheckID ,
Priority ,
FindingsGroup ,
Finding ,
URL ,
Details
)
VALUES( 185,
240,
'Wait Stats',
'Wait Stats Have Been Cleared',
'https://BrentOzar.com/go/waits',
'Someone ran DBCC SQLPERF to clear sys.dm_os_wait_stats at approximately: '
+ CONVERT(NVARCHAR(100),
DATEADD(MINUTE, (-1. * (@MsSinceWaitsCleared) / 1000. / 60.), GETDATE()), 120));
END;
/* @CpuMsSinceWaitsCleared is used for waits stats calculations */
IF @Debug IN (1, 2) RAISERROR('Setting @CpuMsSinceWaitsCleared', 0, 1) WITH NOWAIT;
SELECT @CpuMsSinceWaitsCleared = @MsSinceWaitsCleared * scheduler_count
FROM sys.dm_os_sys_info;
/* If we're outputting CSV or Markdown, don't bother checking the plan cache because we cannot export plans. */
IF @OutputType = 'CSV' OR @OutputType = 'MARKDOWN'
SET @CheckProcedureCache = 0;
/* If we're posting a question on Stack, include background info on the server */
IF @OutputType = 'MARKDOWN'
SET @CheckServerInfo = 1;
/* Only run CheckUserDatabaseObjects if there are less than 50 databases. */
IF @BringThePain = 0 AND 50 <= (SELECT COUNT(*) FROM sys.databases) AND @CheckUserDatabaseObjects = 1
BEGIN
SET @CheckUserDatabaseObjects = 0;
PRINT 'Running sp_Blitz @CheckUserDatabaseObjects = 1 on a server with 50+ databases may cause temporary insanity for the server and/or user.';
PRINT 'If you''re sure you want to do this, run again with the parameter @BringThePain = 1.';
INSERT INTO #BlitzResults
( CheckID ,
Priority ,
FindingsGroup ,
Finding ,
URL ,
Details
)
SELECT 201 AS CheckID ,
0 AS Priority ,
'Informational' AS FindingsGroup ,
'@CheckUserDatabaseObjects Disabled' AS Finding ,
'https://www.BrentOzar.com/blitz/' AS URL ,
'If you want to check 50+ databases, you have to also use @BringThePain = 1.' AS Details;
END;
/* Sanitize our inputs */
SELECT
@OutputServerName = QUOTENAME(@OutputServerName),
@OutputDatabaseName = QUOTENAME(@OutputDatabaseName),
@OutputSchemaName = QUOTENAME(@OutputSchemaName),
@OutputTableName = QUOTENAME(@OutputTableName);
/* Get the major and minor build numbers */
IF @Debug IN (1, 2) RAISERROR('Getting version information.', 0, 1) WITH NOWAIT;
SET @ProductVersion = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128));
SELECT @ProductVersionMajor = SUBSTRING(@ProductVersion, 1,CHARINDEX('.', @ProductVersion) + 1 ),
@ProductVersionMinor = PARSENAME(CONVERT(varchar(32), @ProductVersion), 2);
/*
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.
*/
IF @Debug IN (1, 2) RAISERROR('Running CheckId [%d].', 0, 1, 1) WITH NOWAIT;
IF SERVERPROPERTY('EngineName') <> 8 /* Azure Managed Instances need a special query */
BEGIN
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 ,
'https://BrentOzar.com/go/nobak' AS URL ,
'Last backed up: '
+ 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') /*Backupset ran on current server */
WHERE d.database_id <> 2 /* Bonus points if you know what that means */
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 d.name NOT IN ( SELECT DISTINCT
DatabaseName
FROM #SkipChecks
WHERE CheckID IS NULL OR CheckID = 1)
/*
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())
OR MAX(b.backup_finish_date) IS NULL;
END;
ELSE /* SERVERPROPERTY('EngineName') must be 8, Azure Managed Instances */
BEGIN
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 ,
'https://BrentOzar.com/go/nobak' AS URL ,
'Last backed up: '
+ 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'
WHERE d.database_id <> 2 /* Bonus points if you know what that means */
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 d.name NOT IN ( SELECT DISTINCT
DatabaseName
FROM #SkipChecks
WHERE CheckID IS NULL OR CheckID = 1)
/*
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())
OR MAX(b.backup_finish_date) IS NULL;
END;
/*
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
*/
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
IF @Debug IN (1, 2) RAISERROR('Running CheckId [%d].', 0, 1, 2) WITH NOWAIT;
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 Model w/o Log Backups' AS Finding ,
'https://BrentOzar.com/go/biglogs' AS URL ,
( 'The ' + 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(30)) + 'MB log file has not been backed up 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 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 d.name NOT IN ( SELECT DISTINCT
DatabaseName
FROM #SkipChecks
WHERE CheckID IS NULL OR CheckID = 2)
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
IF @Debug IN (1, 2) RAISERROR('Running CheckId [%d].', 0, 1, 8) WITH NOWAIT;
SET @StringToExecute = 'INSERT INTO #BlitzResults
(CheckID, Priority,
FindingsGroup,
Finding, URL,
Details)
SELECT 8 AS CheckID,
230 AS Priority,
''Security'' AS FindingsGroup,
''Server Audits Running'' AS Finding,
''https://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 OPTION (RECOMPILE);';
IF @Debug = 2 AND @StringToExecute IS NOT NULL PRINT @StringToExecute;
IF @Debug = 2 AND @StringToExecute IS NULL PRINT '@StringToExecute has gone NULL, for some reason.';
EXECUTE(@StringToExecute);
END;
END;
/*
But what if you need to run a query in every individual database?
Hop down to the @CheckUserDatabaseObjects section.
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 Help@BrentOzar.com. 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
IF @Debug IN (1, 2) RAISERROR('Running CheckId [%d].', 0, 1, 93) WITH NOWAIT;
INSERT INTO #BlitzResults
( CheckID ,
Priority ,
FindingsGroup ,
Finding ,
URL ,
Details
)
SELECT
93 AS CheckID ,
1 AS Priority ,
'Backup' AS FindingsGroup ,
'Backing Up to Same Drive Where Databases Reside' AS Finding ,
'https://BrentOzar.com/go/backup' AS URL ,
CAST(COUNT(1) AS VARCHAR(50)) + ' backups done on drive '
+ UPPER(LEFT(bmf.physical_device_name, 3))
+ ' in the last two weeks, where database files also live. 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()) )
/* Filter out databases that were recently restored: */
LEFT OUTER JOIN msdb.dbo.restorehistory rh ON bs.database_name = rh.destination_database_name AND rh.restore_date > DATEADD(dd, -14, GETDATE())
WHERE UPPER(LEFT(bmf.physical_device_name, 3)) <> 'HTT' AND
bmf.physical_device_name NOT LIKE '\\%' AND -- GitHub Issue #2141
@IsWindowsOperatingSystem = 1 AND -- GitHub Issue #1995
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
WHERE mf.database_id <> 2 )
AND rh.destination_database_name IS NULL
GROUP BY UPPER(LEFT(bmf.physical_device_name, 3));
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
IF @Debug IN (1, 2) RAISERROR('Running CheckId [%d].', 0, 1, 119) WITH NOWAIT;
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,
''https://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()) OPTION (RECOMPILE);';
IF @Debug = 2 AND @StringToExecute IS NOT NULL PRINT @StringToExecute;
IF @Debug = 2 AND @StringToExecute IS NULL PRINT '@StringToExecute has gone NULL, for some reason.';
EXECUTE(@StringToExecute);
END;
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 202 )
AND EXISTS ( SELECT *
FROM sys.all_columns c
WHERE c.name = 'pvt_key_last_backup_date' )
AND EXISTS ( SELECT *
FROM msdb.INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME = 'backupset' AND c.COLUMN_NAME = 'encryptor_thumbprint' )
BEGIN
IF @Debug IN (1, 2) RAISERROR('Running CheckId [%d].', 0, 1, 202) WITH NOWAIT;
SET @StringToExecute = 'INSERT INTO #BlitzResults (CheckID, Priority, FindingsGroup, Finding, URL, Details)
SELECT DISTINCT 202 AS CheckID,
1 AS Priority,
''Backup'' AS FindingsGroup,
''Encryption Certificate Not Backed Up Recently'' AS Finding,
''https://BrentOzar.com/go/tde'' AS URL,
''The certificate '' + c.name + '' is used to encrypt database backups. Last backup date: '' + COALESCE(CAST(c.pvt_key_last_backup_date AS VARCHAR(100)), ''Never'') AS Details
FROM sys.certificates c
INNER JOIN msdb.dbo.backupset bs ON c.thumbprint = bs.encryptor_thumbprint
WHERE pvt_key_last_backup_date IS NULL OR pvt_key_last_backup_date <= DATEADD(dd, -30, GETDATE()) OPTION (RECOMPILE);';
IF @Debug = 2 AND @StringToExecute IS NOT NULL PRINT @StringToExecute;
IF @Debug = 2 AND @StringToExecute IS NULL PRINT '@StringToExecute has gone NULL, for some reason.';
EXECUTE(@StringToExecute);
END;
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 3 )
BEGIN
IF DATEADD(dd, -60, GETDATE()) > (SELECT TOP 1 backup_start_date FROM msdb.dbo.backupset ORDER BY backup_start_date)
BEGIN
IF @Debug IN (1, 2) RAISERROR('Running CheckId [%d].', 0, 1, 3) WITH NOWAIT;
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 ,
'https://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
LEFT OUTER JOIN msdb.dbo.restorehistory rh ON bs.database_name = rh.destination_database_name
WHERE rh.destination_database_name IS NULL
ORDER BY bs.backup_start_date ASC;
END;
END;
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 186 )
BEGIN
IF DATEADD(dd, -2, GETDATE()) < (SELECT TOP 1 backup_start_date FROM msdb.dbo.backupset ORDER BY backup_start_date)
BEGIN
IF @Debug IN (1, 2) RAISERROR('Running CheckId [%d].', 0, 1, 186) WITH NOWAIT;
INSERT INTO #BlitzResults
( CheckID ,
DatabaseName ,
Priority ,
FindingsGroup ,
Finding ,
URL ,
Details
)
SELECT TOP 1
186 AS CheckID ,
'msdb' ,
200 AS Priority ,
'Backup' AS FindingsGroup ,
'MSDB Backup History Purged Too Frequently' AS Finding ,
'https://BrentOzar.com/go/history' AS URL ,
( 'Database backup history only retained back to '
+ CAST(bs.backup_start_date AS VARCHAR(20)) ) AS Details
FROM msdb.dbo.backupset bs
ORDER BY backup_start_date ASC;
END;
END;
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 178 )
AND EXISTS (SELECT *
FROM msdb.dbo.backupset bs
WHERE bs.type = 'D'
AND bs.backup_size >= 50000000000 /* At least 50GB */
AND DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) <= 60 /* Backup took less than 60 seconds */
AND bs.backup_finish_date >= DATEADD(DAY, -14, GETDATE()) /* In the last 2 weeks */)
BEGIN
IF @Debug IN (1, 2) RAISERROR('Running CheckId [%d].', 0, 1, 178) WITH NOWAIT;
INSERT INTO #BlitzResults
( CheckID ,
Priority ,
FindingsGroup ,
Finding ,
URL ,
Details
)
SELECT 178 AS CheckID ,
200 AS Priority ,
'Performance' AS FindingsGroup ,
'Snapshot Backups Occurring' AS Finding ,
'https://BrentOzar.com/go/snaps' AS URL ,
( CAST(COUNT(*) AS VARCHAR(20)) + ' snapshot-looking backups have occurred in the last two weeks, indicating that IO may be freezing up.') AS Details
FROM msdb.dbo.backupset bs
WHERE bs.type = 'D'
AND bs.backup_size >= 50000000000 /* At least 50GB */
AND DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) <= 60 /* Backup took less than 60 seconds */
AND bs.backup_finish_date >= DATEADD(DAY, -14, GETDATE()); /* In the last 2 weeks */
END;
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 4 )
BEGIN
IF @Debug IN (1, 2) RAISERROR('Running CheckId [%d].', 0, 1, 4) WITH NOWAIT;
INSERT INTO #BlitzResults
( CheckID ,
Priority ,
FindingsGroup ,
Finding ,
URL ,
Details
)
SELECT 4 AS CheckID ,
230 AS Priority ,
'Security' AS FindingsGroup ,
'Sysadmins' AS Finding ,
'https://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
AND l.name NOT LIKE 'NT SERVICE\%'
AND l.name <> 'l_certSignSmDetach'; /* Added in SQL 2016 */
END;
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 5 )
BEGIN
IF @Debug IN (1, 2) RAISERROR('Running CheckId [%d].', 0, 1, 5) WITH NOWAIT;
INSERT INTO #BlitzResults
( CheckID ,
Priority ,
FindingsGroup ,
Finding ,
URL ,
Details
)
SELECT 5 AS CheckID ,
230 AS Priority ,
'Security' AS FindingsGroup ,
'Security Admins' AS Finding ,
'https://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
IF @Debug IN (1, 2) RAISERROR('Running CheckId [%d].', 0, 1, 104) WITH NOWAIT;
INSERT INTO #BlitzResults
( [CheckID] ,
[Priority] ,
[FindingsGroup] ,
[Finding] ,
[URL] ,
[Details]
)
SELECT 104 AS [CheckID] ,
230 AS [Priority] ,
'Security' AS [FindingsGroup] ,
'Login Can Control Server' AS [Finding] ,
'https://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
IF @Debug IN (1, 2) RAISERROR('Running CheckId [%d].', 0, 1, 6) WITH NOWAIT;
INSERT INTO #BlitzResults
( CheckID ,
Priority ,
FindingsGroup ,
Finding ,
URL ,
Details
)
SELECT 6 AS CheckID ,
230 AS Priority ,
'Security' AS FindingsGroup ,
'Jobs Owned By Users' AS Finding ,
'https://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;
/* --TOURSTOP06-- */
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 7 )
BEGIN
/* --TOURSTOP02-- */
IF @Debug IN (1, 2) RAISERROR('Running CheckId [%d].', 0, 1, 7) WITH NOWAIT;
INSERT INTO #BlitzResults
( CheckID ,
Priority ,
FindingsGroup ,
Finding ,
URL ,
Details
)
SELECT 7 AS CheckID ,
230 AS Priority ,
'Security' AS FindingsGroup ,
'Stored Procedure Runs at Startup' AS Finding ,
'https://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 = 10 )
BEGIN
IF @@VERSION NOT LIKE '%Microsoft SQL Server 2000%'
AND @@VERSION NOT LIKE '%Microsoft SQL Server 2005%'
BEGIN
IF @Debug IN (1, 2) RAISERROR('Running CheckId [%d].', 0, 1, 10) WITH NOWAIT;
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,
''https://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 OPTION (RECOMPILE);';
IF @Debug = 2 AND @StringToExecute IS NOT NULL PRINT @StringToExecute;
IF @Debug = 2 AND @StringToExecute IS NULL PRINT '@StringToExecute has gone NULL, for some reason.';
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
IF @Debug IN (1, 2) RAISERROR('Running CheckId [%d].', 0, 1, 11) WITH NOWAIT;
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,
''https://BrentOzar.com/go/logontriggers/'' AS URL,
(''Server Trigger ['' + [name] ++ ''] is enabled. 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 OPTION (RECOMPILE);';
IF @Debug = 2 AND @StringToExecute IS NOT NULL PRINT @StringToExecute;
IF @Debug = 2 AND @StringToExecute IS NULL PRINT '@StringToExecute has gone NULL, for some reason.';
EXECUTE(@StringToExecute);
END;
END;
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 12 )
BEGIN
IF @Debug IN (1, 2) RAISERROR('Running CheckId [%d].', 0, 1, 12) WITH NOWAIT;
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 ,
'https://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
WHERE CheckID IS NULL OR CheckID = 12);
END;
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 13 )
BEGIN
IF @Debug IN (1, 2) RAISERROR('Running CheckId [%d].', 0, 1, 13) WITH NOWAIT;
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 ,
'https://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
WHERE CheckID IS NULL OR CheckID = 13);
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
IF @Debug IN (1, 2) RAISERROR('Running CheckId [%d].', 0, 1, 14) WITH NOWAIT;
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,
''https://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 state <> 1 /* Restoring */
and name not in (select distinct DatabaseName from #SkipChecks WHERE CheckID IS NULL OR CheckID = 14) OPTION (RECOMPILE);';
IF @Debug = 2 AND @StringToExecute IS NOT NULL PRINT @StringToExecute;
IF @Debug = 2 AND @StringToExecute IS NULL PRINT '@StringToExecute has gone NULL, for some reason.';
EXECUTE(@StringToExecute);
END;
END;
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 15 )
BEGIN
IF @Debug IN (1, 2) RAISERROR('Running CheckId [%d].', 0, 1, 15) WITH NOWAIT;
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 ,
'https://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
WHERE CheckID IS NULL OR CheckID = 15);
END;
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 16 )
BEGIN
IF @Debug IN (1, 2) RAISERROR('Running CheckId [%d].', 0, 1, 16) WITH NOWAIT;
INSERT INTO #BlitzResults
( CheckID ,
DatabaseName ,
Priority ,
FindingsGroup ,
Finding ,
URL ,
Details
)
SELECT 16 AS CheckID ,
[name] AS DatabaseName ,
110 AS Priority ,
'Performance' AS FindingsGroup ,
'Auto-Update Stats Disabled' AS Finding ,
'https://BrentOzar.com/go/aus' AS URL ,
( 'Database [' + [name]
+ '] has auto-update-stats disabled. SQL Server uses statistics to build better execution plans, and without the ability to automatically update them, performance may suffer.' ) AS Details
FROM sys.databases
WHERE is_auto_update_stats_on = 0
AND name NOT IN ( SELECT DISTINCT
DatabaseName
FROM #SkipChecks
WHERE CheckID IS NULL OR CheckID = 16);
END;
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 17 )
BEGIN
IF @Debug IN (1, 2) RAISERROR('Running CheckId [%d].', 0, 1, 17) WITH NOWAIT;
INSERT INTO #BlitzResults
( CheckID ,
DatabaseName ,
Priority ,
FindingsGroup ,
Finding ,
URL ,
Details
)
SELECT 17 AS CheckID ,
[name] AS DatabaseName ,
150 AS Priority ,
'Performance' AS FindingsGroup ,
'Stats Updated Asynchronously' AS Finding ,
'https://BrentOzar.com/go/asyncstats' AS URL ,
( 'Database [' + [name]
+ '] has auto-update-stats-async enabled. When SQL Server gets a query for a table with out-of-date statistics, it will run the query with the stats it has - while updating stats to make later queries better. The initial run of the query may suffer, though.' ) AS Details
FROM sys.databases
WHERE is_auto_update_stats_async_on = 1
AND name NOT IN ( SELECT DISTINCT
DatabaseName
FROM #SkipChecks
WHERE CheckID IS NULL OR CheckID = 17);
END;
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 20 )
BEGIN
IF @Debug IN (1, 2) RAISERROR('Running CheckId [%d].', 0, 1, 20) WITH NOWAIT;
INSERT INTO #BlitzResults
( CheckID ,
DatabaseName ,
Priority ,
FindingsGroup ,
Finding ,
URL ,
Details
)
SELECT 20 AS CheckID ,
[name] AS DatabaseName ,
200 AS Priority ,
'Informational' AS FindingsGroup ,
'Date Correlation On' AS Finding ,
'https://BrentOzar.com/go/corr' AS URL ,
( 'Database [' + [name]
+ '] has date correlation enabled. This is not a default setting, and it has some performance overhead. It tells SQL Server that date fields in two tables are related, and SQL Server maintains statistics showing that relation.' ) AS Details
FROM sys.databases
WHERE is_date_correlation_on = 1
AND name NOT IN ( SELECT DISTINCT
DatabaseName
FROM #SkipChecks
WHERE CheckID IS NULL OR CheckID = 20);
END;
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 21 )
BEGIN
/* --TOURSTOP04-- */
IF @@VERSION NOT LIKE '%Microsoft SQL Server 2000%'
AND @@VERSION NOT LIKE '%Microsoft SQL Server 2005%'
BEGIN
IF @Debug IN (1, 2) RAISERROR('Running CheckId [%d].', 0, 1, 21) WITH NOWAIT;
SET @StringToExecute = 'INSERT INTO #BlitzResults
(CheckID,
DatabaseName,
Priority,
FindingsGroup,
Finding,
URL,
Details)
SELECT 21 AS CheckID,
[name] as DatabaseName,
200 AS Priority,
''Informational'' AS FindingsGroup,
''Database Encrypted'' AS Finding,
''https://BrentOzar.com/go/tde'' AS URL,
(''Database ['' + [name] + ''] has Transparent Data Encryption enabled. Make absolutely sure you have backed up the certificate and private key, or else you will not be able to restore this database.'') AS Details
FROM sys.databases
WHERE is_encrypted = 1
and name not in (select distinct DatabaseName from #SkipChecks WHERE CheckID IS NULL OR CheckID = 21) OPTION (RECOMPILE);';
IF @Debug = 2 AND @StringToExecute IS NOT NULL PRINT @StringToExecute;
IF @Debug = 2 AND @StringToExecute IS NULL PRINT '@StringToExecute has gone NULL, for some reason.';
EXECUTE(@StringToExecute);
END;
END;
/*
Believe it or not, SQL Server doesn't track the default values
for sp_configure options! We'll make our own list here.
*/
IF @Debug IN (1, 2) RAISERROR('Generating default configuration values', 0, 1) WITH NOWAIT;
INSERT INTO #ConfigurationDefaults
VALUES ( 'access check cache bucket count', 0, 1001 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'access check cache quota', 0, 1002 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'Ad Hoc Distributed Queries', 0, 1003 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'affinity I/O mask', 0, 1004 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'affinity mask', 0, 1005 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'affinity64 mask', 0, 1066 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'affinity64 I/O mask', 0, 1067 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'Agent XPs', 0, 1071 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'allow updates', 0, 1007 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'awe enabled', 0, 1008 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'backup checksum default', 0, 1070 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'backup compression default', 0, 1073 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'blocked process threshold', 0, 1009 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'blocked process threshold (s)', 0, 1009 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'c2 audit mode', 0, 1010 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'clr enabled', 0, 1011 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'common criteria compliance enabled', 0, 1074 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'contained database authentication', 0, 1068 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'cost threshold for parallelism', 5, 1012 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'cross db ownership chaining', 0, 1013 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'cursor threshold', -1, 1014 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'Database Mail XPs', 0, 1072 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'default full-text language', 1033, 1016 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'default language', 0, 1017 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'default trace enabled', 1, 1018 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'disallow results from triggers', 0, 1019 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'EKM provider enabled', 0, 1075 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'filestream access level', 0, 1076 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'fill factor (%)', 0, 1020 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'ft crawl bandwidth (max)', 100, 1021 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'ft crawl bandwidth (min)', 0, 1022 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'ft notify bandwidth (max)', 100, 1023 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'ft notify bandwidth (min)', 0, 1024 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'index create memory (KB)', 0, 1025 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'in-doubt xact resolution', 0, 1026 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'lightweight pooling', 0, 1027 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'locks', 0, 1028 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'max degree of parallelism', 0, 1029 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'max full-text crawl range', 4, 1030 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'max server memory (MB)', 2147483647, 1031 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'max text repl size (B)', 65536, 1032 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'max worker threads', 0, 1033 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'media retention', 0, 1034 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'min memory per query (KB)', 1024, 1035 );
/* Accepting both 0 and 16 below because both have been seen in the wild as defaults. */
IF EXISTS ( SELECT *
FROM sys.configurations
WHERE name = 'min server memory (MB)'
AND value_in_use IN ( 0, 16 ) )
INSERT INTO #ConfigurationDefaults
SELECT 'min server memory (MB)' ,
CAST(value_in_use AS BIGINT), 1036
FROM sys.configurations
WHERE name = 'min server memory (MB)';
ELSE
INSERT INTO #ConfigurationDefaults
VALUES ( 'min server memory (MB)', 0, 1036 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'nested triggers', 1, 1037 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'network packet size (B)', 4096, 1038 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'Ole Automation Procedures', 0, 1039 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'open objects', 0, 1040 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'optimize for ad hoc workloads', 0, 1041 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'PH timeout (s)', 60, 1042 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'precompute rank', 0, 1043 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'priority boost', 0, 1044 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'query governor cost limit', 0, 1045 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'query wait (s)', -1, 1046 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'recovery interval (min)', 0, 1047 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'remote access', 1, 1048 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'remote admin connections', 0, 1049 );
/* SQL Server 2012 changes a configuration default */
IF @@VERSION LIKE '%Microsoft SQL Server 2005%'
OR @@VERSION LIKE '%Microsoft SQL Server 2008%'
BEGIN
INSERT INTO #ConfigurationDefaults
VALUES ( 'remote login timeout (s)', 20, 1069 );
END;
ELSE
BEGIN
INSERT INTO #ConfigurationDefaults
VALUES ( 'remote login timeout (s)', 10, 1069 );
END;
INSERT INTO #ConfigurationDefaults
VALUES ( 'remote proc trans', 0, 1050 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'remote query timeout (s)', 600, 1051 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'Replication XPs', 0, 1052 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'RPC parameter data validation', 0, 1053 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'scan for startup procs', 0, 1054 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'server trigger recursion', 1, 1055 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'set working set size', 0, 1056 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'show advanced options', 0, 1057 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'SMO and DMO XPs', 1, 1058 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'SQL Mail XPs', 0, 1059 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'transform noise words', 0, 1060 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'two digit year cutoff', 2049, 1061 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'user connections', 0, 1062 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'user options', 0, 1063 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'Web Assistant Procedures', 0, 1064 );
INSERT INTO #ConfigurationDefaults
VALUES ( 'xp_cmdshell', 0, 1065 );
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 22 )
BEGIN
IF @Debug IN (1, 2) RAISERROR('Running CheckId [%d].', 0, 1, 22) WITH NOWAIT;
INSERT INTO #BlitzResults
( CheckID ,
Priority ,
FindingsGroup ,
Finding ,
URL ,
Details
)
SELECT cd.CheckID ,
200 AS Priority ,
'Non-Default Server Config' AS FindingsGroup ,
cr.name AS Finding ,
'https://BrentOzar.com/go/conf' AS URL ,
( 'This sp_configure option has been changed. Its default value is '
+ COALESCE(CAST(cd.[DefaultValue] AS VARCHAR(100)),
'(unknown)')
+ ' and it has been set to '
+ CAST(cr.value_in_use AS VARCHAR(100))
+ '.' ) AS Details
FROM sys.configurations cr
INNER JOIN #ConfigurationDefaults cd ON cd.name = cr.name
LEFT OUTER JOIN #ConfigurationDefaults cdUsed ON cdUsed.name = cr.name
AND cdUsed.DefaultValue = cr.value_in_use
WHERE cdUsed.name IS NULL;
END;
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 190 )
BEGIN
IF @Debug IN (1, 2) RAISERROR('Setting @MinServerMemory and @MaxServerMemory', 0, 1) WITH NOWAIT;
SELECT @MinServerMemory = CAST(value_in_use as BIGINT) FROM sys.configurations WHERE name = 'min server memory (MB)';
SELECT @MaxServerMemory = CAST(value_in_use as BIGINT) FROM sys.configurations WHERE name = 'max server memory (MB)';
IF (@MinServerMemory = @MaxServerMemory)
BEGIN
IF @Debug IN (1, 2) RAISERROR('Running CheckId [%d].', 0, 1, 190) WITH NOWAIT;
INSERT INTO #BlitzResults
( CheckID ,
Priority ,
FindingsGroup ,
Finding ,
URL ,
Details
)
VALUES
( 190,
200,
'Performance',
'Non-Dynamic Memory',
'https://BrentOzar.com/go/memory',
'Minimum Server Memory setting is the same as the Maximum (both set to ' + CAST(@MinServerMemory AS NVARCHAR(50)) + '). This will not allow dynamic memory. Please revise memory settings'
);
END;
END;
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 188 )
BEGIN
/* Let's set variables so that our query is still SARGable */
IF @Debug IN (1, 2) RAISERROR('Setting @Processors.', 0, 1) WITH NOWAIT;
SET @Processors = (SELECT cpu_count FROM sys.dm_os_sys_info);
IF @Debug IN (1, 2) RAISERROR('Setting @NUMANodes', 0, 1) WITH NOWAIT;
SET @NUMANodes = (SELECT COUNT(1)
FROM sys.dm_os_performance_counters pc
WHERE pc.object_name LIKE '%Buffer Node%'
AND counter_name = 'Page life expectancy');
/* If Cost Threshold for Parallelism is default then flag as a potential issue */
/* If MAXDOP is default and processors > 8 or NUMA nodes > 1 then flag as potential issue */
IF @Debug IN (1, 2) RAISERROR('Running CheckId [%d].', 0, 1, 188) WITH NOWAIT;
INSERT INTO #BlitzResults
( CheckID ,
Priority ,
FindingsGroup ,
Finding ,
URL ,
Details
)
SELECT 188 AS CheckID ,
200 AS Priority ,
'Performance' AS FindingsGroup ,
cr.name AS Finding ,
'https://BrentOzar.com/go/cxpacket' AS URL ,
( 'Set to ' + CAST(cr.value_in_use AS NVARCHAR(50)) + ', its default value. Changing this sp_configure setting may reduce CXPACKET waits.')
FROM sys.configurations cr
INNER JOIN #ConfigurationDefaults cd ON cd.name = cr.name
AND cr.value_in_use = cd.DefaultValue
WHERE cr.name = 'cost threshold for parallelism'
OR (cr.name = 'max degree of parallelism' AND (@NUMANodes > 1 OR @Processors > 8));
END;
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 24 )
BEGIN
IF @Debug IN (1, 2) RAISERROR('Running CheckId [%d].', 0, 1, 24) WITH NOWAIT;
INSERT INTO #BlitzResults
( CheckID ,
DatabaseName ,
Priority ,
FindingsGroup ,
Finding ,
URL ,
Details
)
SELECT DISTINCT
24 AS CheckID ,
DB_NAME(database_id) AS DatabaseName ,
170 AS Priority ,
'File Configuration' AS FindingsGroup ,
'System Database on C Drive' AS Finding ,
'https://BrentOzar.com/go/cdrive' AS URL ,
( 'The ' + DB_NAME(database_id)
+ ' database has a file on the C drive. Putting system databases on the C drive runs the risk of crashing the server when it runs out of space.' ) AS Details
FROM sys.master_files
WHERE UPPER(LEFT(physical_name, 1)) = 'C'
AND DB_NAME(database_id) IN ( 'master',
'model', 'msdb' );
END;
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 25 )
AND SERVERPROPERTY('EngineEdition') <> 8 /* Azure Managed Instances */
BEGIN
IF @Debug IN (1, 2) RAISERROR('Running CheckId [%d].', 0, 1, 25) WITH NOWAIT;
INSERT INTO #BlitzResults
( CheckID ,
DatabaseName ,
Priority ,
FindingsGroup ,
Finding ,
URL ,
Details
)
SELECT TOP 1
25 AS CheckID ,
'tempdb' ,
20 AS Priority ,
'File Configuration' AS FindingsGroup ,
'TempDB on C Drive' AS Finding ,
'https://BrentOzar.com/go/cdrive' AS URL ,
CASE WHEN growth > 0
THEN ( 'The tempdb database has files on the C drive. TempDB frequently grows unpredictably, putting your server at risk of running out of C drive space and crashing hard. C is also often much slower than other drives, so performance may be suffering.' )
ELSE ( 'The tempdb database has files on the C drive. TempDB is not set to Autogrow, hopefully it is big enough. C is also often much slower than other drives, so performance may be suffering.' )
END AS Details
FROM sys.master_files
WHERE UPPER(LEFT(physical_name, 1)) = 'C'
AND DB_NAME(database_id) = 'tempdb';
END;
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 26 )
AND SERVERPROPERTY('EngineEdition') <> 8 /* Azure Managed Instances */
BEGIN
IF @Debug IN (1, 2) RAISERROR('Running CheckId [%d].', 0, 1, 26) WITH NOWAIT;
INSERT INTO #BlitzResults
( CheckID ,
DatabaseName ,
Priority ,
FindingsGroup ,
Finding ,
URL ,
Details
)
SELECT DISTINCT
26 AS CheckID ,
DB_NAME(database_id) AS DatabaseName ,
20 AS Priority ,
'Reliability' AS FindingsGroup ,
'User Databases on C Drive' AS Finding ,
'https://BrentOzar.com/go/cdrive' AS URL ,
( 'The ' + DB_NAME(database_id)
+ ' database has a file on the C drive. Putting databases on the C drive runs the risk of crashing the server when it runs out of space.' ) AS Details
FROM sys.master_files
WHERE UPPER(LEFT(physical_name, 1)) = 'C'
AND DB_NAME(database_id) NOT IN ( 'master',
'model', 'msdb',
'tempdb' )
AND DB_NAME(database_id) NOT IN (
SELECT DISTINCT
DatabaseName
FROM #SkipChecks
WHERE CheckID IS NULL OR CheckID = 26 );
END;
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 27 )
BEGIN
IF @Debug IN (1, 2) RAISERROR('Running CheckId [%d].', 0, 1, 27) WITH NOWAIT;
INSERT INTO #BlitzResults
( CheckID ,
DatabaseName ,
Priority ,
FindingsGroup ,
Finding ,
URL ,
Details
)
SELECT 27 AS CheckID ,
'master' AS DatabaseName ,
200 AS Priority ,
'Informational' AS FindingsGroup ,
'Tables in the Master Database' AS Finding ,
'https://BrentOzar.com/go/mastuser' AS URL ,
( 'The ' + name
+ ' table in the master database was created by end users on '
+ CAST(create_date AS VARCHAR(20))
+ '. Tables in the master database may not be restored in the event of a disaster.' ) AS Details
FROM master.sys.tables
WHERE is_ms_shipped = 0
AND name NOT IN ('CommandLog','SqlServerVersions');
END;
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 28 )
BEGIN
IF @Debug IN (1, 2) RAISERROR('Running CheckId [%d].', 0, 1, 28) WITH NOWAIT;
INSERT INTO #BlitzResults
( CheckID ,
DatabaseName ,
Priority ,
FindingsGroup ,
Finding ,
URL ,
Details
)
SELECT 28 AS CheckID ,
'msdb' AS DatabaseName ,
200 AS Priority ,
'Informational' AS FindingsGroup ,
'Tables in the MSDB Database' AS Finding ,
'https://BrentOzar.com/go/msdbuser' AS URL ,
( 'The ' + name
+ ' table in the msdb database was created by end users on '
+ CAST(create_date AS VARCHAR(20))
+ '. Tables in the msdb database may not be restored in the event of a disaster.' ) AS Details
FROM msdb.sys.tables
WHERE is_ms_shipped = 0 AND name NOT LIKE '%DTA_%';
END;
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 29 )
BEGIN
IF @Debug IN (1, 2) RAISERROR('Running CheckId [%d].', 0, 1, 29) WITH NOWAIT;
INSERT INTO #BlitzResults
( CheckID ,
DatabaseName ,
Priority ,
FindingsGroup ,
Finding ,
URL ,
Details
)
SELECT 29 AS CheckID ,
'model' AS DatabaseName ,
200 AS Priority ,
'Informational' AS FindingsGroup ,
'Tables in the Model Database' AS Finding ,
'https://BrentOzar.com/go/model' AS URL ,
( 'The ' + name
+ ' table in the model database was created by end users on '
+ CAST(create_date AS VARCHAR(20))
+ '. Tables in the model database are automatically copied into all new databases.' ) AS Details
FROM model.sys.tables
WHERE is_ms_shipped = 0;
END;
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 30 )
BEGIN
IF ( SELECT COUNT(*)
FROM msdb.dbo.sysalerts
WHERE severity BETWEEN 19 AND 25
) < 7
BEGIN
IF @Debug IN (1, 2) RAISERROR('Running CheckId [%d].', 0, 1, 30) WITH NOWAIT;
INSERT INTO #BlitzResults
( CheckID ,
Priority ,
FindingsGroup ,
Finding ,
URL ,
Details
)
SELECT 30 AS CheckID ,
200 AS Priority ,
'Monitoring' AS FindingsGroup ,
'Not All Alerts Configured' AS Finding ,
'https://BrentOzar.com/go/alert' AS URL ,
( 'Not all SQL Server Agent alerts have been configured. This is a free, easy way to get notified of corruption, job failures, or major outages even before monitoring systems pick it up.' ) AS Details;
END;
END;
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 59 )
BEGIN
IF EXISTS ( SELECT *
FROM msdb.dbo.sysalerts
WHERE enabled = 1
AND COALESCE(has_notification, 0) = 0
AND (job_id IS NULL OR job_id = 0x))
BEGIN
IF @Debug IN (1, 2) RAISERROR('Running CheckId [%d].', 0, 1, 59) WITH NOWAIT;
INSERT INTO #BlitzResults
( CheckID ,
Priority ,
FindingsGroup ,
Finding ,
URL ,
Details
)
SELECT 59 AS CheckID ,
200 AS Priority ,
'Monitoring' AS FindingsGroup ,
'Alerts Configured without Follow Up' AS Finding ,
'https://BrentOzar.com/go/alert' AS URL ,
( 'SQL Server Agent alerts have been configured but they either do not notify anyone or else they do not take any action. This is a free, easy way to get notified of corruption, job failures, or major outages even before monitoring systems pick it up.' ) AS Details;
END;
END;
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 96 )
BEGIN
IF NOT EXISTS ( SELECT *
FROM msdb.dbo.sysalerts
WHERE message_id IN ( 823, 824, 825 ) )
BEGIN;
IF @Debug IN (1, 2) RAISERROR('Running CheckId [%d].', 0, 1, 96) WITH NOWAIT;
INSERT INTO #BlitzResults
( CheckID ,
Priority ,
FindingsGroup ,
Finding ,
URL ,
Details
)
SELECT 96 AS CheckID ,
200 AS Priority ,
'Monitoring' AS FindingsGroup ,
'No Alerts for Corruption' AS Finding ,
'https://BrentOzar.com/go/alert' AS URL ,
( 'SQL Server Agent alerts do not exist for errors 823, 824, and 825. These three errors can give you notification about early hardware failure. Enabling them can prevent you a lot of heartbreak.' ) AS Details;
END;
END;
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 61 )
BEGIN
IF NOT EXISTS ( SELECT *
FROM msdb.dbo.sysalerts
WHERE severity BETWEEN 19 AND 25 )
BEGIN
IF @Debug IN (1, 2) RAISERROR('Running CheckId [%d].', 0, 1, 61) WITH NOWAIT;
INSERT INTO #BlitzResults
( CheckID ,
Priority ,
FindingsGroup ,
Finding ,
URL ,
Details
)
SELECT 61 AS CheckID ,
200 AS Priority ,
'Monitoring' AS FindingsGroup ,
'No Alerts for Sev 19-25' AS Finding ,
'https://BrentOzar.com/go/alert' AS URL ,
( 'SQL Server Agent alerts do not exist for severity levels 19 through 25. These are some very severe SQL Server errors. Knowing that these are happening may let you recover from errors faster.' ) AS Details;
END;
END;
--check for disabled alerts
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 98 )
BEGIN
IF EXISTS ( SELECT name
FROM msdb.dbo.sysalerts
WHERE enabled = 0 )
BEGIN
IF @Debug IN (1, 2) RAISERROR('Running CheckId [%d].', 0, 1, 98) WITH NOWAIT;
INSERT INTO #BlitzResults
( CheckID ,
Priority ,
FindingsGroup ,
Finding ,
URL ,
Details
)
SELECT 98 AS CheckID ,
200 AS Priority ,
'Monitoring' AS FindingsGroup ,
'Alerts Disabled' AS Finding ,
'https://BrentOzar.com/go/alert' AS URL ,
( 'The following Alert is disabled, please review and enable if desired: '
+ name ) AS Details
FROM msdb.dbo.sysalerts
WHERE enabled = 0;
END;
END;
--check for alerts that do NOT include event descriptions in their outputs via email/pager/net-send
IF NOT EXISTS (
SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL
AND CheckID = 219
)
BEGIN;
IF @Debug IN (1, 2)
BEGIN;
RAISERROR ('Running CheckId [%d].', 0, 1, 219) WITH NOWAIT;
END;
INSERT INTO #BlitzResults (
CheckID
,[Priority]
,FindingsGroup
,Finding
,[URL]
,Details
)
SELECT 219 AS CheckID
,200 AS [Priority]
,'Monitoring' AS FindingsGroup
,'Alerts Without Event Descriptions' AS Finding
,'https://BrentOzar.com/go/alert' AS [URL]
,('The following Alert is not including detailed event descriptions in its output messages: ' + QUOTENAME([name])
+ '. You can fix it by ticking the relevant boxes in its Properties --> Options page.') AS Details
FROM msdb.dbo.sysalerts
WHERE [enabled] = 1
AND include_event_description = 0 --bitmask: 1 = email, 2 = pager, 4 = net send
;
END;
--check whether we have NO ENABLED operators!
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 31 )
BEGIN;
IF NOT EXISTS ( SELECT *
FROM msdb.dbo.sysoperators
WHERE enabled = 1 )
BEGIN
IF @Debug IN (1, 2) RAISERROR('Running CheckId [%d].', 0, 1, 31) WITH NOWAIT;
INSERT INTO #BlitzResults
( CheckID ,
Priority ,
FindingsGroup ,
Finding ,
URL ,
Details
)
SELECT 31 AS CheckID ,
200 AS Priority ,
'Monitoring' AS FindingsGroup ,
'No Operators Configured/Enabled' AS Finding ,
'https://BrentOzar.com/go/op' AS URL ,
( 'No SQL Server Agent operators (emails) have been configured. This is a free, easy way to get notified of corruption, job failures, or major outages even before monitoring systems pick it up.' ) AS Details;
END;
END;
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 34 )
BEGIN
IF EXISTS ( SELECT *
FROM sys.all_objects
WHERE name = 'dm_db_mirroring_auto_page_repair' )
BEGIN
IF @Debug IN (1, 2) RAISERROR('Running CheckId [%d].', 0, 1, 34) WITH NOWAIT;
SET @StringToExecute = 'INSERT INTO #BlitzResults
(CheckID,
DatabaseName,
Priority,
FindingsGroup,
Finding,
URL,
Details)
SELECT DISTINCT
34 AS CheckID ,
db.name ,
1 AS Priority ,
''Corruption'' AS FindingsGroup ,
''Database Corruption Detected'' AS Finding ,
''https://BrentOzar.com/go/repair'' AS URL ,
( ''Database mirroring has automatically repaired at least one corrupt page in the last 30 days. For more information, query the DMV sys.dm_db_mirroring_auto_page_repair.'' ) AS Details
FROM (SELECT rp2.database_id, rp2.modification_time
FROM sys.dm_db_mirroring_auto_page_repair rp2
WHERE rp2.[database_id] not in (
SELECT db2.[database_id]
FROM sys.databases as db2
WHERE db2.[state] = 1
) ) as rp
INNER JOIN master.sys.databases db ON rp.database_id = db.database_id
WHERE rp.modification_time >= DATEADD(dd, -30, GETDATE()) OPTION (RECOMPILE);';
IF @Debug = 2 AND @StringToExecute IS NOT NULL PRINT @StringToExecute;
IF @Debug = 2 AND @StringToExecute IS NULL PRINT '@StringToExecute has gone NULL, for some reason.';
EXECUTE(@StringToExecute);
END;
END;
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 89 )
BEGIN
IF EXISTS ( SELECT *
FROM sys.all_objects
WHERE name = 'dm_hadr_auto_page_repair' )
BEGIN
IF @Debug IN (1, 2) RAISERROR('Running CheckId [%d].', 0, 1, 89) WITH NOWAIT;
SET @StringToExecute = 'INSERT INTO #BlitzResults
(CheckID,
DatabaseName,
Priority,
FindingsGroup,
Finding,
URL,
Details)
SELECT DISTINCT
89 AS CheckID ,
db.name ,
1 AS Priority ,
''Corruption'' AS FindingsGroup ,
''Database Corruption Detected'' AS Finding ,
''https://BrentOzar.com/go/repair'' AS URL ,
( ''Availability Groups has automatically repaired at least one corrupt page in the last 30 days. For more information, query the DMV sys.dm_hadr_auto_page_repair.'' ) AS Details
FROM sys.dm_hadr_auto_page_repair rp
INNER JOIN master.sys.databases db ON rp.database_id = db.database_id
WHERE rp.modification_time >= DATEADD(dd, -30, GETDATE()) OPTION (RECOMPILE) ;';
IF @Debug = 2 AND @StringToExecute IS NOT NULL PRINT @StringToExecute;
IF @Debug = 2 AND @StringToExecute IS NULL PRINT '@StringToExecute has gone NULL, for some reason.';
EXECUTE(@StringToExecute);
END;
END;
IF NOT EXISTS ( SELECT 1
FROM #SkipChecks
WHERE DatabaseName IS NULL AND CheckID = 90 )
BEGIN
IF EXISTS ( SELECT *
FROM msdb.sys.all_objects
WHERE name = 'suspect_pages' )
BEGIN
IF @Debug IN (1, 2) RAISERROR('Running CheckId [%d].', 0, 1, 90)