2

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

  • So I guess your SQL Server 2008 R2 is patched to latest SP ? All the time you looked at SQL Server did you checked at network stack – Shanky Oct 6 '15 at 12:02
  • hey @shanky. We use Microsoft SQL Server 2008 R2 (SP2) (Build 7601: Service Pack 1) (Hypervisor). By network stack, you mean the network performance ( sorry my english is not the best one )?. If so, yes our network is fine, There's almost nothing using it now ( or when I made the backups to test ). the problem came from one day to another. Monday it was OK. Then Tuesday I notice the problem, with my job running for 10Hrs+. – Racer SQL Oct 6 '15 at 12:07
  • Edit: The network is OK because, even doing the backups ( slowly ) we can copy files through servers with no problem ). – Racer SQL Oct 6 '15 at 12:10
  • 2
    nul is not a local disk. nul is nothing. You are measuring the speed at which the media can be read, with no writes issued at all. – Remus Rusanu Oct 6 '15 at 12:26
  • 2
    How fast is a single thready copy of a 7GB (925353/(128*1024)) file over the network ? robocopy source \\dest /MT:1 Have you tried to split the backup to multiple files and manipulate backup buffers (henkvandervalk.com/…) – Spörri Oct 6 '15 at 13:03
1

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/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.