Backup progress

  • We have started the full backup job that uses Veritas netbackup. The server has about 10 databases. 4-5 of them are large databases. There are no transactions showing that relates to backup from the dmv sys.dm_exec_requests but the job is running. I didn’t see any blocking. How to find what the backup job is doing?

    From activity monitor logging & network I/o are showing some wait time.

  • Veritas doesn't show progress bar or other job information ?

    What does SP_WHO2 show for the Veritas SPID ?

    I have used this script, but may not help you if dm_exec_requests shows nothing for backups.

    SELECT session_id as SPID, command, sh.text AS Query, start_time, percent_complete, 
    dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
    FROM sys.dm_exec_requests req CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) sh
    WHERE req.command in ('BACKUP DATABASE','RESTORE DATABASE')

    • This reply was modified 2 months ago by  homebrew01.
    • This reply was modified 2 months ago by  homebrew01.
  • What are you seeing in the logs?

    Do you see a lot of IO Frozen / IO Resumed messaged when the backup is “running”?

    You may be facing slow VSS rather than a SQL bottleneck.

  • No, I am not seeing those messages in the log.

  • What is a sample output of the following query?

    Just want to see where SQL thinks the backups are actually going as if it’s going to a virtual device using VDI or VSS, then the engine offloads the backup command to an alternative provider to do the backup for you so you won’t see any progress in the sessions as it’s all usually done within 10 seconds and then your waiting on VSS or the VDI APIs to complete the remainder of the backup in a different processes.

    SELECT bs.database_name,
    backuptype = CASE
    WHEN bs.type = 'D' AND bs.is_copy_only = 0 THEN 'Full Database'
    WHEN bs.type = 'D' AND bs.is_copy_only = 1 THEN 'Full Copy-Only Database'
    WHEN bs.type = 'I' THEN 'Differential database backup'
    WHEN bs.type = 'L' THEN 'Transaction Log'
    WHEN bs.type = 'F' THEN 'File or filegroup'
    WHEN bs.type = 'G' THEN 'Differential file'
    WHEN bs.type = 'P' THEN 'Partial'
    WHEN bs.type = 'Q' THEN 'Differential partial'
    END + ' Backup',
    CASE bf.device_type
    WHEN 2 THEN 'Disk'
    WHEN 5 THEN 'Tape'
    WHEN 7 THEN 'Virtual device'
    WHEN 9 THEN 'Azure Storage'
    WHEN 105 THEN 'A permanent backup device'
    ELSE 'Other Device'
    END AS DeviceType,
    bms.software_name AS backup_software,
    bs.recovery_model,
    bs.compatibility_level,
    BackupStartDate = bs.Backup_Start_Date,
    BackupFinishDate = bs.Backup_Finish_Date,
    LatestBackupLocation = bf.physical_device_name,
    backup_size_mb = CONVERT(DECIMAL(10, 2), bs.backup_size / 1024. / 1024.),
    compressed_backup_size_mb = CONVERT(DECIMAL(10, 2), bs.compressed_backup_size / 1024. / 1024.),
    database_backup_lsn, -- For tlog and differential backups, this is the checkpoint_lsn of the FULL backup it is based on.
    checkpoint_lsn,
    begins_log_chain,
    bms.is_password_protected
    FROM msdb.dbo.backupset bs
    LEFT JOIN msdb.dbo.backupmediafamily bf
    ON bs.[media_set_id] = bf.[media_set_id]
    INNER JOIN msdb.dbo.backupmediaset bms
    ON bs.[media_set_id] = bms.[media_set_id]
    WHERE bs.backup_start_date > DATEADD(MONTH, - 2, sysdatetime()) --only look at last two months
    ORDER BY bs.database_name ASC,
    bs.Backup_Start_Date DESC;
  • I am seeing that transactional log for database is full due to log_backup messages in the error log whenever I try to run the back. I can’t able to run log back up for that DB.

    The messages sequence are

    Error: 9002 (The transaction log is full)

    Error: 3041 (Backup failed to complete the coomand. Check the backup application)

    Error: 18210 (Backup virtual Device File: Request durable media: Flush failure on backup device. Operating system error 995 (The I/O operation has been aborted because of either a thread exit or an application request)

     

  • If it were me, I'd get on the phone with Veritas .

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Can you run native SQL backups of T-Log and Database so you have some D.R. protection ?

  • Is it require to have any free log space to take the full back of the database or log backup?

  • I tried to run native full backup to local drive. It terminated with the same error message like log full. Does it allow sql backup to local drive when the full database backup was taken by Veritas?

    • This reply was modified 1 month, 4 weeks ago by  ramana3327.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply