Mustafa EL-Masry Event

Next SQL Gulf event “SQL Gulf #2 “ at Microsoft Dubai


logo

SQL Gulf Community reveals about its upcoming SQL Gulf event “SQL Gulf #2” at Microsoft Dubai on 5/9/2015, It is the first SQL Server event ever in the Middle East to talk about SQL Server 2016 ..!
SQL Gulf #1 was at Saudi Arabia, Riyadh on 30/8/2014 , indeed it was extremely successful one that is why we are so excited to spread the word everywhere in the Gulf through many SQL Gulf events at all Gulf cities ,interestingly said that the top notch SQL Server experts and most ever popular speakers are coming from US, UK ..etc are coming to speak to you  at SQL Gulf #2 like Kevin Kline from US and Satya Shyam K Jayanty from UK ..etc  and more importantly to know it is the first SQL Server event in the Middle East ever to talk about SQL Server 2016…

Register here to reserve your seat ASAP, seats are limited , here below is the event program:

Session Abstracts:

1-FIRE! A Fullproof Checklist for Tuning and Troubleshooting

Session Abstract: Learning how to detect, diagnose and resolve performance problems in SQL Server is tough.  Often, years are spent learning how to use the tools and techniques that help you detect when a problem is occurring, diagnose the root-cause of the problem, and then resolve the problem.
In this session, attendees will see demonstrations of the tools and techniques which make difficult troubleshooting scenarios much faster and easier, including:
•             XEvents, Profiler/Traces, and PerfMon
•             Using Dynamic Management Views (DMVs)
•             Advanced Diagnostics Using Wait Stats
•             Reading SQL Server execution plan

Every DBA needs to know how to keep their SQL Server in tip-top condition, and you’ll need skills the covered in this session to do it.
Prerequisites: Intermediate database administration and development skills, especially competence with SSMS.
Goal 1: Learn the “sieve” method of troubleshooting and problem solving, and how to make SQL Server alert you when problems arise.
Goal 2: Discover how to use wait stat analysis, as well as correlate performance information from other sources inside of SQL Server including DMVs, performance counters, and Xevent/trace information.
Goal 3: Learn how to use the most important native tools within SQL Server through live demos to successfully conduct troubleshooting and performance tuning.

Summary: Microsoft ships a multitude of tools to help detect, diagnose and resolve problems inside of SQL Server. But which is best to use and when? This session teaches attendees how to tackle the troubleshooting process to achieve repeated, optimal results.

2-What do I need to know about Data Platform Upgrade best practices & techniques

Data Platform Upgrade topic has been a popular session that I’ve presented in major conferences like Microsoft Tech-Ed (North America, Europe & India), SQLPASS, SQLSaturdays and SQLbits since the year 2008.

In this session, we will overview in depth end-to-end upgrade process that covers the essential phases, steps and issues involved in upgrading SQL Server 2000, 2005, 2008 R2 & SQL Server 2012/2014 (with a good overview on 2016 too) by using best practices and available resources.
We will cover the complete upgrade cycle, including the preparation tasks, upgrade tasks, and post-upgrade tasks. Real-world examples from my Consulting experience expanding on why & how such a solution will work in critical situations.

3-Performance Dreams started at SQL Server 2014 and come true now at SQL Server 2016

“Performance Dreams started at SQL Server 2014 and come true now at SQL Server 2016”
, it is just like this because you Microsoft launched live SQL Server 2014 with unbelievable performance reads and substantial improvement that you cannot give up them , come in here to my session and you will know more about many new features and rich powers of SQL Server 2014 regarding performance particularly like  Microsoft project “Hekaton” for In-memory built in for OLTP , CCI( columnstore index) ,Resource Governor for IO consumption ,  lock priority management  and also Single partition online index rebuild technologies ,you will get much hands-on experience  for all definitions , architecture design , values and benefits ,caveats  and recommendations related to each one of them so that you can drive a conscious decision for upgrading your DBs to SQL Server 2014 but keep in mind that are some limitations for those features that should be considered largely before  production deployments …If not ,don’t be upset as it Is still not the end of way coz Microsoft had really rolled out SQL Server 2016 which could address successfully many of those limitations and indeed SQL Server 2014 performance dreams come true at SQL Server 2016, I will speak there simply to help those who didn’t breath SQL Server for the last little while

4-SQL Server Internals and Architecture

Session Abstract: Let’s face it.  You can effectively do many IT jobs related to SQL Server without knowing the internals of how SQL Server works.  Many great developers, DBAs, and designers get their day-to-day work completed on time and with reasonable quality while never really knowing what’s happening behind the scenes.  But if you want to take your skills to the next level, it’s critical to know SQL Server’s internal processes and architecture.  This session will answer questions like:

–       What are the various areas of memory inside of SQL Server?
–       How are queries handled behind the scenes?
–       What does SQL Server do with procedural code, like functions, procedures, and triggers?
–       What happens during checkpoints?  Lazywrites?
–       How are IOs handled with regards to transaction logs and database?
–       What happens when transaction logs and databases grow or shrinks?

This fast paced session will take you through many aspects of the internal operations of SQL Server and, for those topics we don’t cover, will point you to resources where you can get more information.  So strap on your silly, as we cover all these topics and more at speed with tongue planted firmly in cheek!

Prerequisites: Basic understanding of SQL Server operations and activities – such as transactions, queries, and preventative maintenance tasks like backup and recovery.

Goal 1: Learn about the major components within the SQL Server architecture, starting with the relational engine and storage engine, working down to greater and greater detail.
Goal 2: See what happens inside the SQL Server query optimizer and how the query optimizer affects transaction execution time, both read-only and read-write transactions
Goal 3: Review the major operational processes inside of SQL Server that affect memory management and IO activity.

5-Power BI deployment best practices to deliver data analytics to the business

How to deploy Power BI, how to implement configuration parameters and package BI features as a part of Office 365 roll out in your organisation.

Having said that, cloud computing is another aspect of this technology made is possible to get data within few clicks and ticks to the end-user. Let us review how to manage & connect on-premise data to cloud capabilities that can offer full advantage of data catalogue capabilities by keeping data secure as per Information Governance standards. Not just with nuts and bolts, performance is another aspect that every Admin is keeping up, let us look into few settings on how to maximize performance to optimize access to data as required.

Gain understanding and insight into number of tools that are available for your Business Intelligence needs.

There will be a showcase of events to demonstrate where to begin and how to proceed in BI world.

Mustafa EL-Masry SQL Server 2014

SQL Server 2014 Health check DMV queries Day 2


images (1)

Hi guys in the Previous post i explained How we can get the SQL Server instance version and How we can know the installed date for SQL Server instance put today is DAY 2 and i will show new DMV in our track Instance Level configuration Queries

DMV#3 Server Properties

below DMV is more helpful and useful this DMV This gives you a lot of useful information about your instance of SQL Server like (Computer name , ProcessID , SQL Server collation  , Product level…etc)

DMV#4 SQL Server Agent Job Information

below DMV it will return for us basic information about SQl Server agent and the user name configure it to review it one by one.

DMV#5 SQL Server Agent Alert Information 

by the below DMV we will return the basic information about SQL Server agent alerts

Check the Full posts and the scripts : http://sqlserver-performance-tuning.net/?p=5924

To check all my posts you can direct to my personal website or you can download this Excel sheet My Technical POSTS

View all my tips , LinkedIn , Website , Slideshare ,Youtube Channel.

Mustafa EL-Masry SQL Server 2014

SQL Server 2014 Service Pack 1 has released


Congratulation Microsoft and big Congratulation for us (SQL Server Releases blog)

sql-server-2014-logo

One year ago, Microsoft launched SQL Server 2014. Today, April 15, Microsoft are pleased to announce the release of SQL Server 2014 Service Pack 1 (SP1). The Service Pack will be available for download on the Microsoft Download Center.

SQL Server 2014 SP1 contains fixes provided in SQL Server 2014 CU 1 up to and including CU 5, as well as a rollup of fixes previously shipped in SQL Server 2012 SP2. For highlights of the release, please read the Knowledge Base Article for Microsoft SQL Server 2014 SP1.

