none
How to shrink extremely large SQL log files?

    Question

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

    Friday, June 22, 2018 3:58 PM

All replies

  • 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


    Friday, June 22, 2018 4:42 PM

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

    Saturday, June 23, 2018 2:16 AM
  • WChandlerUK,

    Please refrain from proposing your own response as the answer instantly. Not that you shouldn't do it, you sure can, but give OP some time to get back to this thread and apply your suggestion. I understand this post is more than a month old but I'd leave it to the forum Moderators and Microsoft staff to propose and mark responses (though I can do it too as my response has lapsed good amount of time, I chose not to). Please see this article on when to self-propose. 

    Thanks!


    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.

    Wednesday, August 8, 2018 12:55 PM

  •  Please see this article on when to self-propose. 

    Forget the article, you should never self propose. That article was at time when there were few members now we have many MSFT CSG guys and mods and users who can propose the answer. I believe he went ahead and marked your post as abusive for giving sane advise

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP



    Wednesday, August 8, 2018 2:22 PM

  • Forget the article, you should never self propose. That article was at time when there were few members now we have many MSFT CSG guys and mods and users who can propose the answer. I believe he went ahead and marked your post as abusive for giving sane advise

    Thanks! Glad I adhere to that rule already :) 

    HA! Not sure which part of my response appeared abusive to him. 


    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.

    Wednesday, August 8, 2018 3:37 PM