December 16, 2015 at 9:54 am
Hello,
Can you please suggest which table/view in msdb I can hook onto to find the details?
--how many db pages are backed up
-- rate of backup?
Processed 282800 pages for
database 'XYX', file 'XYXdb' on file 1. [SQLSTATE 01000] (Message 4035)
Processed 18 pages for database 'XYX', file 'XYX_log' on file 1. [SQLSTATE 01000]
(Message 4035) BACKUP DATABASE successfully processed 282818 pages in 41.005 seconds (53.884 MB/sec). [SQLSTATE 01000]
Thanks.
December 16, 2015 at 11:05 am
The number of pages can be found by summing the backed_up_page_count in msdb.dbo.backupfile.
The throughput can be calculated a couple different ways. You could use backup_size in the msdb.dbo.backupset table, using backup_start_date and backup_finish_date in that table to calculate duration (be careful with that, as on very small backups the datetime shown for start and finish can be the same). You could also just use the page_count instead of backup_size (converting from pages to MB, of course).
The throughput calculated that way can also vary a bit from what you actually see in the output from the backup command, because the duration calculated from the start and finish dates won't always give you the exact same duration shown in the output of the backup command (it's usually only about a second or so difference).
On longer-running backups, that won't have much effect, but on very short backups you might see a bigger discrepancy in reported throughput for that reason.
Here's one such way of doing it, using a CASE to avoid divide by zero by assuming any backups with the same datetime for start and finish actually took 3 milliseconds.
SELECT backup_start_date,
backup_finish_date,
database_name,
[type],
pages=SUM(backed_up_page_count),
MB_per_sec=SUM(backed_up_page_count)/
(CASE WHEN backup_start_date!=backup_finish_date
THEN DATEDIFF(ms,backup_start_date,backup_finish_date)
ELSE 3
END
) /.128
FROM msdb.dbo.backupset bs INNER JOIN msdb.dbo.backupfile bf
ON bs.backup_set_id=bf.backup_set_id
GROUP BY backup_start_date, backup_finish_date, database_name, [type]
ORDER BY bs.backup_start_date DESC;
Cheers!
December 16, 2015 at 11:38 am
Thnx Jacob..
Even I wrote this one . But I liked your script.
SELECT TOP 100
s.server_name,
s.database_name,
m.physical_device_name,
CAST(CAST(s.backup_size / 1048576 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
CAST(CAST(s.compressed_backup_size / 1048576 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS CompressedbkSize,
Convert (real,(CAST(s.backup_size / 1048576 AS real)) /nullif( (CAST(DATEDIFF(second, s.backup_start_date,s.backup_finish_date) AS real)),0)) as 'Backup_Speed [MB/Sec]',
convert(real,(s.compressed_backup_size/s.backup_size) * 100) as [Compression Rate%],
((CAST(CAST(s.backup_size / 1048576 AS INT) AS VARCHAR(14)) * 1024) / 8) as DBPagesBackedUP,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
s.backup_start_date,
s.backup_finish_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,
user_name
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
where s.[type] = 'D'
ORDER BY backup_start_date DESC, backup_finish_date
Thanks.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply