January 12, 2015 at 7:56 am
You can easily get an overview of all completed backup commands and their generated backup sizes, as they are registered in msdb !
declare @DbNameLike sysname
declare @OnlyLastBUset bit
Select @DbNameLike = '%' -- = 'mydb'
select BU.server_name
, BU.machine_name
, BU.database_name
, BU.name as BUName
, 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
/* 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]
, BU.recovery_model
, BU.[user_name]
, 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
where BU.backup_start_date > DATEADD(MM, datediff(MM, 0, getdate()) - 1, 0) /* offset last month */
and BU.database_name like @DbNameLike
order by BU.database_name
, BU.backup_start_date
, BU.backup_finish_date
, BUName ;
Does this help out?
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
January 12, 2015 at 8:44 pm
Benki Chendu (1/12/2015)
Grant Fritchey (1/12/2015)
Then the implication is that you have some process or other that's hitting 400 of the 500gb worth of pages on your database. Is there a nightly load or some other process that you're doing that would explain it?I am sure its a heavy duty database which is operational most of the time.
How do I find this out?
Just to give you an example, I had a full backup which completed by 1:00 AM and ran a differential backup in sometime which completed by 7:00 AM.
Within this time frame, I am not sure as what all ran on the application size but the size of diff backup was almost 80-90% of the entire full backup.
Step 1 would be to look at your jobs. A poorly formed index maintenance plan could do it or a combination of that and some wholesale data loads could do it. Or, it could be just some serious junk code.
If you have log file backups that occur, take a look at those. Large log file backups will help isolate the time(s) that are causing the problem. If you don't have such Point-in-time backups running fairly often, consider putting such a thing in place immediately. Until you can find and fix the problem, you might want to consider not doing DIFs and "just" doing a nightly backup and a lot of log file backups probably not to exceed 30 minutes apart.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 13, 2015 at 7:09 am
To ask the silly and obvious: Are they both either compressed or uncompressed?
Doing full as compressed & diffs as uncompressed could get you that closeness in size.
January 13, 2015 at 7:10 am
Benki Chendu (1/12/2015)
I see some 12 tables being modified between the full backup and the differential backup taken in the next 5-6 hours but none of them seem to be consuming any space on the disk.
When you say none seem to be consuming any space on the disk, how did you come to that conclusion? Are they staging tables that get truncated or deleted from when their use is over? Did you run a SELECT against them just looking for data or did you actually check their size based on something like the Disk Usage by Top Tables report?
It could be that even empty these tables take up 80-90% of your database size. It's not like SQL Server releases all that space from the tables when they are emptied. After all, it knows it will need that space again.
January 13, 2015 at 8:02 am
Tony++ (1/13/2015)
To ask the silly and obvious: Are they both either compressed or uncompressed?Doing full as compressed & diffs as uncompressed could get you that closeness in size.
I think part of the key here though is if the DIFs are nearly the size of a FULL backup, you might as well take the FULL backup, instead.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 13, 2015 at 8:18 am
Jeff Moden (1/13/2015)
Tony++ (1/13/2015)
To ask the silly and obvious: Are they both either compressed or uncompressed?Doing full as compressed & diffs as uncompressed could get you that closeness in size.
I think part of the key here though is if the DIFs are nearly the size of a FULL backup, you might as well take the FULL backup, instead.
I agree.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply