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.
When running a maintenance plan, it does not succeed or fail, but status is continues to running
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
3 answers
Sort by: Most helpful
-
-
RahulRandive 9,901 Reputation points
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
orsp_whoisactive
stored procedures to identify the blocking process and the process that is being blocked. Thank you! -
LiHongMSFT-4306 28,041 Reputation points
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.