September 24, 2016 at 8:33 am
Good morning Experts,
Index maintenance job got stuck in suspended state. It has been in that state for more than 3 hours and is not making any progress. I checked the waittype and it is showing as PAGEIOLATCH_SH. What should i do to get the job running?
September 24, 2016 at 8:51 am
coolchaitu (9/24/2016)
Good morning Experts,Index maintenance job got stuck in suspended state. It has been in that state for more than 3 hours and is not making any progress. I checked the waittype and it is showing as PAGEIOLATCH_SH. What should i do to get the job running?
The PAGEIOLATCH_SH is telling you that the procedure is waiting on pages to be read. It may or may not be a problem.
What index maintenance job are you using? Maintenance plans? :sick: Ola's scripts? Minion? Your own? Other?
What's the normal execution time for your index maintenance?
Have things changed significantly since the last time you ran it?
Do you know what it's doing right now? If it's been stuck on a 100-row table for 3 hours, you may have a problem with your disk. On the other hand, if you're processing a 1 TB database and it's been waiting for reads on a bunch of tables for the last 3 hours, you may be fine. This points back to what's a normal run time for the job.
September 26, 2016 at 11:05 am
This job has 2 steps. It is stuck in the second step which does the DBCC CHECKDB. DBCC CHECKDB is in suspended state and the waittype it is showing is PAGEIOLATCH_SH. It is running in single thread and there is no blocking.
September 28, 2016 at 10:23 am
Also, I checked percent_complete for checkdb from sys.dm_exec_requests and it is showing as 0.Its SQL Server 2014 EE 64bit SP1. There are only 2 user databases on this instance. Size of One database is about 1.3TB and the size of other database is about 1.2 GB
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply