Shrinking _log file for live database

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

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

  • 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?

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

  • 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?

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

  • 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

  • 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

  • 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?

  • 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 year, 2 months ago by  DaveBriCam.
  • How much free space is on that D: drive as a whole, and are the data files also on the same drive?

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

  • 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