Clear abandoned statistics

Based on Brent Ozar's query along with a cursor to clear them
https://www.brentozar.com/blitz/hypothetical-indexes-index-tuning-wizard/

Eliminates stats that cannot be updated.


USE [dbname]
GO

DECLARE @tblDrops TABLE  (
tbl  nvarchar(100)
, Index_or_Statistics nvarchar(200)
, DropCmd nvarchar(500)
);
DECLARE @sqlDrop nvarchar(500);


WITH hi AS (
SELECT
QUOTENAME(SCHEMA_NAME(o.[schema_id])) +'.'+ QUOTENAME(OBJECT_NAME(i.[object_id])) AS [Table]
, QUOTENAME([i].[name]) AS [Index_or_Statistics]
, 1 AS [Type]
FROM sys.[indexes] AS [i]
JOIN sys.[objects] AS [o]
ON i.[object_id] = o.[object_id]
WHERE 1=1
AND INDEXPROPERTY(i.[object_id], i.[name], 'IsHypothetical') = 1
AND OBJECTPROPERTY([o].[object_id], 'IsUserTable') = 1

UNION ALL

SELECT
QUOTENAME(SCHEMA_NAME(o.[schema_id])) +'.'+ QUOTENAME(OBJECT_NAME(i.[object_id])) AS [Table]
, QUOTENAME([i].[name]) AS [Index_or_Statistics]
, 2 AS [Type]
FROM sys.[stats] AS [s]
JOIN sys.[objects] AS [o]
ON [o].[object_id] = [s].[object_id]
WHERE [s].[user_created] = 0
AND [o].[name] LIKE '[_]dta[_]%'
AND OBJECTPROPERTY([o].[object_id], 'IsUserTable') = 1
)
INSERT INTO @tblDrops
SELECT
[hi].[Table] ,
[hi].[Index_or_Statistics] ,
CASE [hi].[Type]
       WHEN 1 THEN 'DROP INDEX ' + [hi].[Index_or_Statistics] + ' ON ' + [hi].[Table] + ';'
       WHEN 2 THEN 'DROP STATISTICS ' + hi.[Table] + '.' + hi.[Index_or_Statistics] + ';'
       ELSE 'DEAR GOD WHAT HAVE YOU DONE?'
END AS [T-SQL Drop Command]
FROM [hi]

SELECT DropCmd
FROM @tblDrops;

/*
--Uncomment to run the purge
DECLARE db_cursor CURSOR FOR 
SELECT DropCmd
FROM @tblDrops;

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @sqlDrop

WHILE @@FETCH_STATUS = 0  
BEGIN  
      EXEC (@sqlDrop);

      FETCH NEXT FROM db_cursor INTO @sqlDrop
END 

CLOSE db_cursor  
DEALLOCATE db_cursor 
*/

Move and size temp DB

USE master;

SELECT
name
, physical_name 
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');

/*
ALTER DATABASE [tempdb]
MODIFY FILE ( NAME = tempdev, FILENAME = 'F:\SqlTmp01\tempdb.mdf', SIZE = 2500MB, FILEGROWTH=0)
GO

ALTER DATABASE [tempdb]
ADD FILE ( NAME = tempdb2, FILENAME = 'F:\SqlTmp01\tempdb2.ndf', SIZE = 2500MB, FILEGROWTH=0)
GO

ALTER DATABASE [tempdb]
MODIFY FILE ( NAME = templog, FILENAME = 'G:\SqlLog01\templog.ldf')
GO
*/

Waitstates Analysis

Cool script from SQLServerCentral.com for looking at waitstates

-- Isolate top waits for server instance since last restart or statistics clear
WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK'
,'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE'
,'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT'
,'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT'
,'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'))
SELECT W1.wait_type,
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 99 OPTION (RECOMPILE); -- percentage threshold
GO

View Index Usage by DB and Table

--View Index Usage by DB and Table

USE [DB Name];

DECLARE @DBName nvarchar(50) = '[db name]';

SELECT
OBJECT_NAME(ustats.object_id) AS TableName
, idx.name
, user_seeks
, user_scans
, user_lookups
, user_updates
, last_user_seek
, last_user_scan
, last_user_lookup
, last_user_update
FROM sys.dm_db_index_usage_stats ustats
INNER JOIN sys.indexes idx
ON ustats.object_id = idx.object_id
AND ustats.index_id = idx.index_id
WHERE database_id = DB_ID(@DBName)
AND OBJECT_NAME(ustats.object_id) IN ( '', '');

Find Missing Index Reccomendations

Taken from DotNetVibes - This is derived from this excellent site with a couple very small modifications. I recommend his site.

I have also re-arranged the columns to match SAP DBA cockpit due to the limitations of DBACOCKPIT with SQL Server indexes.

USE master;

SELECT   DISTINCT 
mid.[statement] AS [Database.Schema.Table]
,OBJECT_NAME(mid.[object_id]) AS [Table Name]
,CONVERT(DECIMAL(18, 2) , user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 )) AS [index_advantage]
,migs.avg_total_user_cost
,migs.avg_user_impact
,migs.user_seeks
,mid.equality_columns
,mid.inequality_columns
,mid.included_columns
,migs.last_user_seek
,migs.last_user_scan
,migs.unique_compiles
,migs.avg_total_system_cost
,migs.avg_total_user_cost
, (SELECT MAX(p.[rows]) FROM sys.partitions p WITH ( NOLOCK) WHERE [object_id] = mid.[object_id])  AS [Table Rows]
,'CREATE INDEX [Missing_IXNC_' + OBJECT_NAME(mid.[object_id], mid.[database_id]) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(mid.[equality_columns], ''), ', ', '_'), '[', ''), ']', '') + CASE
    WHEN mid.[equality_columns] IS NOT NULL
        AND mid.[inequality_columns] IS NOT NULL
        THEN '_'
    ELSE ''
    END
+ REPLACE(REPLACE(REPLACE(ISNULL(mid.[inequality_columns], ''), ', ', '_'), '[', ''), ']', '') + '_' + LEFT(CAST(NEWID() AS [nvarchar](64)), 5) + ']' + ' ON ' + mid.[statement] + ' (' + ISNULL(mid.[equality_columns], '') + CASE
    WHEN mid.[equality_columns] IS NOT NULL
        AND mid.[inequality_columns] IS NOT NULL
        THEN ','
    ELSE ''
    END
+ ISNULL(mid.[inequality_columns], '') + ')'
+ ISNULL(' INCLUDE (' + mid.[included_columns] + ')', '')
AS [ProposedIndex]
,CAST(CURRENT_TIMESTAMP AS [smalldatetime]) AS [CollectionDate]
FROM sys.dm_db_missing_index_group_stats AS migs WITH ( NOLOCK )
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH ( NOLOCK ) ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH ( NOLOCK ) ON mig.index_handle = mid.index_handle
INNER JOIN sys.partitions AS p WITH ( NOLOCK ) ON p.[object_id] = mid.[object_id]
WHERE    mid.database_id = DB_ID()
ORDER BY index_advantage DESC
OPTION ( RECOMPILE );

Scripts for moving SQL Server DB files

These are simple but I always forget them.If you are using Always On make sure you run this on all machines and move the files on all machines.Unless you are replicating master.


USE master;

SELECT
name
, physical_name 
FROM sys.master_files
WHERE database_id = DB_ID('[DB Name]');

ALTER DATABASE [DB Name]
MODIFY FILE ( NAME = [Logical Name of File ], FILENAME = '[Filepath]')
GO

Script to Check is a Schema Exists and Create New

USE [master]
GO

IF NOT EXISTS(SELECT schema_id FROM sys.schemas where name = 'DBA')
BEGIN
DECLARE @SQLcmd NVARCHAR(50) = 'CREATE SCHEMA [DBA] AUTHORIZATION [db_owner]';
EXEC sp_executesql @SQLcmd;
END

Return a List of All Dabases with their Storage Amounts

USE [master]
GO

IF NOT EXISTS(SELECT schema_id FROM sys.schemas WHERE name = 'DBA')
BEGIN 
DECLARE @SQLcmd NVARCHAR(50) = 'CREATE SCHEMA [DBA] AUTHORIZATION [db_owner]';
EXEC sp_executesql @SQLcmd;
END 

