Sql-server – Viewing how much transaction log is used in Simple Recovery Model

sql-server-2012transaction-log

We have a DW database in Simple Recovery Model. Every night, we load in data from different sources and run a few procs. I'd like to see how much the transaction log is actually used during this nightly load that takes hours to run.

Currently, the log has ballooned to over 50GB due to a one-time cleanup query we ran recently. The used space on the log is near 0% due to Simple Recovery Model. We'd like to shrink the log file down to a more reasonable size. I suspect that the nightly load only uses 5GB but is there a way to actually find this out? I thought about polling the database every minute or five and capturing the space used and unused in a logging table for a couple nights. Is there a better way?

Best Answer

Seems like an appropriate way to do it.

Create a logging table:

CREATE TABLE dbo.LogSpace
(
  dt DATETIME NOT NULL DEFAULT SYSDATETIME(),
  dbname SYSNAME, 
  log_size_mb DECIMAL(22,7),
  space_used_percent DECIMAL(8,5),
  [status] BIT
);

Do this before and after your load:

INSERT dbo.LogSpace(dbname, log_size_mb, space_used_percent, [status])
EXEC sp_executesql N'DBCC SQLPERF(LogSpace) WITH NO_INFOMSGS;';

Optionally, remove any rows not related to this specific database:

DELETE dbo.LogSpace WHERE dbname <> N'yourdb';

Then you can compare the before and after size/space used for any given date, or for all dates you have collected.

;WITH x AS
(
  SELECT dbname, dt,
  duration = DATEDIFF(SECOND, LAG(dt) OVER 
    (PARTITION BY dbname ORDER BY dt), dt),
  [current] = space_used_percent, 
  previous = LAG(space_used_percent) OVER
    (PARTITION BY dbname ORDER BY dt),
  rn = ROW_NUMBER() OVER 
    (PARTITION BY dbname ORDER BY dt),
  log_size_mb
 FROM dbo.LogSpace
)
SELECT * FROM x WHERE rn % 2 = 0;

Keep in mind that checkpoints that happen during your process can actually make log space be re-used; I remember doing some performance testing recently and after certain operations the space_used_percent actually went down. So you may want to take the max observed over a few days (and maybe run it more often - in which case you want a slightly different query, that doesn't assume pairs of consecutive rows are related to any specific activity), rather than just relying on how it ended up after the load.

Also make sure that the autogrow settings for the log file are reasonable - you don't want 1MB or 10%, but you don't want 10GB, either. Since an autogrow event for a log file will (a) make all transactions wait and (b) does not benefit from instant file initialization, you want a good balance between how many times the log file has to grow during an abnormal operation like your data cleanup, and how long it takes any individual growth event to happen. If that event was recent enough, you can review these events in the default trace to see how long it took then.

Related Question