This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(150.4, 17%, 24%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EP%3C/text%3E%3C/svg%3E)
Hello,
We have Sql 2008R2 and we are running Index Maintenance job (Index optimization) part of Ola's maintenance Solution but during job was running and Sql server Shutdown/Restarted and job still showing as in 'Progress'.
This is a Sql Agent job we have set up on Sql Server 2008.
What i should do? What i should check that it's hang or not?
I have checked CommandLog table of Ola's script and it's not showing any error message.
I have also checked SP_WHO2 and it's not showing any locking/blocking.
None of the steps are looks like active but Jobs shows "in progress".
Any hep or guidance appreciated!
Thank You!
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(108.80000000000001, 46%, 20%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EAM%3C/text%3E%3C/svg%3E)
Hi pdsqsql-8017,
Did the answers help you?
Please feel free to let us know if you have any other question.
If you find any post in the thread is helpful, you could kindly accept it as answer. This would benefit the community, and encourage the community member to keep working on your issues.
Best Regards,
Amelia
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(67.2, 1%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETP%3C/text%3E%3C/svg%3E)
That happens sometimes. Restarting SQL Agent usually fixes it.
Also make sure you have the last patches installed.
Thanks Tom.
We have Sql 2008R2 SP2.
I understand that Sql Agent Restart will help i can stop the job.
But i have question as when i restart the SQL Agent, it will restart the actual job?
This is a Maintenance job i don't want to that happen as this is a Production server and other jobs are also running like one of the job is Tran Log Backup.
Any specific reason why it shows 'In Progress'?
Hi pdsqsql-8017,
> when i restart the SQL Agent, it will restart the actual job?
After restarting the SQL Server Agent, the running actual job will be cancelled and stopped and won't be restarted. You need to start the job manually.
>Any specific reason why it shows 'In Progress'?
Could you please check job history to see if there are any error message?
You can try to stop the job and start job manually.
Please refer to the following thread which might help:
https://dba.stackexchange.com/questions/255176/sql-server-job-stuck-even-after-restart
Best Regards,
Amelia
=======================================
Please remember to click " Accept Answer" and upvote the responses that resolved your issue.
Thanks Amelia for your prompt response.
I have already restarted Sql Agent but job history shows still same as "In Progress".
It doesn't looks like cancelled or stopped.
No error messages.
The history will show "In Progress" because it was externally terminated in the middle of running. This will never change.
Did you tried stopping the job from SQL Server agent ?. Restarting SQL Server agent is fine but it will kill any other job which is running. Please first try to stop the job from SQL Server agent if nothing happens let me know.
Please check what is status of the SPID.
Probably, the SPID which ran for the same in Rollback process which may be the reason the jobs shows in progress.
select * from sys.sysprocesses - check if any rollback in progress.
If you have big DB yes, rollback should be happening for indexing job if that fails in between.
Thanks Sreekanth.
I couldn't see any status as "Rollback" while running the query, mostly it status shows either Sleeping or Background or Runnable.
I also tried to stop the job using EXEC MSDB.dbo.sp_stop_job 'Sql Agent Job' but getting message that
"SQLServerAgent Error: Request to stop job 'Sql Agent Job' refused because the job is not currently running.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(35.2, 30%, 13%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETH%3C/text%3E%3C/svg%3E)
Can you start the job? Perhaps there's some whack in the job history tables causing SQL Server/Agent to believe it is running and starting it again will sort that out?
Thanks Tibor for looking into.
I can't start the job as this is a weekly job and it has multiple steps (Ola's Maintenance Solution), normally we run on Weekend due to all Index optimization and other maintenance.
I also tried to run msdb.dbo.sp_sqlagent_refresh_job but it's still shows "In Progress".
Hi pdsqsql-8017,
Thanks for your reply.
Could you please try to delete the job and recreate the job?
In addition, SQL Server 2008 R2 is out of supported. It is recommended to upgrade SQL Server to the newer version.
Best Regards,
Amelia
Thanks Amelia.
Yes, I can try to delete the job and recreate but it's part of whole other script so need to take a part of it.
I know that Sql 2008 is out of support and we have already in our plate to migrate it but mean while need to fix the issue.
Appreciate further suggetions.
I might also try to Rerun the job and stop immediately, it will work and advisable to do?
You can add a jobstep with a long WAITFOR DELAY as the very first step, gives you ample time to cancel the job. That is what I did when I tried if stopping agent in midst job exec would cause this situation (it didn't).