How to define a maintenance window under SQL 2008?

Hi,
I have number of databases. I wish to schedule database maintenance jobs on them.
The issue I need to address is a window of hours. I am only allowed 8 hours over sunday night from midnight. I need to finish else kill any jobs overrunning after the 8 hours window.

How can I do this?
crazywolf2010Asked:
Who is Participating?
 
Deepak ChauhanSQL Server DBACommented:
You can use maintenance plan wizard to right click on maintenance folder under sql server instance and opt options what you want to include in maintenance and finalization of wizard you have option to run the job at your convenient time

And now your second question you want to stop job that running more than 8 hrs so to do this you can schedule another job and command is

"EXEC msdb.dbo.sp_stop_job N'maintenance job name' " set the time after 8 hrs from the start time of maintenance job ,,
0
 
crazywolf2010Author Commented:
Hi,
Will the sp_stop_job kill current active job session?

Thanks
0
 
Deepak ChauhanSQL Server DBACommented:
No sp_stop_job N'maintenance job name' this will stop only single job which name you pass as a parameter like as "maintenance job name"   . When you create a maintenance plan than complete maintenance will be executed within a single job .

as you say if job is overrunning 8hrs then kill

suppose maintenance job is schedule at 12:01 Am then  

EXEC msdb.dbo.sp_stop_job N'maintenance job name'  will schedule at 8:02AM or 8:15 Am
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Scott PletcherSenior DBACommented:
The command that is running when you issue the KILL will almost certainly continue running.  Usually once control has been passed to the db command the "kill" will not be recognized until the command completes and control returns to the caller.
0
 
crazywolf2010Author Commented:
Hi,
Thanks for details.
What would be the way to stop it? Basically I want to make sure everything coming from a scheduled job is killed by 8 am.

thanks
0
 
Scott PletcherSenior DBACommented:
To be more clear, I should have said:

"will not be recognized until the CURRENT command completes and control returns to the caller."

For example, say I had a string of ALTER INDEX ... REBUILD commands, like so:

ALTER INDEX table1 ... REBUILD
ALTER INDEX table2 ... REBUILD
ALTER INDEX table3 ... REBUILD
ALTER INDEX table4 ... REBUILD

You KILL the task while table2's rebuild is running.  I think table2's will finish, but then table3 and table4 would not run.

Hmm, can't think of any way around that right now.  Certain commands will only process "interrupts" at specific points in the processing.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.