• 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