question

shamim007 avatar image
shamim007 asked

Reorganize Index agent job

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 .
sql-server-2008indexingagent
10 |1200

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

kevaburg avatar image
kevaburg answered
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....
1 comment
10 |1200

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

shamim007 avatar image shamim007 commented ·
i have to perform full back up at night pretty big size of database .every Saturday night 11.45 pm i stop the job and perform my full backup .12am to 8am is our work load time .after 8am starts all transactions.please help me out to get that query thanks
0 Likes 0 ·
kevaburg avatar image
kevaburg answered
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?
1 comment
10 |1200

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

shamim007 avatar image shamim007 commented ·
My database is 219GB my plan is stop Reorganize job at 11.45 pm and start backup automatically at 3 am .once backup is complete i will do reorganize index .please provide me the scripts .@kevaburg
0 Likes 0 ·
shamim007 avatar image
shamim007 answered
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
10 |1200

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.