November 10, 2022 at 3:34 pm
My SQL-Server 2017 standard is running on a Windows Server 2019 with 32 GB of memory. There are an few databases up to 15 GB. Performance so far was always good.
Backups using its own backup function until a few days ago took a few minutes. Since then, without any changes made, backups need hours to complete. A restart of the server did not help. Backups are withoud compression or encryption.
Apart from this, there are no performance issues on my server, server load is a few percent.
Does anybody have an idea, what the reason could be or what diagnostik steps could be taken?
November 10, 2022 at 5:35 pm
Is backup the only thing happening at that time? Is there index maintenance or CHECKDB going on in the same job (or worse, simultaneously)?
What do you mean by "own backup function"?
Where are backups? Local disk? SAN? Have you benchmarked the backup disks w/ something like Crystal DiskMark to see what raw performance is?
Has the location changed recently? If SAN, has it been changed recently?
What is occurring on the server/in SQL Server during backups? Is it a relatively quiet time?
Have you monitored to look for errors, blocking, etc.?
Have you run CHECKDB to see if there are issues w/ the databases? Anything in error logs?
If there is no traffic or other conflicts/errors detected, I might suspect disk errors, SAN issues, and/or slow network
You should probably use compression -- adds some CPU overhead & maybe time to generate backup, but should reduces disk I/O & time to save files.
November 10, 2022 at 5:35 pm
Did the transaction log suddenly grow very large ?
November 10, 2022 at 5:40 pm
Good question, homebrew01.
roland:
Recovery model? Full? Simple? Bulk-logged?
I assume we're talking about full backups. How often?
If full, how often are you doing log backups? Are they succeeding?
November 10, 2022 at 5:48 pm
Double check if the target backup location hasn't been switched to a (windows) compressed volume !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 10, 2022 at 7:19 pm
and unless all your tables are columnstore tables do the backups with native compression turned on - files will be significantly smaller leading to faster backups and less space required.
Restores also become faster.
November 10, 2022 at 7:58 pm
Thanks for your replys!
My backups run on a (virtualized (ESXi)) local disk; by "own backup funktion" I meant, that the backup function of SQL-server is used, no external backup program. I do full backups once a day and incremental backups every hour.
Meanwhile I found out, that backups of my "user-databases" run at "normal" speed until 80 oder 90%, then they stop and there is no more progress. I found this behaviour on different "user-databases", so that I think, a hardware problem (e.g. disk error) is not very probable. System-database backups run without problems (however they are much smaller).
System load during the backups ist minimal exept for Windows Defender Antimalware, which goes up to 15 or 20% of CPU-load. When I switch off the Windows Defender Antimalware real-time scanner, backups continue at normal speed. There are no malware warnings with the scanner on.
So anyhow there must be an "interference" between the SQL-server and Windows Defender real time scanner??? But what to do? If the reason were an update of Windows Defender more people than me should have the problem?
Any idea?
November 10, 2022 at 8:13 pm
Always exclude SQL Server data, backup, & repl folders from antivirus scans -- they are well known to be devastating to performance. Many people have experienced this -- it's just usually not Windows Defender on a SQL Server.
November 11, 2022 at 2:04 pm
I think we used to exclude from virus scans based on extension, not an entire folder because other files may be worth scanning if something sneaks in there. Exclude .MDF, .NDF , .LDF , .TRN , .BAK Any others ?
November 12, 2022 at 12:08 pm
I think we used to exclude from virus scans based on extension, not an entire folder because other files may be worth scanning if something sneaks in there. Exclude .MDF, .NDF , .LDF , .TRN , .BAK Any others ?
Change the folder permissions to exclude any other activity and exclude the folder from the scanner activity!
😎
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply