Backup progress

Question

Post reply

Backup progress

ramana3327

SSCoach

Points: 19722
More actions
November 29, 2022 at 2:31 pm

#4119503

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.

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

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

homebrew01 SSC Guru Points: 55548 More actions · Answer 1

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 years ago by homebrew01.
This reply was modified 2 years ago by homebrew01.

Ant-Green SSC Guru Points: 113507 More actions · Answer 2

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.

ramana3327 SSCoach Points: 19722 More actions · Answer 3

ramana3327

SSCoach

Points: 19722

November 29, 2022 at 5:21 pm

#4119580

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

Ant-Green SSC Guru Points: 113507 More actions · Answer 4

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;

ramana3327 SSCoach Points: 19722 More actions · Answer 5

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)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

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.

Change is inevitable... Change for the better is not.

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

homebrew01 SSC Guru Points: 55548 More actions · Answer 7

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

ramana3327 SSCoach Points: 19722 More actions · Answer 8

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

ramana3327 SSCoach Points: 19722 More actions · Answer 9

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 2 years ago by ramana3327.