Reduce the size of logfile

Avyayah 1,231 Reputation points
2021-05-05T17:42:14.883+00:00

In one of the server, logfile increased in some of the database and the transactional log drive was almost full. Few database backup took sometime to complete but logspace used in few databases are more than 52%. DBCC SQLPERF(LOGSPACE). What can I do to reduce the size of the logspace of those databases

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,758 questions
0 comments
Accepted answer
  1. CathyJi-MSFT 21,096 Reputation points Microsoft Vendor
    2021-05-06T02:30:35.397+00:00

    Hi @Avyayah ,

    >What can I do to reduce the size of the logspace of those databases

    Shrinking log files will reduce the size of log files. In order to shrink transaction log, please follow the steps below:

    1. Perform transaction log backup. We need have a full back up for this database, before doing log backup.
      sometimes, we have to perform multi transaction log backup before the transaction log could be shrank.
    2. Shrink transaction log ( DBCC shrink file or using SSMS UI)
      If the transaction log cannot be shrank, 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. Then please try step 1 and 2 again.

    Refer to MS document Shrink a File and sys.databases (Transact-SQL).

    Suggest you read the blog Eight Reasons why your Transaction Log Files keep growing. . Find the reason for log keep growing, in case this problem happens again.

    If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.

    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 101.4K Reputation points MVP
    2021-05-05T21:37:46.947+00:00

    You can run DBCC SHRINKFILE on these files, but this is pointless, unless you know that the reason that the log files grew was due to some exceptional event that you are sure will not happen in the next six months or so.

    If the growth simply reflect the need of log space for the application, the log will grow again, and because SQL Server has to zero out the log file, this will take up resources.

    A better solution may be to see your local hardware dealer for more disk.

    0 comments