Avoiding Oversized Data & Transaction Log Files

If your SQL Server’s performance is slowing down, the first thing to check is the Physical Storage for any oversized Data and Transaction Log files that could be the cause of the problem.

On SQL Server 2016 the default Auto Shrink setting is set to “False”, so Data & Log files will continue to grow even if records and/or tables are deleted. With an unlimited File size, then, it’s important to monitor file size and keep them in check.Here we show you how to identify oversized files and secondly, how to manually shrink them to maintain optimum performance from your SQL server.

Without careful monitoring we may find that the Data & Log Files are continually growing as transactions take place, but not shrinking, which will obviously over time result in an oversized log file. The default for a database is that Auto Shrink is set to False:

With AutoShrink set to False the Data and Log Files will not reduce in size even if records and/or tables are deleted. It is the DBA’s responsibility to monitor the size of the files and take suitable action to manage the sizes of the files.

Capturing Data File & Transaction Log File Growths

Here are two ways of finding out which files are automatically growing

1. SQL Server Profiler Trace

The script below can be use to search for automatic Log File Growths, using the background profiler trace that SQL Server maintains.

In this case the  script shows Transaction  Log File growth following an UPDATE run on the Sales.SalesOrderDetail (fig 1) table in a database called AdventureWorks2012 (fig 2)

Fig 1:

Fig 2:

and here is the end of the output:

Now The view sys.database_files can be queried to display information about current data and log file sizes:

2. Management Studio

The Disk Usage Report in Management Studio can also be used to view current file sizes and file:

This report shows current file sizes and usage as well as the automatic file growths that have taken place since the instance was started.

Following a ROLLBACK the physical Transaction Log File Size remains at the size it grew to while the transaction ran:

In this example the Transaction Log is at the same size of 285MB, but 24.9% is now unused compared with the 2% unused before the ROLLBACK.  You can also see that the Data File space usage has not changed significantly, as the transaction had not been committed and therefore had no impact on data file usage

The Disk Usage Database Report looks like this following a ROLLBACK:

The Transaction Log is the same size, but 24.9% is now unused compared with the 2% unused before the ROLLBACK. The Data File space usage has not changed significantly as the transaction had not been committed and therefore had no impact of data file usage.

Shrinking a Data or Log File

Now you know which files are oversized,  they can be manually shrunk to a desired size in either management Studio or via the DBCC utility.

Use the statement below for Management Studio:

Here is the equivalent DBCC statement:

After the Shrink File operation on the Log File the Disk Usage Report looks as follows:

The Transaction Log File is now only 235.56MB in size with 9.1% free space.

I hope you have found this article useful. If you have any questions relatingt to this article do contact us at info@ptr.co,.uk. And if you would like to learn more about monitoring and managing SQL Server why not take a look at our SQL Server Training Courses.