• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 326
  • Last Modified:

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?
0
crazywolf2010
Asked:
crazywolf2010
  • 2
  • 2
  • 2
3 Solutions
 
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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now