question

shamim007 avatar image
shamim007 asked

stop and schedule index maintenance job

hi Experts , currently i am feeling like i need something to automatically stop my inedex maintenance job on Saturday night .how do i do that "how to check automatically if index maintenance job is running ,if its running stop it "it have to be happen on saturday night before 12 am . please help
sql-server-2008-r2maintenance-plansautomationagentindex-performance
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image JohnM commented ·
Be aware: if you are doing an index rebuild, killing the job would cause a large rollback operation, which is fairly costly within sql server. A reorg of the index won't cause this rollback. Just something to keep in mind if you are trying to dynamically kill jobs.
0 Likes 0 ·

1 Answer

·
Wilfred van Dijk avatar image
Wilfred van Dijk answered
schedule an additional job which executes a "sp_stop_job" , see [link text][1] Much better: use the index maintenance script from Ola Hallengren ([link text][2]) . With this script you can configure the max runtime. [1]: https://msdn.microsoft.com/en-us/library/ms182793.aspx [2]: http://ola.hallengren.com
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.