As part of our continued commitment to software excellence for our customers, this upgrade is available to all customers with existing SQL Server 2014 deployments via the download links below. SQL Server 2014 with SP1 will be available in additional venues including the Volume Licensing Center and via Microsoft Update starting May 1, 2015.

Microsoft® SQL Server® 2014 SP1
Microsoft® SQL Server® 2014 SP1 Express
Microsoft® SQL Server® 2014 SP1 Feature Pack

To check all my posts you can direct to my personal website or you can download this Excel sheet My Technical POSTS

View all my tips , LinkedIn , Website , Slideshare ,Youtube Channel.

Mustafa EL-Masry SQl server Administration

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


Hello every body in the previous post we figured more detail about How to monitor and mange your SQL Server jobs ..?  but now all thing What we Covered in the Previous Post ..? we Covered 10 Point

  1. Check SQL Server jobs First Vision..?
  2. Check jobs Status ..? Is it enabled or Disabled ..?
  3. When was SQL agent job was created or when was last time it was modified..?
  4. Identify newly created SQL Server Agent Jobs
  5. Check enabled jobs Without notification setup ..?
  6. retrieving enabled operators in SQL Server Agent
  7. Update SQL Server Agent Jobs without any notification by New notification
  8. SQL Server Agent Job Setup and Configuration Information..
  9. Check jobs With Enabled Schedule ..
  10. Check SQL Server Agent Job Schedule Information..

and i will going today to complete this Subject by Covering another Impressive point :

  1. Configuring SQL Agent Jobs to Write to Windows Event Log.
  2. Generate SQL Agent Job Schedule Report
  3. SQL Server Agent Job Setup and Configuration Information
  4. SQL Server Agent Job Steps Execution Information
  5. Jobs Report by OnSuccessAction and on_fail_action
  6. Check or Change job owner
  7. List by jobs are ruining now on your DB Server.
  8. Jobs With Execution Long Time .
  9. Select Failed Job
  10. How to search on your Jobs TEXT.

let’ s drill down to figure this 10 new point one by one .

  • Configuring SQL Agent Jobs to Write to Windows Event Log.

at the first we need to know the jobs not configured to write in the event log

SELECT [name]
 FROM msdb..sysjobs
 WHERE [notify_level_eventlog] = 0

Let’s Configured this job to write in the Event log

UPDATE msdb..sysjobs
 SET [notify_level_eventlog] = 2
 --WHERE [name] like '%Your Joba Name%'
  • Generate SQL Agent Job Schedule Report

by this report we can figured the

Name : Job Name

active_start_date : Start time for the job Execute.

ScheduleDscr : Description for the job Schedule

enabled : IF = 1 (job Enabled ) IF = 0 (Job Disable)

to generate this report we have 2 Steps First one Create Function , Second one the DMV using CTE (Conman Table Expression )

CREATE FUNCTION [dbo].[udf_schedule_description] (@freq_type INT ,
 @freq_interval INT ,
 @freq_subday_type INT ,
 @freq_subday_interval INT ,
 @freq_relative_interval INT ,
 @freq_recurrence_factor INT ,
 @active_start_date INT ,
 @active_end_date INT,
 @active_start_time INT ,
 @active_end_time INT )
