How To Improve Your SQL Server Speed

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.

DECLARE @tracepath nvarchar(260)

--Pick up the path of the background profiler trace for the instance
SELECT 
 @tracepath = path 
FROM sys.traces 
WHERE is_default = 1

--Query the background trace files
SELECT 
 DBName    = g.DatabaseName
, DBFileName   = mf.physical_name
, FileType   = CASE mf.type WHEN 0 THEN 'Row' WHEN 1 THEN 'Log' WHEN 2 THEN 'FILESTREAM' WHEN 4 THEN 'Full-text' END
, EventName   = te.name
, EventGrowthMB  = convert(decimal(19,2),g.IntegerData*8/1024.) -- Number of 8-kilobyte (KB) pages by which the file increased.
, EventTime   = g.StartTime
, EventDurationSec = convert(decimal(19,2),g.Duration/1000./1000.) -- Length of time necessary to extend the file.
, CurrentAutoGrowthSet= CASE
        WHEN mf.is_percent_growth = 1
        THEN CONVERT(char(2), mf.growth) + '%' 
        ELSE CONVERT(varchar(30), convert(decimal(19,2), mf.growth*8./1024.)) + 'MB'
       END
, CurrentFileSizeMB = convert(decimal(19,2),mf.size* 8./1024.)
, MaxFileSizeMB  = CASE WHEN mf.max_size = -1 THEN 'Unlimited' ELSE convert(varchar(30), convert(decimal(19,2),mf.max_size*8./1024.)) END
FROM fn_trace_gettable(@tracepath, default) g
cross apply sys.trace_events te 
inner join sys.master_files mf
on mf.database_id = g.DatabaseID
and g.FileName = mf.name
WHERE g.eventclass = te.trace_event_id
and  te.name in ('Data File Auto Grow','Log File Auto Grow')
order by DBName

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:

BEGIN TRAN

UPDATE Sales.SalesOrderDetail SET OrderQTy = OrderQty + 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:

SELECT * FROM sys.database_files

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:

ROLLBACK

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:

USE [AdventureWorks2012]
GO
DBCC SHRINKFILE (N'AdventureWorks2012_Log' , 0, TRUNCATEONLY)
GO

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.