How to monitor your SQL Server instances..(3/10)?

in the previous Post i finished the Part of How to Diagnosis MSSQL Server or MSSQL Database with fast way..? but to be honest with you this really very big part and can’t Finished it coz this need much T-SQL , Professional Third party tool like dynatrace really wonderful Tool don’t miss any corn.

So today i will start new Part in our Series How to Administrate and Monitor SQL Server Jobs by T-SQL..?

So let’s go to Start

WHAT YOU NEED in SQL SERVER JOB WE COVERED HERE

First thing any DBA need to monitor the SQL Agent at the first HE/SHE need to know the First Vision in his/here SQL Server jobs

Check SQL Server jobs First Vision..?

SELECT DISTINCT substring(a.name,1,100) AS [Job Name],

 'Enabled'=case

 WHEN a.enabled = 0 THEN 'No'

 WHEN a.enabled = 1 THEN 'Yes'

 end,

 substring(b.name,1,30) AS [Name of the schedule],

 'Frequency of the schedule execution'=case

 WHEN b.freq_type = 1 THEN 'Once'

 WHEN b.freq_type = 4 THEN 'Daily'

 WHEN b.freq_type = 8 THEN 'Weekly'

 WHEN b.freq_type = 16 THEN 'Monthly'

 WHEN b.freq_type = 32 THEN 'Monthly relative'

 WHEN b.freq_type = 32 THEN 'Execute when SQL Server Agent starts'

 END,

 'Units for the freq_subday_interval'=case

 WHEN b.freq_subday_type = 1 THEN 'At the specified time'

 WHEN b.freq_subday_type = 2 THEN 'Seconds'

 WHEN b.freq_subday_type = 4 THEN 'Minutes'

 WHEN b.freq_subday_type = 8 THEN 'Hours'

 END,

 cast(cast(b.active_start_date as varchar(15)) as datetime) asactive_start_date,

 cast(cast(b.active_end_date as varchar(15)) as datetime) as active_end_date,

 Stuff(Stuff(right('000000'+Cast(c.next_run_time asVarchar),6),3,0,':'),6,0,':') as Run_Time,

 convert(varchar(24),b.date_created) as Created_Date

FROM msdb.dbo.sysjobs a

INNER JOIN msdb.dbo.sysJobschedules c ON a.job_id = c.job_id

INNER JOIN msdb.dbo.SysSchedules b on b.Schedule_id=c.Schedule_id

GO

This Script will return to you :

Job Name / Enabled or Disabled / Name of the scheduled/ Frequency of the schedule execution / Units for the freq_subday_interval/ active_start_date/ active_end_date/ Run_Time /Created_Date.

From this Script we can do most Customization

Check jobs Status ..? Is it enabled or Disabled ..?

SELECT DISTINCT substring(a.name,1,100) AS [Job Name],

 'Job Enabled..?'=case

 WHEN a.enabled = 0 THEN 'No'

 WHEN a.enabled = 1 THEN 'Yes'

 end

FROM msdb.dbo.sysjobs a

When was a SQL Agent job was created or when was the last time it was modified?

if you need to know this info the the date a SQL Agent job was created or the last time it was modified, you can find the information in the sysjobs table in the MSDB database.

Select Name,

 Date_Created,

 Date_Modified

 From msdb..sysjobs

Identify newly created SQL Server Agent Jobs

this Script will return the Jobs created in last 15 day

SELECT [Name], [Date_Created]

FROM MSDB.dbo.sysjobs

WHERE [Date_Created] BETWEEN DATEADD(dd, -15, GETDATE()) AND GETDATE();

Check enabled jobs Without notification setup ..?

Jobs nightmare Really from past Experience this very Important point IF your jobs is enabled notification or no ? sure it must be YES Enabled to be know what happened in your Jobs to don’t see nightmare.

SELECT [Name], [Date_Created], 'Job Enabled..?'=case

 WHEN enabled = 0 THEN 'No'

 WHEN enabled = 1 THEN 'Yes'

 end ,

 'notification Enabled..?'=case

 WHEN Notify_Level_Email = 0 THEN 'No'

 WHEN Notify_Level_Email = 1 THEN 'Yes'

 end

FROM MSDB.dbo.sysjobs

WHERE [Notify_Level_Email] = 0

AND [Enabled] = 1;

After this Script What you should do if you have some jobs without Notification setup at this time waste your time go direct to update this jobs by the Notification setup.so at the first you should know what the Operator you will pass in the Notification

retrieving enabled operators in SQL Server Agent

SELECT [ID], [Name], [Enabled]

FROM MSDB.dbo.sysoperators

WHERE [Enabled] = 1

ORDER BY [Name];

GO

Now after you select one from sysoperators you should go yo update your Jobs

Update SQL Server Agent Jobs without any notification by New notification

UPDATE S

SET S.[notify_level_email] = 2,

S.[notify_email_operator_id] = 1----Select the ID for your Operator from the Previous Query(retrieving enabled operators)

FROM MSDB.dbo.sysjobs S

WHERE S.[Notify_Level_Email] = 0

AND S.[Enabled] = 1;

GO

SQL Server Agent Job Setup and Configuration Information..

SELECT

 [sJOB].[job_id] AS [JobID]

 , [sJOB].[name] AS [JobName]

 , [sDBP].[name] AS [JobOwner]

 , [sCAT].[name] AS [JobCategory]

 , [sJOB].[description] AS [JobDescription]

 , CASE [sJOB].[enabled]

 WHEN 1 THEN 'Yes'

 WHEN 0 THEN 'No'

 END AS [IsEnabled]

 , [sJOB].[date_created] AS [JobCreatedOn]

 , [sJOB].[date_modified] AS [JobLastModifiedOn]

 , [sSVR].[name] AS [OriginatingServerName]

 , [sJSTP].[step_id] AS [JobStartStepNo]

 , [sJSTP].[step_name] AS [JobStartStepName]

 , CASE

 WHEN [sSCH].[schedule_uid] IS NULL THEN 'No'

 ELSE 'Yes'

 END AS [IsScheduled]

 , [sSCH].[schedule_uid] AS [JobScheduleID]

 , [sSCH].[name] AS [JobScheduleName]

 , CASE [sJOB].[delete_level]

 WHEN 0 THEN 'Never'

 WHEN 1 THEN 'On Success'

 WHEN 2 THEN 'On Failure'

 WHEN 3 THEN 'On Completion'

 END AS [JobDeletionCriterion]

FROM

 [msdb].[dbo].[sysjobs] AS [sJOB]

 LEFT JOIN [msdb].[sys].[servers] AS [sSVR]

 ON [sJOB].[originating_server_id] = [sSVR].[server_id]

 LEFT JOIN [msdb].[dbo].[syscategories] AS [sCAT]

 ON [sJOB].[category_id] = [sCAT].[category_id]

 LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sJSTP]

 ON [sJOB].[job_id] = [sJSTP].[job_id]

 AND [sJOB].[start_step_id] = [sJSTP].[step_id]

 LEFT JOIN [msdb].[sys].[database_principals] AS [sDBP]

 ON [sJOB].[owner_sid] = [sDBP].[sid]

 LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [sJOBSCH]

 ON [sJOB].[job_id] = [sJOBSCH].[job_id]

 LEFT JOIN [msdb].[dbo].[sysschedules] AS [sSCH]

 ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id]

ORDER BY [JobName]

Check Jobs without Schedule ..

Select Name AS [No schedule in this Job]

 from [msdb]..[sysjobs] where job_id not in

 (Select job_id from MSDB..sysjobschedules )

Check jobs With Enabled Schedule ..

Select [sJOB].[name] AS [JobName],

Case

When [sSCH].[schedule_uid] IS Null THEN 'NO'

 ELSE 'Yes'

 End AS 'IS schedules Enabled..?'

 from

 [msdb].[dbo].[sysjobs] AS [sJOB]

 Inner Join [msdb].[dbo].[sysjobschedules] [sJOBSCH]

 On [sJOBSCH].job_id = [sJOB].job_id

 Inner Join [sysschedules] AS [sSCH]

 On [sSCH].schedule_id=[sJOBSCH].schedule_id

Now after we determined what is the jobs have Schedule and what is no Actually we need to know the Schedule Information for each job.

Check SQL Server Agent Job Schedule Information..

---SQL Server Agent Job Schedule Information

SELECT

 [schedule_uid] AS [ScheduleID]

 , [name] AS [ScheduleName]

 , CASE [enabled]

 WHEN 1 THEN 'Yes'

 WHEN 0 THEN 'No'

 END AS [IsEnabled]

 , CASE

 WHEN [freq_type] = 64 THEN 'Start automatically when SQL Server Agent starts'

 WHEN [freq_type] = 128 THEN 'Start whenever the CPUs become idle'

 WHEN [freq_type] IN (4,8,16,32) THEN 'Recurring'

 WHEN [freq_type] = 1 THEN 'One Time'

 END [ScheduleType]

 , CASE [freq_type]

 WHEN 1 THEN 'One Time'

 WHEN 4 THEN 'Daily'

 WHEN 8 THEN 'Weekly'

 WHEN 16 THEN 'Monthly'

 WHEN 32 THEN 'Monthly - Relative to Frequency Interval'

 WHEN 64 THEN 'Start automatically when SQL Server Agent starts'

 WHEN 128 THEN 'Start whenever the CPUs become idle'

 END [Occurrence]

 , CASE [freq_type]

 WHEN 4 THEN 'Occurs every ' + CAST([freq_interval] AS VARCHAR(3)) + ' day(s)'

 WHEN 8 THEN 'Occurs every ' + CAST([freq_recurrence_factor] AS VARCHAR(3))

 + ' week(s) on '

 + CASE WHEN [freq_interval] & 1 = 1 THEN 'Sunday' ELSE '' END

 + CASE WHEN [freq_interval] & 2 = 2 THEN ', Monday' ELSE '' END

 + CASE WHEN [freq_interval] & 4 = 4 THEN ', Tuesday' ELSE '' END

 + CASE WHEN [freq_interval] & 8 = 8 THEN ', Wednesday' ELSE '' END

 + CASE WHEN [freq_interval] & 16 = 16 THEN ', Thursday' ELSE '' END

 + CASE WHEN [freq_interval] & 32 = 32 THEN ', Friday' ELSE '' END

 + CASE WHEN [freq_interval] & 64 = 64 THEN ', Saturday' ELSE '' END

 WHEN 16 THEN 'Occurs on Day ' + CAST([freq_interval] AS VARCHAR(3))

 + ' of every '

 + CAST([freq_recurrence_factor] AS VARCHAR(3)) + ' month(s)'

 WHEN 32 THEN 'Occurs on '

 + CASE [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 [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 ' + CAST([freq_recurrence_factor] AS VARCHAR(3))

 + ' month(s)'

 END AS [Recurrence]

 , CASE [freq_subday_type]

 WHEN 1 THEN 'Occurs once at '

 + STUFF(

 STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)

 , 3, 0, ':')

 , 6, 0, ':')

 WHEN 2 THEN 'Occurs every '

 + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Second(s) between '

 + STUFF(

 STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)

 , 3, 0, ':')

 , 6, 0, ':')

 + ' & '

 + STUFF(

 STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)

 , 3, 0, ':')

 , 6, 0, ':')

 WHEN 4 THEN 'Occurs every '

 + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Minute(s) between '

 + STUFF(

 STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)

 , 3, 0, ':')

 , 6, 0, ':')

 + ' & '

 + STUFF(

 STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)

 , 3, 0, ':')

 , 6, 0, ':')

 WHEN 8 THEN 'Occurs every '

 + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Hour(s) between '

 + STUFF(

 STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)

 , 3, 0, ':')

 , 6, 0, ':')

 + ' & '

 + STUFF(

 STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)

 , 3, 0, ':')

 , 6, 0, ':')

 END [Frequency]

 , STUFF(

 STUFF(CAST([active_start_date] AS VARCHAR(8)), 5, 0, '-')

 , 8, 0, '-') AS [ScheduleUsageStartDate]

 , STUFF(

 STUFF(CAST([active_end_date] AS VARCHAR(8)), 5, 0, '-')

 , 8, 0, '-') AS [ScheduleUsageEndDate]

 , [date_created] AS [ScheduleCreatedOn]

 , [date_modified] AS [ScheduleLastModifiedOn]

FROM [msdb].[dbo].[sysschedules]

ORDER BY [ScheduleName]