Select Page

Unusually Large Log

The log file appears to be quite large compared to the size of the database file.

This can happen if your log file grew too large and has not yet been shrunk.

This can also happen if full backups are not running regularly.

Suggestions:

Check the frequency of your backups.

Links:

Steps to shrink the DBHealthHistory Database if it gets too large.


-- check the size of the files.
SELECT size / 128.0 as sizeMB, name
FROM sys.database_files;

GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE DBHealthHistory SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (DBHealthHistory_log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE DBHealthHistory SET RECOVERY FULL;
GO
-- Be sure to do a full backup, then kick off transaction log backups

-- check the size of the files.
SELECT size / 128.0 as sizeMB, name
FROM sys.database_files;

1 Comment

  1. Daniel Serrano

    Hello,

    Today I noticed the log was at 21GB (Database is 102MB). Everyday I perform a full backup of all databases, including DBHealthHistory.

    May I set Database Recovery mode to Simple? (instead of remember to clean log after each backup)

    Thanks for your help

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

To prove you are not a robot: * Time limit is exhausted. Please reload CAPTCHA.