How can reduce database log file size

Question

How can reduce database log file size

binutb

SSCommitted

Points: 1741
More actions
October 11, 2018 at 1:40 am

#366312

Hi,
Is it possible to reduce database log file (.ldb file) ?
Can you advise any method to reduce log file size.
Regards
Binu

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 1

First of all what size is the ldf file and also the mdf file?
What recovery model is your database using? If it's full, how often are you backing your log up?

Thanks

binutb SSCommitted Points: 1741 More actions · Answer 2

Hi,

database created 1 month back
file size
mdf - 2.81
ldf - 43 gb
Fullback is taken one month back and no log backup is not taken yet.

Regards
Binu

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 3

Take a log backup as soon as you can then schedule regular log backups. Or if log backups aren't needed put the database in simple recovery.

You should also take regular full backups.

Thanks

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 4

binutb - Thursday, October 11, 2018 2:22 AM
Hi,
database created 1 month back
file size
mdf - 2.81
ldf - 43 gb
Fullback is taken one month back and no log backup is not taken yet.
Regards
Binu

This is why. If you are going to leave your database in Full recovery model, you must take regular transaction log backups in order to curtail its growth. Alternatively since this isn't a business critical system by the look of it, you could just switch it to Simple recovery model (then do a one-off shrink of the log).

binutb SSCommitted Points: 1741 More actions · Answer 5

Is any way to reduce the current log backup size?

Regards
Binu

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 6

Run DBCC SQLPERF(LOGSPACE) It will show you how much of you log is actually free space. It's likely that most of it is unused so the backup size will be small.

Thanks

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 7

Why is my log file full?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Johnson Welch SSCoach Points: 18773 More actions · Answer 8

Here is how can you reduce database log file. Shrink the database log file according to the recovery model:
https://www.mssqltips.com/sqlservertutorial/3311/how-to-shrink-the-transaction-log/