Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Backup database taking unrealistic time

Author  Topic 

sks198117
Starting Member

46 Posts

Posted - 2010-07-29 : 03:06:22
Hi,

I have my DB server with following configurations.

OS Name: Microsoftr Windows Serverr 2008 Enterprise
OS Version: 6.0.6002 Service Pack 2 Build 6002
OS Configuration: Member Server
OS Build Type: Multiprocessor Free
System Manufacturer: HP
System Model: ProLiant DL580 G5
System Type: x64-based PC
Processor(s): 4 Processor(s) Installed.
[01]: Intel64 Family 6 Model 29 Stepping 1 GenuineIntel ~2400 Mhz
[02]: Intel64 Family 6 Model 29 Stepping 1 GenuineIntel ~2400 Mhz
[03]: Intel64 Family 6 Model 29 Stepping 1 GenuineIntel ~2400 Mhz
[04]: Intel64 Family 6 Model 29 Stepping 1 GenuineIntel ~2400 Mhz
Total Physical Memory: 32,764 MB
Page File: Max Size: 68,661 MB

and SQL SERVER version is

Microsoft SQL Server 2008 (SP1) - 10.0.2714.0 (X64) May 14 2009 16:08:52 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)


I have a DB of size 225 GB when i started to backed the DB it took 10 hrs 7 mins to get complete.
Which is too much i guess in comparision to DB size.

Can anyone please guide me what could be possible reason and how i can improve performance?

Thanks

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2010-07-29 : 04:14:56
Need more information:
1. How are you doing the backup? E.g. Native BACKUP, 3rd party tool
2. Where are you writing the backup file(*.BAK) to? E.g. local disk, SAN disk, NAS, tape, VTL
3. When are you performing the backup? Any other activity at the same time
4. What is the point of contention? Have you monitored the usual suspects - CPU, memory, IO, network?
Go to Top of Page

sks198117
Starting Member

46 Posts

Posted - 2010-07-29 : 04:49:18
thanks YelloBug for reply
Here is the info
1. I am doing native backup using 'backup database' command
2. I am writing the backup file on local disk
3. There was no any other activities was performing when i started backup but earlier one SSIS package was running and memory utilization was around 31 GB out of 32 GB but when package executed the memory utilization doesn't goes down and it was still showing 31 GB and process which was taking high memory was msdsrv.exe.
4. CPU utilization was normal(10-20%) during this period.

Hope this will help you to figure out the reason

Let me know if u need more info

Thanks
Go to Top of Page

sks198117
Starting Member

46 Posts

Posted - 2010-07-29 : 04:52:59
Sorry about point 2. it's not Local disk I am writing the backup file in SAN disk
Go to Top of Page

sks198117
Starting Member

46 Posts

Posted - 2010-07-30 : 02:54:30
I have restored the backup file on same server in another DB and it took more than 10 hrs to get complete..
It seems some unrealistic.
Can anyone please help out ?

Thanks
Go to Top of Page

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-07-30 : 03:36:43
you need to benchmark some of your activities.

Can you take SQL server out of the picture and copy a file to your SAN disk and see if writing to that location is the bottleneck? i.e test if your environment is kosher before digging into SQL server itself.

use a smaller (but realistic) size file to get an elapsed time that you can scale up to mimic your live situation. you could also have some TCP/IP, network routing problems - can you measure your latency of messages passing around your internal network? I'm not expert in this arena, but I've seen similar questions posted hre by otehrs....just a matter of using the right search words. The top 5 members would have a lot of experience in this arena.
Go to Top of Page

sks198117
Starting Member

46 Posts

Posted - 2010-07-30 : 04:23:41
Thanks Adndrew,
I have tried to copy a file of size 970 MB to SAN disk from our local disk and its took around less than minute to copy it to SAN.
Is that ok? i am not very mmuch sure about these things but i really need some help on above issue.

Thanks
Go to Top of Page

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-07-30 : 07:13:08
Going by those numbers then the base line time to copy a 235GB file to SAN would be approx 240mins - 4 hours. You're now looking to explain the difference between this and the measured 10hours.

One approach to take is to investigate other Backup software backages (ie. maybe @ 235GB SQL is delivering as good as what it can) - www.red-gate.com have a package which seems to have a good reputation, incorporating seamless inegration with SQL Server and also compression abilities.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-07-30 : 08:20:44
... or using the Backup COMPRESSION feature in SQL 2008?
Go to Top of Page

Yeoh Ray Mond
Starting Member

49 Posts

Posted - 2010-07-31 : 20:15:49
Your backup throughput would be around 6 MB/sec, which is extremely poor. Try the suggestions here, which should be able to tell you if it's a problem with the reading phase or the writing phase that's the bottleneck during the backup process ([url]http://www.sqlbackuprestore.com/speedingupbackups.htm[/url]).

Ray Mond
SQLBackupRestore.com - your quick guide to SQL Server backup and recovery issues
SQL Image Viewer - retrieve, view, convert and export images and binary data from SQL Server, Oracle, DB2, PostgreSQL, SQLite, and Firebird databases.
SQL Data Sets - share and distribute SQL Server, Oracle and PostgreSQL data sets securely and easily
Go to Top of Page
   

- Advertisement -