SQL Server Job History

The following query will give you the job history for a specific job. You will need to find the job_id of the target job before executing and plug it in to the WHERE clause.


--FIND YOUR JOB ID FIRST, plug it in at the bottom
SELECT [job].[job_id] AS [JobID]
 , [job].[name] AS [JobName]
 , CASE
 WHEN [jh].[run_date] IS NULL OR [jh].[run_time] IS NULL THEN NULL
 ELSE CAST(CAST([jh].[run_date] AS CHAR(8)) + ' ' + STUFF(STUFF(RIGHT('000000' + CAST([jh].[run_time] AS VARCHAR(6)), 6), 3, 0, ':') , 6, 0, ':') AS DATETIME)
 END AS [LastRunDateTime]
 , CASE [jh].[run_status]
 WHEN 0 THEN 'Failed'
 WHEN 1 THEN 'Succeeded'
 WHEN 2 THEN 'Retry'
 WHEN 3 THEN 'Canceled'
 WHEN 4 THEN 'Running' -- In Progress
 END AS [LastRunStatus]
 , STUFF(STUFF(RIGHT('000000' + CAST([jh].[run_duration] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') AS [LastRunDuration (HH:MM:SS)]
 , [jh].[message] AS [LastRunStatusMessage]
FROM [msdb].[dbo].[sysjobs] AS [job]
 LEFT JOIN (SELECT
 [job_id]
 , [run_date]
 , [run_time]
 , [run_status]
 , [run_duration]
 , [message]
 , ROW_NUMBER() OVER (PARTITION BY [job_id] ORDER BY [run_date] DESC, [run_time] DESC) AS RowNumber
 FROM [msdb].[dbo].[sysjobhistory]
 WHERE [step_id] = 0
 ) AS [jh]
 ON [job].[job_id] = [jh].[job_id]
WHERE job.job_id = '30B151E5-A422-461C-B118-E856D4BED635'
ORDER BY LastRunDateTime desc

From there you can make charts and stuff. Man I like charts and stuff.

Trigger and BCP

Working on a cludgy interface between two software vendors, I will call them Vendor A and Vendor B. Vendor B wanted a CSV file every time a certain condition happened in a Vendor A table. First thought was to write a trigger on the table in Vendor A’s database. After Insert run a query to see if the condition is met…if the condition is met then send data over to a stage table.

Then run a job every minute or so to query the stage table, run some BCP commands to extract the data in a CSV format.

TRIGGER

CREATE TRIGGER Trigger on TEST_TABLE
after insert
as
IF EXISTS (SELECT * from TEST_TABLE as c JOIN inserted AS I ON c.column = i.column where i.columnb = ‘Condition’)
BEGIN
INSERT INTO stage(column1, column2, column3) SELECT column1, column2, column3 from inserted
END
GO

Export Call

declare @cmd varchar(2000)
select @cmd = ‘bcp “select * from stage” queryout c:\audit_’+convert(varchar(8),getdate(),112)+’_’+Replace(CONVERT(CHAR(8),(CONVERT(DATETIME,CURRENT_TIMESTAMP,113)),114), ‘:’, ”)+’.csv -t, -T -c’
EXEC xp_cmdshell @cmd
GO
delete from stage

The Replace(CONVERT(CHAR(8),(CONVERT(DATETIME,CURRENT_TIMESTAMP,113)),114), ‘:’, ”) gives me miltary time…like that a lot. I will store that for later use.

Anyway, this works pretty well in testing. May need to make some tweaks before going live. I first tried running the BCP in the trigger itself, but had the trigger freeze problem that others reported and read some articles about how this is probably not the best idea. This way is not as realtime, but works well for what I need.

Error 14274

So I was trying to modify some of my SQL Agent jobs and I got an Error: 14274. Wild.

Background:
We had to rename our production server after software upgrade this weekend. We did not want anyone hitting the old server. The problem started.

Solution:

1. Run a select @@SERVERNAME. Verify that you have the correct server name. The server name was incorrect due to the server rename so I had to run the following commands:
sp_dropserver ‘Old Server Name’

sp_addserver ‘New Server Name’

You then have to restart the SQL Service. Once you restart run the select @@SERVERNAME command again and verify the name is correct.

2. Select * from msdb..sysjobs. This will show you the Originating Server Column. In my case this was the original server name.

3. Run an update statement to fix the server name: update sysjobs set originating_server = ‘New Server Name’ where originating_server = ‘Old Server Name’

After that final update statement I was then able to update/edit/delete the jobs.

Backup Database to UNC

Mainly to remind me how to backup from SQL Server to a UNC, but maybe this will provide you with a quick step by step and save you some time. Details, performing a Full Database Backup of a SQL Server 2000 database to a UNC. Both the source and destination servers are in the same domain. The SQL Server Service and the SQL Server Agent Service are both started using a domain account.

  1. Set the SQL Server Server service to start as a domain account that has appropriate permissions (Read/Write) to your UNC path
  2. Run the setspn tool to register your domain user servicePrincipalName field in AD. You can use ADSIEDIT.msc console to check the status of the command. setspn MSSQLSvc/ServerName.domain.com Domain\User [see my Epic quest article for more details]
  3. Create the SQL backup job to backup the database to the UNC path: BACKUP DATABASE [dbname] TO  DISK = N’\\servername\share_name\filename.bak’ WITH  INIT ,  NOUNLOAD ,  NAME = N’backup name’,  NOSKIP ,  STATS = 10,  NOFORMAT
  4. Celebrate a job well done, perhaps with a Mt. Dew or your choice of work approved beverage.

Scripting SQL Agent Jobs 2005 and 2000

So I started working on a web site that would list out all the SQL Server Agent jobs for easy access. After several revisions I came up with a set of DTS packages that would hit each SQL server to store the Job list (sysjobs), Job Step Information (sysjobsteps), and Job History Information (sysjobhistory) into a single central server. From there I just created an ASP web page that allows me to see all jobs from a single source. I keep the last 10 days of history in my central repository.

So far it has been pretty handy. One cool thing about the web site is that it contains the actual command of the job step. So if I am in a pinch and I need to recreate a job for some reason, I can quickly pull the job up and check it out. Good for database restores where I do not bring the msdb database over in the restore.

I was also thinking that it would be handy to have the actual .sql script stored off in a location so I could actually just run a script to recreate the jobs. Handy for those servers that have two dozen complex jobs. Not such a big deal for servers that have a single backup job, but to recreate two dozen jobs on a server would take some time.

SQL Server 2000: Just browse down the the SQL Agent in Enterprise Manager and right click on Jobs…Choose the All Tasks option and Generate SQL Script. This will let you generate a single SQL Script for all the jobs. I just store this SQL off on a separate server. I run this periodically, especially when jobs are added/deleted, etc.

SQL Server 2005: Thanks to the helpful tip here: http://www.sqlservercentral.com/Forums/Topic452319-146-1.aspx from Adam. You can just click on the jobs folder under SQL Server Agent, then on the right hand side in the Object Explorer Details you can highlight all the jobs and then Right Click, Script Job as…Create To…File, or New Query Window.

So now I have the web site to see a quick view of all my jobs and I also have the .sql Script to actually build the jobs if necessary. All of this to ease the recovery steps.

SQL Job Monitor

Stumbled on this cool little tool yesterday: SQLJobVis. SQLJobVis is a desktop client application that allows you to view the SQL Agent jobs on your servers. You just add the server you want to connect to and it will connect and show you a timeline of the jobs. Pretty easy.

The one thing I wish the app  would do is allow you to view multiple servers. You have to view the servers one by one. It would be pretty gangster if the allowed multiple servers and then a way to export the timeline. Then you could have a timeline of all your SQL Agent jobs. Anyway, it is still a pretty cool tool and worth checking out.

http://www.sqlsoft.co.uk/

sysjobhistory run_duration Conversion

Below is a quick script to convert the run_duration column into 3 separate columns for Hours, Minutes, and seconds. The script will also convert the run_date column to a date. Useful for dropping into Excel and running reports.

SELECT
 runDate = CONVERT (DATETIME, RTRIM(run_date)),
 run_duration/10000 Hours, --hours
 run_duration/100%100 Minutes, --minutes
 run_duration%100 Seconds --seconds
 from sysJobHistory

Just add a job_id in your where clause if you want to track a single job. I used this script to generate a report of run times for a specific job. We had a SQL job run long (so we thought) and wanted to drill into the numbers a bit. After some investigation we discovered that the job was pretty consistent and that the long run time was consistent with the job history.

You can also just combine the hours, minutes, and seconds into 1 column like so:

SELECT
 runDate = CONVERT (DATETIME, RTRIM(run_date)),CAST(run_duration/10000 as varchar)  + ':' + CAST(run_duration/100%100 as varchar) + ':' + CAST(run_duration%100 as varchar)
 from sysJobHistory

From there you can paste the data into Excel and chart it out or do whatever else you want to do with that data.jobscatter