HI GURUS , i need a query to create Reorganize Indexes job in agent and schedule it to stop on Saturday night 11.45pm . Job name is Reorganize Indexes .right now my Reorganize Indexes is runs every saturday
4.am .saturday night 11.45 pm i stop job for work load .i want something to stop job saturday at 11.45pm .
I CAME UP TO THIS SCRIPTS CAN ANYONE LOOK AT IT PLEASE AND LET ME KNOW IF I HAVE TO ADD OR MODIFY SOMETHING . USE msdb ; GO EXEC sp_add_schedule @schedule_name = N'Stop Reorganize Indexes' , @freq_type = 8, --- 8 is incating for weekend @freq_interval = 64, --64 is for saturday @freq_recurrence_factor=8, @active_start_time = 234500 ; GO EXEC sp_attach_schedule @job_name = N'Reorganize Indexes', @schedule_name = N'Stop Reorganize Indexes' ; GO IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'Reorganize Indexes') EXEC msdb.dbo.sp_delete_job @job_name=N'Reorganize Indexes' , @delete_unused_schedule=1 GO
Are your Indexes and data filegroups separately stored or everything in a single filegroup? My thinking here is that you could rebuild the clustered indexes then start the backup and during the backup do the same with the nonclustered indexes. This would be very easy to to with a basic maintenance plan. I am loath to provide a script to stop the index rebuilds / reorgs because this is something I believe is far more detrimental to the health of your database than simply letting it run in parallel with the backup. How big is your database at the Moment? How long do you plan for the index maintenance and backup?
Is there a reason you need it to stop? You won't be doing yourself any favours by stopping it halfway through. Alternatively, you could try the scripts from Ola Hallengren. They could help you enormously if if you are having trouble keeping an eye on your jobs....