RETURNS NVARCHAR(255) AS
BEGIN
DECLARE @schedule_description NVARCHAR(255)
DECLARE @loop INT
DECLARE @idle_cpu_percent INT
DECLARE @idle_cpu_duration INT
IF (@freq_type = 0x1) -- OneTime
BEGIN
SELECT @schedule_description = N'Once on ' + CONVERT(NVARCHAR, @active_start_date) + N' at ' + CONVERT(NVARCHAR, cast((@active_start_time / 10000) as varchar(10)) + ':' + right('00' + cast((@active_start_time % 10000) / 100 as varchar(10)),2))
RETURN @schedule_description
END
IF (@freq_type = 0x4) -- Daily
BEGIN
SELECT @schedule_description = N'Every day '
END
IF (@freq_type = 0x8) -- Weekly
BEGIN
SELECT @schedule_description = N'Every ' + CONVERT(NVARCHAR, @freq_recurrence_factor) + N' week(s) on '
SELECT @loop = 1
WHILE (@loop <= 7)
BEGIN
IF (@freq_interval & POWER(2, @loop - 1) = POWER(2, @loop - 1))
SELECT @schedule_description = @schedule_description + DATENAME(dw, N'1996120' + CONVERT(NVARCHAR, @loop)) + N', '
SELECT @loop = @loop + 1
END
IF (RIGHT(@schedule_description, 2) = N', ')
SELECT @schedule_description = SUBSTRING(@schedule_description, 1, (DATALENGTH(@schedule_description) / 2) - 2) + N' '
END
IF (@freq_type = 0x10) -- Monthly
BEGIN
SELECT @schedule_description = N'Every ' + CONVERT(NVARCHAR, @freq_recurrence_factor) + N' months(s) on day ' + CONVERT(NVARCHAR, @freq_interval) + N' of that month '
END
IF (@freq_type = 0x20) -- Monthly Relative
BEGIN
SELECT @schedule_description = N'Every ' + CONVERT(NVARCHAR, @freq_recurrence_factor) + N' months(s) on the '
SELECT @schedule_description = @schedule_description +
CASE @freq_relative_interval
WHEN 0x01 THEN N'first '
WHEN 0x02 THEN N'second '
WHEN 0x04 THEN N'third '
WHEN 0x08 THEN N'fourth '
WHEN 0x10 THEN N'last '
END +
CASE
WHEN (@freq_interval > 00)
AND (@freq_interval < 08) THEN DATENAME(dw, N'1996120' + CONVERT(NVARCHAR, @freq_interval))
WHEN (@freq_interval = 08) THEN N'day'
WHEN (@freq_interval = 09) THEN N'week day'
WHEN (@freq_interval = 10) THEN N'weekend day'
END + N' of that month '
END
IF (@freq_type = 0x40) -- AutoStart
BEGIN
SELECT @schedule_description = FORMATMESSAGE(14579)
RETURN @schedule_description
END
IF (@freq_type = 0x80) -- OnIdle
BEGIN
EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
N'IdleCPUPercent',
@idle_cpu_percent OUTPUT,
N'no_output'
EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
N'IdleCPUDuration',
@idle_cpu_duration OUTPUT,
N'no_output'
SELECT @schedule_description = FORMATMESSAGE(14578, ISNULL(@idle_cpu_percent, 10), ISNULL(@idle_cpu_duration, 600))
RETURN @schedule_description
END
-- Subday stuff
SELECT @schedule_description = @schedule_description +
CASE @freq_subday_type
WHEN 0x1 THEN N'at ' + CONVERT(NVARCHAR, cast((@active_start_time / 10000) asvarchar(10)) + ':' + right('00' + cast((@active_start_time % 10000) / 100 asvarchar(10)),2))
WHEN 0x2 THEN N'every ' + CONVERT(NVARCHAR, @freq_subday_interval) + N' second(s)'
WHEN 0x4 THEN N'every ' + CONVERT(NVARCHAR, @freq_subday_interval) + N' minute(s)'
WHEN 0x8 THEN N'every ' + CONVERT(NVARCHAR, @freq_subday_interval) + N' hour(s)'
END
IF (@freq_subday_type IN (0x2, 0x4, 0x8))
SELECT @schedule_description = @schedule_description + N' between ' +
CONVERT(NVARCHAR, cast((@active_start_time / 10000) as varchar(10)) + ':' +right('00' + cast((@active_start_time % 10000) / 100 as varchar(10)),2) ) + N' and ' + CONVERT(NVARCHAR, cast((@active_end_time / 10000) as varchar(10)) + ':' + right('00' + cast((@active_end_time % 10000) / 100 as varchar(10)),2) )
RETURN @schedule_description
END

