Solved

Can I stop a Maintenance Plan Reorganise Index task

Posted on 2009-05-07
3
1,633 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
  • 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 250 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

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.

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…
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.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

895 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

14 Experts available now in Live!

Get 1:1 Help Now