2

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.

closed as off-topic by Michael Green, Erik Darling, mustaccio, RDFozz, LowlyDBA Oct 3 '17 at 21:30

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Michael Green, Erik Darling, RDFozz, LowlyDBA
If this question can be reworded to fit the rules in the help center, please edit the question.

4

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.

  • 1
    There should also be a log for the MP activity in the \LOG folder for your instance...might give a clue. – Kevin3NF Sep 19 '17 at 11:59
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;
0

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.