With CTE AS (
SELECT dbo.sysjobs.name, CAST(dbo.sysschedules.active_start_time / 10000 ASVARCHAR(10))
+ ':' + RIGHT('00' + CAST(dbo.sysschedules.active_start_time % 10000 / 100 ASVARCHAR(10)), 2) AS active_start_time,
dbo.udf_schedule_description(dbo.sysschedules.freq_type,
dbo.sysschedules.freq_interval,
dbo.sysschedules.freq_subday_type,
dbo.sysschedules.freq_subday_interval,
dbo.sysschedules.freq_relative_interval,
dbo.sysschedules.freq_recurrence_factor,
dbo.sysschedules.active_start_date,
dbo.sysschedules.active_end_date,
dbo.sysschedules.active_start_time,
dbo.sysschedules.active_end_time) AS ScheduleDscr, dbo.sysjobs.enabled
FROM dbo.sysjobs INNER JOIN
dbo.sysjobschedules ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id INNERJOIN
dbo.sysschedules ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id
) Select * from CTE
where ScheduleDscr Like '%DAY%'-----Option
  • Information for SQL Server Job Configuration

by this Script we can now all things bout our job Configuration

<em>
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]

  • SQL Server Agent Job Steps Execution Information

by this Script we can find all info related to the Steps and the Execution

SELECT
 [sJOB].[job_id] AS [JobID]
 , [sJOB].[name] AS [JobName]
 , [sJSTP].[step_uid] AS [StepID]
 , [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]
ORDER BY [JobName], [StepNo]
  • Jobs Report by OnSuccessAction and on_fail_action
SELECT
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]
 , 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]
,[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]
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]
 --Where [sJSTP].[on_success_action] = 2
 Where [sJSTP].[on_fail_action] = 1
ORDER BY [JobName], [StepNo]
[/sq]</pre>
<ul>
          <li><strong>Check or Change job owner</strong></li>
</ul>
<pre>
IF you have 50 job on your DB Server and you need to change the Job owner oneby one it will take more time so we will do DMV to manage this issue 
Check the Owner Job:
1
SELECT
 sv.name AS [Name],
 sv.job_id AS [JobID],
 l.name AS UserName
 FROM
 msdb.dbo.sysjobs_view AS sv
 INNER JOIN [master].[sys].[syslogins] l ON sv.owner_sid = l.sid
 ORDER BY
 sv.[Name] ASC

After we check the job owner now we need to change this job owner by anther one

