none
Event for Transaction Log Truncation

    Întrebare

  • Hello,

    Is there an event (via profiler or via the new event mechanism in SQL 2008) through which I can find out when the Transaction log for a database was truncated?

    On similar lines, is there an DMV or SMO API which gives me the date and time of the last log truncation for a database?

    30 decembrie 2010 07:15

Răspunsuri

  • In SQL Server 2008 Extended Events can be used to track this.  First you will need to create an Event Session in Extended Events for the sqlserver.databases_log_truncation Event.

    CREATE EVENT SESSION TrackLogTruncation
    ON SERVER
    ADD EVENT sqlserver.databases_log_truncation
    ADD TARGET package0.ring_buffer
    WITH (STARTUP_STATE=ON)
    GO
    ALTER EVENT SESSION TrackLogTruncation
    ON SERVER
    STATE=START
    GO
    
    

    The STARTUP_STATE=ON specifies that this Event Session should startup whenever the Database Engine starts making sure that you can track this even when you restart the instance or server.  To query the information stored in the target, you can use the following query:

     

    SELECT 
    	event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name,
     DATEADD(hh, 
      DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), 
      event_data.value('(event/@timestamp)[1]', 'datetime2')) AS [timestamp],
     COALESCE(event_data.value('(event/data[@name="database_id"]/value)[1]', 'int'), 
      event_data.value('(event/action[@name="database_id"]/value)[1]', 'int')) AS database_id,
    	event_data.value('(event/data[@name="count"]/value)[1]', 'bigint') AS [count]
    FROM 
    ( SELECT XEvent.query('.') AS event_data 
     FROM 
     ( -- Cast the target_data to XML 
      SELECT CAST(target_data AS XML) AS TargetData 
      FROM sys.dm_xe_session_targets st 
      JOIN sys.dm_xe_sessions s 
       ON s.address = st.event_session_address 
      WHERE name = 'TrackLogTruncation' 
       AND target_name = 'ring_buffer'
     ) AS Data 
     -- Split out the Event Nodes 
     CROSS APPLY TargetData.nodes ('RingBufferTarget/event') AS XEventData (XEvent) 
    ) AS tab (event_data)
    

    This will give you the log truncation information that you are after.  If you want to learn a lot more about the information that Extended Events can provide check out my blog series from last month on SQLBlog; An XEvent A Day: 31 days of Extended Events.


    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    2 ianuarie 2011 07:40

