Tag Archives: SQL Server Job

Find disabled job in SQL Server

Find disabled job in SQL Server

I, generally, don’t tend to keep disabled job in my server. I would always like to remove disabled job after generating the script and keep the backup of script and this is the reason whenever someone intentionally or unintentionally disabled the job, my monitoring application shows the server name and disable job.

There is one small TSQL SELECT script which shows the disabled job along with server name and last modified date.

SELECT
@@servername as ServerName,
name as JobName,
date_modified as LastModifiedDate
FROM
msdb.dbo.sysjobs WITH (NOLOCK)
WHERE
enabled = 0

BTW, I have written few more articles related to SQL Server Job. If you are interested, have a look at following list:

  • Get list of SQL Server Jobs from multiple instance via SSIS package (Click Here)
  • Find SQL Server Agent job ran on specific date with its status (Click Here)
  • Search SQL Server Job based on keyword provided (Click Here)
  • Script all SQL Server Job automatically by SSIS and SMO (Click Here)
  • Get list of failed SQL Server Agent job in SQL Server (Click Here)
  • Query to Find missing job in SQL Server(Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Get list of failed SQL Server Agent job in SQL Server

failedJobGet list of failed SQL Server Agent job in SQL Server

When you are working with mission critical database environment, you have to closely keep a watch on every database operation being performed. SQL Server jobs are one of the very important critical things to watch at.

We might have jobs for some:

  • heavy aggregation or calculations
  • ETL processes
  • different kind of alerts and logs
  • any much more……..

We may have job running once in a day or many be scheduled to run for few times a day or week. No matter how and when job is running, it is prime duty of DBA to keep watch on each and every job. If any job get failed due to any reason then fix it and run it again.

If job can’t run for some or other reason, it might adversely affect business operation.

Now, question comes, how to keep watch on jobs? You may have many jobs running in SQL Server instance or you may have multiple sql server instance which is executing multiple jobs. I have created on small user defined function and one stored procedure which I execute in SQL Server database and it will give me result of failed job.

If I have recurring job and it failed once but if it runs successfully next time, SP will not give you that job name. Whenever you execute the stored procedure give below, it will check only last occurrence of job, if it is failed, SP will return it to draw your attention towards it.

CREATE FUNCTION [Conv_DateTime](@id int, @it int)
RETURNS DATETIME
AS
BEGIN
DECLARE @vt char(6), @d char(8), @dt datetime
SELECT @d = convert(varchar, @id)

IF len(@it) = 5
SET @vt = '0' + convert(char,@it)
ELSE if len(@it) = 4
SET @vt = '00' + convert(char,@it)
ELSE if len(@it) = 3
SET @vt = '000' + convert(char,@it)
ELSE if len(@it) = 2
SET @vt = '0000' + convert(char,@it)
ELSE if len(@it) = 1
SET @vt = '00000' + convert(char,@it)
ELSE if len(@it) = 6
SET @vt = convert(char,@it)
SELECT @dt = left (@d,4) + '-' + substring(@d,5,2) + '-' + right(@d,2) + ' ' + left(@vt,2) + ':' + substring(@vt, 3,2) + ':' + right(@vt, 2)
RETURN (@dt)
END
GO

Once you are ready with function which will return exact time of when the job failed, we will call that function in following stored procedure.

CREATE PROCEDURE Getfailedjobsdetail
AS
BEGIN
SET NOCOUNT ON
SELECT CAST(Serverproperty('servername') as varchar(100)) AS ServerName,
res.jobname AS JobName,
CONVERT(varchar(5),
DATEADD(ss,(CASE len(RunDuration)
WHEN 1 THEN RunDuration
WHEN 2 THEN RunDuration
WHEN 3 THEN (cast(Left(right(RunDuration,3),1) as int)*60) + (right(RunDuration,2))
WHEN 4 THEN (cast(Left(right(RunDuration,4),2) AS int)*60) + (right(RunDuration,2))
WHEN 5 THEN (cast(Left(right(RunDuration,5),1) AS int)*3600) + (cast(Left(right(RunDuration,4),2) AS int)*60) + right(RunDuration,2)
WHEN 6 THEN (cast(Left(right(RunDuration,6),2) AS int)*3600) + (cast(Left(right(RunDuration,4),2) AS int)*60) + right(RunDuration,2)
END ),
conv_datetime(run_date,run_time)),108)as FailedTime
FROM (SELECT Row_number()
OVER(
partition BY sj.name
ORDER BY run_time DESC) AS rnk,
sj.name AS JobName,
run_date,
run_time,
run_duration AS RunDuration,
run_status
FROM msdb.dbo.sysjobhistory sjh WITH (nolock)
INNER JOIN msdb.dbo.sysjobs sj WITH (nolock)
ON sjh.job_id = sj.job_id
WHERE sjh.step_id <> 0
AND run_date = CONVERT(VARCHAR(8), Getdate(), 112)) res
WHERE res.run_status = 0
AND res.rnk = 1
END
GO

After having SP and Function both, you have to execute the stored procedure like this:

EXEC Getfailedjobsdetail
GO

If any job is failed and that job is not ran again successfully, above SP will give you list for the same. I used to call above given SP from my monitoring web page, developed in asp.net, I have created. This web page execute SP in all the servers I have maintain and list Job name with Server name if it fails. As soon as support person see anything failed on that web page, they used to come to me to find the issue and fix it.

This is making my life very easy. Istn’t it!!!!!

BTW, I have written few more articles related to SQL Server Job. If you are interested, have a look at following list:

  • Find SQL Server Agent job ran on specific date with its status (Click Here)
  • Search SQL Server Job based on keyword provided (Click Here)
  • Script all SQL Server Job automatically by SSIS and SMO (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Script all SQL Server Job automatically by SSIS and SMO

Script all SQL Server Job automatically by SSIS and SMO

As per MSDN, SQL Server Management Objects (SMO) is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server.

I have one of my production server in SQL Server 2012 and I wanted to take backup of SQL Server Job script automatically once in a day.

We may have replication / mirroring / log shipping for database but there are lot more things out side the database too. In time of failover the server, I need all the job in other server too and that is the reason I planned to backup all SQL Server Agent jobs automatically.

Let us see, how it goes!!!

Create one SSIS project and take one “Script Task”. Double click on “Script Task” to open its editor. From the editor, click on “Edit Script” button.

1ScriptTaskEditor

Once you get script editor, you will find namespace section in the script, I have added few more namespace listed below:

using Microsoft.SqlServer.Management.Smo.Agent;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Sdk.Sfc;
using System.IO;
using System.Collections.Specialized;

Here is the code I have written in “Main()” method of script editor.

// TODO: Add your code here
StringCollection strCol = new StringCollection();
ScriptingOptions scriptOpt = new ScriptingOptions();
scriptOpt.IncludeDatabaseContext = true;

ServerConnection conn = new ServerConnection();
conn.LoginSecure = false;
conn.Login = "sa";
conn.Password = "sa";
conn.ServerInstance = "serverName";
Server srv = new Server(conn);

try
{
string script = "";
string JobName;
//Looping through the job
foreach (Job J in srv.JobServer.Jobs)
{
script ="";
JobName = J.Name.ToString();
strCol = J.Script(scriptOpt);

//concate the text of job
foreach (string s in strCol)
{
script += s;
}
//save the job file
TextWriter tw = new StreamWriter("D:\\RiteshShah\\ServerName\\JobBackup\\" + JobName.Replace(':', '_').ToString() + ".sql");
tw.Write(script);
tw.Close();
}
Dts.TaskResult = (int)ScriptResults.Success;
}
catch
{
Dts.TaskResult = (int)ScriptResults.Failure;
}

Here is the screen capture of my script editor.

2ScriptEditor

Now, save the script, close the script editor, click “ok” button for “Script Task” property and run your package. If the path for the script you have provided, is exists and the credential is proper, you will be able generate script of all SQL Server Agent Job.

If you like this article, do like “Extreme-Advicepage in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.