IF NOT EXISTS(SELECT object_id FROM sys.tables WHERE name ='tDataDiskUsageHistory')
BEGIN
CREATE TABLE DBA.tDataDiskUsageHistory
(
DatabaseName nvarchar(50) NOT NULL,
DateTested date NOT NULL,
TimeTested time(7) NOT NULL,
ReservedDbSpace decimal(12, 2) NULL,
UsedDbSpace decimal(12, 2) NULL,
FreeSpacePercent decimal(12, 2) NULL,
LogSpaceUsed decimal(12, 2) NULL
)  ON [PRIMARY];

ALTER TABLE DBA.tDataDiskUsageHistory ADD CONSTRAINT
PK_tDataDiskUsageHistory PRIMARY KEY CLUSTERED
(
DatabaseName,
DateTested,
TimeTested
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];

END
GO

USE [master]
GO

CREATE PROCEDURE DBA.DataDiskUsage_Record_AllDatabases
(@DaysToRetain int = 0)
AS

DECLARE @loop INT;
DECLARE @count INT;
DECLARE @db_name NVARCHAR(50);
DECLARE @exec_string NVARCHAR(1000);
DECLARE @id INT;
DECLARE @free_space INT;

--Create tabel to hold database list
DECLARE @current_databases TABLE
(
 id INT IDENTITY(1,1)
 , name nvarchar(50)
);

--create a table for holding all database free space
DECLARE @space_used TABLE
(
id INT IDENTITY(1,1)
, database_name NVARCHAR(50)
, reserved_db_space DECIMAL(12,2)
, used_db_space DECIMAL(12,2)
, free_space_percent   DECIMAL(5,3)
, log_space_used DECIMAL(12,2)
);

--get the names of all user databases
INSERT INTO @current_databases
SELECT name
FROM sys.databases
WHERE database_id &gt 4 --eliminate system databases
AND snapshot_isolation_state = 0; --eliminate snapshots

SET @loop = 1;
SELECT @count = MAX(id) FROM @current_databases;

--loop through each database and get the data file free space    
WHILE @loop <= @count
 BEGIN

 --get our working db
 SELECT @db_name = name
 FROM @current_databases
 WHERE id = @loop;

 SET @exec_string = 'USE ' + @db_name + ';

  DECLARE @DataSpaceReserved DECIMAL(12,2) = 0.0;
  DECLARE @DataSpaceUsed DECIMAL(12,2) = 0.0;
  DECLARE @LogSpaceUsed DECIMAL(12,2) = 0.0;

  SELECT
  @LogSpaceUsed = SUM(f.size/128.0)
  FROM sys.sysfiles f
  WHERE groupid = 0;-- log files

  SELECT
  @DataSpaceReserved = SUM(f.size/128.0)
  ,  @DataSpaceUsed = SUM(CAST(FILEPROPERTY(f.name,  ''spaceused'') AS int)/128.0)
  FROM sys.sysfiles f
  WHERE groupid != 0;-- data files

  SELECT
  ''' + @db_name + ''' as database_name
  , @DataSpaceReserved
  , @DataSpaceUsed
  , 100 * (@DataSpaceReserved - @DataSpaceUsed)/@DataSpaceReserved
  , @LogSpaceUsed';

print @exec_string;

 --pull our space data back and insert into our holding table
 INSERT @space_used
 EXECUTE (@exec_string);

 --next please            
 SET @loop = @loop + 1

END


INSERT INTO [DBA].[tDataDiskUsageHistory]
           ([DatabaseName]
           ,[DateTested]
           ,[TimeTested]
           ,[ReservedDbSpace]
           ,[UsedDbSpace]
           ,[FreeSpacePercent]
           ,[LogSpaceUsed])
SELECT
database_name
,convert (date ,getdate()) AS QueryDate
,convert (time ,getdate()) AS QueryTime
, reserved_db_space
, used_db_space
, free_space_percent
, log_space_used
FROM @space_used;

IF @DaysToRetain > 0
BEGIN
DELETE FROM DBA.tDataDiskUsageHistory WHERE DATEDIFF(d, DateTested, GETDATE()) > @DaysToRetain
END

