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;


Enroll Today!
SteveStedman5
SteveStedman5
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!

1 Comment on “Unusually Large Log

  1. 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

Leave a Reply

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

*

To prove you are not a robot: *