Log File Full

Question

Log File Full

Viewing 9 posts - 16 through 23 (of 23 total)

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

SQLdba-473999 SSCrazy Points: 2761 More actions · Answer 1

lfguzman_25 (7/17/2008)
If you like to reduce the size you have to shrink the log
i usually prefferd to use the TSQL Statement
DBCC SHRINKFILE ('database_log',sizetoreach')

Ok, I can do that. But I remember reading something about shrinkfile and how it's not great to use because it causes fragmentation and an extra load on the server?? But I guess if I'm running out of space, this would be my answer.

Thanks for the information everyone!

And Luis, I might take you up on that offer sometime!

tosscrosby SSCoach Points: 18045 More actions · Answer 2

Shrinking the log file is not so bad, if necessary. Shrinking the data

file(s) is truly not recommended. It will lead NTFS fragmentation and will definitely fragment your indexes, leading to potentially awful performance issues. Is it possible to move one of the files, either the mdf or ldf, to a different partition on the server (if you have one)?

-- You can't be late until you show up.

SQLdba-473999 SSCrazy Points: 2761 More actions · Answer 3

tosscrosby (7/17/2008)
Shrinking the log file is not so bad, if necessary. Shrinking the data
file(s) is truly not recommended. It will lead NTFS fragmentation and will definitely fragment your indexes, leading to potentially awful performance issues. Is it possible to move one of the files, either the mdf or ldf, to a different partition on the server (if you have one)?

Thanks for the info.

There are other partitions, we just have them specifically assigned to each db, so we like to keep them separate. I guess the best answer here is increase the disk space (or LUN since we are connected to the SAN). If free space gets minimal before the LUN expansion, then I will resort to shrinking the log file.

Thank you all for your help. I really appreciate it!

tosscrosby SSCoach Points: 18045 More actions · Answer 4

One other thing you can do, prior to shrinking the log file, is "backup log 'databasename' with truncate_only". This will cleanse the log, as much as possible. Then issue the shrinkfile and then, most importantly, issue a full backup of the database as you've broken the restore chain when issuing the truncate. This will allow you to free up as much space as possible. But be aware, that log file is going to want to grow again, impacting performance when it does. More disk is the optimal solution, obviously.

-- You can't be late until you show up.

SQLdba-473999 SSCrazy Points: 2761 More actions · Answer 5

Actually, CAN I do a shrinkfile in a mirrored environment? I was told by our contractor that you should never truncate the logs since you don't know if all transactions have been propagated to the mirror? Doesn't the shrinkfile involve a truncate?

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

angie stein (7/17/2008)
Actually, CAN I do a shrinkfile in a mirrored environment? I was told by our contractor that you should never truncate the logs since you don't know if all transactions have been propagated to the mirror? Doesn't the shrinkfile involve a truncate?

A shrink file does not truncate. It just changes the size of the file on disk. If you're using database mirroring, transactions that have not been copies to the mirror (only possble in asynchronous mirroring) are marked active and will not be removed by a backup log, whether it be to disk or truncate. Backup log only removes inactive log entries.

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

SQLdba-473999 SSCrazy Points: 2761 More actions · Answer 7

GilaMonster (7/17/2008)
A shrink file does not truncate. It just changes the size of the file on disk. If you're using database mirroring, transactions that have not been copies to the mirror (only possble in asynchronous mirroring) are marked active and will not be removed by a backup log, whether it be to disk or truncate. Backup log only removes inactive log entries.

Thank you for the clarification! I will feel much more at ease now doing the shrinkfile.

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

angie stein (7/18/2008)
Thank you for the clarification! I will feel much more at ease now doing the shrinkfile.

Don't feel too easy with it. It's not something that should be done often. While you can't loose transactions, repeated shrinks and grows of the tran log increase the number of virtual log files within the log. That makes tran log backups lower. Repeated shrinks and grows can also result in fragmentation at the file level.

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

SQLdba-473999 SSCrazy Points: 2761 More actions · Answer 9

GilaMonster (7/18/2008)
Don't feel too easy with it. It's not something that should be done often. While you can't loose transactions, repeated shrinks and grows of the tran log increase the number of virtual log files within the log. That makes tran log backups lower. Repeated shrinks and grows can also result in fragmentation at the file level.

Right, that was my earlier statement in this thread. I was worried about fragmentation and possibly putting an extra load on the server. I think I'll use it just as a last result, but at least I know it will not cause any inconsistencies or loss of transactions.

Thanks again for all the information!