?
Solved

Can I stop a Maintenance Plan Reorganise Index task

Posted on 2009-05-07
3
Medium Priority
?
1,819 Views
Last Modified: 2012-05-06
Is it safe to stop a Maintenance Plan Reorganise Index task? We have a job that has been running for 6 hours and is slowing down our most important server. The job is called DBMaintenance.Index Reorganise (Daily) which, I think, was the name given to it by the company that set up the server. The job appears to originate in the Maintenance Plans section of Management. It contains the following components: Reorganize Index Task, Update Statistics Task, Maintenance Cleanup Task. Assuming one of these is currently running, what will be the consequences of stopping the job?

Should a job of this sort last between 3 and 8 hours (times taken from Log File Viewer)?
Is it possible to see what the server is actually stuck on at the moment?
0
Comment
Question by:Blim2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 14

Expert Comment

by:Jagdish Devaku
ID: 24324026
hi,

do not stop this job mainly the tasks that works on index.

it better you created separate sub plan for reorganize index task and schedule it in the late hours...

statitics task & cleanup tasks will not effect much....



 for your second question... (Is it possible to see what the server is actually stuck on at the moment?)

you run script given in the snippet...

bye....



use master
 
SELECT
             s.spid, BlockingSPID = s.blocked, DatabaseName = DB_NAME(s.dbid),
             s.program_name, s.loginame, ObjectName = OBJECT_NAME(objectid,                      s.dbid), Definition = CAST(text AS VARCHAR(MAX))
 INTO        #Processes
 FROM      sys.sysprocesses s
 CROSS APPLY sys.dm_exec_sql_text (sql_handle)
 
select * from #Processes;
 
WITH Blocking(SPID, BlockingSPID, BlockingStatement, RowNo, LevelRow)
 AS
 (
      SELECT
       s.SPID, s.BlockingSPID, s.Definition,
       ROW_NUMBER() OVER(ORDER BY s.SPID),
       0 AS LevelRow
     FROM
       #Processes s
       JOIN #Processes s1 ON s.SPID = s1.BlockingSPID
     WHERE
       s.BlockingSPID = 0
     UNION ALL
     SELECT
       r.SPID,  r.BlockingSPID, r.Definition,
       d.RowNo,
       d.LevelRow + 1
     FROM
       #Processes r
      JOIN Blocking d ON r.BlockingSPID = d.SPID
     WHERE
       r.BlockingSPID > 0
 )
 SELECT * FROM Blocking
 ORDER BY RowNo, LevelRow
 
Drop table #Processes

Open in new window

0
 

Author Comment

by:Blim2
ID: 24324088
Hi,

Thanks for the code. Can I just confirm what it is doing: is the first result set a list of jobs currently running and the second result set a sub set  of those based on what might be blocking?

Is stopping a reindex job a very bad thing?

Thanks
0
 
LVL 14

Accepted Solution

by:
Jagdish Devaku earned 750 total points
ID: 24324492
hi,

stopping reindex job when it is running is sometimes very risky... as i personally faced lot of issues doing so...

so i dont prefer doing this...

regarding the code details... you are partially right.

in second result set you need to check the last row which might may be in the suspended mode.

please let me know if you still have any questions...

bye...
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

801 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