Monday 24 November 2014

SQL Server Agent Jobs History

SQL Server stores Agent job information and execution history in MSDB system tables. SSMS GUI provides a navigation to Jobs and history for all the jobs but sometimes that is not sufficient for deep analysis. The Job history viewer is not easy to get which jobs running longer, steps getting failed, do not have schedules and notification alerts.
Scripts in post will make analysis and auditing of SQL Server Agent Jobs many times easier.

List of currently running jobs and their Status
--- Script By: Amna Asif___________________For: sqlcache.blogspot.com
--- Purpose: Currently running jobs and their Status
 
SELECT  jb.name,
        jb.originating_server ServerName,
        ja.run_requested_date JobStartTime,
        DATEDIFF(MINUTE, ja.run_requested_date, GETDATE()) as TimeElapsed_Minutes,
        case when ja.last_executed_step_id is null
             then 'Step '+Cast (start_step_id as varchar(20)) +' ['+sstep.step_name+'] executing'
             else 'Step ' +Cast ( last_executed_step_id+1 as varchar(20))+' ['+ lstep.step_name
                  + '] executing' 
        end CurrentStepID , sstep.step_name JobStartedfromStep
        , Case ja.run_requested_source when 1 then 'SCHEDULER'
                               when 2 then 'ALERTER'
                               when 3 then 'BOOT'
                               when 4 then 'USER'
                               when 6 then 'ON_IDLE_SCHEDULE' else 'Unknown' end JobRunSource
FROM    msdb.dbo.sysjobs_view jb
        INNER JOIN msdb.dbo.sysjobactivity ja ON jb.job_id = ja.job_id
        INNER JOIN msdb.dbo.syssessions sess ON sess.session_id = ja.session_id
        LEFT JOIN MSDB.DBO.sysjobsteps sstep ON sstep.step_id =start_step_id and sstep.job_id=jb.job_id
        LEFT JOIN MSDB.DBO.sysjobsteps lstep ON lstep.step_id =last_executed_step_id + 1 and lstep.job_id=jb.job_id 
WHERE   stop_execution_date IS NULL 
        AND run_requested_date IS NOT NULL

Execution time history for one month per Job in minutes
--- Script By: Amna Asif___________________For: sqlcache.blogspot.com
--- Purpose: Jobs execution time in minutes 

SELECT jb.name as 'JobName'
 , msdb.dbo.agent_datetime(jh.run_date, jh.run_time) as JobRunDate
 , ((jh.run_duration/10000*3600 + (jh.run_duration/100)%100*60 + jh.run_duration%100 + 31 ) / 60) AS JobRunDuration_Minutes
 , Case jh.run_status when 0 then 'Failed'
                              when 1 then 'Succeeded'
                              when 2 then 'Retry'
                              when 3 then 'Stopped' else 'Unknown' end  JobRunStatus                           
FROM msdb.dbo.sysjobs jb 
INNER JOIN msdb.dbo.sysjobhistory jh  ON jb.job_id = jh.job_id 
WHERE step_id=0 -- '(Job outcome)'
AND DATEDIFF(MM, msdb.dbo.agent_datetime(jh.run_date, jh.run_time), GETDATE())=0 --- One Month
AND ((jh.run_duration/10000*3600 + (jh.run_duration/100)%100*60
         + jh.run_duration%100 + 31 ) / 60)> 5 -- Greater than 5 mins 
AND jb.name='Job infinite loop'                                                            
ORDER BY JobName, JobRunDate DESC

Execution time history for one month per Job Step in minutes
--- Script By: Amna Asif___________________For: sqlcache.blogspot.com
--- Purpose: Job Steps execution time in minutes 

SELECT rank() over(ORDER BY jb.name,msdb.dbo.agent_datetime(jh.run_date, jh.run_time) desc) JobExecutionID
,jb.name as 'JobName'
, jstp.step_name as 'StepName'
, msdb.dbo.agent_datetime(jh.run_date, jh.run_time) as 'JobRunDate'
, ((jh.run_duration/10000*3600 + (jh.run_duration/100)%100*60 + jh.run_duration%100 + 31 ) / 60) AS 'JobRunDurationMinutes'
, Case jh.run_status when 0 then 'Failed'
                              when 1 then 'Succeeded'
                              when 2 then 'Retry'
                              when 3 then 'Stopped' else 'Unknown' end  JobRunStatus                             
FROM msdb.dbo.sysjobs jb 
INNER JOIN msdb.dbo.sysjobsteps jstp ON jb.job_id = jstp.job_id
INNER JOIN msdb.dbo.sysjobhistory jh  ON jb.job_id = jh.job_id 
WHERE jh.step_id >0 -- '(Job outcome)'
AND DATEDIFF(MM, msdb.dbo.agent_datetime(jh.run_date, jh.run_time), GETDATE())=0 --- One Month
AND ((jh.run_duration/10000*3600 + (jh.run_duration/100)%100*60                                    
        + jh.run_duration%100 + 31 ) / 60)> 5 -- Greater than 5 mins
AND jb.name='Job infinite loop'
ORDER BY JobName, JobRunDate DESC

Get job history last executed by whom
--- Script By: Amna Asif___________________For: sqlcache.blogspot.com
--- Purpose: Get job history last executed by whom  

SELECT case jb.[enabled] when 1 then 'Active' else 'Inactive' end JobCurrentStatus
, jb.name JobName , ja.start_execution_date LastExecutionDate
, Case ja.run_requested_source when 1 then 'SCHEDULER'
                               when 2 then 'ALERTER'
                               when 3 then 'BOOT'
                               when 4 then 'USER'
                               when 6 then 'ON_IDLE_SCHEDULE' else 'Unknown' end 'JobRunSource'
,Case jh.run_status when 0 then 'Failed'
                              when 1 then 'Succeeded'
                              when 2 then 'Retry'
                              when 3 then 'Stopped' else 'Unknown' end  JobRunStatus

FROM msdb.dbo.sysjobs AS jb
INNER JOIN msdb.dbo.sysjobactivity AS ja ON ja.job_id = jb.job_id
INNER JOIN msdb.dbo.sysjobhistory jh on jh.instance_id=ja.job_history_id
WHERE exists (  SELECT sja.job_id,MAX(sja.start_execution_date)
                FROM msdb.dbo.sysjobactivity sja
                WHERE start_execution_date IS NOT NULL
                AND sja.job_id=ja.job_id
                AND sja.start_execution_date=ja.start_execution_date
                group by sja.job_id
              )
ORDER BY ja.start_execution_date DESC

Get jobs list by notification alter operators
--- Script By: Amna Asif___________________For: sqlcache.blogspot.com
--- Purpose: Get jobs list by notification alter operators 

SELECT jb.name JobName, 
CASE notify_level_email WHEN 0 THEN 'Never'
WHEN 1 THEN 'When the job succeeds'
WHEN 2 THEN 'When the job fails'
WHEN 3 THEN 'Whenever the job completes (regardless of the job outcome)' 
ELSE 'Unknown' END  'NotificationEvent'
, isnull(op.name ,'') OperatorName
, Case WHEN op.name is not null THEN Case op.[enabled] when 1 then 'Active' else 'Inactive' end  ELSE '' END 
FROM msdb.dbo.sysjobs jb left join msdb.dbo.sysoperators op
on jb.notify_email_operator_id=op.id
ORDER BY isnull(op.name ,''), jb.name 

Saturday 22 November 2014

SQL Server Database Mail

Database Mail is a great feature for sending email messages and notification alerts from SQL Server Database Engine. Once you configured SMTP settings, database applications can send email messages which may contain query results, include files and notification alerts. Database Mail uses msdb service broker queues to send email messages and logs email activity to msdb database tables.

There are two ways to configure Database Mail. I prefer T-SQL because we run the same setup on multiple servers.
  1. Transact-SQL
  2. Database Mail Configuration Wizard
Example in post shows to configure SQL Server Database Mail using Gmail Account.

Method 1: Transact-SQL
  • Enable Database Mail
sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE 'Database Mail XPs', 1
GO
RECONFIGURE
GO
  • Create Database Mail Account
--- Script By: Amna Asif___________________For: http://sqlcache.blogspot.com  
--- Purpose: Create Database Mail Account

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'DBA',
@email_address = sqlcache@gmail.com', -- change valid email address
@display_name = 'SQL Cache DBA Mail',
@description = 'Database Mail Account',
@mailserver_name = 'smtp.gmail.com' ,
@mailserver_type =  'SMTP' ,
@port =  587 ,
@username =  'sqlcache@gmail.com',  -- change valid email address
@password =  'password',   -- email password
@use_default_credentials=0,
@enable_ssl=1
Parameter
Description
@use_default_credentials
When this value 1, Database Mail uses the credentials of Database Engine.

 When this value 0, Database Mail uses the @username and @password provided with Account creation for authentication on the SMTP server.
@enable_ssl
if SSL is required on your SMTP server. enable_ssl is bit
  • Create Database Mail profile
--- Script By: Amna Asif___________________For: http://sqlcache.blogspot.com  
--- Purpose: Create Database Mail profile

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'DBA Profile',
@description = 'SQL Cache DBA Profile' ; 
  • Add the account to the profile
--- Script By: Amna Asif___________________For: http://sqlcache.blogspot.com  
--- Purpose: Add the account to the profile

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'DBA Profile',
@account_name = 'DBA',
@sequence_number =1 ; --- check this
  • Grant access to the profile to all users
