Ygthkb

Not sure how to set up the Laplacian/Poisson Equation

Is it Safe to Plug an Extension Cord Into a Power Strip?

How do I write a maintainable, fast, compile-time bit-mask in C++?

Can I legally make a website about boycotting a certain company?

SQL Server 2017 crashes when backing up because filepath is wrong

Is Screenshot Time-tracking Common?

Why write a book when there's a movie in my head?

Minimum energy path of a potential energy surface

What is formjacking?

Do the speed limit reductions due to pollution also apply to electric cars in France?

How can changes in personality/values of a person who turned into a vampire be explained?

Sets which are both Sum-free and Product-free.

What does it mean when an external ID field follows a DML Statement?

What does an unprocessed RAW file look like?

Identical projects by students at two different colleges: still plagiarism?

Can you wish for more wishes from an Efreeti bound to service via an Efreeti Bottle?

Can I do anything else with aspersions other than cast them?

Do these large-scale, human power-plant-tending robots from the Matrix movies have a name, in-universe or out?

Given the mapping a = 1, b = 2, ... z = 26, and an encoded message, count the number of ways it can be decoded

How can I portray body horror and still be sensitive to people with disabilities?

Variance of sine and cosine of a random variable

How many copper coins fit inside a cubic foot?

What is the name of this perspective and how is it constructed?

How can guns be countered by melee combat without raw-ability or exceptional explanations?

Slow SQL backup to local disk problem, with odd workaround

SQL Server backup fails on disk space errorFast local backup - Very slow backup via NetworkUnShrunk SQL Server database restore which is larger than available disk spaceSQL Server 2008R2 - BAK file backup performance issueSQL Server: backup error 112: not enough space on the disk - true or not?Large Database backup approx 2 TB and restore with limited disk spaceBackup suddenly is taking too long to completeSQL Server Editions: Backup restore of Enterprise Edition 2014 on Standard Edition 2017Required free Disk Space for Index rebuild offline OperationsLog shipping from SQL Server 2014 Enterprise db to SQL Server 2017 Standard with partitioning

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

sql-server backup sql-server-2017

share|improve this question

edited 13 mins ago

M Secher

asked yesterday

M Secher M Secher

62

New contributor

M Secher is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  yesterday
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Perhaps file system fragmentation. A 64K allocation unit size may help.
  
  – Dan Guzman
  yesterday
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Did you try trace flag 3042 (skips the large pre-allocation of the backup file)? mssqltips.com/sqlservertip/3626/…
  
  – Tibor Karaszi
  yesterday
  
  
  

  
  
  
  


• 
  
  
  

  
  3
  

  
  
  
  
  
  

  
  
  Some storage will become slower when it has very little free space.
  
  – Tony Hinkle
  yesterday
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @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
  20 hours ago
  
  
  

  
  
  
  

 | 
show 4 more comments

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

sql-server backup sql-server-2017

share|improve this question

edited 13 mins ago

M Secher

asked yesterday

M Secher M Secher

62

New contributor

M Secher is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  yesterday
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Perhaps file system fragmentation. A 64K allocation unit size may help.
  
  – Dan Guzman
  yesterday
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Did you try trace flag 3042 (skips the large pre-allocation of the backup file)? mssqltips.com/sqlservertip/3626/…
  
  – Tibor Karaszi
  yesterday
  
  
  

  
  
  
  


• 
  
  
  

  
  3
  

  
  
  
  
  
  

  
  
  Some storage will become slower when it has very little free space.
  
  – Tony Hinkle
  yesterday
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @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
  20 hours ago
  
  
  

  
  
  
  

 | 
show 4 more comments

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

sql-server backup sql-server-2017

share|improve this question

edited 13 mins ago

M Secher

asked yesterday

M Secher M Secher

62

New contributor

M Secher is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

edited 13 mins ago

M Secher

asked yesterday

M Secher M Secher

62

New contributor

M Secher is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

edited 13 mins ago

M Secher

asked yesterday

M Secher M Secher

62

New contributor

M Secher is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked yesterday

M Secher M Secher

62

New contributor

M Secher is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked yesterday

M Secher M Secher

62