mardi 3 février 2015

Calculate total size of transaction log backups in the last 24 hours


I wanted to get the total backup sizes in KB, MB and GB for each database on an instance. I have pieced together the below but haven't been able to do the sum part.



SELECT
A.[Server],
A.DATABASE_NAME,
A.last_db_backup_date,
B.backup_start_date,
B.expiration_date,
CAST(CAST(b.backup_size / 1000 AS INT) AS VARCHAR(14)) + ' ' + 'kB' AS bkSize,
CAST(CAST(b.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'mB' AS bkSize,
CAST(CAST(b.backup_size / 1000000000 AS INT) AS VARCHAR(14)) + ' ' + 'gB' AS bkSize,
B.logical_device_name,
B.physical_device_name,
B.backupset_name,
B.description
FROM
(
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date AS last_db_backup_date
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset
ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE msdb..backupset.type = 'l'
--GROUP BY
-- msdb.dbo.backupset.database_name
) AS A

LEFT JOIN

(
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE msdb..backupset.type = 'l'
) AS B
ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date]
WHERE backup_start_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()
ORDER BY
A.database_name




Aucun commentaire:

Enregistrer un commentaire