SQL Log Size Backup

SQL Management studio is run on the machine.After new query the following code is executed.This query shows the last database and log backup date.

 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,
        CASE msdb..backupset.type
            WHEN 'D'
            THEN 'Database'
            WHEN 'L'
            THEN 'Log'
            WHEN 'I'
            THEN 'Inc DB'
            ELSE msdb..backupset.type
        END AS backup_type, 
        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(CONVERT(DATETIME, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
 ORDER BY msdb.dbo.backupset.backup_finish_date DESC;
   

If you want to view log space this query execution ;

DBCC SQLPERF (LOGSPACE)

Leave a Reply

Your email address will not be published. Required fields are marked *