none
How to shrink extremely large SQL log files?

    Frage

  • I inherited a server hosting TFS 2015 & SQL Server 2012. 

    Users were unable to do their work due to loss of storage. Looking at the server is seems as if log files are taking up so much space. I am very under educated on SQL server and how to handle safely shrink these enormous log files without causing data loss. 

    I have done a little research, but I am still weary!

    There is a Configuration Log that is over 200GB and a Warehouse log that is about the same size. 

    Other log files are also extremely large. 

    Any help would be appreciated. 

    Freitag, 22. Juni 2018 15:58

Alle Antworten

  • Hello,

    To avoid log space issue you can schedule log backup for the database , you can also perform the shrink operation on log so that space can be reclaimed.  below link may help you to get the log backup and perform log shrink on DB:

    Back Up a Transaction Log (SQL Server)

    DBCC SHRINKFILE (Transact-SQL)

    you can find many more related to database maintenance in below link :

    SQL Server Maintenance Tips

    Please mark me as answer if my post resolves your issue.

    Br

    ChetanV


    Freitag, 22. Juni 2018 16:42

  • There is a Configuration Log that is over 200GB and a Warehouse log that is about the same size. 

    Other log files are also extremely large. 

    Just to clarify, by Log files, do you mean the database's Log files (with .LDF extension)? I believe so but wanted to confirm.

    Assuming they are SQL Server database Log files, you have a couple of options:

    1. If the database (whose log file is huge) is in Full recovery model, make sure that log backups are running for it. If not, the log would continue to grow because the recoverable VLFs in the log cannot be reused until they are backed up by a log backup. Once Log backup completes, run the following:

    --Assuming 2 is the log file ID
    --you can query sys.database_files to get the fileID for the file you want to run shrink on
    --This will reset the size to the default size
    
    DBCC SHRINKFILE(2)


    2. If the above doesn't help, you can run the following to temporarily switch the Database to Simple recovery model to allow log truncation.

    ALTER DATABASE <DATABASENAME> SET RECOVERY SIMPLE

    Once the above command completes, run the DBCC SHRINKFILE(2) again and see if space is reclaimed.

    Important: As soon as #2 is done, switch the DB back to FULL recovery model by running the same Alter Database command but replacing SIMPLE with "FULL" and immediately kick off a FULL backup of the DB, otherwise, the DB would run in auto-truncate mode.

    If neither works, check for active transactions that could be holding up the log truncation.

    Hope this helps.


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Samstag, 23. Juni 2018 02:16