SQL Server 2008 R2 – Fast Local Backup, Very Slow Backup via Network

backupNetworksql-server-2008-r2

Since last week, i'm having this problem.

Backups are taking forever to be done if I make them via Network.

If I backup the database to a local disk ( or N'nul' ) it's very fast.

It's strange because even with my personal PC I can't make network backups ( there's nothing with the cluster, or configuration that we have in the production instances ).

In This connect item they say something about a SQL 2005 issue.

What can we track to find the issue? What perfmon counter can we use?

We have already restarded the servers. Restarted instances. restarted clusters. Disabled Anti-virus. Nothing helped us.

This is some logs:

Via NUL:

backup database My_DB to
disk =N'nul'
–Processed 542896 pages for database 'My_DB ', file
'My_DB ' on file 1.

–Processed 382456 pages for database 'My_DB ', file
'My_DB ' on file 1.

–Processed 1 pages for database 'My_DB ', file
'My_DB ' on file 1.

–BACKUP DATABASE successfully processed 925353 pages in 19.911 seconds (
363.081 MB/sec).

Via Network:

backup database My_DB to
disk ='\Server\D\BACKUPS_TEST\teste.My_DB .bkp'

–Processed 542896 pages for database 'My_DB ', file
'My_DB ' on file 1.

–Processed 382456 pages for database 'My_DB ', file
'My_DB ' on file 1.

–Processed 1 pages for database 'My_DB ', file
'My_DB ' on file 1.

–BACKUP DATABASE successfully processed 925353 pages in 129.223 seconds
(15.944 MB/sec).

I can copy files through servers with no problems. I really have no Idea what to do. I would be grateful to share more information.

EDIT1:


Something I notice is this query below ( in the destination server ):

WAITFOR (RECEIVE message_body FROM WMIEventProviderNotificationQueue),    TIMEOUT 5000

There are a lot of websites saying different things about it, so, I don't know for sure what it is.

I can see this query running via trace or with sp_whoisactive. It runs, then it restarts itself. Could this be the problem ??

Best Answer

You could attempt to backup up the database using compression instead. As long as you have the CPU avaliable, It will definately reduce the time (around 15%-20%) that it takes for your backups to complete. These backups also take less time to restore as well.

Good Luck,

Hope this helps.

Good resource: http://blogs.lessthandot.com/index.php/datamgmt/dbadmin/testing-backup-compression-in-sql-server-2008/