Solved

How to define a maintenance window under SQL 2008?

Posted on 2012-04-03
6
319 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:
deepakChauhan 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:deepakChauhan
deepakChauhan 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
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…

776 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