Oversized Log File

Question

Oversized Log File

mike 57299

SSCrazy

Points: 2529
More actions
September 25, 2015 at 8:36 am

#304948

I have a database:
mdf: 135 GB
ldf: 768 GB
We do daily full backups but the log file is continuing to grow.
I would like to do the following:
Checkpoint;
full backup;
set recovery model to simple;
set log size to 25 GB (approx 20%)
set recovery model to full
Does anyone see a problem with this?
Thanks,
Mike

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Are you doing transaction log backups? How often? There's no need to change to simple mode and back again - just back up the log and shrink it to the size it's going to need to be. But don't bother shrinking it if you know or suspect it's only going to grow again - that's a waste of server resources and is likely to cause physical fragmentation on disk.

John

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

So you're not doing log backups?

Either you change your recovery mode to simple or you start taking log backups and managing the transaction log correctly.

Changing the recovery model to simple and then to full makes absolutely no sense.

More information: http://www.sqlservercentral.com/articles/Stairway+Series/73779/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sreekanth B SSCertifiable Points: 6145 More actions · Answer 3

If your Recovery model is set to FULL and you are taking only Full backups, then the log file will grow. A full backup won't truncate the T-log. So, if you want to leave your database in FULL Recovery model, you need to schedule regular T-Log backups as well besides daily Full backups. Or you can leave your database in SIMPLE recovery model if you don't want point in time recovery(I wouldn't do that on a production database).

mike 57299 SSCrazy Points: 2529 More actions · Answer 4

Yes, we are doing t-log backups every 24 min.

Also, I don't understand something about logging... doesn't a checkpoint command clear all dirty log issues? What is in a transaction log after a checkpoint that isn't in the actual mdf file?

Thanks,

Mike

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

mike 57299 (9/25/2015)
Also, I don't understand something about logging... doesn't a checkpoint command clear all dirty log issues?

No. A checkpoint writes all dirty database pages to disk and, in simple recovery only, truncates the log

What is in a transaction log after a checkpoint that isn't in the actual mdf file?

Meaningless question, since the log isn't a 'holding spot' for changes.

Take a read through this: http://www.sqlservercentral.com/articles/books/94938/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Leeland SSCrazy Eights Points: 8959 More actions · Answer 6

For additional info on backups I always liked this article from Paul

https://technet.microsoft.com/en-us/magazine/2009.07.sqlbackup.aspx

mike 57299 SSCrazy Points: 2529 More actions · Answer 7

Thank you all.

I did a DBCC SQLPERF and found out that the log is only using .99% of the 700+ GB file. Later tonight, I will do a shrink file on it.

Mike

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Don't shrink it to zero. Shrink it to around half of the size of the msf, and monitor the file growth and usage.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass