RSS

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

17 May

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].[nameAS [JobName]
 , [sDBP].[nameAS [JobOwner]
 , [sCAT].[nameAS [JobCategory]
 , [sJOB].[description] AS [JobDescription]
 CASE [sJOB].[enabled]
 WHEN THEN 'Yes'
 WHEN THEN 'No'
 END AS [IsEnabled]
 , [sJOB].[date_created] AS [JobCreatedOn]
 , [sJOB].[date_modified] AS [JobLastModifiedOn]
 , [sSVR].[nameAS [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].[nameAS [JobScheduleName]
 CASE [sJOB].[delete_level]
 WHEN THEN 'Never'
 WHEN THEN 'On Success'
 WHEN THEN 'On Failure'
 WHEN 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].[nameAS [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]
 , [nameAS [ScheduleName]
 CASE [enabled]
 WHEN THEN 'Yes'
 WHEN 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 THEN 'One Time'
 WHEN THEN 'Daily'
 WHEN 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 THEN 'Occurs every ' CAST([freq_interval] AS VARCHAR(3)) + ' day(s)'
 WHEN 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 THEN 'First'
 WHEN THEN 'Second'
 WHEN THEN 'Third'
 WHEN THEN 'Fourth'
 WHEN 16 THEN 'Last'
 END
 ' '
 CASE [freq_interval]
 WHEN THEN 'Sunday'
 WHEN THEN 'Monday'
 WHEN THEN 'Tuesday'
 WHEN THEN 'Wednesday'
 WHEN THEN 'Thursday'
 WHEN THEN 'Friday'
 WHEN THEN 'Saturday'
 WHEN THEN 'Day'
 WHEN 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 THEN 'Occurs once at '
 + STUFF(
 STUFF(RIGHT('000000' CAST([active_start_time] AS VARCHAR(6)), 6)
 , 3, 0, ':')
 , 6, 0, ':')
 WHEN 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 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 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]

this not the end :)  Do not go here and there Check your mobile notification daily check your email notification daily check our blog 

Follow Me to see my new posts and to complete with me this Amazing part How to Administrate and Monitor SQL Server Jobs by T-SQL..?

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s