Log file size is bigger than the datafile size

Avyayah 1,231 Reputation points
2020-12-11T06:15:23.593+00:00

One of the database has datafile size 6.5 GB and Transactional Log 21 GB. What should be the process of reducing the log file size? Backup job is enabled in Transactional and Full backup on 2016 server.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,768 questions
0 comments
Accepted answer
  1. Tom Phillips 17,716 Reputation points
    2020-12-11T18:27:59.893+00:00

    Just to be clear.

    The log file NEVER gets physically smaller, unless you manually shrink it. A log backup set the data inside as "logically" available for reuse. It does NOT physically make the file smaller.

    The log file is that big, because at some point is was required to be that size.

    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Shashank Singh 6,246 Reputation points
    2020-12-11T06:31:18.68+00:00

    What is output of select log_reuse_wait_desc from sys.databases where name='db_name' whatever output comes is what holding your log from being truncated. If nothing comes means log file VLF are truncated and logs can be reused and if you really need the space you may shrink but shrinking is bad. Is their replication, mirroring configured for this database ?

    Its also likely that you may need to increase frequency of your log backup

  2. CathyJi-MSFT 21,096 Reputation points Microsoft Vendor
    2020-12-11T07:42:13.59+00:00

    Hi @Avyayah ,

    There are a number of reasons a log file can fill to extreme sizes. The most common one by far is that the database is in full recovery model, and Transaction Log backups are not happening fast enough. Next to that, it could be that you had a massive transaction happen such as a huge data import, rebuild all indexes, etc. These are logged and stay there until the .ldf file is backed up.

    If the transaction log cannot be shrank and reused, please execute the following statement to check why the transaction log cannot be shrink. 

    SELECT log_reuse_wait_desc FROM sys.databases WHERE name='<database name>'

    If the result is other than NOTHING, please perform corresponding operation.

    To reduce the physical size of a physical log file, you must shrink the log file. This is useful when you know that a transaction log file contains unused space.

    In order to shrink transaction log, please follow the steps below:

    1. Perform full backup of database
    2. Perform transaction log backup
      sometimes, we have to perform multi transaction log backup before the transaction log could be shrank.
    3. Shrink transaction log. Right click “your database”->tasks->shrink->files->change file type to “log”->Choose “Reorganize Pages before releasing unused space”->Shrink file to ** MB.

    Suggest you read the MS document Manage the size of the transaction log file and the blog Why is my SQL Log File Huge to get more information.

    Best regards,
    Cathy

    If the response is helpful, please click "Accept Answer" and upvote it, thank you.

  3. tibor_karaszi@hotmail.com 4,301 Reputation points
    2020-12-11T14:37:52.687+00:00

    Here's an article post I wrote regarding handling of the transaction log, including what to do if you have a "too large" log file: https://karaszi.com/large-transaction-log-file