July 2, 2007 at 2:43 am
Hey there,
on a SS2005 Build 3152 i've scheduled a mp with reindexing a 120 GB db. The mp is scheduled to start every sunday at 08.00am.
In the agents history log i can see that it start its job at 8.00am, but the first reindex starts at 11:00pm.
Why do I have such a big delay? Is the job waiting because of some locks, or does the job have to check some things before reindexing? But 15 hours? Is it a bug in mp?
Any ideas?
Thanks in advance
tobias
July 2, 2007 at 4:56 am
It is strange indeed; can you run/save some profiler traces to see what happends in the 15 hours? Are there waits? Can you monitor the wait type?
July 2, 2007 at 5:51 am
Does anyother process or job go in parallel with this job. Particulary check for the backup database job.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 2, 2007 at 8:37 am
Thanks for your feedback.
I run transaction log backups every hour. At 07pm (so 11 hours later) another mp starts, wich backups the whole database and updates the statistics (okay, not very clever to update stats when reindexing).
I do an offline reindex (with enterprise edition on an active-passive cluster).
The curious thing is that the agent starts the job at 08am, but the start time of the mp is 23.xx pm (infos from the view history form), so i don't think it's something with locking, but something with the SQL Server Agent and mp in general or so.
I logged a snapshot of the sysprocesses table every 30 minutes but i can't see anything interesting. Only that the first "alter index" command is set up after 11pm. After 11pm the reindexing and update statistic jobs run concurrent (how will the stats look like when reindexing und update stats at the same time :-))
rg
tobias
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply