We had a problem where imports would hang when importing from Access MDB's on a nightly basis. I ended up looking at some system stored procedures and based upon that created a job to look for hung import jobs. This job runs after the expected MDB imports should have completed and only looks for jobs classified using a certain name. If an import job is running the check job attempts to stop and restart it. If any import jobs are found hung, an email is sent to an email distribution list.
-- Step 1: Create intermediate work tables
CREATE TABLE #xp_results (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, -- BOOL
request_source INT NOT NULL,
request_source_id sysname NULL,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)
CREATE TABLE ##tsp_help_job
(namesysname)
-- Step 2: Capture job execution information (for local jobs only since that's all SQLServerAgent caches)
DECLARE @is_sysadmin INT
DECLARE @job_owner sysname
SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)
SELECT @job_owner = SUSER_SNAME()
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
-- Step 3: Retrieve executing job names
-- WHICH ARE IDENTIFIED with category 'MDB Import'
DECLARE @category_id INT
SELECT @category_id = category_id
FROM msdb.dbo.syscategories
WHERE (category_class = 1) -- Job
AND (name = 'MDB Import')
INSERT INTO ##tsp_help_job
SELECT sjv.name
FROM #xp_results xpr,
msdb.dbo.sysjobs_view sjv
WHERE (sjv.job_id = xpr.job_id) AND (sjv.category_id=@category_id)
AND (xpr.running = 1)
-- Step 4: Process if found
IF (SELECT COUNT(*) FROM ##tsp_help_job) > 0
BEGIN
DECLARE hungjob_csr CURSOR FOR
SELECT Name FROM ##tsp_help_job
DECLARE @JobName sysname, @RetVal int
OPEN hungjob_csr
FETCH NEXT FROM hungjob_csr INTO @JobName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC @retval = SP_STOP_JOB @job_name=@JobName
IF (@retval = 0)
BEGIN -- wait one minute and try to restart job
WAITFOR DELAY '00:01:00'
EXEC SP_Start_JOB @job_name=@JobName
END
FETCH NEXT FROM hungjob_csr INTO @JobName
IF (@@FETCH_STATUS = 0) --- if another hung job wait 5 minutes
WAITFOR DELAY '00:05:00'
END
DEALLOCATE hungjob_csr
EXEC master.dbo.xp_sendmail @subject='MDB Imports Hung - Attempted to Restart',@recipients='MDBHung_DistList',
@query='SELECT Rtrim(Left(Name,60)) FROM ##tsp_help_job', @no_Header='TRUE'
END
-- Step 5: Clean Up
DROP TABLE ##tsp_help_job
DROP TABLE #xp_results