Toate mesajele

  • Have you tried looking at the Transaction Log Event class in SQL Server Profiler.

     


    John Sansom | SQL Server DBA Blog | SQL Server Consultants - Santech Solutions
    • Marcat ca răspuns de MSDN Student 30 decembrie 2010 07:33
    • Anulare marcare ca răspuns de MSDN Student 30 decembrie 2010 08:48
    30 decembrie 2010 07:27
  • I am using the profiler know and looking at this event.. the problem is that it is logging too much data and I am really not able to figure out which event is actually truncating the log.

    At this point of time I somehow want to filter this event so that it only shows me the log truncation.

     

    30 decembrie 2010 08:52
  • Good question.

    I would have thought that the information would be available within the DMV's sys.trace_events and sys.trace_subclass_values like so:

    select *
    from sys.trace_events te
    	LEFT JOIN sys.trace_subclass_values tsv ON
    		te.trace_event_id = tsv.trace_event_id
    where te.name = 'TransactionLog'

    Interestingly however, there appear to be no values for the TransactionLog Event Class in the sys.trace_subclass_values DMV.

     

     


    John Sansom | SQL Server DBA Blog | SQL Server Consultants - Santech Solutions
    30 decembrie 2010 14:09
  • Hello John,

     

    Thanks for your response but I am not able to understand the output of this query. What I see on my computer is

     

    trace_event_id    category_id    name                   trace_event_id    trace_column_id    subclass_name    subclass_value
    54                      12                 TransactionLog     NULL                   NULL                    NULL                   NULL

     

    But this doesn't tell me whether the log was truncted.... when was it truncted.

     

     

    30 decembrie 2010 14:39
  • Exactly, that's just it. The documentation would seem to suggest that these DMV's should be able to provide the information. The TraceEvent TransactionLog exists but there appears to be no associated event_subclass_value data.

    As an aside, why do you need to know when the transaction log was truncated? It should "for the most part" be when you last performed a Transaction Log backup.


    John Sansom | SQL Server DBA Blog | SQL Server Consultants - Santech Solutions
    30 decembrie 2010 15:52
  • Hello John,

     

    Thanks for your response but I am not able to understand the output of this query. What I see on my computer is

     

    trace_event_id    category_id    name                   trace_event_id    trace_column_id    subclass_name    subclass_value
    54                      12                 TransactionLog     NULL                   NULL                    NULL                   NULL

     

    But this doesn't tell me whether the log was truncted.... when was it truncted.

     

     


    The sys.trace_events catalog view contains all SQL Server events and the sys.trace_subclass_values catalog view contains a list of named column values. Before you can get values from them, you need to create a trace using sp_trace_create, using sp_trace_setevent to add event (TransactionLog) or columns to the trace and then start the trace.

    However, the TransactionLog event could not give us when the database transaction was truncated which tracks when transactions are written to the transaction log. SQL Server database log truncation occurs automatically, however I cannot find any catelog view or DMV can list all log truncation records. For more information, please refer to Transaction Log Truncation (http://msdn.microsoft.com/en-us/library/ms189085(v=SQL.100).aspx).

    Hope this helps.

    Thanks,
    Chunsong


    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    31 decembrie 2010 04:54
  • so... so ... you are telling me that there is NO way I can get a timestamp for when the T-log was truncated for a database?

    How could this be??

     

    31 decembrie 2010 06:32
  • Hi ChunSong Feng,

    Thanks for your feedback.

    So are you saying that a Trace must be created and started, before any of the associated(as defined by the Trace) Event Monitoring data will appear in the two DMV's mentioned previously?

    I created a ServerSide Trace in order to test this, made some data modifications to a database and performed a Transaction Log backup and still no entries appear in the sys.trace_subclass_values DMV for the relevant Trace Event. I can validate that TransactionLog Event class values are being recorded in the trace results file, so the relevant activity is certainly being generated.

    It would seem that there are no entries for the TransactionLog Event class in the DMV sys.trace_subclass_values irrespective of whether or not a trace is performed.

    My own goal here, is not to identify when log truncation occurs, as the original poster wishes. I am simply looking to identify the meaning/description for the relevant Transaction Log Event Subclass value that is available through Profiler. This metadata does not seem to be available via the conventional means.

    Any ideas how we can obtain this information?

    Thanks for your help!

     


    John Sansom | SQL Server DBA Blog | SQL Server Consultants - Santech Solutions
    31 decembrie 2010 09:11
  • In SQL Server 2008 Extended Events can be used to track this.  First you will need to create an Event Session in Extended Events for the sqlserver.databases_log_truncation Event.

    CREATE EVENT SESSION TrackLogTruncation
    ON SERVER
    ADD EVENT sqlserver.databases_log_truncation
    ADD TARGET package0.ring_buffer
    WITH (STARTUP_STATE=ON)
    GO
    ALTER EVENT SESSION TrackLogTruncation
    ON SERVER
    STATE=START
    GO
    
    

    The STARTUP_STATE=ON specifies that this Event Session should startup whenever the Database Engine starts making sure that you can track this even when you restart the instance or server.  To query the information stored in the target, you can use the following query:

     

    SELECT 
    	event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name,
     DATEADD(hh, 
      DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), 
      event_data.value('(event/@timestamp)[1]', 'datetime2')) AS [timestamp],
     COALESCE(event_data.value('(event/data[@name="database_id"]/value)[1]', 'int'), 
      event_data.value('(event/action[@name="database_id"]/value)[1]', 'int')) AS database_id,
    	event_data.value('(event/data[@name="count"]/value)[1]', 'bigint') AS [count]
    FROM 
    ( SELECT XEvent.query('.') AS event_data 
     FROM 
     ( -- Cast the target_data to XML 
      SELECT CAST(target_data AS XML) AS TargetData 
      FROM sys.dm_xe_session_targets st 
      JOIN sys.dm_xe_sessions s 
       ON s.address = st.event_session_address 
      WHERE name = 'TrackLogTruncation' 
       AND target_name = 'ring_buffer'
     ) AS Data 
     -- Split out the Event Nodes 
     CROSS APPLY TargetData.nodes ('RingBufferTarget/event') AS XEventData (XEvent) 
    ) AS tab (event_data)
    

    This will give you the log truncation information that you are after.  If you want to learn a lot more about the information that Extended Events can provide check out my blog series from last month on SQLBlog; An XEvent A Day: 31 days of Extended Events.


    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    2 ianuarie 2011 07:40
  • Perfect, thanks Jonathan.

    Can you suggest alternatives for those still working with SQL 2005, that is other than to upgrade of course :-) ?


    John Sansom | SQL Server DBA Blog | SQL Server Consultants - Santech Solutions
    2 ianuarie 2011 09:18
  • Perfect, thanks Jonathan.

    Can you suggest alternatives for those still working with SQL 2005, that is other than to upgrade of course :-) ?


    John Sansom | SQL Server DBA Blog | SQL Server Consultants - Santech Solutions


    Why exactly do you need to know when the last time the log truncated was?  If the goal to know when someone did something like truncate the log rather than back it up, there are trace flags that can be used to prevent that.  If there is another goal, I'd be interested to know what it is, because its not overly useful information in my opinion.  Its cool be able to see with XEvents and know more about how things work, but I am more concerned with why a log isn't truncating which is tracked in sys.databases in the log_reuse_wait_desc column.

     


    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    3 ianuarie 2011 00:57
  • This will give you the log truncation information that you are after.  If you want to learn a lot more about the information that Extended Events can provide check out my blog series from last month on SQLBlog; An XEvent A Day: 31 days of Extended Events.

    Wow, great stuff!

    The above link may be incorrect, it actually can be found at http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/12/01/a-xevent-a-day-31-days-of-extended-events.aspx.

    Reagrds,
    Chunsong


    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    3 ianuarie 2011 02:56
  • My own goal here, is not to identify when log truncation occurs, as the original poster wishes. I am simply looking to identify the meaning/description for the relevant Transaction Log Event Subclass value that is available through Profiler. This metadata does not seem to be available via the conventional means.

    Any ideas how we can obtain this information?

    Thanks for your help!


    John Sansom | SQL Server DBA Blog | SQL Server Consultants - Santech Solutions


    John,

    Interesting problem, and something I felt deserved more than just a forums response.  See my blog post for a response to your question.  The simple response is yes, you can map this information out if you are willing to do the leg work in SQL Server Denali CTP1, using Extended Events, and I would expect that a majority of this information would be backwards portable to SQL Server 2005 but undocumented items are as always subject to change without notification, so usage is up to you to determine.

    http://sqlblog.com/blogs/jonathan_kehayias/archive/2011/01/03/using-extended-events-in-sql-server-denali-ctp1-to-map-out-the-transactionlog-sql-trace-event-eventsubclass-values.aspx


    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    3 ianuarie 2011 06:45
  • This will give you the log truncation information that you are after.  If you want to learn a lot more about the information that Extended Events can provide check out my blog series from last month on SQLBlog; An XEvent A Day: 31 days of Extended Events.

    Wow, great stuff!

    The above link may be incorrect, it actually can be found at http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/12/01/a-xevent-a-day-31-days-of-extended-events.aspx.

    Reagrds,
    Chunsong


    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.


    Thanks Chunsong,

    I've corrected the link in my original post to point to the correct location.  For some reason when I copy link locations from Community Server, it truncates the domain information from the appropriate link.  I usually fix this but sometimes I forget. Always good to get feedback on the content I produce.

     


    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    3 ianuarie 2011 06:50
  • Thanks a ton!!

     

    Two question

     

    1. Any way to achieve the same thing in powershell.

    2. Should I do this for all databases 200+ databases that I have?

    3 ianuarie 2011 10:50
  • Thanks a ton!!

     

    Two question

     

    1. Any way to achieve the same thing in powershell.

    2. Should I do this for all databases 200+ databases that I have?


    Same question I asked John.  Why does it matter, why do you care?  I can't see any purpose in doing this.  Like I said, I'd rather know why its not truncating, if there is a growth problem, a piece of information that is infinitely more useful and actionable.
    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    3 ianuarie 2011 12:59
  • I created a database called TestDB and in it I created a table called testtable. I inserted a few records in the table and then did a full backup of the database. (testdb has recovery option of FULL)

    Now the query returns me 1106 rows but the latest row says

     

    databases_log_truncation                           2010-12-27 15:33:03.79 4           5607

     

    The systemdate on the database is Jan 4th... so why is latest row is showing 27th? I did do a full backup of the DB so it should have truncated the log when I did a full backup?

    4 ianuarie 2011 07:29
  • I created a database called TestDB and in it I created a table called testtable. I inserted a few records in the table and then did a full backup of the database. (testdb has recovery option of FULL)

    Now the query returns me 1106 rows but the latest row says

     

    databases_log_truncation                           2010-12-27 15:33:03.79 4           5607

     

    The systemdate on the database is Jan 4th... so why is latest row is showing 27th? I did do a full backup of the DB so it should have truncated the log when I did a full backup?

    You are assuming something wrong here - Database backup be it a full /differential will not truncate your logfile . The log is only truncated when you run BACKUP LOG WITH TRUNCATE_ONLY , DBCC SHRINKFILE with truncateonly or switch the database’s recovery model from FULL to SIMPLE.


    Thanks, Leks
    4 ianuarie 2011 07:59
  • > Same question I asked John.  Why does it matter, why do you care?  I can't see any purpose in doing this.  Like I said, I'd rather know why its not truncating, if there > is a growth problem, a piece of information that is infinitely more useful and actionable.

     

    The reason why I want to know this is because I am using DPM 2010 to backup my SharePoint environment. We have 100 content databases. Now when I have a database in full recovery mode, and I natively backup the database and log, the log truncation occurs.

    But I am not sure if after setting up DPM the logs are getting truncated at all.

    That is why I want to generate a report which lists down the databases, and the timestamp at which the log truncation occurred.

     

    4 ianuarie 2011 08:25
  • Also I have a database called  intg_user_profile_svc_app_profile which has a database ID of 12

    In the windows event viewer I see the following event logged

    Log was backed up. Database: intg_user_profile_svc_app_profile, creation date(time): 2010/12/20(10:17:40), first LSN: 3513:14737:1, last LSN: 3513:14827:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'E:\DPM_SQL_PROTECT\ETC784994\intg_user_profile_svc_app_profile_log.LDF\Backup\Current.log'}). This is an informational message only. No user action is required.

     

    But when I run the query above, it doesn't list database id 12.

     

    what am i missing?

    4 ianuarie 2011 09:39
  • Also I have a database called  intg_user_profile_svc_app_profile which has a database ID of 12

    In the windows event viewer I see the following event logged

    Log was backed up. Database: intg_user_profile_svc_app_profile, creation date(time): 2010/12/20(10:17:40), first LSN: 3513:14737:1, last LSN: 3513:14827:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'E:\DPM_SQL_PROTECT\ETC784994\intg_user_profile_svc_app_profile_log.LDF\Backup\Current.log'}). This is an informational message only. No user action is required.

     

    But when I run the query above, it doesn't list database id 12.

     

    what am i missing?


    The Event Session works just like a Trace, it only captures Events from the point in time it started, so a backup from 12/20/2010 that truncated the log will not be shown in the Event Session.  That said, how it shows you information from 12/27/2010 when I posted the Event Session to this thread on 1/2/2011 tells me that there is something not right with your server and how it is configured for time.  Is it a VM or a physical server?  Are your servers setup to sync time to a central time server, or if it is a VM is it setup to sync to the host, if so, was it moved the vMotion/Live Migration?  The code I posted already performs the UTC offset calculation, so if you are getting events with a timestamp of 12/27/2010, something is not right with your servers clock settings, because that would be impossible unless you knew to use Extended Events before I responded to this post.
    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    4 ianuarie 2011 10:12
  • Perfect, thanks Jonathan.

    Can you suggest alternatives for those still working with SQL 2005, that is other than to upgrade of course :-) ?


    John Sansom | SQL Server DBA Blog | SQL Server Consultants - Santech Solutions


    Actually, yeah I can suggest an alternative that works for SQL 2000 as well.  Monitor the Performance Counters for SQL Server:Databases\Log Truncations\* and you can see it at the database level, but you'd have to look at perfmon data to figure it out, or in 2005, set a WMI Agent Performance Alert that would fire, but I wouldn't do that.  You could also track percent log used and when it drops, you know it truncated, that could be done easily in TSQL by querying the dm_os_performance_counters DMV, a bit less precise, but like I've said before, I don't care when it truncates, I only care when its not truncating, and the log starts to fill up, that's what matters to me.

     


    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    4 ianuarie 2011 10:20
  • on my SQL Server if I say

    select getdate()

    it says

    2011-01-04 07:18:49.553

     

    Now I say

     

    drop event session [TrackLogTruncation] on server
    go

    CREATE EVENT SESSION TrackLogTruncation ON SERVER
    ADD EVENT sqlserver.databases_log_truncation
    ADD TARGET package0.ring_buffer
    WITH (startup_state=ON)
    GO

    alter event session tracklogtruncation on server
    state=start
    go

     

    Now I type the simplified version of your query

    SELECT CAST(target_data AS XML) AS TargetData
      FROM sys.dm_xe_session_targets st
      JOIN sys.dm_xe_sessions s
       ON s.address = st.event_session_address
      WHERE name = 'TrackLogTruncation'
       AND target_name = 'ring_buffer'

     

    The XML I get has all dates in 2010!!!!

    <RingBufferTarget eventsPerSec="0" processingTime="0" totalEventsProcessed="3" eventCount="3" droppedCount="0" memoryUsed="150">
      <event name="databases_log_truncation" package="sqlserver" id="50" version="1" timestamp="2010-12-28T01:23:40.456Z">
        <data name="count">
          <type name="uint64" package="package0" />
          <value>5842</value>
          <text />
        </data>
        <data name="database_id">
          <type name="uint16" package="package0" />
          <value>4</value>
          <text />
        </data>
      </event>
      <event name="databases_log_truncation" package="sqlserver" id="50" version="1" timestamp="2010-12-28T01:24:48.726Z">
        <data name="count">
          <type name="uint64" package="package0" />
          <value>5843</value>
          <text />
        </data>
        <data name="database_id">
          <type name="uint16" package="package0" />
          <value>4</value>
          <text />
        </data>
      </event>
      <event name="databases_log_truncation" package="sqlserver" id="50" version="1" timestamp="2010-12-28T01:26:15.731Z">
        <data name="count">
          <type name="uint64" package="package0" />
          <value>5844</value>
          <text />
        </data>
        <data name="database_id">
          <type name="uint16" package="package0" />
          <value>4</value>
          <text />
        </data>
      </event>
    </RingBufferTarget>

     

     

    4 ianuarie 2011 12:21
  • Also the Machine is a physical machine it is not a VM

    I am using Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7600: )

    Is it time to contact Microsoft support?

    I have dropped the event session many times but upon re-creating it, the values are always in 2010

    4 ianuarie 2011 12:27
  • You are assuming something wrong here - Database backup be it a full /differential will not truncate your logfile . The log is only truncated when you run BACKUP LOG WITH TRUNCATE_ONLY , DBCC SHRINKFILE with truncateonly or switch the database’s recovery model from FULL to SIMPLE.


    Thanks, Leks

    This is not correct.  The transaction log truncates when a regular log backup occurs in FULL Recovery, or at Checkpoint in Simple Recovery.  You do not have to change the recovery model, shrink the log, or worst of all do a BACKUP LOG WITH TRUNCATE ONLY for the log to truncate/
    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    4 ianuarie 2011 13:12
  • What does GETUTCDATE() show?  Do you have power management turned off in the BIOS and Windows set to High Performance for the power scheme?  I am grasping at straws here, but I've never seen what you are describing before with Extended Events.


    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    4 ianuarie 2011 13:19
  • select getutcdate()

    2011-01-04 13:24:07.420

     

    Also I note that the XML which is getting created is having enteries for database_id 2 and 4 which is temp and msdb.

     

    The XEvent is not tracking any other database. I am sure I am writing the code incorrectly

     

    drop event session [TrackLogTruncation] on server
    go

    CREATE EVENT SESSION [TrackLogTruncation] ON SERVER
    ADD EVENT sqlserver.databases_log_truncation
    ADD TARGET package0.ring_buffer
    WITH (startup_state=OFF)
    GO

    alter event session [TrackLogTruncation] on server
    state=start
    go

    SELECT CAST(target_data AS XML) AS TargetData
      FROM sys.dm_xe_session_targets st
      JOIN sys.dm_xe_sessions s
      ON s.address = st.event_session_address
      WHERE name = 'TrackLogTruncation'
      AND target_name = 'ring_buffer'
      
    Initially the XML is like this

    <RingBufferTarget eventsPerSec="0" processingTime="0" totalEventsProcessed="0" eventCount="0" droppedCount="0" memoryUsed="0" />

     

    After waiting for few minutes it becomes

    <RingBufferTarget eventsPerSec="0" processingTime="0" totalEventsProcessed="2" eventCount="2" droppedCount="0" memoryUsed="100">
      <event name="databases_log_truncation" package="sqlserver" id="50" version="1" timestamp="2010-12-28T02:39:36.001Z">
        <data name="count">
          <type name="uint64" package="package0" />
          <value>5903</value>
          <text />
        </data>
        <data name="database_id">
          <type name="uint16" package="package0" />
          <value>4</value>
          <text />
        </data>
      </event>
      <event name="databases_log_truncation" package="sqlserver" id="50" version="1" timestamp="2010-12-28T02:40:40.613Z">
        <data name="count">
          <type name="uint64" package="package0" />
          <value>5904</value>
          <text />
        </data>
        <data name="database_id">
          <type name="uint16" package="package0" />
          <value>4</value>
          <text />
        </data>
      </event>
    </RingBufferTarget>

    4 ianuarie 2011 13:43
  • Don't know bios settings as I work on RDP. but the power plan is Balanced (recommended).
    4 ianuarie 2011 13:44
  • Don't know bios settings as I work on RDP. but the power plan is Balanced (recommended).


    Read This:

    http://sqlserverperformance.wordpress.com/2010/09/28/windows-power-plans-and-cpu-performance/

    Just because Windows says (Recommended) doesn't mean that it is really the best thing to follow.  I don't even run my laptops on Balanced unless I am on a airplane and accept longer battery life for decreased performance.

    Before I would contact product support, I would schedule a maintenance window where you can apply Windows Server patches and the latest Cumulative Update to SQL Server 2008 R2 (http://support.microsoft.com/kb/2438347/).  These are going to be recommended steps in troubleshooting it.  I found another post, I was wrong apparently I have seen this before, where applying the Windows Updates to a server resolved the timestamp being inaccurate:

    http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/f166d45d-7e45-4188-8553-ce23976bede0


    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    4 ianuarie 2011 14:48
  • I installed all the patches and asked my admin to look into the bios. After a reboot, the dates are now appearing correctly.

    However I have a database called TestDB in which I have a table. Even when I insert few rows into the DB and then take a FULL and TRANSACTIONAL backup of the database, I still don't see it in your query.

    I have another DB which has simple recovery model and this always appear in query results.

    Can you please create a database with FULL recovery model and tell me the steps which you do, so that the database_id for that database appears in the query results?

    6 ianuarie 2011 13:06
  • I have setup an event for capturing log truncation event.

     

    drop event session [TrackLogTruncation] on server
    go

    CREATE EVENT SESSION [TrackLogTruncation] ON SERVER
    ADD EVENT sqlserver.databases_log_truncation
    ADD TARGET package0.ring_buffer
    WITH (startup_state=OFF)
    GO

    alter event session [TrackLogTruncation] on server
    state=start
    go

    Now I create two databases TestDB3 and TestDB4. TestDB3 is in the Full Recovery Model. TestDB4 is the Simple Recovery Model.

    Now I take a Full and Transactional Backup of both the datases.

    When I execute the following query I only see a record for TestDB4 which is in Simple Mode.... but no row for the Full. Can you please tell me why is this happening?

    1. Either the log is not getting truncated for TestDB3. Why?

    2. Or the Xevent is only catching the event for simple recovery model.

    Can you please shed some light on the subject?

    If possible can you please create a test database with full recovery model and see if you are able to see log truncation event?

    7 ianuarie 2011 06:01
  • Hello MSDN student,

    When a database is in Full recovery model, a transaction-log backup never truncate the log. It only removes the inactive portion of the transaction-log, which make space available for the next active transactions. for this reason, even after a transaction-log backup, the size of the transaction-log file will not reduce; however, its growth is reduced.

    check this post , for the details. Hope, this may help.


    SKG: Please Marked as Answered, if it resolves your issue.
    7 ianuarie 2011 07:59
  • Hello MSDN student,

     

    As said by Ganguly TTransaction Log never truncates the log.it just removes the inactive and commited transactions...To check your event try

    Backup log DBNAME with truncate_only.... which truncates your log(however this truncate_only is not applicable for 2008).

     

    The above command may return reuslts to you event

     

    All the very best

     

    Thanks,


    Thanks, Aditya Badramraju, _____________________________________________________ Please mark answered if your question is completely answered; vote it as helpful to help others find a solution quicker. This posting is provided "AS IS"; with no warranties, and confers no rights. _______________________________________________________
    7 ianuarie 2011 09:13
  • To add to Aditya, Truncate_Only as mentioned above, will remove the transaction-log (all committed inactive transactions); however, it breaks the Log-Chain (log backup chain). To utilize the transaction-log backup feature, you will need to initiate a full backup after doing a truncate_only. You should not do it anytime, in a production environment.
    SKG: Please Marked as Answered, if it resolves your issue.
    7 ianuarie 2011 09:29
  • I've merged your threads because the problem is the same and the continued issue should have stayed in the original thread.  I have an email out to the PM for Extended Events at Microsoft asking him to look at the code for this.  When I hear back from him, which generally takes a few days because he has to get a Dev to review the relevant portions of code, and the Dev may be on a team outside of his control.  It could be that this is a bug with the Extended Event and there is a code path not firing the Event that should.  I'll update this thread when I hear back from the PM.

    Regardless of whether or not the Event fires for log clearing, which is taught as truncating by Paul Randal and the MCM program (see http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/680ec1bc-0db5-4ebe-8709-ebe392029295 for comments from Paul), you can still see that it occurred by monitoring the performance counters for log usage that I mentioned previously on this thread.  If all you want to do is validate a DPM 2010 log backup clears the inactive portion of the log, look at the performance counters for log usage, and if it is clearing the inactive portion the usage will drop and free % increase when the backup completes.


    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    9 ianuarie 2011 03:20
  • It appears that the Event not firing is a bug and the PM requested that it be logged on Connect. 

    https://connect.microsoft.com/SQLServer/feedback/details/635378/sqlserver-databases-log-truncation-extended-event-not-firing-in-full-recovery

    You can vote on it there, and track whether its fixed or not on the connect item once they look into it further.  I don't know if they will fix this in 2008 or 2008R2, you'll have to wait and see what the response is on the Connect item.


    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    10 ianuarie 2011 17:38
  • Thanks a lot Jonathan!! I really appreciate your help.
    11 ianuarie 2011 06:01