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.
In MSSQL you can change the recovery model at anytime by right clicking your database, select properties and then go to Options.
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))
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
,te.name AS EventName
,DB_NAME(ftg.databaseid) AS DatabaseName
,(ftg.IntegerData*8)/1024.0 AS GrowthMB
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