backup files get smaller, what could be the reason

Question

backup files get smaller, what could be the reason

sqlfriend

SSC Guru

Points: 52588
More actions
December 6, 2012 at 12:07 pm

#285962

I am just reading from a blog and use a query to check database backup file growth.
I used the query to get data from our databases, I found the database backup size gets smaller in last 5 months - month by month.
I suppose it will get bigger, although maybe the data entered is not many, but at least the size should not reduce for the back up file.
What could be the reason?
SELECT
[database_name] AS "Database",
DATEPART(month,[backup_start_date]) AS "Month",
AVG([backup_size]/1024/1024) AS "Backup Size MB"
FROM msdb.dbo.backupset
WHERE [database_name] = N'Core'
AND [type] = 'D'
GROUP BY [database_name],DATEPART(mm,[backup_start_date]);
http://www.sqlskills.com/blogs/erin/category/baselines.aspx

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Chris Becker SSCrazy Points: 2585 More actions · Answer 1

Could be a few things. When is full bak taken? Immediately after index maintenance? Do you rebuild indexes nightly with a maint plan or under control, ie only rebuild the ones that need it? Full baks also backup log info

Did you start a purge routine? maybe capture tables sizes weekly/monthly to trend

Regards,

Chris

mssqlconsulting.com

Chris Becker bcsdata.net

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

posted below

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

sanket kokane Hall of Fame Points: 3445 More actions · Answer 3

sqlfriends (12/6/2012)
I suppose it will get bigger, although maybe the data entered is not many

It will not get bigger ,if you have maintenance plan for regular backup log.

but at least the size should not reduce for the back up file.

The reason, might be you have comparatively less transactions (insert,update,delete) than before.

-----------------------------------------------------------------------------
संकेत कोकणे

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

Chris Becker (12/6/2012)
Could be a few things. When is full bak taken? Immediately after index maintenance? Do you rebuild indexes nightly with a maint plan or under control, ie only rebuild the ones that need it? Full baks also backup log info

How come these factors reduce the backup files ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Gazareth One Orange Chip Points: 27737 More actions · Answer 5

Dropping NC indexes can obviously reduce space used, and rebuilds of clustered indexes or heaps can also reclaim space.

Are either of these likely to have happened?

Also I notice the query only groups on month not year - are months from previous years included in the backup history?

Could be skewing the figures.

sqlfriend SSC Guru Points: 52588 More actions · Answer 6

Thanks all, that makes sense.

Only month is included is because the backup history we only keep 6 months.

But good point though, I will include year as well, in case there are some old ones not cleaned.