question

SahaSaha-5270 avatar image
0 Votes"
SahaSaha-5270 asked Cathyji-msft edited

Reduce the size of logfile

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-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered Cathyji-msft edited

Hi @SahaSaha-5270,

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.