Solved

Can I stop a Maintenance Plan Reorganise Index task

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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
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…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

717 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