Solved

Can I stop a Maintenance Plan Reorganise Index task

Posted on 2009-05-07
3
1,597 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
Comment Utility
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
Comment Utility
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
Comment Utility
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 Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
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…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

763 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

12 Experts available now in Live!

Get 1:1 Help Now