• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1952
  • Last Modified:

Can I stop a Maintenance Plan Reorganise Index task

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
Blim2
Asked:
Blim2
  • 2
1 Solution
 
Jagdish DevakuSr DB ArchitectCommented:
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
 
Blim2Author Commented:
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
 
Jagdish DevakuSr DB ArchitectCommented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now