Maintenance plan don't stop & don't backup all databases [closed]

Question

I have a server with 100 databases, I have a maintenance plan (MP) for backups. I noticed that the maintenance plan don't backup all databases when I'm looking at the backup folder destination I can see that it stops after 50 databases +-. The job is still in running mode - it doesn't stop.

So in one hand, I can see that there are no new backups files (after the first 50) and on the other, the job is not stopping.

Answer 1

If the job is still running, check what command it's currently executing with Adam Machanic's excellent free stored procedure, sp_WhoIsActive. After installing it (typically in the master database), you can run sp_WhoIsActive to list the running queries and see what command they're executing right now.

There's even a Percent Complete column that gets populated for backups and restores.

You might have a particularly large database (or log file) that's just in the midst of getting backed up.

For further followups & clarification, try taking a picture or copy/pasting the sp_WhoIsActive results in, showing what the maintenance plan's currently doing.

Answer 2

Using these 2 queries you can find what your job is waiting for.

The first query lists all running user processes and if you don't know your job spid you'll find it filtering by program.

The second query will show what your job is waiting for:

select s.session_id, s.host_name, s.login_name, 
       db_name(s.database_id) as db,
       r.percent_complete,
       s.login_time,
       s.last_request_start_time, 
       r.status as r_status,        
       r.wait_type, 
       s.program_name, 
       s.cpu_time, 
       s.reads, 
       s.logical_reads,
       c.num_reads,
       c.num_writes, 
       cast(s.logical_reads / 1024. * 8/ 1024 as decimal(20,2)) as Gb ,
       s.row_count,
       s.writes,
       c.last_read,
       c.last_write,
       r.command, 
       r.wait_time, 
       r.last_wait_type,
       [individual query] = substring(t.text , r.statement_start_offset / 2 + 1, (
                                case
                                    when r.statement_end_offset = - 1
                                    then len(convert(nvarchar(max), t.text)) * 2
                                    else r.statement_end_offset
                                end - r.statement_start_offset
                                ) / 2),

       t.text
       --p.query_plan
from sys.dm_exec_sessions s
     join sys.dm_exec_connections c
        on s.session_id = c.session_id
     /*left*/ join sys.dm_exec_requests r
        on s.session_id = r.session_id  
     outer apply sys.dm_exec_sql_text(r.sql_handle) t 
    -- outer apply sys.dm_exec_query_plan(r.plan_handle) p
where s.is_user_process = 1 and s.session_id  <> @@spid
--order by 2

select s.login_name,      
       wt.*
from sys.dm_os_waiting_tasks wt
     join sys.dm_exec_sessions s
        on wt.session_id = s.session_id
where s.is_user_process = 1 and s.session_id <> @@spid
order by wt.session_id;

Answer 3

Since it was a development environment I restarted the instance.
Problem solved.