DECLARE @Jobname Nvarchar
DECLARE @NewOwner varchar(200)
DECLARE @OldName varchar(200)
SET @NewOwner = 'New User Name'
SET @OldName = 'sa'
SELECT
sv.name AS [Name],
sv.job_id AS [JobID],
l.name AS [OwnerName]
INTO #SQLJobs
FROM
msdb.dbo.sysjobs_view AS sv
INNER JOIN [master].[sys].[syslogins] l ON sv.owner_sid = l.sid
WHERE l.name like @OldName
ORDER BY
sv.[Name] ASC
SELECT * FROM #SQLJobs
WHILE (SELECT COUNT(*) FROM #SQLJobs ) > 0 BEGIN
 SELECT TOP 1 @JobID = JobID FROM #SQLJobs
 EXEC msdb.dbo.sp_update_job @job_id= @JobID,
 @owner_login_name=@NewOwner
 DELETE FROM #SQLJobs WHERE JobID = @JobID
END
DROP TABLE #SQLJobs
  • List by jobs are ruining now on your DB Server.
IF EXISTS (SELECT *
FROM tempdb.dbo.sysobjects
WHERE id = OBJECT_ID(N'[tempdb].[dbo].[Temp1]')
)
DROP TABLE [tempdb].[dbo].[Temp1]
GO
CREATE TABLE [tempdb].[dbo].[Temp1]
(
job_id uniqueidentifier NOT NULL,
last_run_date nvarchar (20) NOT NULL,
last_run_time nvarchar (20) NOT NULL,
next_run_date nvarchar (20) NOT NULL,
next_run_time nvarchar (20) NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL,
request_source INT NOT NULL,
request_source_id sysname
COLLATE database_default NULL,
running INT NOT NULL,
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)
DECLARE @job_owner sysname
DECLARE @is_sysadmin INT
SET @is_sysadmin = isnull (is_srvrolemember ('sysadmin'), 0)
SET @job_owner = suser_sname ()
INSERT INTO [tempdb].[dbo].[Temp1]
--EXECUTE sys.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
UPDATE [tempdb].[dbo].[Temp1]
SET last_run_time = right ('000000' + last_run_time, 6),
next_run_time = right ('000000' + next_run_time, 6);
-----
SELECT j.name AS JobName,
j.enabled AS Enabled,
CASE x.running
WHEN 1
THEN
'Running'
ELSE
CASE h.run_status
WHEN 2 THEN 'Inactive'
WHEN 4 THEN 'Inactive'
ELSE 'Completed'
END
END
AS CurrentStatus,
coalesce (x.current_step, 0) AS CurrentStepNbr,
CASE
WHEN x.last_run_date > 0
THEN
convert (datetime,
substring (x.last_run_date, 1, 4)
+ '-'
+ substring (x.last_run_date, 5, 2)
+ '-'
+ substring (x.last_run_date, 7, 2)
+ ' '
+ substring (x.last_run_time, 1, 2)
+ ':'
+ substring (x.last_run_time, 3, 2)
+ ':'
+ substring (x.last_run_time, 5, 2)
+ '.000',
121
)
ELSE
NULL
END
AS LastRunTime,
CASE h.run_status
WHEN 0 THEN 'Fail'
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancel'
WHEN 4 THEN 'In progress'
END
AS LastRunOutcome,
CASE
WHEN h.run_duration > 0
THEN
(h.run_duration / 1000000) * (3600 * 24)
+ (h.run_duration / 10000 % 100) * 3600
+ (h.run_duration / 100 % 100) * 60
+ (h.run_duration % 100)
ELSE
NULL
END
AS LastRunDuration
FROM [tempdb].[dbo].[Temp1] x
LEFT JOIN
msdb.dbo.sysjobs j
ON x.job_id = j.job_id
LEFT OUTER JOIN
msdb.dbo.syscategories c
ON j.category_id = c.category_id
LEFT OUTER JOIN
msdb.dbo.sysjobhistory h
ON x.job_id = h.job_id
AND x.last_run_date = h.run_date
AND x.last_run_time = h.run_time
AND h.step_id = 0
where x.running = 1
CREATE TABLE #enum_job (
 Job_ID UNIQUEIDENTIFIER
 ,Last_Run_Date INT
 ,Last_Run_Time INT
 ,Next_Run_Date INT
 ,Next_Run_Time INT
 ,Next_Run_Schedule_ID INT
 ,Requested_To_Run INT
 ,Request_Source INT
 ,Request_Source_ID VARCHAR(100)
 ,Running INT
 ,Current_Step INT
 ,Current_Retry_Attempt INT
 ,STATE INT
 )
INSERT INTO #enum_job
EXEC master.dbo.xp_sqlagent_enum_jobs 1
 ,garbage
SELECT R.NAME
 ,R.last_run_date
 ,R.RunningForTime
 ,GETDATE() AS now
FROM #enum_job a
INNER JOIN (
 SELECT j.NAME
 ,J.JOB_ID
 ,ja.run_requested_date AS last_run_date
 ,(DATEDIFF(mi, ja.run_requested_date, GETDATE())
 ) AS RunningFor
 ,CASE LEN(CONVERT(VARCHAR(5), DATEDIFF(MI, JA.
 RUN_REQUESTED_DATE, GETDATE()) / 60))
 WHEN 1
 THEN '0' + CONVERT(VARCHAR(5), DATEDIFF(mi, ja.
 run_requested_date, GETDATE()) / 60
 )
 ELSE CONVERT(VARCHAR(5), DATEDIFF(mi, ja.
 run_requested_date, GETDATE()) / 60)
 END + ':' + CASE LEN(CONVERT(VARCHAR(5), (
 DATEDIFF(MI, JA.RUN_REQUESTED_DATE,
 GETDATE()) % 60
 )))
 WHEN 1
 THEN '0' + CONVERT(VARCHAR(5), (
 DATEDIFF(mi, ja.
 run_requested_date, GETDATE()
 ) % 60
 ))
 ELSE CONVERT(VARCHAR(5), (
 DATEDIFF(mi, ja.run_requested_date,
 GETDATE()) % 60
 ))
 END + ':' + CASE LEN(CONVERT(VARCHAR(5), (
 DATEDIFF(SS, JA.RUN_REQUESTED_DATE,
 GETDATE()) % 60
 )))
 WHEN 1
 THEN '0' + CONVERT(VARCHAR(5), (
 DATEDIFF(ss, ja.
 run_requested_date, GETDATE()
 ) % 60
 ))
 ELSE CONVERT(VARCHAR(5), (
 DATEDIFF(ss, ja.run_requested_date,
 GETDATE()) % 60
 ))
 END AS RunningForTime
 FROM msdb.dbo.sysjobactivity AS ja
 LEFT OUTER JOIN msdb.dbo.sysjobhistory AS jh ON ja.
 job_history_id = jh.instance_id
 INNER JOIN msdb.dbo.sysjobs_view AS j ON ja.job_id = j.job_id
 WHERE (
 ja.session_id = (
 SELECT MAX(session_id) AS EXPR1
 FROM msdb.dbo.sysjobactivity
 )
 )
 ) R ON R.job_id = a.Job_Id
 AND a.Running = 1
