Take the 2-minute tour ×
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. It's 100% free, no registration required.

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.

add comment

1 Answer 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.

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
    
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
add comment

Your Answer

 
discard

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