11

So I have a basic SQL agent job that runs a Robocopy script to move all files from one folder to another.

Job is a pretty basic set up. Enabled

With a pretty basic schedule.

Schedule

And yet it has yet to run. I don't mean run successfully either I mean run at all. Is there any reason this might be the case?

For additional information I'll script out the job as well.

USE [msdb]
GO

/****** Object:  Job [MoveMantisFilesToArchive]    Script Date: 12/23/2015 10:21:52 AM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 12/23/2015 10:21:52 AM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'MoveMantisFilesToArchive', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=2, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'Moves Mantis files to archive. It''s a very descriptive title.', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'sa', 
        @notify_email_operator_name=N'MyEmailGroup', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Move the files in the afformentioned title.]    Script Date: 12/23/2015 10:21:53 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Move the files in the afformentioned title.', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'CmdExec', 
        @command=N'robocopy MySoruce MyDestination /mov', 
        @flags=0, 
        @proxy_name=N'RunsAs'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'M-F', 
        @enabled=1, 
        @freq_type=8, 
        @freq_interval=62, 
        @freq_subday_type=1, 
        @freq_subday_interval=0, 
        @freq_relative_interval=0, 
        @freq_recurrence_factor=1, 
        @active_start_date=20151218, 
        @active_end_date=99991231, 
        @active_start_time=170000, 
        @active_end_time=235959, 
        @schedule_uid=N'bcb83273-19e8-49fb-a456-8517642370e3'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO
  • Okay when It was originally set up it was running as the service account. It's since been changed to a different account and is running fine. – Zane Dec 23 '15 at 17:34
4

Commentary on this question: Looking over this post I observe that your job was originally running as 'sa'. It appears that the service account for your SQL Server was not given rights to the necessary file shares.

This is apparently what led to the job looking as if it were "Running" forever. Of course, nothing was actually happening.

It is a best practice to withhold giving the SQL Server service account rights to any non-essential folders. This helps keep the SQL Server environment from being exploited for unsafe activities. (Much the same reason that the xp_cmdshell stored procedure is disabled by default.)

When you switched from sa to an account that had the needed rights to the file system everything worked. Which was, of course, the right thing to do.

Scheduled SQL Agent jobs do sometimes hang (but look like they are still 'running') for a long time. Likely this is usually due to external issues, such as not getting access to file system.

As long as the SQL Agent believes the job is "running", it will not try to start the job again.

Simple lessons:

  1. Think of 'sa' as ruling the SQL Server, but must beg for rights elsewhere.
  2. When reviewing SQL Agent job history, be alert to jobs that have been running much too long. That usually means that the SQL Agent does not realize that the process has died.
  3. Always plan to use a proxy account for SQL Agent jobs that need to access data or objects outside of the SQL Server. And make sure that the rights are granted to the Credential that the Proxy is using.

And, of course, every rule has exceptions.

  • TLDR: Wasn't paying attention and did something stupid. – Zane Jan 5 '16 at 18:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.