Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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!

share|improve this question

migrated from stackoverflow.com Jun 14 '13 at 7:57

This question came from our site for professional and enthusiast programmers.

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.

share|improve this answer
1  
Looks like you're answering a different question. – spaghettidba Jun 14 '13 at 8:16
1  
No, this answer was for this question. How would you interpret the regarding 'automatic transaction log truncation'? – souplex Jun 14 '13 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 Jun 14 '13 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 Jun 14 '13 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 Jun 18 '13 at 5:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.