Shrinking the Transaction Log with SMO

This videos shows you how to shrink the transaction log of a database using SMO.

Tim Mitchell is a Microsoft SQL Server database developer, business intelligence consultant, writer, and speaker. He has been working professionally with SQL Server for over six years, and holds the MCTS and MCDBA certifications from Microsoft as well as a Bachelor's degree in computer science from Texas A&M University - Commerce.

Glenn Thompson on 10/22/2008

You are assuming a person knows how to use SMO, but us part time DBAs usually just fly by the seat of our pants when it comes to anything DBA related. :)

John Farner on 10/22/2008

This is a very effective method for content delivery. Could you add a "download code" button?

Chris Rock on 10/22/2008

John, we're working on it. The code should be available soon.

Tonci on 9/23/2009

Hi there, Your code works wonders. However, I think you might have a typo in your demo. foreach (Database db in svr.Databases) { foreach (LogFile log in db.LogFiles) { //truncate log file only if more than 50% of its declared size is used if ((log.UsedSpace / log.Size) > 0.5) { //optimal size of log file is 20% the size of its database log.Shrink(Convert.ToInt32(db.Size * 0.2), ShrinkMethod.Default); I think it should be if ((log.UsedSpace / log.Size) > 0.5) instead of if ((log.UsedSpace / log.Size) < 0.5) Anyway, if I am wrong at this, I will find out. Still, it looks good in a development server. Also, recommended log file size is 20% of database size. That is why, I used log.Shrink(Convert.ToInt32(db.Size * 0.2), ShrinkMethod.Default); Best regards, Tonci.

Cornelius Hendricks on 11/23/2009

More details on building script.

Dan on 2/15/2010

Slick!