Managing Log Files Sizes the Easy Way

  • roworthm

    Old Hand

    Points: 313

    Comments posted to this topic are about the item Managing Log Files Sizes the Easy Way

  • kevaburg

    SSCoach

    Points: 17910

    Good morning!

    This is certainly an interesting topic because in our firm we have two DWHs in place to facilitate a cut-over from one system to the second. The new machine is a SQL Server 2014 machine. The storage for this DWH is on a NetApp storage appliance and for those out there that have one, you know how convincing admins to give up space on such an expensive piece of hardware is.

    Because of this reason I had to come up with a plan to simplify transaction log management and reduce the total amount of space required by te database.

    My solution was to run the DWH in SIMPLE Recovery Mode and increase the CHECKPOINTs over a given period.

    Why?

    Firstly it is a Data Warehouse. The information fed into it comes from systems running constantly in production. The DWH in it's own right is not responsible for data Manipulation but rather OLAP processes so that Management can make its decisions. On an hourly Basis this Information is compared with its productive source and the Delta imported to Keep the Information as up-to-date as it can be.

    What if we Need to restore the whole machine?

    Naturally, at first we will have to restore the DWH from the previous full backup. Because we are running in SIMPLE Recovery Mode we don't have the capability to restore to a Point-in-time. This though isnt a Problem because during the first Delta run, the Information that Needs to be imported is extracted and inserted into the relevant DWH tables. Within 24 hours it is back up and running.

    Ensuring that the DWH developers understand the Need to regularly commit changed data and increasing the amount of checkpoints throughout the day has kept our Transaction Log size dowon to a Minimum.

    Just my take on the subject......

  • Cookie SQL

    Right there with Babe

    Points: 715

    I would look at the active portions of the log 'DBCC LogInfo' , check that its viable to shrink or it will just fail.

    The other thing not mentioned or in the script is VLF's, I have a similar script but check that its shrunk as expected and the VLF count at the end.

    My other concern would be drive fragmentation for spinning disks.

    Remember growing a log file is a blocking operation so be careful how much you shrink because your slowing the other processes down. Maybe you should manage the growth before your import and directly shrink it back after with some checks in place.

  • roworthm

    Old Hand

    Points: 313

    kevaburg (3/9/2015)


    Good morning!

    This is certainly an interesting topic because in our firm we have two DWHs in place to facilitate a cut-over from one system to the second. The new machine is a SQL Server 2014 machine. The storage for this DWH is on a NetApp storage appliance and for those out there that have one, you know how convincing admins to give up space on such an expensive piece of hardware is.

    Because of this reason I had to come up with a plan to simplify transaction log management and reduce the total amount of space required by te database.

    My solution was to run the DWH in SIMPLE Recovery Mode and increase the CHECKPOINTs over a given period.

    Why?

    Firstly it is a Data Warehouse. The information fed into it comes from systems running constantly in production. The DWH in it's own right is not responsible for data Manipulation but rather OLAP processes so that Management can make its decisions. On an hourly Basis this Information is compared with its productive source and the Delta imported to Keep the Information as up-to-date as it can be.

    What if we Need to restore the whole machine?

    Naturally, at first we will have to restore the DWH from the previous full backup. Because we are running in SIMPLE Recovery Mode we don't have the capability to restore to a Point-in-time. This though isnt a Problem because during the first Delta run, the Information that Needs to be imported is extracted and inserted into the relevant DWH tables. Within 24 hours it is back up and running.

    Ensuring that the DWH developers understand the Need to regularly commit changed data and increasing the amount of checkpoints throughout the day has kept our Transaction Log size dowon to a Minimum.

    Just my take on the subject......

    Agreed. All our source system databases, staged and clean are run in simple mode, as is our DW main database. Our whole overnight load process could be considered to be like a single transaction, we either want it to work, or to continue with the previous day's data. The only non system database that is in Full mode is the one that contains the reference data for loading and logging server operations. The approach I've used specifically wouldn't work for OLTP, only OLAP, as it only compacts log files for DBs where there are no current connections.

    How I would love to have a second DW, so that I could just switch reports from one to the other once the load is successfully completed.

  • roworthm

    Old Hand

    Points: 313

    cookie SQL (3/9/2015)


    I would look at the active portions of the log 'DBCC LogInfo' , check that its viable to shrink or it will just fail.

    The other thing not mentioned or in the script is VLF's, I have a similar script but check that its shrunk as expected and the VLF count at the end.

    My other concern would be drive fragmentation for spinning disks.

    Remember growing a log file is a blocking operation so be careful how much you shrink because your slowing the other processes down. Maybe you should manage the growth before your import and directly shrink it back after with some checks in place.

    Our server is in a SAN so file fragmentation is an issue I have no visibililty of (and becomes a beast of a different nature, I think). Being a developer foremost and less of a DBA, I've not paid a vast amount of attention to VLFs (other than making sure we don't have hundreds). The max number we have across the staging, clean and DW is 5, so personally not too worried. Strangely enough there is always at least one VLF of status=2, but presumably you would always have at least VLF in a log file.

  • Cookie SQL

    Right there with Babe

    Points: 715

    roworthm (3/9/2015)


    cookie SQL (3/9/2015)


    I would look at the active portions of the log 'DBCC LogInfo' , check that its viable to shrink or it will just fail.

    The other thing not mentioned or in the script is VLF's, I have a similar script but check that its shrunk as expected and the VLF count at the end.

    My other concern would be drive fragmentation for spinning disks.

    Remember growing a log file is a blocking operation so be careful how much you shrink because your slowing the other processes down. Maybe you should manage the growth before your import and directly shrink it back after with some checks in place.

    Our server is in a SAN so file fragmentation is an issue I have no visibililty of (and becomes a beast of a different nature, I think). Being a developer foremost and less of a DBA, I've not paid a vast amount of attention to VLFs (other than making sure we don't have hundreds). The max number we have across the staging, clean and DW is 5, so personally not too worried. Strangely enough there is always at least one VLF of status=2, but presumably you would always have at least VLF in a log file.

    Depending on where in the file the active log is will affect your shrink, to be honest with simple recovery its often at the beginning.

    I would just check how you are growing the log, is it autogrowth % or Size ? is it small/large chunks ?

    Insert, Update and Delete can all be affected by large numbers of VLF's and will definitely be affected by growing the log as its a blocking operation. If you know how large the log needs to get, grow it back before your import operation (I grow in 8GB chunks) so it doesn't have to wait for growth during your import operation.

    How many VLF's did you have before the shrink ?

  • ken.trock

    SSCertifiable

    Points: 5147

    We also run a DWH with almost all the DBs in simple recovery mode. I run a script like this every night though I don't take the extra check of making sure no connections are in there presently. Due to active transactions, even in simple recovery DBCC SHRINKFILE doesn't always reclaim the space.

    Ken

  • kevaburg

    SSCoach

    Points: 17910

    ken.trock (3/9/2015)


    We also run a DWH with almost all the DBs in simple recovery mode. I run a script like this every night though I don't take the extra check of making sure no connections are in there presently. Due to active transactions, even in simple recovery DBCC SHRINKFILE doesn't always reclaim the space.

    Ken

    That is the reason I increase the amount of CHECKPOINTs.

  • PHYData DBA

    SSCertifiable

    Points: 7541

    cookie SQL (3/9/2015)


    I would look at the active portions of the log 'DBCC LogInfo' , check that its viable to shrink or it will just fail.

    The other thing not mentioned or in the script is VLF's, I have a similar script but check that its shrunk as expected and the VLF count at the end.

    My other concern would be drive fragmentation for spinning disks.

    Remember growing a log file is a blocking operation so be careful how much you shrink because your slowing the other processes down. Maybe you should manage the growth before your import and directly shrink it back after with some checks in place.

    +1 to everything said above.

    I understand the need to shrink any SQL Transaction Log file that has experienced ONE Temporary Over Growth.

    I see the need to do something automated when disk space for the fast and near storage that log files should be deployed on is a premium, but only when multiple databases have to use this same storage area.

    And only if there was something that would grow each log file back to where it was before these data imports start.

    Your solution that runs every 15 minutes outside of the SSIS packages were this task should be seem illogical and dangerous.

    At best I would imagine it would shrink a log file right before that log file would auto grow and slow down anything using that log file.

    A log file that becomes a certain size every night does so because it needs to be that size to do the task at hand.

    You are ALWAYS going to need that much disc space available and a Transaction Log of that size .

    Creating something that runs every 15 minutes to Shrink any Tran Log because there are no active connections to the database is at best performance robbing not enhancing.

    If you have a SSIS package that has a need for a large Transaction log when it runs then add tasks to that package or the SQL Agent Job that executes it to Grow and then Shrink the Tran log files that are affected.

    I do admit this is an interesting temporary solution for your specific problem.

    Just would feel better if the permanent problems that it creates and glosses over were detailed better in your article.

  • Perry Whittle

    SSC Guru

    Points: 233824

    first thing I would ask is what is the recovery model in use on the DW database?

    I'm sure you've heard a hundred times "don't shrink your transaction logs".

    If you haven't heard this then, "Don't shrink your transaction logs!!"

    -----------------------------------------------------------------------------------------------------------

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • kevaburg

    SSCoach

    Points: 17910

    Perry Whittle (3/9/2015)


    first thing I would ask is what is the recovery model in use on the DW database?

    I'm sure you've heard a hundred times "don't shrink your transaction logs".

    If you haven't heard this then, "Don't shrink your transaction logs!!"

    ***smile***

    The OP mentioned that the DHW runs mstly in SIMPLE. My solution to Keep the log small was to increase the frequency of CHECKPOINTs that Information would be written to disk more often and the space released.

  • ken.trock

    SSCertifiable

    Points: 5147

    Perry Whittle (3/9/2015)


    first thing I would ask is what is the recovery model in use on the DW database?

    I'm sure you've heard a hundred times "don't shrink your transaction logs".

    If you haven't heard this then, "Don't shrink your transaction logs!!"

    Actually, I've always heard "Don't shrink your database files!!". I thought it was ok that we shrink the tranlogs. Actually, we have no choice sometimes :angry:

    My issue and I suspect Mark's and others is that we cannot grow all our logs to their comfortable working size without running out of space. So we shrink when we must.

    Ken

  • SQLKnowItAll

    SSC Guru

    Points: 61781

    ken.trock (3/9/2015)


    Perry Whittle (3/9/2015)


    first thing I would ask is what is the recovery model in use on the DW database?

    I'm sure you've heard a hundred times "don't shrink your transaction logs".

    If you haven't heard this then, "Don't shrink your transaction logs!!"

    Actually, I've always heard "Don't shrink your database files!!". I thought it was ok that we shrink the tranlogs. Actually, we have no choice sometimes :angry:

    My issue and I suspect Mark's and others is that we cannot grow all our logs to their comfortable working size without running out of space. So we shrink when we must.

    Ken

    In a perfect world, there are several ways to keep the logs smaller; i.e. Don't do everything in one large transaction. Batch it into smaller transactions. Your log shouldn't need to be 80GB on a DW that doesn't need point in time recovery.

    Thanks,
    Jared
    PFE - Microsoft
    SQL Know-It-All
    How to post data/code on a forum to get the best help - Jeff Moden[/url]

  • ken.trock

    SSCertifiable

    Points: 5147

    In a perfect world, there are several ways to keep the logs smaller; i.e. Don't do everything in one large transaction. Batch it into smaller transactions. Your log shouldn't need to be 80GB on a DW that doesn't need point in time recovery.

    Even something like instead of updating a month's worth of data in one fell swoop, looping thru the month and updating a day at a time.

  • Jeff Moden

    SSC Guru

    Points: 996120

    While I have an appreciation for a limited budget, it is much more expensive to have to come up with these types of workarounds than it is to buy more harddisk space (provided there's a rack with an open slot in it). A terabyte of disk space really doesn't cost that much an will make life a whole lot easier and code a whole lot faster if you have enough.

    I do appreciate the article, though. It's a great temporary work around (although such workarounds frequently give people the excuse to not do it right).

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 30 total)

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