Share via

When running a maintenance plan, it does not succeed or fail, but status is continues to running

준영 이 40 Reputation points
2024-01-13T17:11:05.2166667+00:00

It used to be used normally, but it has been operating in the same state as the title since some time ago.. Even a simple query will last for several days. I ask for your help. ex query) select 1

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,869 questions
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 102.2K Reputation points
    2024-01-13T17:17:57.02+00:00

    What I would do If I were to run into this situation is to run my beta_lockinfo, which shows the current activity in the server. It would tell me if the maintenance plan is stuck somewhere in SQL Server. For instance, an index rebuild being blocked by a transaction left open.

  2. Rahul Randive 8,826 Reputation points Microsoft Employee
    2024-01-13T17:36:31.09+00:00

    Hi 준영 이 •, Verify if there have been any changes to the maintenance schedule or the introduction of new activities during the same time, which might be causing interference with maintenance and other routine queries. If blocking is happening on your server, please refer document “Understand and resolve SQL Server blocking problems” Blocking on SQL Server occurs when one transaction holds a lock on a resource, such as a table or a row, and another transaction is blocked from accessing that resource. This can cause performance issues and can lead to deadlocks if not resolved. Identify the blocking process: Use the sp_who2 or sp_whoisactive stored procedures to identify the blocking process and the process that is being blocked. Thank you!

  3. CosmogHong-MSFT 23,561 Reputation points Microsoft Vendor
    2024-01-15T02:54:27.6266667+00:00

    Hi @준영 이

    Use sys.dm_exec_requests which returns information about each request that is executing in SQL Server.

    Run the query below while the Maintenance Plan is running and check if there are any blocks or anything that the process is waiting on.

    SELECT * FROM sys.dm_exec_requests

    Best regards, Cosmog Hong

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.