2

I had a database that was working in FULL recovery mode but never had transaction log backups done.

The consequence of that was the fact that the log file kept growing and now its 10 GB in size.

I configured scheduled backups for this database (full + differential + log) and now log % space used is around 1%.

I want to shrink the 10GB file to a sensible size (maybe 200MB with autogrow 5 MB?).

How can I do it and what are the consequences of shrinking the file?

  • 5
    IMHO, 5MB autogrowth is not sensible. You should monitor your log growth using default trace and find a good value for autogrowth. (I will post the script later). – Kin Shah Feb 9 '15 at 13:20
  • Great, Im looking forward to seeing the script. I have a question though. Why shouldn't I use autogrow if a log file is really being recreated after a differential backup? – BuahahaXD Feb 9 '15 at 14:15
  • @ŁukaszKastelik I don't think kin ment to say that autogrow is not sensible, but that 5MB is too small an increment. As for the script, there is a nice version here that I've used myself to monitor database growth. – Reaces Feb 9 '15 at 14:31
  • @ŁukaszKastelik The whole log file is not being re-created. Where did you read that? – Aaron Bertrand Feb 9 '15 at 16:56
  • @AaronBertrand Maybe I don't understand it right but this is what I think it is like: when the DB is backed up regularly, the log file contains transaction history since the last full or differential backup. Am I right or wrong? – BuahahaXD Feb 9 '15 at 18:15
3

I want to shrink the 10GB file to a sensible size (maybe 200MB with autogrow 5 MB?).

IMHO, 5MB autogrowth is not sensible. You should monitor your log growth using default trace and find a good value for autogrowth.

Below script will help you monitor your Log (and Data file) autogrowths. Trend it over a week (or whatever suits your need). This will help you get a "near to best number" for your autogrowth setting.

IF OBJECT_ID('tempdb..#autogrowthTotal') IS NOT NULL
    DROP TABLE #autogrowthTotal;
IF OBJECT_ID('tempdb..#autogrowthTotal_Final') IS NOT NULL
    DROP TABLE #autogrowthTotal_Final;

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] as LogicalFileName
,(ftg.IntegerData*8)/1024.0 AS GrowthMB
,(ftg.duration/1000)AS DurMS
,mf.physical_name AS PhysicalFileName
into #autogrowthTotal
FROM ::fn_trace_gettable(@filename, DEFAULT) AS ftg
INNER JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id 
join sys.master_files mf on (mf.database_id = ftg.databaseid) and (mf.name = ftg.[FileName]) 
WHERE (ftg.EventClass = 92  -- Data File Auto-grow
    OR ftg.EventClass = 93) -- Log File Auto-grow
ORDER BY ftg.StartTime

select count(1) as NoOfTimesEventFired
        , CONVERT(VARCHAR(10), StartTime, 120) as StartTime
        , EventName
        , DatabaseName
        , [LogicalFileName]
        , PhysicalFileName
        , SUM(GrowthMB) as TotalGrowthMB
        , SUM(DurMS) as TotalDurationMS
into #autogrowthTotal_Final
from #autogrowthTotal
group by CONVERT(VARCHAR(10), StartTime, 120),EventName,DatabaseName, [LogicalFileName], PhysicalFileName
having count(1) > 5 or SUM(DurMS)/1000 > 60 -- change this for finetuning....
order by CONVERT(VARCHAR(10), StartTime, 120)

-- Report back the results
select * from #autogrowthTotal_Final
order by TotalDurationMS desc
-- optional Filters
-- where DatabaseName = ''

Why shouldn't I use autogrow if a log file is really being recreated after a differential backup?

This is not true. When you take "Log Backups", SQL Server - for databases in proper FULL or BULK_LOGGED recovery models will truncate the transaction log i.e the portion of transaction log will be marked as "no longer needed" and will be overwritten.

Read up more on : Misconceptions around the log and log backups: how to convince yourself and 8 Steps to better Transaction Log throughput

I want to shrink the 10GB file to a sensible size (maybe 200MB with autogrow 5 MB?). How can I do it and what are the consequences of shrinking the file?

You can use DBCC SHRINKFILE(yourdb_log, 200);. Remember to use SHRINKFILE and NOT SHRINKDATABASE.

I would suggest you to be PROACTIVE (monitor your log autogrowths and give a sensible value for autogrowth) rather being REACTIVE (frequently shrink log file, because you think that you will need disk space (or for whatever reasons)).

You should read up answers from Mike Walsh and Aaron Bertrand for Why Does the Transaction Log Keep Growing or Run Out of Space? to understand the consequences of shrinking log file.

  • Do you know a way to monitor your log to see how full it is over time? Get a high water mark before they do the shrink at all? – Kenneth Fisher Feb 9 '15 at 17:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.