Delete SQL Agent Job History

-- delete sql agent job history for named job

EXEC msdb.dbo.sp_purge_jobhistory @job_name = N'data load' ;


-- delete sql agent job history for named job, with date parameters

EXEC dbo.sp_purge_jobhistory  @job_name = N'data load', @oldest_date = '2008-12-31'
 

-- delete all sql agent job history

EXEC msdb.dbo.sp_purge_jobhistory

Management Studio - Scripting Agent Jobs Annoyance :/

I've noticed this a few times, but thought I'd finally note it because I still cannot think of any sensible reason for it. It's an annoyance / feature when you script sql agents jobs in Management Studio via the 'Generate Scripts' option.

To reproduce -
1) Right click an agent job
2) Select 'Script Job as'
3) Select either 'DROP To' or 'DROP And CREATE To' ...

My annoyance is at the top of the generated script in the delete step (sp_delete_job). You'll note below that the job to be deleted is referenced by job_id which is server specific (meaning the script cannot be reused without modification...

IF  EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'my agent job')
EXEC msdb.dbo.sp_delete_job @job_id=N'c3776ebf-d7f4-4b97-bc74-c2d3aa745054', @delete_unused_schedule=1
GO

The modification is a simple one, and is demonstrated below. Pass the name of the job via the @job_name parameter rather than the @job_id. Maybe there is some security / safety reason for this behaviour but it just seems a pain to me...

IF  EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'my agent job')
EXEC msdb.dbo.sp_delete_job @job_name= N'my agent job', @delete_unused_schedule=1
GO

Execute a SQL Agent job from TSQL

exec msdb.dbo.sp_start_job 'jobname'

(does what it says on the tin!)

Find SQL Agent Job Steps that do / don't generate an output file

Find SQL Agent Job Steps that generate an output file for debugging -

select 
  @@servername
, name
, step_name
, output_file_name
from msdb.dbo.sysjobs j
inner join msdb.dbo.sysjobsteps s
on j.job_id = s.job_id
where output_file_name is not null

Find SQL Agent Job Steps without output files -

select 
  @@servername
, name
, step_name
, output_file_name
from msdb.dbo.sysjobs j
inner join msdb.dbo.sysjobsteps s
on j.job_id = s.job_id
where output_file_name is null

Management Studio : Script Multiple Jobs quickly

1. Expand SQL Server Agent
2. Click On 'Jobs'
3. Click F7 (summary pane will appear on right)
4. Highlight the jobs (Ctrl & Shift to select multiple)
5. Right Click selected jobs
6. Select 'Script Job as'
7. Select script destination

Developer Permissions on SQL Agent Jobs

I wanted to grant developers permission to see SQL Agent jobs, but not to mess with them...

USE msdb;
GO
EXECUTE sp_addrolemember @rolename = 'role', @membername = 'username'
GO

Where role is -

SQLAgentReaderRole - allows users to see all jobs
SQLAgentUserRole - allows users to create jobs and to manage the jobs that they create.
SQLAgentOperatorRole - allows users all of the rights assigned to the SQLAgentReaderRole along with the permissions necessary to execute local jobs that they do not own.

TSQL : Agent Job Notififications via email

A quick run through of setting email notifications for emails

1) Set Sql agent to use a database mail profile.

-- Set default database mail profile for sql agent
-- My dbamail profile name is 'SQL Operator'

USE [msdb]
GO
EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', N'SQL Operator'
GO

2) Add an operator (email recipient) for the alerts.

-- Add Operator Recipient for Sql Agent jobs
USE [msdb]
GO
EXEC msdb.dbo.sp_add_operator @name=N'Agent Job Operator', 
  @enabled=1, 
  @pager_days=0, 
  @email_address=N'dba@mydomain.co.uk'
GO

3) Set the jobs to send emails on failure.

USE [msdb]
GO
sp_update_job 
select 'exec sp_update_job @job_name =  '''+name+''' , @notify_email_operator_name = ''Agent Job Operator'' , @notify_level_email = 2'
from msdb.dbo.sysjobs
where enabled = 1
/*
note values for @notify_level_email signify when to send emails are - 
0 - never
1 - success
2 - failure
3 - always
*/

TSQL : Searching Job Steps

Searches steps of jobs defined on server.
Use to locate sql code performing a certain function.

use msdb
go

select sysjobs.name, sysjobsteps.command
from sysjobs
inner join sysjobsteps
on sysjobs.job_id = sysjobsteps.job_id
where command like '%searchstring%'
order by name