March 30, 2017 at 3:12 am
Morning All,
I had to cancel a full backup that was underway as it was started in error.
As a result the differential that is scheduled to run is now the same size as the full backup normally is? Does something happen during a full backup that is not undone on cancelling that effects the differential in this way?
Cheers
Alex
March 30, 2017 at 3:26 am
The differential will just include any changes since the last successful full backup so as the full backup was cancelled you should expect the diff backup to be larger than usual.
What is the size of the diff backup and what is the 'usual' size?
Has a large update/insert/delete taken place on the database since the last full backup?
Thanks
March 30, 2017 at 3:34 am
Do you have index maintenance scheduled, and if so has it run since the last successful full backup? The reason for the size of your differential is most likely that something large such as that has taken place since your last full, not something specifically related to the cancellation.
John
March 30, 2017 at 4:06 am
No, its very peculiar and I have seen this behaviour before at a previous place on 2008R2 but didn't pay it much mind as it was a small database (< 150GB) anway.
But in this case its 5TB.
To out line what happened:
A full run once a week and creates a 2TB compressed file
Diff runs at 4am every day... this mornings Diff was 30GB compressed.
A full backup got kicked off by accident and then cancelled.
Finally a diff is run again (an hour later) and it has already created a 2TB backup file and its 8% through the backup.
No maintenance or large changes have run between any of those backups.
Most confused.
March 30, 2017 at 4:14 am
When the backup has finished, run RESTORE HEADERONLY on the backup file. How many rows do you get, and what are the BackupType(s)?
John
March 30, 2017 at 5:25 am
Are you thinking there are multiple backups to the device? the backup is performed with init...
March 30, 2017 at 5:37 am
That was one of my thoughts, yes - I think we have the following possibilities:
(1) Backup somehow performed with NOINIT
(2) Backup is, for some reason, a full backup
(3) Something big happened between this morning's diff and the diff taken after the cancelled backup
(4) Cancelling the backup caused something mysterious to happen
Do you have somewhere to test restore your full (with no recovery) followed by the big diff? You could test option (4) by backing up a database on a test server, then doing a diff, then cancelling a full, then doing another diff.
John
March 30, 2017 at 5:56 am
I am part way through doing Option 4 on a different database on dev to see if it is repeatable behaviour.
A full backup on there is consistently 50GB.
I am going to take a full.
Delete a few rows so its only a small diff.
Take a diff for size
Start a full backup and cancel at about 75%
Take a diff and check the size.
March 31, 2017 at 1:40 pm
I can report back that the odd behaviour is recreatable.
Cancel a full backup and the next diff and subsequent diffs are in fact FULL backups in terms of size. Yet they cannot be restored as a full.
Start Full... Wait 15 minutes or so.. about 5%
CAancel it
Take a diff.... Check the size 2TB
Now take a full and let it finish
Size is (barring bytes) identical.
Source DB is in 2005 Compatibility mode.
April 1, 2017 at 9:10 am
And looking at the backup history in msdb also confirms that its a full backup that was created even though differentil was specified.
April 3, 2017 at 2:16 am
Any reason to be at SQL Server 2005 compatibility level? What version is the server? If you can recreate on a version that's still supported, it might be worth filing a bug report.
John
April 3, 2017 at 5:57 am
Absolutely no reason to be on this level. Oversight I guess from the previous DBA.
It's been fully tested on the correct Compatibility but the CAB wont authorise the change until migrated on to 2016. So rather than argue about it (battle picking) I am going to wait until fully migrated and then re-test.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply