Solved

How to define a maintenance window under SQL 2008?

Posted on 2012-04-03
6
321 Views
Last Modified: 2012-04-10
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
Comment
Question by:crazywolf2010
  • 2
  • 2
  • 2
6 Comments
 
LVL 15

Accepted Solution

by:
Deepak Chauhan earned 333 total points
ID: 37802313
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
 

Author Comment

by:crazywolf2010
ID: 37802364
Hi,
Will the sp_stop_job kill current active job session?

Thanks
0
 
LVL 15

Assisted Solution

by:Deepak Chauhan
Deepak Chauhan earned 333 total points
ID: 37802522
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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37803384
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
 

Author Comment

by:crazywolf2010
ID: 37804939
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
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 167 total points
ID: 37806576
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question