September 8, 2020 at 10:01 pm
Hi
Looking for a script which will show me the total amount of transaction logs taken in mb or gigs between 2 dates & times.
thanks in advance
Caz100
September 8, 2020 at 11:03 pm
September 8, 2020 at 11:11 pm
Thanks but nothing there is what I am after.
I need to know what was the total size of transaction log backups between datetime A and datetimeB.
This will give me an idea of what amount of space I require for the transaction log drive incase we have backup failures.
September 9, 2020 at 10:03 am
You could start with this query:
/*
type char(1) Backup type. Can be:
D = Database
I = Differential database
L = Log
F = File or filegroup
G =Differential file
P = Partial
Q = Differential partial
Can be NULL.
*/
declare @DbNameLike sysname
declare @OnlyLastBUset bit
Select @DbNameLike = '%'
, @OnlyLastBUset = 1
select BU.server_name
, BU.machine_name
, BU.database_name
, BU.name as BUName
, dateadd(HH, - ( BU.time_zone / 4 ) , BU.backup_start_date ) as backup_start_date_UTC
, BU.backup_start_date
, BU.backup_finish_date
, case BU.[TYPE]
when 'D' then 'Full'
when 'I' then 'Diff'
when 'L' then 'Log'
when 'F' then 'File or filegroup'
when 'G' then 'Diff file'
when 'P' then 'Partial'
when 'Q' then 'Diff partial'
else '???'
end as BuType
, CAST(BU.backup_size / 1024 / 1024 as decimal(18, 3)) as backup_size_MB
, dateadd(ss, datediff(ss, BU.backup_start_date, BU.backup_finish_date ), '1900-01-01') ElapsSS
/* SQL2008 added compressed_backup_size */
, CAST(BU.compressed_backup_size / 1024 / 1024 as decimal(18, 3)) as COMPRESSED_BU_size_MB
, BU.position
, BU.First_LSN
, BU.Last_LSN
, BU.Checkpoint_LSN
, BU.Database_backup_LSN
, BU.[description]
/* SQL2005 added BU.recovery_model */
--, BU.recovery_model
, BU.[user_name]
-- hebben we niet in gebruik , BU.expiration_date
, BMF.physical_device_name
from msdb.dbo.backupset BU
inner join msdb.dbo.backupmediaset BS
on BS.media_set_id = BU.media_set_id
inner join msdb.dbo.backupmediafamily BMF
on BMF.media_set_id = BU.media_set_id
left join (
select @@ServerName as Server_Name
, D.name as DbName
, max(BU.backup_start_date) as Last_backup_start_date
, max(BU.backup_finish_date) as Last_backup_finish_date
from master.dbo.sysdatabases D
left join msdb.dbo.backupset BU
on D.[Name] = BU.database_name
and BU.[type] = 'D' -- D = FullDatabasebackup
group by D.name
--order by D.name
) LastFullBU
On LastFullBU.Server_Name = BU.server_name
and LastFullBU.DbName = BU.database_name
where BU.database_name like @DbNameLike
and BU.backup_start_date >= case @OnlyLastBUset
when 1 then dateadd(mi, -15, LastFullBU.Last_backup_start_date)
else BU.backup_start_date
end
order by case when BU.[TYPE] in ( 'D', 'I', 'L', 'F', 'G', 'P', 'Q' ) then 1
else 0
end
, BU.database_name
, BU.backup_start_date
, BU.backup_finish_date
, BUName ;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 9, 2020 at 2:34 pm
An alternate approach would be to look at the files in the file system. If you select multiple files (in Windows anyways), it will tell you the file size. And if your files are sorted by the timestamp, it is easy to select a bunch and see the size OR to look at them and know the size.
Something to note though about the TLOG backup size, if you look at it during peak times and slow times, the file size will be different. I have a system where during peak times, my tlog backups are just under 500 MB, but during the downtime it is under 100 KB!
The script provided is definitely nice, but Windows handles this for me automatically, I just need to eyeball the date/time that I want and I am good. The only time it gets a bit more tricky is if I have FULL backups mixed in with the LOG backups, but my file naming convention for backups makes it easy to spot those (plus the file size is drastically larger than my log backups) so I can pick it out pretty easy and deselect those.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply