December 29, 2010 at 3:25 am
Hi,
Sub:Full and differential backup size same.
I am facing the following problem on production server,I am getting the full backup(600MB) and differential backup size(598MB).On off peak hours I have taken full backup and just after a min I have taken differential backup but the sizes of both backups are almost same.As per BOL diff backup will contain only the modified extents after the recent full backup.
Do I have to make any changes in sql server settings before taking backups.We have 3 different servers and I am facing this problem only on one of the 3 servers,other servers are giving differential backups in KBs (as expected) if I take it within a span of one minute but I am facing this problem only on server1. I am using sql server R2.
I have also scheduled transaction log backup every 1 hour and trans log backup size is in kbs(14kb);but the problem is with diff backup size.
Only maintenance plan is running for taking backups(full,diff,trans log)
No other job is running which might continuously update tables.
Please help me in solving this issue.
Thanks.
December 29, 2010 at 7:40 am
I'm not sure what the problem might be, but why are you taking a differential backup at one minute intervals, but only backing up the log once an hour. It seems like you kind of have those things reversed.
"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
December 29, 2010 at 10:01 am
Is it possible your full backups are using the 'WITH COPY_ONLY' clause? This will prevent the differential bitmap from being updated & your differential backups will have all changes since the last 'conventional' backup...
December 29, 2010 at 10:19 pm
Thanks Jason Gibson for your quick reply.
Just to make sure that full backup is not using WITH COPY_ONLY option I have taken a full backup without selecting WITH_COPY_ONLY option and to test just after a minute I have taken a differential backup and I am getting the sizes of both (full and diff ) backup almost same.
I am using the same approach as described above on other servers and it is working fine but only on one server is giving that problem.
Do I have to make any changes in database settings, server settings (SQL Server) ....?
Thanks.
December 29, 2010 at 10:45 pm
just for testing can you restore the database from the file which you thinking is of full backup and then differential in some test environment.
I am assuming there is some problem the way the database is getting backedup.
----------
Ashish
December 30, 2010 at 3:38 am
you can run the following against the database you are having trouble with to determine when the differential BASE backup was taken:
select name, physical_name, differential_base_lsn, differential_base_time from sys.database_files
the differential_base_time will indicate the time the base backup was taken - while this won't solve the issue it will point to when it was taken to help determine if the full backup you are taking immediately before the diff is in fact the real base. You can also run the following against a full backup file - compare the FirstLSN column value with the differential_base_lsn value returned above to determine if the base backup file you think is the base really is! Again, this won't solve the issue but may prove useful with troubleshooting...
restore headeronly from disk = N'filepath and filename of your base backup file'
Nothing else springs to mind at the moment
Jason
December 30, 2010 at 6:15 am
Hi,
Crazy4sql thanks for your reply.
As you have suggested I have restored the full backup and differential backup as well and it got restored successfully.
But still my problem is unresolved why diff backup is of same size as that of full backup.
Please help me in solving this problem.
Thanks.
December 30, 2010 at 7:44 am
thats really interesting to investigate.
Can you find out the top 5 largest table in your database and how frequently they are getting updated?
Also,
If you take full backup now and log backup after 15 min then after few minute take diff backup, is it still of same size as of your old full backup.
----------
Ashish
January 3, 2011 at 2:58 am
Hi,
Following is the output of Restore header from full backup.
BackupType BackupSize FirstLSN LastLSN
1 3935463424 19000448530800001 20000021365600001
CheckpointLSN DatabaseBackupLSN
20000021356900224 20000017546900066
Following is the output of Restore header from differential backup.
BackupType BackupSize FirstLSN LastLSN
5 2227660800 19000448530800001 20000026349100001
CheckpointLSN DatabaseBackupLSN DifferentialBaseLSN
20000026339700443 20000021356900224 20000021356900224
The above mentioned backup files were generated within a span of 1 min.ie I had taken a full backup and after 1 min differential backup is taken but size of diff backup is so large on off peak hour.
In the above outputs FirstLSN value of full backup and differential backup are both equal , might this be the reason of getting full and differential backup sizes almost same?
Please help me out in resolving this issue.
Thanks.
January 5, 2011 at 6:32 am
can you try to reproduce it ? Size of differential can be only if there is some heavy DML operation within that 1 min interval.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 6, 2011 at 2:42 pm
You are not by any chance running the reindex maintenance plan between your full backup and differential backup?
January 6, 2011 at 9:49 pm
Nils Gustav Stråbø (1/6/2011)
You are not by any chance running the reindex maintenance plan between your full backup and differential backup?
maintenance plan just within 1 min interval ..strange for me.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 6, 2011 at 10:54 pm
Based on the LSN info you posted, I would exptect to see the FirstLSN for the differencial backup to be 20000026339700443 rather than 19000448530800001.
What version of SQL are you running? Could you please run the following code after you run full and differencial backup and post the results? (replace <your data file name> with appropriate name)
select distinct top 10 bs.type,bs.backup_Start_Date,bs.backup_finish_date,bs.first_lsn,bs.last_lsn,bs.checkpoint_lsn,bs.database_backup_lsn
from msdb..backupset bs
inner join msdb..backupfile bf on bs.backup_set_id = bf.backup_set_id
where
type <> 'l' and
logical_name like '<you data file name>%'
--and backup_start_date between '<full backup start time - 1minute>' and '<differencial backup end time>'
order by backup_Start_Date desc
You may add time filter to retrive your previous backup info if you do not want try to run another full/differencial backup
January 7, 2011 at 12:00 am
Bhuvnesh (1/6/2011)
Nils Gustav Stråbø (1/6/2011)
You are not by any chance running the reindex maintenance plan between your full backup and differential backup?maintenance plan just within 1 min interval ..strange for me.
I was just asking in order to be able to eliminate that option. Something strange is happening with the full and/or differential backup, so I was just asking to rule it out.
January 7, 2011 at 4:10 am
The DifferentialBaseLSN should equal the FirstLSN of the base full backup. The FirstLSN of your differential backup is quite a bit older then the DifferentialBaseLSN and I wonder if you have a log record that has been active for a while? The differential backup will include all extents that have been changed with LSNs greater than or equal to the DifferentialBaseLSN but as the FirstLSN is the first record in the backup set and this is quite a bit older than your DifferentialBaseLSN, I wonder if all changes since the FirstLSN are being included.
Run the script below against your SQL instance and see if it indicates if anything is affecting the log reuse of the database in question. Please post the results.
select log_reuse_wait, log_reuse_wait_desc from sys.databases
If something is holding this log record active, it may be the reason your differential backup begins with this log record...
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply