SQL Job Agent: T-SQL Script to Shrink All Log Files on the Server


In some situations a scheduled shrink of the log files is needed to release disk space. The script bellow can be scheduled in SQL Server Agent Job.

Create database

Create test table

Check Log files sizes (will be reused)

Check Log Sizes 1

Fill the log file with fake data

Check Log files sizes (run the script above)

Check Log Sizes 2

And the script to shrink the log files

The databases with recovery model = FULL are altered to SIMPLE, shrinked and altered back to FULL as explained in MSDN.

Shrink Log Files

Check Log files sizes (run the script above)

Check Log Sizes 3

Keep it simple :-)

Leave a comment

Your email address will not be published. Required fields are marked *