Backup Behavior
in [Server Configuration]
|
From: cbrichards via SQLMonster.com on 14 Jul 2010 16:42 I am limited in my testing environment, so I am trying to validate my findings via this post. I am trying to understand: (1) if the size of the log file affects backups? (2) if the free space in the log file affects backups? I am not sure how to test for #2 above, but in my tests regarding #1, if I run a backup with a 10GB log file, (with an 8GB data file), the backup took 2 minutes and 35 seconds. If I then shrunk the log file to 5 GB and ran the backup, the backup still took the same amount of time, and the size of the BAK file was nearly the same size. Can you explain how backups behave based upon the two questions above? -- Message posted via http://www.sqlmonster.com
From: Andrew J. Kelly on 17 Jul 2010 11:37 The size of the log file for the most part doesn't affect the backups anywhere near as much as the amount of actual data in the log file does. I can't comment on your tests since there are too many factors to consider without knowing anything about how you did the tests and what was in the log to begin with. -- Andrew J. Kelly SQL MVP Solid Quality Mentors "cbrichards via SQLMonster.com" <u3288(a)uwe> wrote in message news:ab00f474edc9c(a)uwe... > I am limited in my testing environment, so I am trying to validate my > findings via this post. I am trying to understand: > (1) if the size of the log file affects backups? > (2) if the free space in the log file affects backups? > > I am not sure how to test for #2 above, but in my tests regarding #1, if I > run a backup with a 10GB log file, (with an 8GB data file), the backup > took 2 > minutes and 35 seconds. If I then shrunk the log file to 5 GB and ran the > backup, the backup still took the same amount of time, and the size of the > BAK file was nearly the same size. > > Can you explain how backups behave based upon the two questions above? > > -- > Message posted via http://www.sqlmonster.com >
From: cbrichards via SQLMonster.com on 20 Jul 2010 13:54 So, if I understand what you are saying, lets say I have a 20 GB log file. If the log file has very little free space, (lets say it only has 1GB free, or 19GB of data in the log file) then, by what you are saying it would take longer to execute this backup (all other things being equal), than if the log had 19GB free space and 1 GB of data in the log file? Andrew J. Kelly wrote: >The size of the log file for the most part doesn't affect the backups >anywhere near as much as the amount of actual data in the log file does. -- Message posted via http://www.sqlmonster.com
From: Erland Sommarskog on 20 Jul 2010 17:39 cbrichards via SQLMonster.com (u3288(a)uwe) writes: > So, if I understand what you are saying, lets say I have a 20 GB log file. > > If the log file has very little free space, (lets say it only has 1GB > free, or 19GB of data in the log file) then, by what you are saying it > would take longer to execute this backup (all other things being equal), > than if the log had 19GB free space and 1 GB of data in the log file? Yes, the *log* backup would take longer time to execute. I would not expect the time for the the database backup (full or differential) to be affected by how much that is in the log file. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Andrew J. Kelly on 22 Jul 2010 07:35 Basically yes but as always it depends on what is in the log file etc. But the more active VLF's there are the more potential work the backup must do when it looks thru the log to see what it needs to backup and of course what it backs up. -- Andrew J. Kelly SQL MVP Solid Quality Mentors "cbrichards via SQLMonster.com" <u3288(a)uwe> wrote in message news:ab4aecbb099bb(a)uwe... > So, if I understand what you are saying, lets say I have a 20 GB log file. > > If the log file has very little free space, (lets say it only has 1GB > free, > or 19GB of data in the log file) then, by what you are saying it would > take > longer to execute this backup (all other things being equal), than if the > log > had 19GB free space and 1 GB of data in the log file? > > Andrew J. Kelly wrote: >>The size of the log file for the most part doesn't affect the backups >>anywhere near as much as the amount of actual data in the log file does. > > -- > Message posted via http://www.sqlmonster.com >
|
Pages: 1 Prev: Getting a LockMatchID error using sp_rename Next: Index Rebuild and Statistics |