April 29, 2010 at 3:17 am
top 2 waitstats on one of the server are BackupBuffer and BackupIO. The backup is being done at a network location using symantec backup utility. There are around 20 databases with transaction log backup scheduled to occur at 30 min interval. Does that mean that I need to add storage to the server or to the network location where the backup is being done.
Regards,
[font="Verdana"]Sqlfrenzy[/font]
April 29, 2010 at 4:34 am
Do you back up on a tape? Any trouble with that tape?
April 29, 2010 at 5:06 am
As I said..its a network storage.
Regards,
[font="Verdana"]Sqlfrenzy[/font]
April 29, 2010 at 6:38 am
No, waits are not because of inadequate storage. It's because either the network, or the storage system itself, is running slow and causing SQL Server to wait on it. Most of time, most people, try to perform backups to a local drive for the server and then copy it across the network to networked storage just because you don't want to subject the database backup routine to the vagaries of network traffic.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 29, 2010 at 8:10 am
Grant Fritchey (4/29/2010)
No, waits are not because of inadequate storage. It's because either the network, or the storage system itself, is running slow and causing SQL Server to wait on it. Most of time, most people, try to perform backups to a local drive for the server and then copy it across the network to networked storage just because you don't want to subject the database backup routine to the vagaries of network traffic.
I also had a similar view but I am also getting pagelatch waits in top 10 wait stats.
Regards,
[font="Verdana"]Sqlfrenzy[/font]
April 29, 2010 at 8:27 am
Sqlfrenzy (4/29/2010)
Grant Fritchey (4/29/2010)
No, waits are not because of inadequate storage. It's because either the network, or the storage system itself, is running slow and causing SQL Server to wait on it. Most of time, most people, try to perform backups to a local drive for the server and then copy it across the network to networked storage just because you don't want to subject the database backup routine to the vagaries of network traffic.I also had a similar view but I am also getting pagelatch waits in top 10 wait stats.
If you're getting pagelatch waits, I'd suggest reading this from the SQL CAT.
I still don't think that has anything at all with space allocation at the backup location.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 1, 2010 at 12:20 am
Grant Fritchey (4/29/2010)
Sqlfrenzy (4/29/2010)
Grant Fritchey (4/29/2010)
No, waits are not because of inadequate storage. It's because either the network, or the storage system itself, is running slow and causing SQL Server to wait on it. Most of time, most people, try to perform backups to a local drive for the server and then copy it across the network to networked storage just because you don't want to subject the database backup routine to the vagaries of network traffic.I also had a similar view but I am also getting pagelatch waits in top 10 wait stats.
If you're getting pagelatch waits, I'd suggest reading this from the SQL CAT.
I still don't think that has anything at all with space allocation at the backup location.
If its because of network..I think if i decrease the tlog backup time from 30min to 15min then it will be of smaller size and It may help..
Regards,
[font="Verdana"]Sqlfrenzy[/font]
May 3, 2010 at 3:07 am
Sqlfrenzy (4/29/2010)
top 2 waitstats on one of the server are BackupBuffer and BackupIO. The backup is being done at a network location using symantec backup utility. There are around 20 databases with transaction log backup scheduled to occur at 30 min interval. Does that mean that I need to add storage to the server or to the network location where the backup is being done.
I have had fun with NetBackup and Backup Exec in the past 🙁
You don't say whether you are seeing a problem in terms of backup throughput or not.
Have you tried performing a native backup direct to the same network location for comparison?
There's a process to go through to determine where the bottleneck is.
There's some excellent advice in the following White Paper, documenting how a 2TB database was backed up (very) remotely in 34 minutes:
http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/Technical%20Case%20Study-Backup%20VLDB%20Over%20Network_Final.docx (Word document - well worth the download).
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 3, 2010 at 3:46 am
Paul White NZ (5/3/2010)
Sqlfrenzy (4/29/2010)
top 2 waitstats on one of the server are BackupBuffer and BackupIO. The backup is being done at a network location using symantec backup utility. There are around 20 databases with transaction log backup scheduled to occur at 30 min interval. Does that mean that I need to add storage to the server or to the network location where the backup is being done.I have had fun with NetBackup and Backup Exec in the past 🙁
You don't say whether you are seeing a problem in terms of backup throughput or not.
Have you tried performing a native backup direct to the same network location for comparison?
There's a process to go through to determine where the bottleneck is.
There's some excellent advice in the following White Paper, documenting how a 2TB database was backed up (very) remotely in 34 minutes:
http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/Technical%20Case%20Study-Backup%20VLDB%20Over%20Network_Final.docx (Word document - well worth the download).
I haven't compared it with native backups and it's difficult because the problem is on production server.
will go through the doc...it is for 2008 and am on 2005.
thanks.
Regards,
[font="Verdana"]Sqlfrenzy[/font]
May 3, 2010 at 3:49 am
Sqlfrenzy (5/3/2010)
I haven't compared it with native backups and it's difficult because the problem is on production server.
Understood. Remember that COPY_ONLY backups are available if that helps.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 3, 2010 at 5:41 am
Sqlfrenzy (5/3/2010)
Paul White NZ (5/3/2010)
Sqlfrenzy (4/29/2010)
top 2 waitstats on one of the server are BackupBuffer and BackupIO. The backup is being done at a network location using symantec backup utility. There are around 20 databases with transaction log backup scheduled to occur at 30 min interval. Does that mean that I need to add storage to the server or to the network location where the backup is being done.I have had fun with NetBackup and Backup Exec in the past 🙁
You don't say whether you are seeing a problem in terms of backup throughput or not.
Have you tried performing a native backup direct to the same network location for comparison?
There's a process to go through to determine where the bottleneck is.
There's some excellent advice in the following White Paper, documenting how a 2TB database was backed up (very) remotely in 34 minutes:
http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/Technical%20Case%20Study-Backup%20VLDB%20Over%20Network_Final.docx (Word document - well worth the download).
I haven't compared it with native backups and it's difficult because the problem is on production server.
will go through the doc...it is for 2008 and am on 2005.
thanks.
Most of the syntax and concepts will be the same between 2008 & 2005. Compression, of course, being the exception.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply