Log File Expansion Automation

SQL Server Database Log file expansion can be fairly tedious if you need to make the log bigger in many reasonably-sized growth increments. It can be tedious because you may need to write and execute a large number of ALTER DATABASE ... MODIFY FILE ... commands.

Automatic Log File Expansion lets you dream of food, glorious food!

Automatic Log File Expansion lets you dream of food, glorious food! – Still Life of Fruit by Giovanni Stanchi

The following code automatically grows a SQL Server Database log file, using the size and growth increments you configure in the script. If you set the @DebugOnly flag to 1, the script will only print the commands required, instead of executing them. This allows you to see what exactly will be executed ahead of time. Alternately, you could copy-and-paste the commands into a query window and execute them one-by-one.

Proactively monitoring and maintaining the SQL Server Transaction Log is one of the most important aspects of database management. The first being always take-and-test your backups! If you don’t have a backup, nothing else matters. However, once you’ve got backups setup, ensuring the transaction log is in tip-top shape is of paramount importance to good performance and up-time.

If you need to shrink-and-grow a log file to reduce the number of Virtual Log Files present in the log, check this post for a script that does that automatically.

Also, check out the rest of our series on recovery and our database tools.

Let me know if you enjoyed this post, and if you found the script useful. Alternately, if you think this needs further clarification, or more detail, please let me know in the comments!

Ads by Google, Paying the Rent: