Monitoring the SQL log file


After deploying a project on the client's machine, the sql db log file has grown to up to 450G, although the db size is less than 100MB, The logging mode is set to Simple mode, and the transactions are send from a windows service that send insertion and updating transaction every 30 seconds. my question is, how to know the reason of db log file growth? I would like to know how to monitor the log file to know what is the exact transaction that causes the problem. should i debug the front end ? or there is away that expose the transactions that cause db log file growth. Thank you.

Note that a simple recovery model does not allow for log backups since it keeps the least amount of information and relies on CHECKPOINT, so if this is a critical database, consider protecting the client by use of a FULL RECOVERY plan. Yes, you have to use more space, but disk space is cheap and you can have greater control over the point in time recovery and managing your log files. Trying to be concise:

  • A) Your database in Simple Mode will only truncate transactions in your transaction log as when a CHECKPOINT is created.

  • B) Unfortunately, large/lots of uncommitted transactions, including BACKUP, creation of SNAPSHOT, and LOG SCANs, among other things will stop your database from creating those checkpoints and your database will be left unprotected until those transactions are completed.

  • Your current system relies on having the right edition of your .bak file, which depending on the size may mean hours of potential loss.

In other words, it is that ridiculous size because your database is not able to create a CHECKPOINT to truncate these transactions often enough....

a little note on log files

Foremost, Log files are not automatically truncated every time a transaction is committed (otherwise, you would only have the last committed transaction to go back to). Taking frequent log backups will ensure pertinent changes are kept (point in time) and SHRINKFILE will squeeze the log file to the smallest size available/size specified.

Use DBCC SQLPERF(logspace) to see how much of your log file is in use and how large it is. Once you perform a full backup, the log file will be truncated to the remaining uncommitted/active transactions. (Do not confuse this with shrinking the size)

Some suggestions on researching your transactions:

  • You can use the system tables to see the most expensive cache, frequent, and active plans.
  • You can search the log file using an undocumented extended stored procedure, fn_dblog.
  • Pinal has great info on this topic that you can read at this webpage and link: Beginning Reading Transaction Log