Shrinking Log files

Question

Post reply

Shrinking Log files

nocode99

Old Hand

Points: 304
More actions
August 5, 2011 at 9:05 am

#241196

Hello,
I've been shrinking log files (first by truncating the log and then shrink via management studio) and I've been reading that this isn't the best practice as the log file will just continue to grow and cause more fragmentation.
What is the best way to handle the log file size?
For example, our Sharepoint db is about 4.3 GB. The log file is now at 23.4 GB. The DB is set to Full. I've read that if I take a backup of the transaction log, this will shrink the file? What is the proper way to manage this?
I apologize as I'm a total noob. I'm System Admin and my DBA recently left so I've been handed all these tasks until we find a replacement.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Short answer. Take tlog backups every x minutes.

I would personally shrinkg it ONCE to 2-3 GBs and then leave it alone.

All the details here :

http://www.sqlservercentral.com/articles/64582/

http://www.sqlservercentral.com/articles/Transaction+Log/72488/

magasvs SSCertifiable Points: 7660 More actions · Answer 2

Shrinking the log file is not the same as shrinking data file. If you shrink data file - yes, it will lead to fragmentation. Shrinking the log file will improve log file performance when you have large number of virtual logs (shrink it to the minimal size, then resize it to the size that you usually have - for example 500 Mb).

More information is here: http://www.sqlskills.com/BLOGS/KIMBERLY/category/Transaction-Log.aspx#p3

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

magasvs (9/2/2011)
Shrinking the log file will improve log file performance when you have large number of virtual logs (shrink it to the minimal size, then resize it to the size that you usually have - for example 500 Mb).

That's not what he's doing though. A once-off shrink and regrow fixes log fragmentation. Repeated shrinks and grows (with bad autogrow settings) is what causes VLF fragmentation in the first place.

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

saravanan_skcet SSC Journeyman Points: 94 More actions · Answer 4

Hi ,

Initially to reduce the log size ,take the log backup and shrink log file .

As the db is in full recovery model ,make sure you have log backups configured and it should run every X hours .(X should be based on how fast log file grows)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

saravanan_skcet (9/13/2011)
Hi ,
Initially to reduce the log size ,take the log backup and shrink log file .
As the db is in full recovery model ,make sure you have log backups configured and it should run every X hours .(X should be based on how fast log file grows)

X should be based on your RPO & RTO, not on how fast it grows.