Title: Shrink Transaction Log file
Description: Shrink log automatically.
Author: Paulo Santos
eMail: pjondevelopment@gmail.com
Environment: MS SQL
Keywords: MSSQL, SQL Server, Shrink log, log

Shrink Transaction Log file

DISCLAIMER

The Software is provided "AS IS", without warranty of any kind, express or implied, including but not limited to the warranties of merchantability, fitness for a particular purpose and non-infringement. in no event shall the authors or copyright holders be liable for any claim, damages or other liability, whether in an action of contract, tort or otherwise, arising from, out of or in connection with the software or the use or other dealings in the software.

Introduction

At the company I’m currently working for we’re in the process of setting up ERP software that was bought by our current management.

Regardless the quality (or lack thereof) of the software being installed for use at the company, one of my duties is to care for the good maintenance of the SQL Servers.

Well, long story short, this ERP software that are being installed at the company I work for created a log of 30 GB for a database of 1 GB.

It was outrageous.

So in order to prevent it to grow further I created a job that shrink the log daily.

Although it is pretty straightforward to do this for a single database, I decided to raise the bar a bit and create the same procedure for every single database on all our servers.

How to do this? It’s a two steps procedure.

First I ran a query that creates the SQL commands to generate the procedures on every database on the server.

Then I create a job that will run on the server daily, just prior to the full database backup, to keep the log file small.