Microsoft SQL Transaction Logs File Size

Our .LDF (SQL Server Database Transaction Log File) has grown to 45GB over the course of a year.

We are looking to minimize that file to a maximum of 2GB.

Using SSMS 2008, we had ENABLE AUTOGROWTH with a RESTRICTED FILE GROWTH of 2GB.

Why has the RESTRICTED F开发者_如何转开发ILE GROWTH not been respected?

What are the steps we should take to minimize the .LDF file to 2GB and ensure that it respects the RESTRICTED FILE GROWTH.

Any help is greatly appreciated :) Thanks!

The reason you have a very large Transaction Log is because the database is set to FULL recovery mode (tha's normal for production servers), but you do not have a regular TLog backup occurring (and possibly no full backup either?)

A Transaction Log should rarely increase beyond a known operating size, based upon the frequency of Log backups and query workload (and the size of largest clustered index for index rebuilds). Wanting to shrink your Log is a symptom, and won't fix the real cause.

How do I reduce transaction log backup size after a full backup?