February 14, 2011 at 4:18 am
how to find the tlog backup size of all databases in sql 2005
February 14, 2011 at 4:23 am
U can query the msdb database which contains the tables storing the backup information.
Cheers,
Satnam
February 14, 2011 at 4:30 am
can u list the query to find ?
February 14, 2011 at 4:37 am
It's not a hard query (though not one I have offhand). There are 5 or 6 backup tables in msdb, all names starting with backup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 14, 2011 at 4:37 am
Dup
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 14, 2011 at 4:50 am
SELECT
DATEPART(dd,MST.backup_start_date)) + '-' + CONVERT(VARCHAR(3),DATENAME(mm, MST.backup_start_date)) + '-' + CONVERT(VARCHAR(4), DATEPART(yyyy, MST.backup_start_date)),'') ,
ISNULL(CONVERT(VARCHAR(100), MST.database_name), ''),
ISNULL(CONVERT(VARCHAR(100), MST.name), ''),
CASE Type
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' 'Log'
WHEN 'F' THEN 'File or Filegroup'
WHEN 'G' THEN 'File Differential'
WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'Partial Differential'
ELSE 'Unknown'
END
ISNULL(CONVERT(VARCHAR(50), MST.backup_start_date), '') ,
ISNULL(CONVERT(VARCHAR(50), MST.backup_finish_date), '') ,
ISNULL(CONVERT(VARCHAR(10), CAST((MST.backup_size/1024)/1024/1024 AS DECIMAL(10,2))), '')
FROM
backupset MST
ORDER BY
MST.backup_start_date DESC
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply