x

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 .

more ▼

asked Dec 22, 2016 at 04:18 PM in Default

avatar image

shamim007
210 6

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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....

more ▼

answered Dec 22, 2016 at 05:55 PM

avatar image

kevaburg
21 2

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

Dec 22, 2016 at 07:30 PM shamim007
(comments are locked)
10|1200 characters needed characters left

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?

more ▼

answered Dec 23, 2016 at 10:12 AM

avatar image

kevaburg
21 2

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

Dec 23, 2016 at 01:34 PM shamim007
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Dec 23, 2016 at 06:17 PM

avatar image

shamim007
210 6

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2170
x150
x17

asked: Dec 22, 2016 at 04:18 PM

Seen: 77 times

Last Updated: Dec 23, 2016 at 06:17 PM

Copyright 2017 Redgate Software. Privacy Policy