question

cornpoppy avatar image
cornpoppy asked

SQL server becomes slower when i copy backup files to another server

Hi, When i copy database backup files(11 GB) from one server to another server at the same time SQL server run so slowly , another users can not work with SQL server,The problem did not exist before,What can i do ?Why this happened ?
sql-serverperformancedbanetworkdba-developer
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Since the problem did not exists before, are you aware if anything changed recently for the server?
4 Likes 4 ·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
If you copy files to or from the drives where SQL Server keeps its data files, I/O operations will be slower, because the file copy keeps the disk occupied. Paul Randal has written a script which captures I/O latency over a short period of time. It can be found here: http://www.sqlskills.com/blogs/paul/capturing-io-latencies-period-time/ If you run that script for five minutes (change the WAITFOR to be '00:05:00' instead of '01:00:00') during normal operation and then again for five minutes while the file copy is going on, you will probably see some changes in latency. You could also look in eg Activity Monitor to see wait times for Buffer IO. One way to get past this is to let SQL Server create its backup files on another drive than the drive where the data files sit on.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you,but backup files that are on another drive and specific hard disc in server.
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
When you say SQL runs slowly, what do you mean? What are the symptoms? Is the server itself slow (eg if you remote on and try to use Windows?) Or just SQL Server? Is this a physical or virtual server? Is the storage local or network? How long does it take to copy 11GB? What is the network like between these 2 servers?
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
Check network configuration on both server and client and the path - check for full duplex, and that the NICs aren't trying to negotiate speeds.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.