pds qsql
asked on
Index Maintenance Job hung during Server Restart - Sql 2008R2
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".
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".
ASKER
Thanks Mate for your response.
I tried to restart Sql Agent but it still shows "In Progress", 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.
Also running some Job status/History query but it not showing as Running.
We are running this job as weekly so i should wait till then as i can't restart the server as it's production server.
I tried to restart Sql Agent but it still shows "In Progress", 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.
Also running some Job status/History query but it not showing as Running.
We are running this job as weekly so i should wait till then as i can't restart the server as it's production server.
Try to run these:
exec msdb.dbo.sp_sqlagent_refresh_jobexec msdb.dbo.sp_start_job 'JobName'
exec msdb.dbo.sp_stop_job 'JobName'ASKER
Thanks Mate once again for your kind support.
I tried Refresh command but still it shows "In Progress"
I can't run now the sp_start_job procedure 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 tried again sp_stop_job procedure but it throwing an error message:
"SQLServerAgent Error: Request to stop job 'Sql Agent Job' refused because the job is not currently running."
I tried Refresh command but still it shows "In Progress"
I can't run now the sp_start_job procedure 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 tried again sp_stop_job procedure but it throwing an error message:
"SQLServerAgent Error: Request to stop job 'Sql Agent Job' refused because the job is not currently running."
You can run start and stop right after each. This way it will be interrupted immediately so the maintenance will not start.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
This is just an entry in msdb database.
That job is actually not running only Agent Job Monitor shows it is running.
Just do a Stop Job command and then it will be fixed automatically.
Next time when you restart the server stop that job manually.