1

I have some SQL Server 2017 (14.0.3037) servers, both Standard Edition and Developer Edition, running on Windows Server 2016 Standard in a VMware environment (v6.5). The disks are on fast SAN storage

Disk config:

C: OS, incl. swapfile
D: SQL program files, system db and SQL Backup
E: DB Data
F: DB Tlog
G: TempDB

The problem is that we see very slow SQL backups. On a 50 GB database that normally took 2 minutes to backup, now it takes 77 minutes.

When there is enough free space on the disk for a compressed backup, and the backup can complete. But if there is too little free space the backup is very slow.

But I found a strange workaround... During the backup, if I find that it is running slow, (by measuring MB/sec on the data disk), if I then delete a big file, the backup speeds up immediately, the performance (MB/sec on the data disk) goes up, and the backup is fast.

So now I have expanded the disk so it has more free space, and the backup is fast.

I see this as a workaround, not a solution, so I would like to hear if anyone has seen this problem? If anyone has more info about why the SQL backup runs very slow if SQL Server thinks that there is not enough free space on the disk...

Is this a new SQL 2017 feature?

Edit: This is also happens on SQL 2016, and probably from 2008 and up...

Microsoft has released a info about how much a compressed SQL Backup pre-allocate when backup starts, it is 1/3 of the reserved data in a database: https://support.microsoft.com/en-gb/help/2001026/inf-space-requirements-for-backup-devices-in-sql-server

  • Are you backing up to disk E? If so, have you got any performance monitor / disk statistics for the period of the backup? – George.Palacios Feb 20 at 13:18
  • Perhaps file system fragmentation. A 64K allocation unit size may help. – Dan Guzman Feb 20 at 13:33
  • Did you try trace flag 3042 (skips the large pre-allocation of the backup file)? mssqltips.com/sqlservertip/3626/… – Tibor Karaszi Feb 20 at 13:34
  • 4
    Some storage will become slower when it has very little free space. – Tony Hinkle Feb 20 at 14:01
  • @George.Palacios I am normally backing up to D-drive, but this problem is on any drive i backup to, if there is not 'enough' free space, it do slow backup. – M Secher Feb 21 at 13:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.