differential backup almost the same size as full

Question

differential backup almost the same size as full

sqlfriend

SSC Guru

Points: 52781
More actions
February 11, 2013 at 9:31 am

#274038

We have a vendor production (surveryor) with SQL server database. The recovery mode is simple, we don't need to do point of failure recovery. We do a full at Sunday, and daily differential at 9:00 pm weekdays.
Each day the backup file increased a little bit. But I noticed the differential backup of each night is almost same size as full backup, I guess there are a lot of transactions each day, so I wonder to do a full at Sunday and differential backup each day is a good approach or not.
Any recommendations?
Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

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

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 1

Your differential backups will include everything since your last full backup and will grow daily (which you already know), check your transaction log file size, it could be that it's quite large and could use a little shrinking or a large portion of your log file is still active (at a minimum the active portion of the log file must be included in the backup in order to maintain DB consistency).

Do you have any "maintenance" type jobs running during the evening before this differential backup kicks off? Perhaps index maintenance?

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

sqlfriend SSC Guru Points: 52781 More actions · Answer 2

Thanks much.

I checked and currenly the data file is 7.9 GB, the log size is 500 MB with 97% free.

I heard it is not a good practice to shrink log file though, for it will grow any way.

There is no any other job run before the daily differential backup. Reindex happens only at weekend, and it is after differential backup.

Any recommendations?

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 3

My only guess is that this database has a lot of "changing" data. Your differential backup contains everything since the last full backup so if it's nearly the same size as your full, then that would make me think that there's so much going on in this DB daily that it's making your diff's large. Have you tried taking a full backup every 2-3 days with the diffs happening in between?

Something to check: Are you using backup compression? Maybe the Full has compression turned on but the differential does not? Is there any large batch imports going on?

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

sqlfriend SSC Guru Points: 52781 More actions · Answer 4

I think there are a lot of transactions going each day. It is a software that manages our organization each workstation power management.

We have not used the compression yet, I was going to turn on it before the post, but just wonder since the differential bakcup is almost the same size of full, the approach we are using now is almost the same as doing a full backup each day.

But since the differential backup is still a little bit smaller than full, so maybe we should keep this way.

Thanks

Bill (DBAOnTheGo) Hall of Fame Points: 3841 More actions · Answer 5

you will need your full and your differential to restore so you would need both files. If the file is the same size or say 10% +/- of it... I personally would only use a full backup each night. I'm guessing it's taking around the same time as a full anyways? this would mean you only need to restore one file instead of both in the event it goes down. The saving of 1-4GB (hypothetical) may not be worth the time added to restore? just a personal opinion on it.

.

sqlfriend SSC Guru Points: 52781 More actions · Answer 6

sqlfriend

SSC Guru

Points: 52781

February 11, 2013 at 11:05 am

#1586691

Thanks much!

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 7

An 8gb database? Unless you're running it on a VERY tiny little drive, there's no need to do a differential. Just do a FULL on a daily basis. In fact, I've run systems up to 200gb in size where we had enough maintenance time to do a FULL backup each night. It's only as you get past approximately that size that the time to backup and the size of backups make it worth breaking down into differentials, etc.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Abu Dina SSChampion Points: 14155 More actions · Answer 8

Heh... you think this is bad, take a look at one of my databases below LOLOLOLOL 🙂

---------------------------------------------------------

It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn

raadee SSC Eights! Points: 881 More actions · Answer 9

Abu Dina.

How about INIT or using diffrent filenames for each backup =)

sqlfriend SSC Guru Points: 52781 More actions · Answer 10

Grant Fritchey (2/12/2013)
An 8gb database? Unless you're running it on a VERY tiny little drive, there's no need to do a differential. Just do a FULL on a daily basis. In fact, I've run systems up to 200gb in size where we had enough maintenance time to do a FULL backup each night. It's only as you get past approximately that size that the time to backup and the size of backups make it worth breaking down into differentials, etc.

Thanks for the recommendation, the reason we use differerential backup is we have a standard here for each SQL server do full on sunday, daily differential, and 3 hours transaction log.

In order to comply with that standard, we do this way, and the database is increasing.

Probably I will make an exception for this server to do full each day.

Is there a comon practice like what size of database is recommended to do full each day other than full, differential,

Thanks

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 11

sqlfriends (2/12/2013)
Grant Fritchey (2/12/2013)
An 8gb database? Unless you're running it on a VERY tiny little drive, there's no need to do a differential. Just do a FULL on a daily basis. In fact, I've run systems up to 200gb in size where we had enough maintenance time to do a FULL backup each night. It's only as you get past approximately that size that the time to backup and the size of backups make it worth breaking down into differentials, etc.
Thanks for the recommendation, the reason we use differerential backup is we have a standard here for each SQL server do full on sunday, daily differential, and 3 hours transaction log.
In order to comply with that standard, we do this way, and the database is increasing.
Probably I will make an exception for this server to do full each day.
Is there a comon practice like what size of database is recommended to do full each day other than full, differential,
Thanks

Not really. It's what works best in your environment.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning