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 ?
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?
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.