February 27, 2003 at 5:54 am
Hi! I have a db which is daily backuped (full - backup size=15 gb.), this process normally takes 5 hours to complete, and the backup device reside in a network drive. Last sunday i made some index defragmentations which reduces de data size for about 1,2 gb, leaving the backup size in 13.8 gb.
Since that change the daily backup time increases in 80 minutes, instead of reduce because the lower new size...any logical explanation???
TIA!
February 27, 2003 at 7:47 am
Any problem with your network?
February 27, 2003 at 8:14 am
No, i was think the same, but the network is Ok...
February 27, 2003 at 9:04 am
I'd double check the fragmentation of the db. More fragmentation will make the backup take longer as it follows the chains. Might also check that the disk itself is defragmented, backup will be slower if the free space is not contiguous.
Andy
March 4, 2003 at 10:40 am
Andy, do you know any tool to check/repair the fragmentation of a full db?
March 4, 2003 at 1:34 pm
For OS level fragmentation you can run the defrag that comes with Windows, that way your free space stays pretty much on one contiguous chunk. For the db, all you have is DBCC. You can use the ShowContig command to see the db fragmentation. If fragmented, then you rebuild the indexes to fix it.
Andy
March 5, 2003 at 9:03 am
I've already run defragmentation processes over certainly tables, as you can see in the original topic, obtaining good results. After this is when i start to note this slower backup. I agree with the idea about the OS level fragmentation, but i don´t want to run that kind of processes over a production box. Tell me if i'm wrong with this: if i drop the db and then restore it from a backup located in other server of the network, the fragmentation at OS level dissapears?
March 6, 2003 at 7:13 am
It should, assuming you have a chunk of contiguous space to restore into.
Andy
March 6, 2003 at 8:52 am
Thanks for your support!
March 10, 2003 at 9:49 am
It may be possible for you to reduce your backup time significantly from 5 hours by changing hardware configuration.
For example, we have a 10.5 gb backup running from a 3-year old 600MHz machine to another 600MHz machine. We added a second 100Mb network card to each machine and connected them directly with a crossover cable. Backup time across the 100Mb connection is ~45 minutes for the 10.5 Gig dump, which would translate to maybe 70 minutes for your 15 Gig dump. A gigabit ethernet connection should be even faster.
My experience is that if the backup has to travel through hubs, switches or routers it takes more time even if the network is not very busy, because each switch introduces some delay as the messages travel back and forth.
Of course, if your database (or network) is quite busy during the backup window, that can also have a significant effect on backup speed. SQL 6.5 was horrifically slow in that situation; SQL 7 and 2000 deal with it better.
The main reason I like to get the full backups over quickly is that while the full backup is going on, transaction log backups are stalled. If your database is not in use during the backup, then of course that doesn't matter.
March 10, 2003 at 11:08 am
Have you guys used sqllitespeed
quick small backups
Len.. I am still evaulating this products.
March 11, 2003 at 6:47 am
I can't change the net configuration, because this server is accesed by several machines. The main server use a gigabit card, and the destination server a 10/100 card, both connected through a switch. The db load during the backup window is pretty quiet, because it's made it during the night when only 5-10 users are working...
Maybe i'm gonna try your 'crossover method'.
lkalman: No, i can't use it, my management policy is 'no more money'!!!!!!
March 13, 2003 at 6:18 am
A trick we use to speed up backups over a LAN connection is to backup to multiple files. I have found for our network that eight files is the optimum for backing up a 35GB database, but you will have to play with it. Syntax is:
Backup database x to disk='\\server\share\backupname1.bak',
disk='\\server\share\backupname2.bak',
disk='\\server\share\backupname3.bak'
etc.
March 14, 2003 at 2:46 am
hi tombrattin,
if we backup to multiple files, what is the restore process?
praveen
Praveen Kumar Peddi
March 14, 2003 at 4:05 am
Same as normal restore except you list all files like so.
RESTORE DATABASE x FROM disk='\\server\share\backupname1.bak',
disk='\\server\share\backupname2.bak',
disk='\\server\share\backupname3.bak'
etc.
The reason it is faster is because each object gets it's own thread and connection to the other location. Average is 8. Concept works similar to the way download utilites like GoZilla and FlashGet do.
Edited by - antares686 on 03/14/2003 04:05:30 AM
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply