0

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!

Improve this question

1 Answer 1

Reset to default
1

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.

Improve this answer
5
  • 1
    Looks like you're answering a different question.
    – spaghettidba
    Commented Jun 14, 2013 at 8:16
  • 1
    No, this answer was for this question. How would you interpret the regarding 'automatic transaction log truncation'?
    – souplex
    Commented Jun 14, 2013 at 8:24
  • +1, yes. FIle getting smaller is not truncation but shrinking - with a tons of problems. Truncation is merely a marker "reuse the virtual log file X in the physical file".
    – TomTom
    Commented Jun 14, 2013 at 9:36
  • 1
    In fairness, transaction log shrinking isn't as problematic as data file shrinking, but if you're doing it on a regular basis, then you're probably doing something wrong.
    – db2
    Commented Jun 14, 2013 at 12:29
  • Thanks I mean in simple recovery mode - automatic truncation occurs when a checkpoint is reached or the log gets over 70 % in size - is there any way to notify the user that the automatic truncation is happening ?
    – user25055
    Commented Jun 18, 2013 at 5:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.