DROP TABLE #enum_job
  • Jobs With Execution Long Time
DECLARE @HistoryStartDate datetime
 ,@HistoryEndDate datetime
 ,@MinHistExecutions int
 ,@MinAvgSecsDuration int
SET @HistoryStartDate = '19000101'
SET @HistoryEndDate = GETDATE()
SET @MinHistExecutions = 1.0
SET @MinAvgSecsDuration = 1.0
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 HAVINGCOUNT(*) >= @MinHistExecutions
 AND AVG(secs_duration*1.) >= @MinAvgSecsDuration
)
SELECT jd.job_id
 ,j.name AS [JobName]
 ,MAX(act.start_execution_date) AS [ExecutionDate]
 ,AvgDuration AS [Historical Avg Duration (secs)]
 ,AvgPlus2StDev AS [Min Threshhold (secs)]
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 secs_duration > AvgPlus2StDev
AND DATEDIFF(SS, act.start_execution_date, GETDATE()) > AvgPlus2StDev
AND crj.job_state = 1
GROUP BY jd.job_id, j.name, AvgDuration, AvgPlus2StDev
  • Select Failed Job
select J.Name,JH.Run_Status,JH.Message,
convert(datetime, rtrim(JH.run_date))
+ ((JH.run_time/10000 * 3600)
+ ((JH.run_time%10000)/100*60)
+ (JH.run_time%10000)%100) / (86399.9964 ) as run_datetime
,* from msdb..sysjobs J inner join msdb..sysjobhistory JH
on J.Job_ID = JH.job_id
--Where J.Name = 'data_mart_tab4'
where JH.Run_Status = 0
  • How to search on your Jobs TEXT.

You can find my post ( Which Job Executed my Stored Procedures..? ) about this point

Now we Finished the Part of How to monitor and mange your SQL Server jobs ..?  after we Covered 20 Point with more than 20 Script 

Point Covered in the 2 posts of How to manage and Monitor SQL Server Jobs

  1. Check SQL Server jobs First Vision..?
  2. Check jobs Status ..? Is it enabled or Disabled ..?
  3. When was SQL agent job was created or when was last time it was modified..?
  4. Identify newly created SQL Server Agent Jobs
  5. Check enabled jobs Without notification setup ..?
  6. retrieving enabled operators in SQL Server Agent
  7. Update SQL Server Agent Jobs without any notification by New notification
  8. SQL Server Agent Job Setup and Configuration Information..
  9. Check jobs With Enabled Schedule ..
  10. Check SQL Server Agent Job Schedule Information.
  11. Configuring SQL Agent Jobs to Write to Windows Event Log.
  12. Generate SQL Agent Job Schedule Report
  13. SQL Server Agent Job Setup and Configuration Information
  14. SQL Server Agent Job Steps Execution Information
  15. Jobs Report by OnSuccessAction and on_fail_action
  16. Check or Change job owner
  17. List by jobs are ruining now on your DB Server.
  18. Jobs With Execution Long Time .
  19. Select Failed Job
  20. How to search on your Jobs TEXT.
Follow Me 
Mostafa Elmasry