Sql-server – SQL Server automatic transaction log truncation

rdbmssql serversql-server-2008transaction-log

Have looked through msdn but cant find answers to these:

Any help much appreciated:

In simple recovery mode:

  1. Is there any TSQL or alternative functionality that will provide a notification either written to the event logs or a message or an alert , stating that the Microsoft automatic transaction log truncation is happening i.e when a checkpoint is reached sql server truncates.
    (According to this: http://technet.microsoft.com/en-us/library/ms189085(v=sql.105).aspx)

  2. Is there anyway to find out how long it takes for automatic truncation to happen /how long it takes for the transaction log to be truncated?

  3. Does the SQL Server condition performance alerts happen in real time or in intervals or say 5 mins?

Thanks all!

Best Answer

No physical truncation actually occurs, the size of the file would not get smaller.

Parts of the logfile (virtual logfile/vlf) are merely flagged as 'the content is allowed to be overwritten'. If such an event exists, and I know of none, there would be no practical duration.

If you are referring to 'auto-shrink', I would highly recommend you read this post 'Auto-shrink – turn it OFF!' by Paul Randal.

Related Question