Post reply

Estimate transaction log backup size

  • August 4, 2011 at 11:11 am

    #255305

    Hi,

    I started a new job and I notice that all databases here only execute a full backup every night. Some of the databases have lot of activity, so, I need to implement a transaction log backup ASAP!

    The problem: the servers don't have too much available space and my manager ask me to estimate the size of the backup of transaction log and differential to see if the actual disks will fit or if we have to buy new ones.

    Is there any way to predict that?

    Thanks!

  • August 4, 2011 at 11:19 am

    #1363839

    Try this, see if it helps:

    CREATE TABLE #T (

    DB sysname,

    LogSize FLOAT,

    SpaceUsed FLOAT,

    Stat BIT);

    INSERT INTO #T (DB, LogSize, SpaceUsed, Stat)

    EXEC ('DBCC SQLPERF (LOGSPACE)');

    SELECT * FROM #T;

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • August 4, 2011 at 11:25 am

    #1363844

    Thank you, I already got this information, but how accurate is this? There are logfile with 900 MB, so the size of the backup will be 900 MB?

    Thanks

  • August 4, 2011 at 11:29 am

    #1363846

    It's not that precise. Try a few in a dev environment, you'll get a feel for it.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • August 4, 2011 at 11:31 am

    #1363847

    Thanks! I'll do it.

  • August 4, 2011 at 11:32 am

    #1363848

    Try one and see. Shouldn't be far off if not 100% accurate.

    Edit : That's what you get for not refreshing b4 replying :hehe:

  • August 4, 2011 at 12:44 pm

    #1363898

    rafa.aborges (8/4/2011)

    Thank you, I already got this information, but how accurate is this? There are logfile with 900 MB, so the size of the backup will be 900 MB?

    Thanks

    It's the used space, not the log size that you're interested in.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 4, 2011 at 1:03 pm

    #1363913

    Got an interesting information from a friend of mine.

    He said that in 16 hours, it generated 29 GB of transaction log backup file and on the DBCC SQLPERF (LOGSPACE) shows the Log Size of 3,3 GB and 134 MB of Used Log.

    Unfortunately, the info grom LOGSPACE is not precise.

    Is there any other way, other than test on a dev environment?

    Thanks!

  • August 4, 2011 at 1:22 pm

    #1363927

    rafa.aborges (8/4/2011)

    Got an interesting information from a friend of mine.

    He said that in 16 hours, it generated 29 GB of transaction log backup file and on the DBCC SQLPERF (LOGSPACE) shows the Log Size of 3,3 GB and 134 MB of Used Log.

    Unfortunately, the info grom LOGSPACE is not precise.

    At what point did he check LogSpace? To get something close to accurate he'd have had to run that right before each log backup (not after) and summed the used space values. Just running it once at the end of the 16 hours is only going to say how much log is used at that point, it'll be the log generated since the last log backup.

    Log backups mark log space as reusable, hence the used space drops after each log backup.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 5, 2011 at 12:09 pm

    #1364570

    Thanks for all the replies.

    I'm about to implement the transaction log backup. Do you agree with this strategy?

    Full backup at midnight (12:00 am)

    Transaction log every 15 minutes (appending the backup log or creating a new one after each backup?)

    Differential backup at 6am, 12am, 18am.

    Then, copy everything to tape and start overwriting all the backups.

    Is this OK?

    Thanks

  • August 5, 2011 at 12:16 pm

    #1364575

    I personally preffer to create a new log file everytime.

    3 diffs can be ok but it's actually a big overkill.

    Over here we have "little" write operations to the db. So I have the luxury of keeping an extra full backup a couple months back and every log backups in between. That gives me 24/7/365 point in time restore capability (I started this 1 month ago, but assuming the transaction volume stays consistant I'll be able to go back 1 year with this before having to do any compression).

    You also forgot the 2 most important steps. Checkdb() + test your restore. A backup is useless if you can't restore it.

  • August 8, 2011 at 2:05 pm

    #1365503

    Great, I'll do it.

    Another doubt, should I delete the transaction logs backups from the previous day or keep it?

  • August 8, 2011 at 2:25 pm

    #1365522

    That depends entirely on your backup strategy and how much back data you need to keep to be in order with your sla.

    You might want to hit google on this one and dig a little deeper on the subject.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply