B&R reads entire disk during incremental backup

[perjonsson1960]

Folks,

We have a backup job that backs up most of our SQL servers in VMware, via virtual proxies using Virtual appliance transport mode [hotadd], and sometimes the job reads an entire disk. For example, one of the servers has a disk which is 3.1 TB in size, and B&R reads 3.1 TB, thus:

2020-06-17 03:05:40 :: Hard disk 2 (3,1 TB) 3,1 TB read at 269 MB/s [CBT]

That took 3h22m according to the job log. And when I look at the properties of the backup under "Backups -> Disk", it says that the data size is 488 GB, and the backup size is 510 GB for all disks in that server. Another example with another server during the same backup session:

2020-06-17 02:39:47 :: Hard disk 3 (1,5 TB) 1,5 TB read at 184 MB/s [CBT]
Data size 558 GB and backup size 311 GB for all disks in that server.
That took 2h19m.

Because of this, the backup window becomes unnecessarily large... Any pointers in the right direction will be greatly appreciated!

We are running B&R v10.0.0.4461 P2

Regards,
Per Jonsson
Sweden

[foggy]

Hi Per, this most likely comes down to CBT which seems to return the entire disk as the changes since the previous job run. Could be due to CBT reset or another CBT-related issue - I'd contact technical support for the investigation. Thanks!

[Gostev]

In general, databases applications like SQL Server will normally rewrite the entire disk content every day, sometimes even a few times, with transaction logs. So this is totally expected for very busy databases... transaction logs and actual database updates means virtually all parts of disks will receive writes.

What about Hard Disk 1 statistics? If changed data there is much less than the disk size, as I'd expect for system disks, then this would be an indication that VMware CBT is functioning normally for the given VM, and that the SQL Server is indeed re-writing the entire data disk each day.

[perjonsson1960]

With Hard disk 1 usually very little data is read, which then indicates that CBT is functioning for the VM. Actually, the previous night the "entire disk" scenario did NOT happen with the two servers that I mentioned in my example, but it happened with another server. So I guess, as you say, that this is probably normal behaviour. Thanks!

PJ

[Gostev]

Sounds good. Based on the periodic nature of the event, I would suspect there is some sort of scheduled periodic SQL Server maintenance job.

[mkaec]

In general, databases applications like SQL Server will normally rewrite the entire disk content every day...

I disagree with the notion that it is normal for an SQL Server to completely rewrite its disk every day. I'm sure that happens with some systems. It's also quite common for a database to acquire large amounts of historical data that never changes.