Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Can I stop a Maintenance Plan Reorganise Index task

Posted on 2009-05-07
3
Medium Priority
?
1,907 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

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…
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 tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

609 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