SQL Server backup suddenly extremly slow

KKRohr 26 Reputation points
2022-11-10T10:43:41.68+00:00

Since some days, my MS SQL Server database backup runs extremely slow.

I am running an SQL Server 2019 instance on a virtual server with a SSD drive. The database size is about 18 GB and the backup normally runs in 15 minutes, directly to a network USB drive. Suddenly the backup takes up to 5 hours. The database contains 2 filestream tables, one table with 70000 documents. The filestream data is about 16 GB in size.

Now when I run the backup it sometimes starts fast and writes for example 10 GB in 5 minutes, but then gets very slow and can even get stuck.

What I tried so far:

  • restored the database to a development environment and tried a backup from there - same problem with slow performance
  • used a different backup location
  • checked SSD write / read speed of the server
  • restarted SQL service / instance
  • a sheduled task from SQLBackupAndFTP got stuck after 3 hours with 2 GB written, a manual task runs succesful but takes 1 hour to finish
  • backed up a different database on the same instance with 3 GB in 20 seconds

I assume, that there is a problem with the database itself or with some data inside.

What possibilities do I have to find out the reason for this behavior?

Thank you
Michael

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,080 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 103.5K Reputation points MVP
    2022-11-11T05:29:11.633+00:00

    There was a thread recently where the poster also had problems with slow backup in Filestream. In that case, the poster was able to get good speed by configuring the antivirus to not check the filestream area.

    2 people found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Seeya Xi-MSFT 16,451 Reputation points
    2022-11-11T06:53:48.347+00:00

    Hi @KKRohr ,

    Can you run sp_Whoisactive during the backup and see what is going on?
    About how to use sp_Whoisactive, please refer to this blog: Monitoring activities using sp_WhoIsActive in SQL Server

    Best regards,
    Seeya


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.