Log space 98% free but drive has no space

  • hi folks

    just after some advice

    if the Log space has 98% reported free space but the physical drive has virtually no free space left

    will trans logs for the db post ok?

    cheers

    m

  • Yes, the free Log space is within the allocation of the log file(s).

    😎

    Obviously, you should monitor the free space within the files and turn off any autogrowth for the files.

  • cheers Eirkur! just testing some theory !

  • Be careful of autogrowth here. Also, it's a good idea on any drive with auto-growth enabled, create an file called 'deletemeinemergencies' or similar. Make it big enough, say 20 or 50 mb, so that you can use it to easily reclaim a little space when the drive filled by accident. It makes recovery much easier.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • dopydb wrote:

    hi folks

    just after some advice

    if the Log space has 98% reported free space but the physical drive has virtually no free space left

    will trans logs for the db post ok?

    cheers

    m

    I'd also suggest that you need to figure out what caused your log file to grow to the extent that it has and if it's still occurring.  Whatever it is, it needs to be found and fixed.

    Typically, it's one of two things...

    1. A poorly written query with one or more accidental many-to-many joins (and they typically have a DISTINCT or even a GROUP BY in them to "get rid of the duplicate rows" they return).
    2. Index Maintenance, especially if you're using supposed "Best Practice" methods of using REORGANIZE between 5 and 30% logical fragmentation.  REORGANIZE isn't the tame, little, resource efficient kitty that a lot of people would have you believe that it is.  It's REALLY tough on the log file especially on large indexes and can come close to producing log file entries almost twice the size as the data in the index.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Grant Fritchey wrote:

    Be careful of autogrowth here. Also, it's a good idea on any drive with auto-growth enabled, create an file called 'deletemeinemergencies' or similar. Make it big enough, say 20 or 50 mb, so that you can use it to easily reclaim a little space when the drive filled by accident. It makes recovery much easier.

    OOOOH! I like that idea! Why didn't I think of that! 🙂

    "Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
    — Samuel Johnson

  • david.edwards 76768 wrote:

    Grant Fritchey wrote:

    Be careful of autogrowth here. Also, it's a good idea on any drive with auto-growth enabled, create an file called 'deletemeinemergencies' or similar. Make it big enough, say 20 or 50 mb, so that you can use it to easily reclaim a little space when the drive filled by accident. It makes recovery much easier.

    OOOOH! I like that idea! Why didn't I think of that! 🙂

    For the log file, it's real handy if (for example, you have a 100GB drive just for the log file), to allocate only 10 or 20GB and then setup an alert for when it grows.  That way, you can have some time to find out what is causing the log file to grow.  You could even make it so the alert runs code to see what's running to help narrow down the candidates or scare up some code that will tell you what everything that's currently running is using for logfile space.

     

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Those are handy to help trick the network folks into thinking that you're using more space than you are so you have time to make a request for more space but that goes against my nature (Absolute honesty, etc).  It also won't help code and won't help you find the problem.

    For log files, I'd do like I stated above with alerts.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

Viewing 9 posts - 1 through 9 (of 9 total)

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