CODEWITHSUNDEEP
sql-server
MK86
MK86

Reputation: 141

Keeping the size of ldf file under certain limit

I have scenario where ldf file on SQL Server grew to more than 30GB in course of 4-5 days. As this presents potential problem with space (the HDD is a bit more than 250GB) I searched few solutions but came baffled as anything that I find on shrinking of ldf file says that it will grew again. One solution is to periodically perform this operation but I am not sure if it will be helpful in long term. Is it possible somehow to fixate maximum size of ldf file and then olders records in it would removed automatically by SQL Server?

If this doesn't sound good please do share some ideas on how should I manage this? What is the best course of action?

Thanks in advance!

Upvotes: 1

Views: 6331

Answers (1)

tonyd
tonyd

Reputation: 340

You've got two scenarios with this situation.

Scenario 1 - database is in simple recovery and there is a large process growing the transaction log. In this case, there's not much you can but leave the transaction log at the largest size and upgrade your drive space if possible.

Scenario 2 (most likely) - Your database is in full recovery mode and you don't have transaction log backups configured. A good backup practice is a daily full backup (if your database isn't too big) and an hourly transaction log backup. After you have a good full backup followed by a good transaction log backup you can shrink the transaction log.

Here's some code that may help:

Shrink Transaction Log - MSDN

DBCC SHRINKFILE (LogicalLogName, SizeInMB)

How much of the transaction log is being used -

USE DatabaseName
GO

DBCC SQLPERF (logspace)

If you find that after you shrink the transaction log that the file size is the same, try to adjust the file size manually in the transaction log settings. Alternatively, you can set the database to simple recovery and then back to full recovery. Although the latter will break your transaction log backups until the next full backup.

Upvotes: 3

Related Questions