Solved

How to define a maintenance window under SQL 2008?

Posted on 2012-04-03
6
316 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
Comment Utility
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
Comment Utility
Hi,
Will the sp_stop_job kill current active job session?

Thanks
0
 
LVL 15

Assisted Solution

by:deepakChauhan
deepakChauhan earned 333 total points
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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
Comment Utility
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:ScottPletcher
ScottPletcher earned 167 total points
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
2008 to 2016 SQL migration.. 5 27
SQL Query Syntax Error 9 29
Auditing with Temporal Tables 4 15
T-SQL Using IN with a subquery 3 12
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now