<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 Administration
»
Backup taking MUCH longer on prod HELP:)
41 posts, Page 1 of 5
1
2
3
4
5
»
»»
Backup taking MUCH longer on prod HELP:)
Rate Topic
Display Mode
Topic Options
Author
Message
micber
micber
Posted Thursday, August 23, 2012 8:59 AM
SSC Rookie
Group: General Forum Members
Last Login: Wednesday, February 13, 2013 7:48 AM
Points: 28,
Visits: 193
I have two environments:
DEV:
VM
4 virtual cores
16 GB
Mydatabase 137242.19 MB
Attached SAN storage
SQL 2008 R2 standard
PROD:
Physical
32 Core 2 Processor
64 GB RAM
Mydatabase 134892.19 MB
Attached SAN storage (LUN not shared with any other server)
SQL 2008 R2 standard
I monitor prod with Idera Diagnostic Manager and have stats during the backup time.
CPU/IO/Memory paging etc.. all low.
backup script
BACKUP DATABASE [MyDatabase] TO DISK = N'F:\TestBackup\MyDatabase_SQL_Compress.bak'
WITH COPY_ONLY, NOFORMAT, NOINIT, NAME = N'MyDatabase-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO
Backup on VM takes 17 min
backup on Prod takes 88 min
I have no idea why. thoughts?
Michael B
Data Architect
MCT, MCITP(SQL DBA 2005/2008),MCTS, MCP,MCDBA
Post #1349180
Steve Jones - SSC Editor
Steve Jones - SSC Editor
Posted Thursday, August 23, 2012 9:58 AM
SSC-Dedicated
Group: Administrators
Last Login: 2 days ago @ 1:47 PM
Points: 31,406,
Visits: 13,722
Load on prod? I/O path to the backup drive? Compression requires CPU. Is the prod server busier?
Follow me on Twitter:
@way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
Post #1349230
micber
micber
Posted Thursday, August 23, 2012 10:32 AM
SSC Rookie
Group: General Forum Members
Last Login: Wednesday, February 13, 2013 7:48 AM
Points: 28,
Visits: 193
I was the only process running at that time. and CPU was < 5 % utilized
Michael B
Data Architect
MCT, MCITP(SQL DBA 2005/2008),MCTS, MCP,MCDBA
Post #1349250
sandeep rawat
sandeep rawat
Posted Thursday, August 23, 2012 10:44 AM
Say Hey Kid
Group: General Forum Members
Last Login: Monday, May 13, 2013 4:06 AM
Points: 681,
Visits: 298
check For IO of disk.
Post #1349253
micber
micber
Posted Thursday, August 23, 2012 10:51 AM
SSC Rookie
Group: General Forum Members
Last Login: Wednesday, February 13, 2013 7:48 AM
Points: 28,
Visits: 193
max during that run:
for 10 min Physical IO was 440 page writes per second.. otherwise is was 40 max over the other the 77 min
average disk ms/write was 120 for 5 min of the 88 min. average was 60
Michael B
Data Architect
MCT, MCITP(SQL DBA 2005/2008),MCTS, MCP,MCDBA
Post #1349257
micber
micber
Posted Thursday, August 23, 2012 11:36 AM
SSC Rookie
Group: General Forum Members
Last Login: Wednesday, February 13, 2013 7:48 AM
Points: 28,
Visits: 193
anyone? This is baffling to me!
Michael B
Data Architect
MCT, MCITP(SQL DBA 2005/2008),MCTS, MCP,MCDBA
Post #1349271
Robert Davis
Robert Davis
Posted Thursday, August 23, 2012 12:24 PM
SSCommitted
Group: General Forum Members
Last Login: Today @ 8:09 AM
Points: 1,558,
Visits: 1,396
Is the SAN shared with other servers? I've experienced issues like this running backups to a shared SAN because everyone else was backing up their databases at the same time and flooding the SAN throughput. I moved the backup time from midnight to 4 AM and the problem disappeared.
Is there anything other than backups using the F drive? Databases files? Replication snapshot folder or distribution database files? Backup of other databases running simultaneously? Log backups?
My blog: SQL Soldier
Twitter:
@SQLSoldier
Microsoft Certified Master: SQL Server 2008
Sr. Product Consultant and Chief SQL Server Evangelist @ Idera
My book: Pro SQL Server 2008 Mirroring
Post #1349291
Edward Dortland
Edward Dortland
Posted Thursday, August 23, 2012 12:38 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, March 28, 2013 7:33 AM
Points: 151,
Visits: 145
sorry, misread your post. forget about what I just wrote.
Instead:
can you look at the waitstats during your backup and post the results.
you can find a good waitstat query here:
http://www.sqlskills.com/BLOGS/PAUL/post/Wait-statistics-or-please-tell-me-where-it-hurts.aspx
Edward
Post #1349301
micber
micber
Posted Thursday, August 23, 2012 12:42 PM
SSC Rookie
Group: General Forum Members
Last Login: Wednesday, February 13, 2013 7:48 AM
Points: 28,
Visits: 193
Edward Dortland (8/23/2012)
what does this say:
SELECT db.name,
er.percent_complete,
er.total_elapsed_time/60000 AS ElapsedMinutes,
er.estimated_completion_time/60000 AS remaining_minutes
FROM
sys.sysdatabases db
INNER JOIN sys.dm_exec_requests er
ON db.DBID=er.database_id
AND er.command LIKE '%BACKUP%'
and does the output change over time?
Edward
I get nothing.. since it is not running at this time of day.
The output doesnt really change as far as the size of the backups.
Michael B
Data Architect
MCT, MCITP(SQL DBA 2005/2008),MCTS, MCP,MCDBA
Post #1349302
micber
micber
Posted Thursday, August 23, 2012 12:45 PM
SSC Rookie
Group: General Forum Members
Last Login: Wednesday, February 13, 2013 7:48 AM
Points: 28,
Visits: 193
Robert Davis (8/23/2012)
Is the SAN shared with other servers? I've experienced issues like this running backups to a shared SAN because everyone else was backing up their databases at the same time and flooding the SAN throughput. I moved the backup time from midnight to 4 AM and the problem disappeared.
Is there anything other than backups using the F drive? Databases files? Replication snapshot folder or distribution database files? Backup of other databases running simultaneously? Log backups?
I have been told that this LUN is dedicated to this server. I am sure other servers share the whole SAN though. Most of my backups occur on a different LUN or completely diff SAN I believe. I will confirm this..
nothing else is on this volumues/drive except backups. no replication.
I watched it last night to ensure nothing else was running on that server.
Michael B
Data Architect
MCT, MCITP(SQL DBA 2005/2008),MCTS, MCP,MCDBA
Post #1349304
« Prev Topic
|
Next Topic »
41 posts, Page 1 of 5
1
2
3
4
5
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
