Shrink Database Log Files One by One

  • Comments posted to this topic are about the item Shrink Database Log Files One by One

    :-PManie Verster
    Developer
    Johannesburg
    South Africa

    I can do all things through Christ who strengthens me. - Holy Bible
    I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

  • I've just started reading your article, Manie.  I don't believe you'll find many experts that would crucify anyone for using a cursor for process control.  RBAR process of rows?  Yeah, fair game but not for things like the subject of your article.

    A lot of people will say you could have avoided the cursor by using that bloody sys.sp_MSforeachdb system stored procedure.  Apparently, very few of those folks have look at that proc... it's actually a Cursor on steroids!  They also don't realize that it's not the Cursor that's going to take the time here.

    I do have a question for you, though... why are these database bases in the FULL Recovery Model?  It sounds like you may not be taking regularly scheduled full and log file backups.  If you are, then we need to make a couple of additions to your script but I don't want to take the time to write those suggestions up if you're not backing up these databases. 😀

    --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.

    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)

  • Keep in mind, your LOG file(s) may not shrink at first attempt !!!

    ref Shrink a log file

     

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Johan Bijnens wrote:

    Keep in mind, your LOG file(s) may not shrink at first attempt !!!

    ref Shrink a log file

    Even there, they don't mention that you should do a CHECKPOINT first and then wait for the rocks to stop moving. 😀

    --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.

    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)

  • Good article,  and it does brign up the question of why a database is in full recovery model with no backups being made to keep the log file contained.

    This process would be hard to execute in a Availability Group database as you first need to remove the database from the AG group before altering the recovery model.

    And not to try to tear things apart but the use of nvarchar(max) is not a good habit to practice.

    Those things aside, thank you for taking the time that you did in your contributions with this article.

     

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

  • indeed. Good point.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • That's what Powershell is for.

    Invoke-DBAShrink -SQLInstance Instance -AllUserDatabases -FileType Log

  • This was removed by the editor as SPAM

  • You're a day early if this is an April Fools day joke.

    Unless the DBs need FULL recovery model for something, just change them to SIMPLE. Do you need point-in-time recovery, log shipping, or always or availability groups? If not just change them to simple recovery model.

    If they need point in time recovery you need to set up a regularly scheduled transaction log backup.

    • This reply was modified 11 months, 1 week ago by  chuck.hamilton. Reason: spelling correction
    • This reply was modified 11 months, 1 week ago by  chuck.hamilton.
  • Another approach is to back it up to NUL

    CHECKPOINT

    BACKUP LOG [databasename] TO DISK = 'NUL'

  • That doesn't shrink the file.

    And the logfile of a simple database can also grow large if you try to do everything in one transaction.

  •  

     

  • Agree 100%,, I will add that you will need the DBATools cmdlets https://docs.dbatools.io/ .    They even have a video on how to install it, you only need to install on your workstation or a dev box that you use to manage your SQL servers.

     

     

  •  

     

     

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

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