none
Backup status in Processing

Answers

  • This should help:

    select database_name,  datediff(mi, backup_Start_Date, backup_finish_date) Duration_in_minutes ,backup_Start_Date from msdb.dbo.backupset
    where type='D'

    • Marked as answer by S.Vijay Kumar Friday, April 21, 2017 8:37 PM
    Friday, April 21, 2017 8:09 PM

All replies

  • Hello Vijay,

    Could you please post the results of this query -

    SELECT command, s.text, start_time, percent_complete,
    CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
    + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
    + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
    CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
    + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
    + CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
    dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time
    FROM sys.dm_exec_requests r
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
    WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')
    

    Friday, April 21, 2017 6:43 PM
  • Hi,

    Please find screenshot below:

    Friday, April 21, 2017 6:48 PM
  • It looks like your backup job is progressing. Why do you believe there is a problem? How large is the databases you are backing up?
    Friday, April 21, 2017 7:05 PM
  • Total all backup size around 9 TB.
    Friday, April 21, 2017 7:25 PM
  • what is version ? are using backup compress ?

    SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time 
    FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a 
    WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE') 
    try use Ola script for backup .

    keep backup information in Error log-->DBCC TRACEON (3226, -1)

    SELECT TOP 100
     s.database_name,
    m.physical_device_name,
    CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
    CAST(DATEDIFF(second, s.backup_start_date,
    s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
    s.backup_start_date,
    CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
    CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
    CASE s.[type] WHEN 'D' THEN 'Full'
    WHEN 'I' THEN 'Differential'
    WHEN 'L' THEN 'Transaction Log'
    END AS BackupType,
    s.server_name,
    s.recovery_model
    FROM msdb.dbo.backupset s
    INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id


    Please Mark it as Answered if it answered your question OR mark it as Helpful if it help you to solve your problem.

    Friday, April 21, 2017 7:47 PM
  • If it is 9TB I think this sort of backup time is to be expected.
    Friday, April 21, 2017 7:50 PM
  • Thank you.

    Actually we are taking full backup every Friday.

    Now how can i check last friday full backup total duration using any T-SQL Script?

    Friday, April 21, 2017 8:05 PM
  • Hi Vishe,

    This script only shows Diff and Log backup.

    But i am looking for Fullbackup.

    Please help.


    Friday, April 21, 2017 8:08 PM
  • This should help:

    select database_name,  datediff(mi, backup_Start_Date, backup_finish_date) Duration_in_minutes ,backup_Start_Date from msdb.dbo.backupset
    where type='D'

    • Marked as answer by S.Vijay Kumar Friday, April 21, 2017 8:37 PM
    Friday, April 21, 2017 8:09 PM
  • Use Hilary's query instead. It filters so you only see the full backups.

    The problem with Vishe's query is that it includes the other backup types, and only elects the more 100 recent backups.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Monday, April 24, 2017 1:29 PM
    Moderator