Menu

Want to work with us? We're on the lookout for a talented Digital Project Manager.

We're hiring

How to stop MSSQL database log files from growing too large

Web Bureau


29 July 2013 by Web Bureau

We recently had to help a client with their mssql database where the log files had grown very large. This was starting to impact their server space and site performance so to resolve the issue we had a look at their backup recovery model and database growth/shrink settings. If you have problems with log file sizes, these are two areas you might want to look at.

MSSQL Recovery Models

  1.  Full
    This was the model in use on our client's box and is where you can recover the database to a given point in time. This model requires log backups which allow for changes since the last backup to be recoverable. Importantly though, with Full recovery mode your log file will continue to grow until you take a backup of it.

  2. Simple
    With this option, no log backups are taken - so changes since the last database backup cannot be recovered. If you don’t need point-in-time recovery this is a good option and will stop your log file growing out of control.

  3. Bulk Logged
    This is an extension of the Full model and provides support for high-performance bulk copy operations. You can read more about this and the other two models here http://msdn.microsoft.com/en-us/library/ms189275.aspx

In MSSQL you can change the recovery model at anytime by right clicking your database, select properties and then go to Options.

 

 Auto Shrink and Auto Growth Events

 An auto growth event is where the database expands or grows when it needs new space. Within your MSSQL, the auto growth setting are set individually on both the database and log files and default to growth of 1MB on the database and 10% growth on the log files.

It’s worth looking at these settings and trying to base them to the needs of your database.

Remember that during the auto growth events, database performance is impacted while the auto growth event is performed and we get slower response times.

Also bear in mind that for each growth, MSSQL will go off to find storage space on the drive – which quite possibly isn’t right next to the existing space the database is using. This leads to fragmentation and in turn impacts performance as SQL Server needs to move around the disk more in order to retrieve data.

Auto growth settings therefore are best updated once you know often your database need to grow. The script below – taken from Greg Larsen’s article is useful to identify these events.

 

DECLARE @filename NVARCHAR(1000);
DECLARE @bc INT;
DECLARE @ec INT;
DECLARE @bfn VARCHAR(1000);
DECLARE @efn VARCHAR(10);

-- Get the name of the current default trace
SELECT @filename = CAST(value AS NVARCHAR(1000))
FROM ::fn_trace_getinfo(DEFAULT)
WHERE traceid = 1 AND property = 2;

-- rip apart file name into pieces
SET @filename = REVERSE(@filename);
SET @bc = CHARINDEX('.',@filename);
SET @ec = CHARINDEX('_',@filename)+1;
SET @efn = REVERSE(SUBSTRING(@filename,1,@bc));
SET @bfn = REVERSE(SUBSTRING(@filename,@ec,LEN(@filename)));

-- set filename without rollover number
SET @filename = @bfn + @efn

-- process all trace files
SELECT
ftg.StartTime
,te.name AS EventName
,DB_NAME(ftg.databaseid) AS DatabaseName
,ftg.Filename
,(ftg.IntegerData*8)/1024.0 AS GrowthMB
,(ftg.duration/1000)AS DurMS
FROM ::fn_trace_gettable(@filename, DEFAULT) AS ftg
INNER JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id
WHERE (ftg.EventClass = 92 -- Date File Auto-grow
OR ftg.EventClass = 93) -- Log File Auto-grow
ORDER BY ftg.StartTime

 

With this information, you can update your auto growth settings so they best suit your own database. Right click your database, select properties and view the Files section from the left.

Remember these settings are per file. Click the button to the right (highlighted above) to access the setting window.

 

Here we can choose whether file growth is by blocks (MB) or based on a percentage of the current file size. The percentage option is the default but pay attention to this option if your database is already very large as this can lead to over allocation of disk space. Finally you can set a maximum file size which is useful to prevent any database using all your disk space. On the other hand using unrestricted growth will minimise any application failures that could be caused by setting a restricted growth.

For more information, in particular on the performance implications of this – see Microsoft’s article Considerations for the "autogrow" and "autoshrink" settings in SQL Server or read Greg Larsen’s post on SQL Server Database Growth and Autogrowth Settings

Grow your businessStart a project with us today.