--- Script By: Amna Asif___________________For: http://sqlcache.blogspot.com  
--- Purpose: Grant access to the profile to all users

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'DBA Profile',
@principal_name = 'public', 
@is_default = 1 ; 
  • Verify DBA profile and accounts
--- Script By: Amna Asif___________________For: http://sqlcache.blogspot.com  
--- Purpose: Verify DBA profile and accounts

SELECT * FROM msdb.dbo.sysmail_account  WHERE  name like '%DBA%'
SELECT * FROM msdb.dbo.sysmail_profile WHERE  name like '%DBA%'
  • Enable less secure apps option on link to receive emails on Gmail account.
  • Send test email to verify setup is configured correctly
--- Script By: Amna Asif___________________For: http://sqlcache.blogspot.com  
--- Purpose: Send test email

EXEC msdb.dbo.sp_send_dbmail 
@profile_name='DBA Profile',
@recipients='sqlcache@gmail.com',
@subject='Test message',
@body='This is the body of the test message.'
  • Verify status of the email message
--- Script By: Amna Asif___________________For: http://sqlcache.blogspot.com  
--- Purpose: Check email message status

--Status of the message 
SELECT last_mod_date, case sent_status 
when 1 then 'Sent Successfully' 
when 2 then 'Failed' 
when 3 then 'Unsent' end sent_status ,[subject] ,recipients 
FROM msdb.dbo.sysmail_mailitems 
ORDER BY last_mod_date DESC

--Reason of the message failure 
SELECT top 1  profile_id,items.last_mod_date,items.[subject],l.[description]  ErrorMessage,items.sent_status,recipients
FROM msdb.dbo.sysmail_faileditems as items 
INNER JOIN msdb.dbo.sysmail_event_log AS l ON items.mailitem_id = l.mailitem_id
ORER BY last_mod_date DESC
  • Update the information of existing Database Mail account if required. In my case I entered wrong password while account creation.
--- Script By: Amna Asif___________________For: http://sqlcache.blogspot.com  
--- Purpose: Update existing Database Mail account 

EXECUTE msdb.dbo.sysmail_update_account_sp 
@account_id = 5, --- ID of DBA account
@account_name = null,
@description = null, 
@email_address = null,
@display_name = null, 
@replyto_address = NULL, 
@mailserver_name = null,
@mailserver_type = null, 
@port = null, 
@timeout = null,
@username =  'sqlcache@gmail.com', --change valid email account 
@password =  'emailpassword', -- email password
@use_default_credentials = 0, 
@enable_ssl = 1 ; 
  • Database Mail can also be configured using Hotmail, Yahoo and AOL SMTP and account details.
SMTP Servers
Media
SMTP Mail Server Name
Port
Gmail
smtp.gmail.com
587
Hotmail
smtp.live.com
587
AOL
smtp.aol.com
587
Yahoo
smpt.mail.yahoo.com
25


Method 2:  D
atabase Mail Configuration Wizard 


















Reference link:
DatabaseMail Messaging Objects

Sunday 9 November 2014

Predicate effect in ON and WHERE clause

There is always a question, Is using predicate in ON clause gives the same result using predicate in Where clause of a query?
Actually it depends on the order in which the conditions of a query are evaluated logically in SQL Server; but if the query is an inner join, then there is no difference between using predicate as part of ON clause or Where. They might generate different result when query is with OUTER joins because the predicates in ON clause applied to the table before the join, whereas WHERE clause is applied to the result of the join.

SQL Server Logical Query Execution Order:

1. FROM
2. ON
3. OUTER
4. WHERE
5. GROUP BY
6. CUBE | ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10 ORDER BY
11. TOP

ON clause predicate with Outer Join:

In case of OUTER JOIN query will follow sequence
SELECT  *
FROM    dbo.Employee emp
        LEFT JOIN dbo.EmployeeStatus es ON emp.Status_ID = es.Status_ID
                                           AND emp.Status_ID <> 2
1.       Table Employee scanning ( Table at left side of the Outer clause )
2.       Table EmployeeStatus scanning with predicate Status_ID<>2


Result Set:

WHERE clause predicate with Outer Join:

In Case of WHERE clause query will follow the sequence
SELECT  *
FROM    dbo.Employee emp
        LEFT JOIN dbo.EmployeeStatus es ON emp.Status_ID = es.Status_ID
WHERE   emp.Status_ID <> 2
1.       Table Employee scanning
2.       Table EmployeeStatus scanning
3.       Result set filtering based on predicate Status_ID<>2


Result Set :

Reference Link:
http://msdn.microsoft.com/en-us/library/ms177634.aspx