Category Archives: Scripts

This section will store simple scripts

Drop and Recreate Synonyms

This script is useful to script out all synonyms on a database before restoring on top another copy from another environment.

It is also useful to script out what's there, examine it, and see if any modifications are needed. See this one to verify synonyms

Rememer to change the output to "results to text".

 

SET NOCOUNT ON
SELECT
'IF EXISTS (SELECT NAME FROM SYS.OBJECTS WHERE NAME = '''+NAME+''')
DROP SYNONYM ['+NAME+'];
CREATE SYNONYM ['+NAME+'] FOR '+BASE_OBJECT_NAME+'

'
FROM sys.synonyms


Verify Synonyms

The following script gets a list of the synonyms, parses them, and then the checks if the existence of the object on the sys.objects table.

Any errors encountered are assumed as "not a valid synonym"

/*
Author: Miguel Quintana

Description:
Reads all SYNONYMS on the database and test if they are valid.
It checks their EXISTENCE on the SYSOBJECTS table
and uses the Link Server if available

No configuration needed. Just run it under the DB that you are testing Synonyms

*/


-- this variables are for the cursor
DECLARE @NAME NVARCHAR(100)
  ,@SERVERNAME NVARCHAR(20)
  ,@DBName NVARCHAR(100)
  ,@SCHEMA NVARCHAR(100)
  ,@OBJECTNAME NVARCHAR(1000)
  ,@BaseObjectName NVARCHAR(1000)
-- This are to process the data
DECLARE @CMD NVARCHAR(MAX)
DECLARE @RESULTS TABLE (
  VALUE NVARCHAR(100)
  ,SYNONYM NVARCHAR(100)
  ,BaseObjectName NVARCHAR(100)
  ,Link_SERVER NVARCHAR(100)
  )
DECLARE CUR CURSOR
FOR
SELECT NAME
  ,PARSENAME(base_object_name, 4) ServerName
  ,COALESCE(PARSENAME(base_object_name, 3), DB_NAME(DB_ID())) AS dbName
  ,COALESCE(PARSENAME(base_object_name, 2), SCHEMA_NAME(SCHEMA_ID())) AS schemaName
  ,PARSENAME(base_object_name, 1) AS objectName
  ,BASE_OBJECT_NAME
FROM sys.synonyms
order by ServerName DESC, NAME



OPEN CUR
FETCH NEXT FROM CUR INTO 
   @name
  ,@SERVERNAME
  ,@DBNAME
  ,@Schema
  ,@ObjectName
  ,@BaseObjectName
WHILE @@FETCH_STATUS = 0
BEGIN
  -- Test SYNONYMS that use Link Servers
  IF @SERVERNAME IS NOT NULL
  BEGIN
    SET @cmd = 'EXEC (''IF EXISTS(select name from ' + @DBName + '.[sys].[objects] where name = ''''' + @OBJECTNAME + ''''')
    SELECT ''''VALID SYNONYM'''',''''' + @NAME + ''''',''''' + @BaseObjectName + ''''',''''' + @SERVERNAME + '''''
    ELSE
    SELECT ''''NOT A VALID SYNONYM'''',''''' + @NAME + ''''',''''' + @BaseObjectName + ''''',''''' + @SERVERNAME + ''''''') AT [' + @SERVERNAME + ']'
    print @cmd
	BEGIN TRY
      INSERT INTO @RESULTS
	  EXEC (@cmd)
    END TRY
    -- IF the exec fails for any reason, the SYNONYM is not valid
    BEGIN CATCH
      INSERT INTO @RESULTS
      VALUES (
        'NOT A VALID SYNONYM'
        ,@NAME
        ,@BaseObjectName
        ,@SERVERNAME
        )
    END CATCH
  END
  -- Test SYNONYMS that do not use Link Servers
  ELSE
  BEGIN
    SET @cmd = 'EXEC (''IF EXISTS(select name from ' + @DBName + '.[sys].[objects] where name = ''''' + @OBJECTNAME + ''''')
    SELECT ''''VALID SYNONYM'''',''''' + @NAME + ''''',''''' + @BaseObjectName + ''''','''' ''''
    ELSE
    SELECT ''''NOT A VALID SYNONYM'''',''''' + @NAME + ''''',''''' + @BaseObjectName + ''''',''''' + @@SERVERNAME + ''''''')'
    print @cmd    
	
	BEGIN TRY
      INSERT INTO @RESULTS
      EXEC (@cmd)
    END TRY
    -- IF the exec fails for any reason, the SYSNONYM is not valid
    BEGIN CATCH
      INSERT INTO @RESULTS
      VALUES (
        'NOT A VALID SYNONYM'
        ,@NAME
        ,@BaseObjectName
        ,@@SERVERNAME
        )
    END CATCH
  END
  FETCH NEXT FROM CUR INTO
     @name
    ,@SERVERNAME
    ,@DBNAME
    ,@Schema
    ,@ObjectName
    ,@BaseObjectName
END
CLOSE CUR
DEALLOCATE CUR

SELECT Value
  ,[SYNONYM]
  ,UPPER(Link_SERVER) 'Link Server'
  ,BaseObjectName
  ,COALESCE(ss.data_source,'') 'Data Source'
FROM @RESULTS r
LEFT JOIN master.sys.servers ss on r.Link_SERVER = ss.name


ORDER BY VALUE


Link Servers are supported.

 

Sometimes, it will report as not valid SYNONYM even though you are able to use them (select top 1 * from <sn_syonym_name>). but users without sysadmin cannot.

There are many reasons why that would happen, usually drop & recreate fixes the issue or even running the following:

 


EXEC sp_serveroption @server = '',@optname = 'remote proc transaction promotion', @optvalue = 'false' ;

 

Duration of SQL Jobs – steps

The follownig list the duration of the steps of jobs.

For an overall duration, see http://www.cookingsql.com/2017/02/duration-of-sql-jobs/

 


SELECT TOP (100) PERCENT
 msdb.dbo.sysjobs.name AS JobName,
 msdb.dbo.sysjobsteps.step_id AS Step,
 msdb.dbo.agent_datetime(msdb.dbo.sysjobhistory.run_date,
 msdb.dbo.sysjobhistory.run_time) AS RunDateTime,
 (msdb.dbo.sysjobhistory.run_duration / 10000 * 3600 + msdb.dbo.sysjobhistory.run_duration / 100 % 100 * 60) 
                      + msdb.dbo.sysjobhistory.run_duration % 100 AS RunDurationSeconds,
 msdb.dbo.sysjobsteps.step_name AS StepName,
 msdb.dbo.sysjobsteps.command AS StepCommand, 
 msdb.dbo.sysjobsteps.subsystem AS StepType, 
 msdb.dbo.sysjobsteps.database_name AS DBName, 
 CASE SysJobSteps.last_run_outcome 
		WHEN 0 THEN 'Failed' 
		WHEN 1 THEN 'Succeeded' 
		ELSE 'Other' END AS StepResult

FROM         msdb.dbo.sysjobs 
INNER JOIN msdb.dbo.sysjobsteps ON msdb.dbo.sysjobs.job_id = msdb.dbo.sysjobsteps.job_id 
LEFT OUTER JOIN msdb.dbo.sysjobhistory ON msdb.dbo.sysjobsteps.job_id = msdb.dbo.sysjobhistory.job_id 
								AND msdb.dbo.sysjobsteps.step_id = msdb.dbo.sysjobhistory.step_id 
								AND msdb.dbo.sysjobhistory.step_id <> 0
WHERE   
    msdb.dbo.sysjobs.enabled = 1
AND (msdb.dbo.sysjobs.name NOT LIKE '%Backups.%') 
AND (msdb.dbo.sysjobs.name NOT LIKE 'syspolicy%') 
AND (msdb.dbo.agent_datetime(msdb.dbo.sysjobhistory.run_date, 0) = DATEADD(dd, - 1, CAST(GETDATE() AS date)))

ORDER BY RunDateTime


 

 

SQL Agent Job Details

Detailed view of the jobs

 

 


/*
Job details, including steps, and commands
*/

SELECT
	  [sJOB].[name] AS [JobName]
    , [sJSTP].[step_id] AS [StepNo]
    , [sJSTP].[step_name] AS [StepName]
    , CASE [sJSTP].[subsystem]
        WHEN 'ActiveScripting' THEN 'ActiveX Script'
        WHEN 'CmdExec' THEN 'Operating system (CmdExec)'
        WHEN 'PowerShell' THEN 'PowerShell'
        WHEN 'Distribution' THEN 'Replication Distributor'
        WHEN 'Merge' THEN 'Replication Merge'
        WHEN 'QueueReader' THEN 'Replication Queue Reader'
        WHEN 'Snapshot' THEN 'Replication Snapshot'
        WHEN 'LogReader' THEN 'Replication Transaction-Log Reader'
        WHEN 'ANALYSISCOMMAND' THEN 'SQL Server Analysis Services Command'
        WHEN 'ANALYSISQUERY' THEN 'SQL Server Analysis Services Query'
        WHEN 'SSIS' THEN 'SQL Server Integration Services Package'
        WHEN 'TSQL' THEN 'Transact-SQL script (T-SQL)'
        ELSE sJSTP.subsystem
      END AS [StepType]
    , [sPROX].[name] AS [RunAs]
    , [sJSTP].[database_name] AS [Database]
    , [sJSTP].[command] AS [ExecutableCommand]
    , CASE [sJSTP].[on_success_action]
        WHEN 1 THEN 'Quit the job reporting success'
        WHEN 2 THEN 'Quit the job reporting failure'
        WHEN 3 THEN 'Go to the next step'
        WHEN 4 THEN 'Go to Step: ' 
                    + QUOTENAME(CAST([sJSTP].[on_success_step_id] AS VARCHAR(3))) 
                    + ' ' 
                    + [sOSSTP].[step_name]
      END AS [OnSuccessAction]
    , [sJSTP].[retry_attempts] AS [RetryAttempts]
    , [sJSTP].[retry_interval] AS [RetryInterval (Minutes)]
    , CASE [sJSTP].[on_fail_action]
        WHEN 1 THEN 'Quit the job reporting success'
        WHEN 2 THEN 'Quit the job reporting failure'
        WHEN 3 THEN 'Go to the next step'
        WHEN 4 THEN 'Go to Step: ' 
                    + QUOTENAME(CAST([sJSTP].[on_fail_step_id] AS VARCHAR(3))) 
                    + ' ' 
                    + [sOFSTP].[step_name]
      END AS [OnFailureAction]
FROM
    [msdb].[dbo].[sysjobsteps] AS [sJSTP]
    INNER JOIN [msdb].[dbo].[sysjobs] AS [sJOB]
        ON [sJSTP].[job_id] = [sJOB].[job_id]
    LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOSSTP]
        ON [sJSTP].[job_id] = [sOSSTP].[job_id]
        AND [sJSTP].[on_success_step_id] = [sOSSTP].[step_id]
    LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOFSTP]
        ON [sJSTP].[job_id] = [sOFSTP].[job_id]
        AND [sJSTP].[on_fail_step_id] = [sOFSTP].[step_id]
    LEFT JOIN [msdb].[dbo].[sysproxies] AS [sPROX]
        ON [sJSTP].[proxy_id] = [sPROX].[proxy_id]

 

SQL Agent Jobs and their Schedules

The following query lists jobs and their schedules

 


/*
Basic Job info, including schedules

*/

SELECT	 [JobName] = [jobs].[name]
		,[Category] = [categories].[name]
		,[Owner] = SUSER_SNAME([jobs].[owner_sid])
		,[Enabled] = CASE [jobs].[enabled] WHEN 1 THEN 'Yes' ELSE 'No' END
		,[Scheduled] = CASE [schedule].[enabled] WHEN 1 THEN 'Yes' ELSE 'No' END
		--,[Description] = [jobs].[description]
		,[Schedule_Name] = [schedule].[name]
		,[Occurs] = 
				CASE [schedule].[freq_type]
					WHEN   1 THEN 'Once'
					WHEN   4 THEN 'Daily'
					WHEN   8 THEN 'Weekly'
					WHEN  16 THEN 'Monthly'
					WHEN  32 THEN 'Monthly relative'
					WHEN  64 THEN 'When SQL Server Agent starts'
					WHEN 128 THEN 'Start whenever the CPU(s) become idle' 
					ELSE ''
				END
		,[Occurs_detail] = 
				CASE [schedule].[freq_type]
					WHEN   1 THEN 'O'
					WHEN   4 THEN 'Every ' + CONVERT(VARCHAR, [schedule].[freq_interval]) + ' day(s)'
					WHEN   8 THEN 'Every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' weeks(s) on ' + 
						LEFT(
							CASE WHEN [schedule].[freq_interval] &  1 =  1 THEN 'Sunday, '    ELSE '' END + 
							CASE WHEN [schedule].[freq_interval] &  2 =  2 THEN 'Monday, '    ELSE '' END + 
							CASE WHEN [schedule].[freq_interval] &  4 =  4 THEN 'Tuesday, '   ELSE '' END + 
							CASE WHEN [schedule].[freq_interval] &  8 =  8 THEN 'Wednesday, ' ELSE '' END + 
							CASE WHEN [schedule].[freq_interval] & 16 = 16 THEN 'Thursday, '  ELSE '' END + 
							CASE WHEN [schedule].[freq_interval] & 32 = 32 THEN 'Friday, '    ELSE '' END + 
							CASE WHEN [schedule].[freq_interval] & 64 = 64 THEN 'Saturday, '  ELSE '' END , 
							LEN(
								CASE WHEN [schedule].[freq_interval] &  1 =  1 THEN 'Sunday, '    ELSE '' END + 
								CASE WHEN [schedule].[freq_interval] &  2 =  2 THEN 'Monday, '    ELSE '' END + 
								CASE WHEN [schedule].[freq_interval] &  4 =  4 THEN 'Tuesday, '   ELSE '' END + 
								CASE WHEN [schedule].[freq_interval] &  8 =  8 THEN 'Wednesday, ' ELSE '' END + 
								CASE WHEN [schedule].[freq_interval] & 16 = 16 THEN 'Thursday, '  ELSE '' END + 
								CASE WHEN [schedule].[freq_interval] & 32 = 32 THEN 'Friday, '    ELSE '' END + 
								CASE WHEN [schedule].[freq_interval] & 64 = 64 THEN 'Saturday, '  ELSE '' END 
							) - 1
						)
					WHEN  16 THEN 'Day ' + CONVERT(VARCHAR, [schedule].[freq_interval]) + ' of every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' month(s)'
					WHEN  32 THEN 'The ' + 
							CASE [schedule].[freq_relative_interval]
								WHEN  1 THEN 'First'
								WHEN  2 THEN 'Second'
								WHEN  4 THEN 'Third'
								WHEN  8 THEN 'Fourth'
								WHEN 16 THEN 'Last' 
							END +
							CASE [schedule].[freq_interval]
								WHEN  1 THEN ' Sunday'
								WHEN  2 THEN ' Monday'
								WHEN  3 THEN ' Tuesday'
								WHEN  4 THEN ' Wednesday'
								WHEN  5 THEN ' Thursday'
								WHEN  6 THEN ' Friday'
								WHEN  7 THEN ' Saturday'
								WHEN  8 THEN ' Day'
								WHEN  9 THEN ' Weekday'
								WHEN 10 THEN ' Weekend Day' 
							END + ' of every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' month(s)' 
					ELSE ''
				END
		,[Frequency] = 
				CASE [schedule].[freq_subday_type]
					WHEN 1 THEN 'Occurs once at ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':')
					WHEN 2 THEN 'Occurs every ' + 
								CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Seconds(s) between ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')
					WHEN 4 THEN 'Occurs every ' + 
								CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Minute(s) between ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')
					WHEN 8 THEN 'Occurs every ' + 
								CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Hour(s) between ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')
					ELSE ''
				END
		,[AvgDurationInSec] = CONVERT(DECIMAL(10, 2), [jobhistory].[AvgDuration])
		,[Next_Run_Date] = 
				CASE [jobschedule].[next_run_date]
					WHEN 0 THEN CONVERT(DATETIME, '1900/1/1')
					ELSE CONVERT(DATETIME, CONVERT(CHAR(8), [jobschedule].[next_run_date], 112) + ' ' + 
						 STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [jobschedule].[next_run_time]), 6), 5, 0, ':'), 3, 0, ':'))
				END
FROM	 [msdb].[dbo].[sysjobs] AS [jobs] WITh(NOLOCK) 
LEFT OUTER JOIN [msdb].[dbo].[sysjobschedules] AS [jobschedule] WITh(NOLOCK)  ON [jobs].[job_id] = [jobschedule].[job_id] 
LEFT OUTER JOIN [msdb].[dbo].[sysschedules] AS [schedule] WITh(NOLOCK)  ON [jobschedule].[schedule_id] = [schedule].[schedule_id] 
INNER JOIN [msdb].[dbo].[syscategories] [categories] WITh(NOLOCK)  ON [jobs].[category_id] = [categories].[category_id] 
LEFT OUTER JOIN (	SELECT	 [job_id],
							 [AvgDuration] = (SUM((([run_duration] / 10000 * 3600) + (([run_duration] % 10000) / 100 * 60) + 
																 ([run_duration] % 10000) % 100)) * 1.0) / COUNT([job_id])
					FROM	 [msdb].[dbo].[sysjobhistory] WITh(NOLOCK)
					WHERE	 [step_id] = 0 
					GROUP BY [job_id]  ) AS [jobhistory]  ON [jobhistory].[job_id] = [jobs].[job_id]

where 1=1
and [jobs].[name] like '%backup%'



 

Querying CommandLog

If you have implemented Ola Hallenger scripts, http://ola.hallengren.com/, you will have a CommandLog table where you keep track of execution times.

The following query will help you get information from it, very useful for tracking backup failures:

USE DBA
SELECT
DatabaseName,
StartTime,
DATEDIFF(MINUTE,StartTime,EndTime) 'Duration in Min',
EndTime,
ErrorNumber,
ErrorMessage,
Command,
replace(
cast(SERVERPROPERTY('ErrorLogFileName') as nvarchar(1000))
,'errorlog','')
FROM DBO.COMMANDLOG
WHERE 1=1
--and StartTime > dateadd(dd,-1,getdate())
and CommandType in ('BACKUP_DATABASE','BACKUP_LOG','RESTORE_VERIFYONLY')
--and CommandType in ('xp_create_subdir','xp_delete_file')
--and CommandType in ('DBCC_CHECKDB')

--and ErrorNumber <> 0

order by ID desc

Below, we take advantage of window functions and we can see the last time a database had a DBCC checkdb done, along with its duration

 

    USE DBA
    
	;with Last_DBBC as
	(
	SELECT
    DatabaseName,
    StartTime,
    EndTime,
	ROW_NUMBER() over (PARTITION by databasename order by StartTime desc) 'rownumber'
    FROM DBO.COMMANDLOG
    WHERE 1=1
	and CommandType in ('DBCC_CHECKDB')
   	)
	select
	DatabaseName,
	StartTime, EndTime,
	DATEDIFF(MI,StartTime,EndTime) 'Duration in Min',
	DATEDIFF(DD,StartTime,GETDATE()) 'Age of DBCC in days'
	from Last_DBBC
	where 1=1
	and rownumber = 1

Detect long Running Jobs Store Proc

We already have a query to detect long running jobs:

http://www.cookingsql.com/2017/02/detect-long-running-jobs-store-proc/

So the next logical step is to encapsulate the code on your DBA database and run it all the time. If a job is considered "long running", we should alert the DBA team.

There are several things we need to take in consideration:

  • Define what a long running job is.
  • Define how much data we are going to use to determine what a long running job is.
  • Ensure that we don't send emails too often.

A long running job is defined as one that's been running longer than two standard deviation above the average.

You can modify this definition within the code, but it's not really recommended.

The question of how much data we are going to use has to do with ensuring the average is meaningful.  Within the code we are taking the average of all data points within the last 30 days, and only consirer jobs that have at least @MinHistExecutions executions (with 10 the default).  Additionally, we are only interested on jobs that their duration takes longer than @MinAvgSecsDuration (with 5 mins the default).  The idea of these parameters is to reduce excessive notifications.

Since we don't want to flood the DBA team with notfications, we have within the code some logic that prevents from sending emails more often than 30 mins.

In order to keep track, we are forced to use a table, which should be created within the DBA team's database.

Here is the script to create the table:


USE [DBA]
GO

/****** Object:  Table [dbo].[Jobs_Running_Long]    Script Date: 1/27/2017 3:16:15 PM ******/

IF OBJECT_id('dbo.Jobs_Running_Long') IS NOT NULL
DROP TABLE [dbo].[Jobs_Running_Long]
GO

/****** Object:  Table [dbo].[Jobs_Running_Long]    Script Date: 1/27/2017 3:16:15 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Jobs_Running_Long](
	[job_id]	 uniqueidentifier	NOT NULL,
	[JobName]	 sysname			NOT NULL,
	[State]		 varchar(29)		NOT NULL,
	[ExecutionDate]	datetime		NOT NULL,
	[AverageSecs] numeric(38, 6)	NOT NULL,
	[DurationSecs] float			NOT NULL,
	[capture_time] datetime			NULL,
	[email_done]   BIT				NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

After we create the table, we can create the Store Procedure, which is the one that does all the work:


USE DBA
GO
IF OBJECT_ID('dbO.SP_DETECT_LONG_RUNNING_JOBS') IS NOT NULL
drop procedure SP_DETECT_LONG_RUNNING_JOBS

go


CREATE PROCEDURE SP_DETECT_LONG_RUNNING_JOBS 
   @MinHistExecutions int   = 10.0
  ,@MinAvgSecsDuration int  = 600.0
  ,@JOB_STATE INT = 1
  ,@skip_email INT = 0
AS
BEGIN
/*=============================================
  File: long_running_jobs.sql
 
  Author: Thomas LaRock, http://thomaslarock.com/contact-me/
 
 MODIFIED AND ADAPTED BY MIGUEL QUINTANA 01/27/2017

  Summary: This script will check to see if any currently
			running jobs are running long. 
Sample:
exec SP_DETECT_LONG_RUNNING_JOBS @MinHistExecutions=5, @MinAvgSecsDuration =600 , @JOB_STATE =4 , @skip_email = 1

 
  Variables:
	@MinHistExecutions - Minimum number of job runs we want to consider 
	@MinAvgSecsDuration - Threshold for minimum duration we care to monitor
        @HistoryStartDate - Start date for historical average
        @HistoryEndDate - End date for historical average
 
        These variables allow for us to control a couple of factors. First
        we can focus on jobs that are running long enough on average for
        us to be concerned with (say, 30 seconds or more). Second, we can
        avoid being alerted by jobs that have run so few times that the
        average and standard deviations are not quite stable yet. This script
        leaves these variables at 1.0, but I would advise you alter them
        upwards after testing.
 
  Returns: One result set containing a list of jobs that
	are currently running and are running longer than two standard deviations 
        away from their historical average. The "Min Threshold" column
        represents the average plus two standard deviations. 
 
  Date: October 3rd, 2012
 
  SQL Server Versions: SQL2005, SQL2008, SQL2008R2, SQL2012
 
  You may alter this code for your own purposes. You may republish
  altered code as long as you give due credit. 
 
  THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY
  OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT
  LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR
  FITNESS FOR A PARTICULAR PURPOSE.
 
=============================================*/

SET NOCOUNT ON

DECLARE   
	@HistoryStartDate datetime 
   ,@HistoryEndDate datetime  
   
SET @HistoryStartDate = DateAdd(dd,-30,GETDATE()) --'19000101'
SET @HistoryEndDate = GETDATE()

Declare @DBATeam nvarchar(100) = 'DBASpt@cir2.com'



/***** DO NOT MODIFY BELOW *********/
DECLARE @RESULTS TABLE (
	[job_id] [uniqueidentifier] NOT NULL,
	[JobName] [sysname] NOT NULL,
	[State] [varchar](29) NOT NULL,
	[ExecutionDate] [datetime] NULL,
	[AverageSecs] [numeric](38, 6) NULL,
	[DurationSecs] [float] NULL,
	[capture_time] [datetime] NOT NULL
)

 
DECLARE @currently_running_jobs TABLE (
    job_id UNIQUEIDENTIFIER NOT NULL
    ,last_run_date INT NOT NULL
    ,last_run_time INT NOT NULL
    ,next_run_date INT NOT NULL
    ,next_run_time INT NOT NULL
    ,next_run_schedule_id INT NOT NULL
    ,requested_to_run INT NOT NULL
    ,request_source INT NOT NULL
    ,request_source_id SYSNAME NULL
    ,running INT NOT NULL
    ,current_step INT NOT NULL
    ,current_retry_attempt INT NOT NULL
    ,job_state INT NOT NULL
    ) 
 
--capture details on jobs
INSERT INTO @currently_running_jobs
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,''
 
;WITH JobHistData AS
(
  SELECT job_id
	,date_executed=msdb.dbo.agent_datetime(run_date, run_time)
	,secs_duration=run_duration/10000*3600
                      +run_duration%10000/100*60
                      +run_duration%100
  FROM msdb.dbo.sysjobhistory
  WHERE step_id = 0   --Job Outcome
  AND run_status = 1  --Succeeded
)
,JobHistStats AS
(
  SELECT job_id
        ,AvgDuration = AVG(secs_duration*1.)
        ,AvgPlus2StDev = AVG(secs_duration*1.) + 2*stdevp(secs_duration)
  FROM JobHistData
  WHERE date_executed >= DATEADD(day, DATEDIFF(day,'19000101',@HistoryStartDate),'19000101')
  AND date_executed < DATEADD(day, 1 + DATEDIFF(day,'19000101',@HistoryEndDate),'19000101')   
  GROUP BY job_id   HAVING COUNT(*) >= @MinHistExecutions
  AND AVG(secs_duration*1.) >= @MinAvgSecsDuration
)

INSERT INTO  @RESULTS 
SELECT JHS.job_id	
		,j.name AS [JobName]
      ,case crj.job_state
		when 0 then 'not idle or suspended'
		when 1 then	'Executing'
		when 2 then	'Waiting for thread'
		when 3 then	'Between retries'
		when 4 then	'Idle'
		when 5 then	'Suspended'
		when 7 then	'Performing completion actions'
		else ''
 	   end as [State]
	  ,MAX(act.start_execution_date) AS [ExecutionDate]
      ,AvgDuration AS [AverageSecs]
      ,AvgPlus2StDev AS [DurationSecs]
	  ,GETDATE() AS [email_Done]
FROM JobHistData jd
JOIN JobHistStats jhs on jd.job_id = jhs.job_id
JOIN msdb..sysjobs j on jd.job_id = j.job_id
JOIN @currently_running_jobs crj ON crj.job_id = jd.job_id
JOIN msdb..sysjobactivity AS act ON act.job_id = jd.job_id
AND act.stop_execution_date IS NULL
AND act.start_execution_date IS NOT NULL

WHERE 1=1
AND secs_duration > AvgPlus2StDev
AND DATEDIFF(SS, act.start_execution_date, GETDATE()) > AvgPlus2StDev

-- THIS SECTION EXCLUDE JOBS ALREADY IN THE SYSTEM FOR LESS THAN 30 MINS
AND JHS.job_id NOT IN (SELECT A.JOB_ID FROM DBA.dbo.JOBS_Running_Long A WHERE DATEDIFF(mi,A.CAPTURE_TIME,GETDATE()) < 30)


AND crj.job_state = @JOB_STATE


GROUP BY  JHS.job_id, j.name, AvgDuration, AvgPlus2StDev, crj.job_state


IF @SKIP_EMAIL = 1

BEGIN

SELECT JobName, State, ExecutionDate,
convert(decimal(8,1),AverageSecs/60) 'Average in Mins' ,
convert(decimal(8,1),DurationSecs/60) 'Duration in Mins'
FROM @RESULTS

END


IF @skip_email = 0 AND @JOB_STATE = 1
BEGIN


INSERT INTO DBA.dbo.Jobs_Running_Long
SELECT [job_id]
      ,[JobName]
      ,[State]
      ,[ExecutionDate]
      ,[AverageSecs]
      ,[DurationSecs]
	  ,[capture_time]
	  ,NULL 
FROM @RESULTS


-- /*  Builds an Email for the DBAs

DECLARE @SUBJECT NVARCHAR(100)
DECLARE @BODY NVARCHAR(MAX)
DECLARE @xml NVARCHAR(MAX)

SET @SUBJECT = 'LONG RUNNING QUERY DETECTED ON '+CAST(SERVERPROPERTY('SERVERNAME') AS VARCHAR(15))
SET @xml = CAST((
SELECT
td =					  [JobName],'',
[td/@align]='right', td = convert(decimal(10,2),[AverageSecs]/60),'',
[td/@align]='right', td = convert(decimal(10,2),[DurationSecs]/60),''
FROM  DBA.DBO.JOBS_RUNNING_LONG
WHERE email_done is null 

FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @body =
'

Long running Job Detected

One or more jobs were found to be running longer than average, please investigate. Please note, the job might have stopped by the time you receive this message. Check the table dba.dbo.jobs_running_long for details

' SET @body = @body + @xml +'
Job Name AverageDuration (Mins) Duration_Mins
' IF (SELECT COUNT(*) FROM DBA.DBO.JOBS_RUNNING_LONG WHERE email_done is null)>0 begin EXEC MSDB.DBO.SP_SEND_DBMAIL @RECIPIENTS = @DBATeam, @SUBJECT = @SUBJECT, @BODY = @BODY, @body_format ='HTML' end UPDATE DBA.DBO.JOBS_RUNNING_LONG SET email_done = 1 WHERE email_done IS NULL END -- SKIP EMAIL ELSE PRINT '@SKIP EMAIL = 0 MUST BE USED WITH @JOB_STATE = 1. Aborting Email' END

It is important to note that the store proc uses the default mail profile.

After all of this is created, the DBA team can run the following command to check the status of jobs:

-- For running jobs
exec SP_DETECT_LONG RUNNING_JOBS @MinHistExecutions=5, @MinAvgSecsDuration =600 , @JOB_STATE =1 , @skip_email = 1
-- For idle jobs
exec SP_DETECT_LONG_RUNNING_JOBS @MinHistExecutions=5, @MinAvgSecsDuration =600 , @JOB_STATE =4 , @skip_email = 1

After the store proc is ready, We can create a job that runs every 15 mins with one of the statements above.

 

.

Detecting Long Running Jobs

This script is taken from Thomas LaRock, and I'm shamelessly modifying it a little… just a little.

The original article is here

https://thomaslarock.com/2012/10/how-to-find-currently-running-long-sql-agent-jobs/

 

/*=============================================
  
  Author: Thomas LaRock, http://thomaslarock.com/contact-me/
 
 MODIFIED AND ADAPTED BY MIGUEL QUINTANA 01/27/2017

  Summary: This script will check to see if any currently
			running jobs are running long. 
 
  Variables:
	@MinHistExecutions - Minimum number of job runs we want to consider 
	@MinAvgSecsDuration - Threshold for minimum duration we care to monitor
        @HistoryStartDate - Start date for historical average
        @HistoryEndDate - End date for historical average
 
        These variables allow for us to control a couple of factors. First
        we can focus on jobs that are running long enough on average for
        us to be concerned with (say, 30 seconds or more). Second, we can
        avoid being alerted by jobs that have run so few times that the
        average and standard deviations are not quite stable yet. This script
        leaves these variables at 1.0, but I would advise you alter them
        upwards after testing.
 
  Returns: One result set containing a list of jobs that
	are currently running and are running longer than two standard deviations 
        away from their historical average. The "Min Threshold" column
        represents the average plus two standard deviations. 
 
  Date: October 3rd, 2012
 
  SQL Server Versions: SQL2005, SQL2008, SQL2008R2, SQL2012
 
  You may alter this code for your own purposes. You may republish
  altered code as long as you give due credit. 
 
  THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY
  OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT
  LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR
  FITNESS FOR A PARTICULAR PURPOSE.
 
=============================================*/
SET NOCOUNT ON

DECLARE   
	@HistoryStartDate datetime 
   ,@HistoryEndDate datetime
   ,@JOB_STATE INT = 4
   -- @JOB_STATE = 4 idle, @JOB_STATE = 1 running
   ,@MinHistExecutions int   = 10.0
  ,@MinAvgSecsDuration int  = 600.0  
   
SET @HistoryStartDate = DateAdd(dd,-30,GETDATE()) --'19000101'
SET @HistoryEndDate = GETDATE()


/***** DO NOT MODIFY BELOW *********/
DECLARE @RESULTS TABLE (
	[job_id] [uniqueidentifier] NOT NULL,
	[JobName] [sysname] NOT NULL,
	[State] [varchar](29) NOT NULL,
	[ExecutionDate] [datetime] NULL,
	[AverageSecs] [numeric](38, 6) NULL,
	[DurationSecs] [float] NULL,
	[capture_time] [datetime] NOT NULL
)

 
DECLARE @currently_running_jobs TABLE (
    job_id UNIQUEIDENTIFIER NOT NULL
    ,last_run_date INT NOT NULL
    ,last_run_time INT NOT NULL
    ,next_run_date INT NOT NULL
    ,next_run_time INT NOT NULL
    ,next_run_schedule_id INT NOT NULL
    ,requested_to_run INT NOT NULL
    ,request_source INT NOT NULL
    ,request_source_id SYSNAME NULL
    ,running INT NOT NULL
    ,current_step INT NOT NULL
    ,current_retry_attempt INT NOT NULL
    ,job_state INT NOT NULL
    ) 
 
--capture details on jobs
INSERT INTO @currently_running_jobs
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,''
 
;WITH JobHistData AS
(
  SELECT job_id
	,date_executed=msdb.dbo.agent_datetime(run_date, run_time)
	,secs_duration=run_duration/10000*3600
                      +run_duration%10000/100*60
                      +run_duration%100
  FROM msdb.dbo.sysjobhistory
  WHERE step_id = 0   --Job Outcome
  AND run_status = 1  --Succeeded
)
,JobHistStats AS
(
  SELECT job_id
        ,AvgDuration = AVG(secs_duration*1.)
        ,AvgPlus2StDev = AVG(secs_duration*1.) + 2*stdevp(secs_duration)
  FROM JobHistData
  WHERE date_executed >= DATEADD(day, DATEDIFF(day,'19000101',@HistoryStartDate),'19000101')
  AND date_executed < DATEADD(day, 1 + DATEDIFF(day,'19000101',@HistoryEndDate),'19000101')   
  GROUP BY job_id   HAVING COUNT(*) >= @MinHistExecutions
  AND AVG(secs_duration*1.) >= @MinAvgSecsDuration
)

-- INSERT INTO  @RESULTS 
SELECT JHS.job_id	
		,j.name AS [JobName]
      ,case crj.job_state
		when 0 then 'not idle or suspended'
		when 1 then	'Executing'
		when 2 then	'Waiting for thread'
		when 3 then	'Between retries'
		when 4 then	'Idle'
		when 5 then	'Suspended'
		when 7 then	'Performing completion actions'
		else ''
 	   end as [State]
	  ,MAX(act.start_execution_date) AS [ExecutionDate]
      ,AvgDuration AS [AverageSecs]
      ,AvgPlus2StDev AS [DurationSecs]
	  ,GETDATE() AS [email_Done]
FROM JobHistData jd
JOIN JobHistStats jhs on jd.job_id = jhs.job_id
JOIN msdb..sysjobs j on jd.job_id = j.job_id
JOIN @currently_running_jobs crj ON crj.job_id = jd.job_id
JOIN msdb..sysjobactivity AS act ON act.job_id = jd.job_id
AND act.stop_execution_date IS NULL
AND act.start_execution_date IS NOT NULL

WHERE 1=1
AND secs_duration > AvgPlus2StDev
AND DATEDIFF(SS, act.start_execution_date, GETDATE()) > AvgPlus2StDev

-- THIS SECTION EXCLUDE JOBS ALREADY IN THE SYSTEM FOR LESS THAN 30 MINS
AND JHS.job_id NOT IN (SELECT A.JOB_ID FROM DBA.dbo.JOBS_Running_Long A WHERE DATEDIFF(mi,A.CAPTURE_TIME,GETDATE()) < 30)


AND crj.job_state = @JOB_STATE


GROUP BY  JHS.job_id, j.name, AvgDuration, AvgPlus2StDev, crj.job_state

 

 

Duration of SQL Jobs

List SQL Job duration

If you need step details, see http://www.cookingsql.com/2017/02/duration-of-sql-jobs-steps/

/*
DESCRIPTION	List duration of jobs 
CONFIGURATION none
some filtering might be used

Compatibility List
SQL 2008 and up

*/
select 
 j.name as 'JobName',
 msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',
 ((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60) 
         as 'RunDurationMinutes'
From msdb.dbo.sysjobs j 
INNER JOIN msdb.dbo.sysjobhistory h  ON j.job_id = h.job_id 
where 1=1
and j.enabled = 1   --Only Enabled Jobs
and h.step_id = 0   --Only show the total duration
and j.name like '%backup%'           --Uncomment to search for a single job
/*
and msdb.dbo.agent_datetime(run_date, run_time) 
BETWEEN '01/01/2017' and '02/01/2017'  --Uncomment for date range queries
*/
order by JobName, RunDateTime desc

 

 

Link Server Information

 

A very simply script to list all link servers available on the SQL Server


SELECT 
 ss.name 
,ss.product 
,ss.provider
,ss.data_source 
,case sl.uses_self_credential 
	when 1 then 'Uses Self Credentials' 
    else coalesce(ssp.name,'') 
	end 'Local login'
, coalesce(sl.remote_name,'') 'Remote Login Name'
,case ss.is_rpc_out_enabled
	when 1 then 'True'
	else 'False'
	end 'RPC Out Enabled'
 ,case ss.is_data_access_enabled 
   when 1 then 'True'
   else 'False'
   end 'Data Access Enabled'
,ss.modify_date 
FROM sys.Servers ss 
 LEFT JOIN sys.linked_logins sl ON ss.server_id = sl.server_id 
 LEFT JOIN sys.server_principals ssp ON ssp.principal_id = sl.local_principal_id
 where 1=1
Order by ss.name