none
SQl Server T log bigger than DB RRS feed

  • Question

  • Hi experts 

    our  databases on 2016 SE and some of the DBs Log are higher than the DBs,   WE did shrink for T log after routine backups . but its not size will grow higher than use to be.  I understand if DB activities update need 50GB space  and no  point reduce back to 10GB as still needed it. But DB size is less than the T log sizes. As a option I can expand the L drive  then L drive is 300GB and DB size is 100 GB. 

    Any one can explain to manage the situation 

    cheers


    Monday, April 27, 2020 4:14 AM

Answers

  • Hi Shanky

    T log size is 96GB, but used space less than 30%, Full recovery mode, Log backup every 1 hour, but time to time L will grow. ad I do manual shrink. Not sure any sudden process used 96GB and go  back normal. This will happen intermittently 

    Thank you 

    In this case i think for a DB size 100 GB requiring 96 GB of log file there are some transactions running which needs this space and shrinking is not a solution but would only create slowness as Erland pointed out. You need to find out transactions causing the log file to grow and may be execute them in small bacthes

    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

    • Marked as answer by ashwan Wednesday, May 6, 2020 9:57 PM
    Thursday, April 30, 2020 7:30 AM
    Moderator

All replies

  • Hello ashwan,

    Shrinking the transaction log reduces its physical size by removing one or more inactive virtual log files. A virtual log file that holds any active log records,  is part of the logical log, and it cannot be removed. You can use below query to verify that the file has adequate free space to remove: 

    SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB FROM sys.database_files

    DBCC SQLPERF(LOGSPACE);
    GO

    If insufficient free space is available, the shrink operation cannot reduce the file size any further. If sufficient free space, then you should select log_reuse_wait_desc to find out what reason block the shrinking process. 

    Please note that when you alter database simple recovery model, the log chain will break. If some corruption occurs unluckily, you cannot restore database to anytime you want and you will lose the data while corruption. Please decide carefully before implemetation.

    In the other words, shrink log file is never a good practice which may lead some fragmentation in the log file. You should backup log file periodically in case of log file increase to large.

    More detail, you can refer to below articles:

    Transaction Log Too Large?

    SQL Server Transaction Log Backup, Truncate and Shrink Operations

    SQL Server Transaction Log Administration Best Practices

    Hope it will help.

    Best Regards

    Dawn


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, April 28, 2020 1:44 AM
  • What is recovery mode of Database,. if it is FULL then you have to take log backup frequently. 

    you can also check that what is blocking to reduce the log file by  below script.

    select log_reuse_wait_desc from sys.databases where name = 'Your_DBname'


    regards Ashok

    Tuesday, April 28, 2020 6:58 AM
  • Run following command and let me know the output for all the DB's having log file greater than data file

    select log_reuse_wait_desc from sys.databases where name='db_name'
    The output will tell you reason why log file is growing so much. If output is log backup you need to take log backup and then you can shrink the file. To avoid this in future make sure you have frequent log backup job in place


    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

    Tuesday, April 28, 2020 11:52 AM
    Moderator
  • Hi Shanky

    T log size is 96GB, but used space less than 30%, Full recovery mode, Log backup every 1 hour, but time to time L will grow. ad I do manual shrink. Not sure any sudden process used 96GB and go  back normal. This will happen intermittently 

    Thank you 

    Wednesday, April 29, 2020 1:00 AM
  • STOP SHRINKING THE LOG!

    It is completely pointless to shrink something that will grow again. And not only is it pointless - it is harmful. To grow the log, SQL Server needs to zero out the area allocated and that takes time and resources from the system.

    Shrinking a log file is OK, if you know that there was some exceptional event that you know will not happen again. In this case, when you don't know what is taking up space in the log, don't meddle with the size. If you are the DBA, your task is to provide a service to the business and if the business has a workload which requires a 96 GB transaction log, this is what you shold provide.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, April 29, 2020 9:21 PM
  • Hi Shanky

    T log size is 96GB, but used space less than 30%, Full recovery mode, Log backup every 1 hour, but time to time L will grow. ad I do manual shrink. Not sure any sudden process used 96GB and go  back normal. This will happen intermittently 

    Thank you 

    In this case i think for a DB size 100 GB requiring 96 GB of log file there are some transactions running which needs this space and shrinking is not a solution but would only create slowness as Erland pointed out. You need to find out transactions causing the log file to grow and may be execute them in small bacthes

    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

    • Marked as answer by ashwan Wednesday, May 6, 2020 9:57 PM
    Thursday, April 30, 2020 7:30 AM
    Moderator