Shrinking _log file for live database

  • DaveBriCam

    SSCarpal Tunnel

    Points: 4022

    I have a live database in SQL2016 (in Production, in use) that I need to shrink its l_log file which has grown to over 250GB.

    Can I use this type of command block safely?:

    ALTER DATABASE ExampleDB SET RECOVERY SIMPLE
    DBCC SHRINKFILE('ExampleDB_log', 0, TRUNCATEONLY)
    ALTER DATABASE ExampleDB SET RECOVERY FULL
  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719107

    It's safe, but there are two things to note here.

    1. you can cause slowdown in the activity for the database, as this is moving things around.
    2. you break the recovery chain here, so you need a full backup after this.
  • Jeffrey Williams

    SSC Guru

    Points: 88448

    You don't actually need to switch recovery model - unless you cannot perform a transaction log backup due to space issues.  It may take several attempts - but you should perform the transaction log backup, issue the shrink file - and repeat until the log file is at the correct size.

    You also do not need the TRUNCATE_ONLY option for the shrink file.  You cannot shrink the file unless the VLF's at the end are not currently utilized - which is why you need to perform a transaction log backup and then shrink.  The shrink will remove all VLF that it can which will leave the last VLF in the file as used - the next transaction log backup will cause that VLF to be marked as reusable and move to the beginning of the file.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • DaveBriCam

    SSCarpal Tunnel

    Points: 4022

    Would the truncate only option be necessary since this database will have activity going on while I'm reducing this file size? I have no room on my server to back it up.

  • Chris Harshman

    SSC-Forever

    Points: 42080

    Since this is a transaction log file, what Jeffrey says holds true, you don't need the TRUNCATE ONLY.

    What I think is more important, before you try this, do you know why the transaction log grew this much to begin with?  How frequently do you take transaction log backups?

  • DaveBriCam

    SSCarpal Tunnel

    Points: 4022

    Quite honestly no one including myself has any idea how this file grew so large or what to do about it while our space is quickly running out. I don't know how to delete it or truncate it without an error message

    Msg 8985, Level 16, State 1, Line 2

    Could not locate file 'ExampleDB_log' for database 'ExampleDB' in sys.database_files. The file either does not exist, or was dropped.

  • Chris Harshman

    SSC-Forever

    Points: 42080

    DaveBriCam wrote:

    ...I have no room on my server to back it up.

    And hopefully you are putting your backup files somewhere other than the server you are backing up correct?

  • Chris Harshman

    SSC-Forever

    Points: 42080

    DaveBriCam wrote:

    Msg 8985, Level 16, State 1, Line 2

    Could not locate file 'ExampleDB_log' for database 'ExampleDB' in sys.database_files. The file either does not exist, or was dropped.

    Check how much of your transaction log is used:

    SELECT f.file_id, f.name AS file_name, f.type_desc,
    f.physical_name, f.size/128 AS size_MB,
    FILEPROPERTY(f.name, 'SpaceUsed')/128 AS used_MB,
    f.size/128 - FILEPROPERTY(f.name, 'SpaceUsed')/128 AS available_MB,
    CASE WHEN f.is_percent_growth = 1 THEN f.growth ELSE f.growth / 128 END AS growth,
    f.is_percent_growth
    FROM sys.database_files f;

    If there is space that is not used, you can try the shrink.  If you're not sure the name of the log file, you can use the file id, which will always be 2 for the transaction log.  Take a transaction log backup and then run:

    USE yourdatabasenamehere;
    DBCC SHRINKFILE (2, 0);

    Again as Jeffrey says, it will only shrink log files if the virtual logs in the end of the file are not in use.

  • DaveBriCam

    SSCarpal Tunnel

    Points: 4022

    It says the file is in use. The backup is on the same server as the SQL database unfortunately. There seems to be some inconsistency of naming too (?)

     

    file_id       file_name      type_desc         physical_name size_MB                used_MB           available_MB       growth        is_percent_growth

    1          Search_log               LOG          D:\Databases\Data\ExampleDB_log.LDF            247763        31           247732        10        1

  • Chris Harshman

    SSC-Forever

    Points: 42080

    Try this to see why it can't reuse the log space:

    SELECT database_id, name, recovery_model_desc, log_reuse_wait_desc FROM sys.databases WHERE recovery_model_desc <> 'SIMPLE'

    --edit oops I read your results wrong

  • Chris Harshman

    SSC-Forever

    Points: 42080

    With that much free space it should be able to shrink some after a transaction log backup.  Unfortunately I don't know a way to determine which VLF in the transaction log are in use.  It looks odd that it says the file_id = 1?

  • DaveBriCam

    SSCarpal Tunnel

    Points: 4022

    So the file size of my log file being 253GB is nothing to worry about (?). The total size of the drive is 1 terabyte. The file ID 2 is actually  what appears when I run the below:

    SELECT f.file_id, f.name AS file_name, f.type_desc,
    f.physical_name, f.size/128 AS size_MB,
    FILEPROPERTY(f.name, 'SpaceUsed')/128 AS used_MB,
    f.size/128 - FILEPROPERTY(f.name, 'SpaceUsed')/128 AS available_MB,
    CASE WHEN f.is_percent_growth = 1 THEN f.growth ELSE f.growth / 128 END AS growth,
    f.is_percent_growth
    FROM sys.database_files f;

    • This reply was modified 1 month, 4 weeks ago by  DaveBriCam.
  • Chris Harshman

    SSC-Forever

    Points: 42080

    How much free space is on that D: drive as a whole, and are the data files also on the same drive?

  • DaveBriCam

    SSCarpal Tunnel

    Points: 4022

    On the D: drive there is 239GB available out of 839GB. And the data and logs are on the same drive

  • Chris Harshman

    SSC-Forever

    Points: 42080

    Okay, so you do have some room here.  Do you know how much your files are growing by week or by month?

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

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