Index Usage Query Using DMV's

SELECT
obj.name AS TableName
, idx.name AS IndexName
, ius.index_id
, ius.user_seeks
, ius.user_scans
, ius.user_lookups
, ius.user_updates
,  ius.user_seeks + ius.user_scans + ius.user_lookups + ius.user_updates AS TotalUserUsages
, last_user_seek
, last_user_scan
, last_user_lookup
, last_user_update
, idx.fill_factor
, idx.is_padded
, idx.is_primary_key
, idx.is_disabled
FROM sys.dm_db_index_usage_stats ius
INNER JOIN sys.objects obj
ON ius.object_id  = obj.object_id 
INNER JOIN sys.indexes idx
ON ius.object_id  = idx.object_id 
AND ius.index_id = idx.index_id
--WHERE obj.name = '[table name]'
--This will find indexes without user usage
WHERE ius.user_seeks + ius.user_scans + ius.user_lookups + ius.user_updates = 0

ORDER BY is_disabled, index_id

Defining an Index for the Rest of the World

Picture a data as just a pile of information like all the chapters of a book on the floor in a pile.

Indexes are like the indexes of a book

1. One usually puts the book in the order the author intended (Primary Key)
2. Several other might order specific things like give me a way to find the first page a character appears on or all the fight scenes. (indexes)

Each of these allow you to either get a collection of pages or a specific value without having to start at page one and make a list of the values you want. For example, I want to know when Chad is first mentioned in a book. Without the index I have to start at page one of the book and read it until I find chad even if he is only on the last page. With the index of characters I would call up a list of the characters by name and it would tell me the page number. As you can see the lookup took a lot less time and fewer resources.

That is the good. In life nothing is free. For the index to be any good it has to be maintained when data changes especially inserts and deletes. Every index slows the time for these down. The key then is to only add indexes that have value to what the users are doing. Therefore we look at how often it is used before making a choice. For example, if you are only going to look for Chad once when you read the book the first time there is no value in making and maintaining a list of character entry points so you wouldn’t waste time with it.

Query to review job status

Queries all enabled agent jobs and returns either jobs not in success status or jobs where the run time is greater than the run time of the last X runs + the standard deviation of the last X runs. The default is 10 but is set in the opening @TargetDate statement.This will reduce the number of jobs that need interrogating.


DECLARE @TargetDate DATE = DATEADD(d, -10, GETDATE());
--PRINT @TargetDate;
--PRINT FORMAT(MONTH(@TargetDate), 'd2');
DECLARE @RunTo INT = CAST(CAST(YEAR(@TargetDate) AS NCHAR(4)) + FORMAT(MONTH(@TargetDate), 'd2') + FORMAT(DAY(@TargetDate), 'd2') AS INT);

DECLARE @tJobHistory TABLE 
(job_id uniqueidentifier
, avgRunDurationLast10 decimal(10,2)
, stdRunDurationLast10 decimal(10,2)
);

WITH cteLast10DaysJobs AS
(
SELECT
job_id
, run_duration
FROM msdb..sysjobhistory
WHERE step_id = 0
AND run_status = 1
AND run_date >= @RunTo
)
INSERT INTO @tJobHistory
SELECT
job_id
,AVG(CAST (run_duration as float))as average_run_duration
,COALESCE(STDEV(CAST (run_duration as float)), 0) as stddev_run_duration
FROM cteLast10DaysJobs
--WHERE  job_id = '0AF6CA6E-E573-4FB8-B065-EE9994F3898D'
GROUP BY job_id

SELECT
sj.name AS Job_Name
,sjh.[message] AS Job_Message
, CASE sjh.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Success'
        WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
END AS Job_Status
,sjh.run_date AS Last_Run_Date
,sjh.run_time AS Job_Last_Run_Time
,sjh.run_duration AS Total_Run_Time
,jh.avgRunDurationLast10 AS Avg_Run_Time_Last_10_Runs
, jh.stdRunDurationLast10 AS  Stddev_Run_Time_Last_10_Runs
, sjh.run_duration - jh.avgRunDurationLast10 - jh.stdRunDurationLast10 AS Run_Time_Greater_Then_Dev
FROM msdb..sysjobs sj
INNER join @tJobHistory jh
ON sj.job_id = jh.job_id
INNER join msdb..sysjobhistory sjh
ON sj.job_id = sjh.job_id
WHERE sj.enabled = 1
AND sjh.step_id = 0
AND (sjh.run_duration - round(jh.avgRunDurationLast10,2) > jh.stdRunDurationLast10  OR run_status != 1)
AND instance_id in (SELECT MAX(instance_id) FROM msdb..sysjobhistory GROUP BY by job_id)
ORDER BY
run_date desc
, sjh.run_duration - jh.avgRunDurationLast10 - jh.stdRunDurationLast10 DESC;

SQL Jobs change job step proxy

The purpose of this script is to set a proxy on each job step to allow the owner of the job to not have access to the target database. This applies when a separate server is used for ETL and data storage. This also has a strong effect on the performance of the database server as significantly less memory has to be allocated to the Integration Services service.

This assume you can segregate the jobs by job category to make sure only the targeted step are changed. This could also be done easily by job owner.


USE MSDB
GO

SELECT * FROM sysproxies;
--Use this to restrict the jobs to only the categories you want to change
DECLARE @categoryMatch nvarchar(30) = 'test_%';
--Set the proxy value to the values desired in the above query
DECLARE @proxy_value nvarchar(10) = 2;
DECLARE @CurJobId nvarchar(50);
DECLARE @curStepId nvarchar(50);

SELECT sj.job_id, sjs.step_id, proxy_id
FROM sysjobs sj
INNER JOIN sysjobsteps sjs
ON sj.job_id = sjs.job_id
WHERE category_id IN ( select category_id FROM syscategories WHERE  name like @categoryMatch)
AND (sjs.proxy_id != @proxy_value OR proxy_id IS NULL);

DECLARE db_cursor CURSOR FOR
SELECT sj.job_id, sjs.step_id
FROM sysjobs sj
INNER JOIN  sysjobsteps sjs
ON sj.job_id = sjs.job_id
WHERE  category_id IN (SELECT category_id FROM syscategories WHERE  name like @categoryMatch)
AND (sjs.proxy_id != @proxy_value OR proxy_id IS NULL);


OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @CurJobId, @curStepId

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Job_ID:  ' + @curJobId + '     Job Step:  ' + @curStepId;
EXEC dbo.sp_update_jobstep
@job_id = @curJobId
,@step_id = @curStepId
,@proxy_id = @proxy_value;

    FETCH NEXT FROM db_cursor INTO @CurJobId, @curStepId
END

CLOSE db_cursor
DEALLOCATE db_cursor

Best update database owner tools

In my never ending quest to automate the setting of DB owners here is the best script yet. It assumes there is a user called [Domain User]/[Server name]Server running the server.

USE Master
GO

DECLARE @userName nvarchar(50);
DECLARE @userNamePostfix nvarchar(10) = 'Server'; --'Admin';

IF  CHARINDEX('\', @@SERVERNAME, 0) > 0
SET @userName = '[Domain User]\' +  SUBSTRING(@@SERVERNAME, 0, CHARINDEX('\', @@SERVERNAME, 0)) + @userNamePostfix ;
ELSE
SET @userName = '[Domain User]\' +  @@SERVERNAME + @userNamePostfix;

--DECLARE @userName nvarchar(50) = '[Domain User]\' +  SUBSTRING(@@SERVERNAME, 0, CHARINDEX('\', @@SERVERNAME, 0)) + 'Admin';
-- Use this section to override if the pattern does not work
--SET @userName = 'override User';

PRINT @userName;

--Require the user to exist and be a sysadmin
IF EXISTS(SELECT sid FROM sys.syslogins WHERE name = @userName AND sysAdmin = 1)
BEGIN

--Should be modified to ignore DBs which are not assigned to a defaulte value
DECLARE @prefix  nvarchar(200) = 'IF ''?'' NOT IN (SELECT name from sys.databases WHERE owner_sid = SUSER_ID(''' + @userName + ''') OR DB_NAME(database_id) IN (''master'', ''msdb'',''model'', ''tempdb'', ''distribution'')) BEGIN  ';
DECLARE @postfix  nvarchar(50) = ' END';
DECLARE @cmd  nvarchar(512);

SET @cmd =  @prefix + 'PRINT ''?''; USE ?  exec sp_changedbowner @loginame=''' + @userName + ''' ' + @postfix;
PRINT @cmd;
EXEC master.sys.sp_MSforeachdb  @command1 = @cmd;
PRINT 'Successfully set DB owner to ' + @userName;
END
ELSE
BEGIN
  PRINT @userName + ' NOT found';
END

Fix for Chrome in Reporting Services 2012 and 2008R2

Add the script below to the file:
In 2008R2
...\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportManager\Pages\Report.aspx

In 2012
...\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\ReportManager\Pages\Report.aspx

Find the Number of Rows in Database Tables

Had a need to track empty tables in a DB to see if converted systems were correctly hooked up and wanted to add filters.Sorry I didn't keep the link to the original website.

SELECT
QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
, SUM(sdmvPTNS.row_count) AS [RowCount]
FROM sys.objects AS sOBJ
INNER JOIN sys.dm_db_partition_stats AS sdmvPTNS
ON sOBJ.object_id = sdmvPTNS.object_id
WHERE 
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
      AND sdmvPTNS.index_id < 2
--Find all by schema
-- AND SCHEMA_NAME(sOBJ.schema_id) = ''
GROUP BY
      sOBJ.schema_id
      , sOBJ.name
--Only find empty tables
--HAVING SUM(sdmvPTNS.row_count) = 0
ORDER BY [TableName]
, [RowCount];
GO

Add Log Percent Used Alert to all non System DBs

in my never ending quest to automate more monitoring I have added this to my pre-production deployment package. It assumes a Operator group of the name DBgroup has been created to transmit the alerts.
Also Database Mail must be set up on the agent.

USE [msdb]
GO

DECLARE @RemoveExisting as bit = 1; --Set to 1 to purge any existing

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.sys.databases
WHERE name NOT IN ('model','master', 'msdb','tempdb')

DECLARE @DBname nvarchar(max) = '';
DECLARE @AlertName nvarchar(max) = '';
DECLARE @AlertText nvarchar(max) = '';

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DBname

WHILE @@FETCH_STATUS = 0
BEGIN
SET @AlertName = @DBname  + N'_LogPercentUsed';
SET @AlertText = N'Databases|Percent Log Used|' + @DBname + '|>|90';

IF EXISTS(select id from sysalerts where name = @AlertName) AND  @RemoveExisting = 1
EXEC msdb.dbo.sp_delete_alert @AlertName;

IF NOT EXISTS(select id from sysalerts where name = @AlertName)
BEGIN
EXEC msdb.dbo.sp_add_alert @name= @AlertName ,
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=1715,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@performance_condition= @AlertText
, @job_id=N'00000000-0000-0000-0000-000000000000';

EXEC msdb.dbo.sp_add_notification @alert_name= @AlertName, @operator_name=N'DBGroup', @notification_method = 1;

END

  FETCH NEXT FROM db_cursor INTO @DBname
END

CLOSE db_cursor
DEALLOCATE db_cursor

Fail a jobs is any pre-requisite jobs have failed

USE [msdb]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/*
Used to detemine if any jobs in a category have failed
All jobs must be assigned to the same category

Parameters:
@JobCategoryName - The category name set up in hte local instance
@HoursToCheck - the number of hours back in history you want the call to look. DEFAULT = 24
@IsSuccessful - Whether or not any jobs meet the criteria
To fine all failed jobs put 0

RETURNS
0 = No Jobs in the tested status
1 = Jobs exist in the tested status

Example of use to fail a job when any jobs in the category have a failure
IF (SELECT [dbo].[CheckJobCategoryStatus] ('SAP_to_staging', 24, 0)) = 1
  BEGIN
    RAISERROR('SAP_to_staging pre-requisite has failed. Re-run all failed prerequisites', 16, 1)
  END
*/

CREATE FUNCTION [dbo].[CheckJobCategoryStatus]
(@JobCategoryName nvarchar(50)
, @HoursToCheck int = 24
, @IsSuccessful bit = 0
)
RETURNS bit
AS 
BEGIN
--DECLARE @JobCatagory nvarchar(50) = 'SAP_to_staging';
--DECLARE @HoursToCheck int = 24;
--DECLARE @IsSuccessful bit = 1;

DECLARE @IsCategoryStatusTrue bit = 0;

IF EXISTS(SELECT
SJ.NAME AS [Job Name]
,RUN_STATUS AS [Run Status]
,MAX(DBO.AGENT_DATETIME(RUN_DATE, RUN_TIME)) AS [Last Time Job Ran On]
FROM dbo.SYSJOBS SJ
LEFT OUTER JOIN dbo.SYSJOBHISTORY JH
ON SJ.job_id = JH.job_id
WHERE JH.step_id = 0
AND jh.run_status = @IsSuccessful
and category_id IN (select category_id
from dbo.syscategories
where name = @JobCategoryName)
and DATEDIFF(HH , DBO.AGENT_DATETIME(RUN_DATE, RUN_TIME), getdate()) <= @HoursToCheck
GROUP BY SJ.name, JH.run_status)
SET  @IsCategoryStatusTrue = 1;
ELSE
SET  @IsCategoryStatusTrue = 0;
RETURN @IsCategoryStatusTrue;
END


GO

Monitor Job Satus through script

Had a need to verify prerequisite site had run prior to downstream asynchronous jobs so I found and modified a couple queries.

USE [msdb]
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*
Used to detemine if any jobs in a category have failed and list them
All jobs must be assigned to the same category

Parameters:
@JobCategoryName - The category name set up in hte local instance
@HoursToCheck - the number of hours back in history you want the call to look. DEFAULT = 24
@IsSuccessful - Whether or not any jobs meet the criteria
To fine all failed jobs put 0

RETURNS
Table with 
Job Name
Success or Failure status
Date Time last run

Example of a table of all failed jobs in a category
SELECT * FROM [dbo].[JobCategoryStatusList] ('SAP_to_staging', 24, 1)
*/


CREATE FUNCTION [dbo].[JobCategoryStatusList]
(@JobCategory nvarchar(50)
, @HoursToCheck int = 24
, @IsSuccessful bit = 0
)
RETURNS @tAllJobsInStatus TABLE
(
JobName nvarchar(128)
, RunStatus bit
, LastTimeJobRan datetime
)
AS
BEGIN
--DECLARE @JobCatagory nvarchar(50) = 'SAP_to_staging';
--DECLARE @HoursToCheck int = 24;
--DECLARE @IsSuccessful bit = 1;
INSERT INTO @tAllJobsInStatus
SELECT
SJ.NAME AS [Job Name]
,RUN_STATUS AS [Run Status]
,MAX(DBO.AGENT_DATETIME(RUN_DATE, RUN_TIME)) AS [Last Time Job Ran On]
FROM dbo.SYSJOBS SJ
LEFT OUTER JOIN dbo.SYSJOBHISTORY JH
ON SJ.job_id = JH.job_id
WHERE JH.step_id = 0
AND jh.run_status = @IsSuccessful
and category_id IN (select category_id
from dbo.syscategories
where name = @JobCategory)
and DATEDIFF(HH , DBO.AGENT_DATETIME(RUN_DATE, RUN_TIME), getdate()) <= @HoursToCheck
GROUP BY SJ.name, JH.run_status
RETURN
END;

Yet a more improve bulk DB set up file

This on attempt to do more and protect the system DBs

This script will:
1. Change the DB owner
2. Set all to simple mode. Cannot figure out how to stop the tempdb message
3. Set all DBs to 2012 mode
4. Add the domain users as owners. Please use this as a prototype for other test and dev users
5. Shrink all logs to 500 MB to ensure there is plenty of space

Left to do standardize file growths, block create user when the user exists

USE Master
GO


DECLARE @prefix nvarchar(120) = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN  ';
DECLARE @postfix nvarchar(50) = ' END';
DECLARE @cmd nvarchar(512);

SET @cmd =  @prefix + 'PRINT ''?''; USE ? exec sp_changedbowner sa ' + @postfix;
PRINT @cmd;
EXEC master.sys.sp_MSforeachdb  @command1 = @cmd
PRINT 'Successfully set DB owner to sa';


SET @cmd =  @prefix + 'ALTER DATABASE ? SET RECOVERY SIMPLE;' + @postfix;
PRINT @cmd;
EXEC master.sys.sp_MSforeachdb  @command1 = @cmd;
PRINT 'Set all DBs to SIMPLE';

SET @cmd =  @prefix + 'ALTER DATABASE ? SET COMPATIBILITY_LEVEL = 110;'+ @postfix;
PRINT @cmd;
EXEC master.sys.sp_MSforeachdb  @command1 = @cmd;
PRINT 'Upgrade all DBs to 2012';

SET @cmd =  @prefix + 'Use ?; CREATE USER [[DOMAIN]\[USER]] WITH DEFAULT_SCHEMA = dbo;' + @postfix;
PRINT @cmd;
EXEC master.sys.sp_MSforeachdb  @command1 = @cmd;
PRINT 'Add group [DOMAIN]\[USER]';

SET @cmd =  @prefix + 'Use ?; ALTER ROLE db_owner ADD MEMBER [[DOMAIN]\[USER]];' + @postfix;
PRINT @cmd;
EXEC master.sys.sp_MSforeachdb  @command1 = @cmd;
PRINT 'Add db_owner [DOMAIN]\[USER]';

SET @cmd =  @prefix + 'Use ?; DBCC SHRINKFILE (N''?_log'' , 500);' + @postfix;
PRINT @cmd;
EXEC master.sys.sp_MSforeachdb  @command1 = @cmd;
PRINT 'Shrink all logs to 500 MB';

SQL Connection Error: "The target principal name is incorrect. Cannot generate SSPI context"

Welcome to the rabbit hole.

This can be caused when there are more than one entry for a SQL Server entry in Kerberos. Sometimes it is caused when a SQL Server is installed under one domain user and is then is switch to another.

Technet article: How to troubleshoot the "Cannot generate SSPI context" error message
https://support.microsoft.com/en-us/kb/811889?wa=wsignin1.0 

Really good description but no examples:
How Windows Server 2012 Eases the Pain of Kerberos Constrained Delegation, Part 2

Basically you delete the existing entries and make new ones. You have to be an AD admin to make the deletions.

Commands of use:

List Command 

setspn -L [Machine name if default instance]

C:\windows\system32>setspn -L wkonedev01

Registered ServicePrincipalNames for CN=WKONEDEV01,OU=Member Servers,DC=******,

DC=com:

        MSSQLSvc/WkOneDev01.******.com:1433

        MSSQLSvc/WkOneDev01.******.com

        WSMAN/wkonedev01.******.com

        TERMSRV/wkonedev01.******.com

        RestrictedKrbHost/wkonedev01.******.com

        HOST/wkonedev01.******.com

        WSMAN/WKONEDEV01

        TERMSRV/WKONEDEV01

        RestrictedKrbHost/WKONEDEV01

        HOST/WKONEDEV01

Delete Command

setspn -D MSSQLsvc/[Machine Name].[Domain].com:1433 [Domain]\[Domain User Name]

 C:\windows\system32>setspn -D MSSQLsvc/wkonedev01.******.com:1433 ******\wkone

dev01server

Unregistering ServicePrincipalNames for CN=wkonedev01Server,OU=Service Accounts,

DC=*******,DC=com

        MSSQLsvc/wkonedev01.*******.com:1433

Updated object

Safe Add Command

setspn -S MSSQLsvc/[Machine Name].[Domain].com:1433 [Domain]\[Domain User Name]

C:\windows\system32>setspn -A MSSQLsvc/wkonedev01.*******.com:1433 ********\wkone

dev01server

Registering ServicePrincipalNames for CN=wkonedev01Server,OU=Service Accounts,DC

=********,DC=com

        MSSQLsvc/wkonedev01.********.com:1433

Updated object

After the commands make sure AD is given time to update the DNS then run

C:>ipconfig /flushdns

C:>ipconfig /renew