{"id":541,"date":"2010-12-23T19:33:00","date_gmt":"2010-12-23T19:33:00","guid":{"rendered":"\/blogs\/jonathan\/post\/An-XEvent-a-Day-(23-of-31)-e28093-How-it-Works-e28093-Multiple-Transaction-Log-Files.aspx"},"modified":"2017-04-13T12:18:28","modified_gmt":"2017-04-13T16:18:28","slug":"an-xevent-a-day-23-of-31-how-it-works-multiple-transaction-log-files","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-23-of-31-how-it-works-multiple-transaction-log-files\/","title":{"rendered":"An XEvent a Day (23 of 31) \u2013 How it Works \u2013 Multiple Transaction Log Files"},"content":{"rendered":"

While working on yesterday\u2019s blog post The Future \u2013 fn_dblog() No More? Tracking Transaction Log Activity in Denali<\/a> I did a quick Google search to find a specific blog post by Paul Randal to use it as a reference, and in the results returned another blog post titled, Investigating Multiple Transaction Log Files in SQL Server<\/a> caught my eye so I opened it in a new tab in IE and went about finishing the blog post.  It probably wouldn\u2019t have gotten my attention if it hadn\u2019t been on the SqlServerPedia<\/a> site.  When I was finished I went back and read through the post, and I found that some of the information presented in it was incorrect, so I attempted to post a comment, and not surprisingly the blog had moderation controls turned on, I have it turned on here if you aren\u2019t a SQLBlog member so I don\u2019t have a problem with that necessarily, and the comment didn\u2019t show up on the site.<\/p>\n

Interestingly enough, yesterday SQL Server Central had an editorial by Tim Mitchell titled Calling Out Bad Advice<\/a> that discussed the problem of bad information on the internet and how to go about calling people out for publishing bad advice.  Lets face it, people are human, at least I am, and mistakes happen from time to time, either through our own misunderstandings of our personal experiences and what we perceived from the information we had, or by shear accident in some cases.  This afternoon I got an email back from the blog post author and we traded a few emails about the post, and in the end the author made changes to the original post which have been syndicated to SQLServerPedia already, so to see the original you have do something like look at the Google Cached Copy<\/a>.  The author also posted a follow up blog post today on the subject.<\/p>\n

So why this blog post?  Well even with the corrections, some of the conclusions are still wrong. <\/p>\n

\"image\"<\/a> <\/p>\n

I am not trying to knock this guy for what he saw or perceived from the information he collected, but 2, 3 and 4 are still incorrect.  What\u2019s great is we can prove this by using Extended Events in SQL Server 2008 and that is what the real purpose behind this blog post is.  To set things up, we first need to create a database that roughly matches the available information shown in the pictures of the original blog post.  The database will have a single database file, that I am sizing initially at 128MB and will have a fixed autogrowth value of 64MB.  The database will have four log files that are initially sized at 1MB each, and the first log file will have a fixed autogrowth value of 32MB, with the last three transaction log files having fixed growth values of just 1MB.  Don\u2019t comment on this configuration, I understand completely that there is no reason to create multiple log files on the same disk array (half the purpose behind this post is to show that there is no benefit to having multiple log files like this, which is also the intended purpose behind the original blog post as well), and I wouldn\u2019t do this in production, but it works perfectly for the tests that we are about to run.  Once the database is created, we\u2019ll switch to that database, and dump out the transaction log VLF information using DBCC LOGINFO.<\/p>\n

\n
-- Create our Test database with\r\n--        1 data file sized at 128MB with 64MB autogrowth\r\n--        1 log file sized at 1MB with 32MB autogrowth\r\n--        3 log files sized at 1MB with 1MB autogrowth\r\n<\/span>CREATE DATABASE <\/span>[Test] ON  \r\nPRIMARY \r\n    <\/span>(    \r\n        <\/span>NAME = <\/span>N'Test'<\/span>, \r\n        <\/span>FILENAME <\/span>= <\/span>N'D:\\SQLData\\MSSQL10.MSSQLSERVER\\MSSQL\\DATA\\Test.mdf'<\/span>, \r\n        <\/span>SIZE = <\/span>131072KB, \r\n        <\/span>FILEGROWTH = <\/span>65536KB\r\n    )\r\n<\/span>LOG <\/span>ON \r\n    <\/span>(    \r\n        <\/span>NAME = <\/span>N'Test_log'<\/span>, \r\n        <\/span>FILENAME <\/span>= <\/span>N'L:\\SQLData\\MSSQL10.MSSQLSERVER\\MSSQL\\DATA\\Test_log.ldf'<\/span>, \r\n        <\/span>SIZE = <\/span>1024KB, \r\n        <\/span>MAXSIZE = <\/span>131072KB, \r\n        <\/span>FILEGROWTH = <\/span>32768KB\r\n    ), \r\n    (    \r\n        <\/span>NAME = <\/span>N'Test_log2'<\/span>,     \r\n        <\/span>FILENAME <\/span>= <\/span>N'L:\\SQLData\\MSSQL10.MSSQLSERVER\\MSSQL\\DATA\\Test_log2.ldf'<\/span>, \r\n        <\/span>SIZE = <\/span>1024KB, \r\n        <\/span>MAXSIZE = <\/span>131072KB, \r\n        <\/span>FILEGROWTH = <\/span>1024KB \r\n    ), \r\n    ( \r\n        <\/span>NAME = <\/span>N'Test_log3'<\/span>, \r\n        <\/span>FILENAME <\/span>= <\/span>N'L:\\SQLData\\MSSQL10.MSSQLSERVER\\MSSQL\\DATA\\Test_log3.ldf'<\/span>, \r\n        <\/span>SIZE = <\/span>1024KB, \r\n        <\/span>MAXSIZE = <\/span>131072KB, \r\n        <\/span>FILEGROWTH = <\/span>1024KB \r\n    ), \r\n    ( \r\n        <\/span>NAME = <\/span>N'Test_log4'<\/span>, \r\n        <\/span>FILENAME <\/span>= <\/span>N'L:\\SQLData\\MSSQL10.MSSQLSERVER\\MSSQL\\DATA\\Test_log4.ldf'<\/span>, \r\n        <\/span>SIZE = <\/span>1024KB, \r\n        <\/span>MAXSIZE = <\/span>131072KB, \r\n        <\/span>FILEGROWTH = <\/span>1024KB \r\n    )\r\n<\/span>GO\r\n<\/span>-- Switch to our Test database\r\n<\/span>USE <\/span>[Test]\r\nGO\r\n<\/span>-- Dump the VLF Usage information\r\n<\/span>DBCC <\/span>LOGINFO\r\nGO<\/span><\/pre>\n
  <\/a><\/p><\/blockquote>\n
\"image\"<\/a><\/p>\n
Each of the log files VLF\u2019s have been highlighted in a different color above to point out the separation of the four different files.  Note that the active VLF is in the first log file, FileId=2, as shown by the Status=2.  With our test database created, we can now set out to create the Extended Events Event Session that:<\/p>\n
    \n
      \n
    1. The transaction logs are written to sequentially starting with the first VLF in FileId=2 and then when the last VLF in FileId=2 is full, the log begins writing log records to the first VLF of FileId=3 and when the last VLF in FileId=3 is full, the log begins writing log records to the first VLF of FileId=4 and when the last VLF in FileId=4 is full, the log begins writing log records to the first VLF of FileId=5 and when the last VLF in FileId=5 is full, the log circles back to the first VLF of FileId=2 which will still be active because we are going to work within a single explicit transaction for the duration of the test.  Since the file is full it has to be grown, and because it has a growth factor of 32MB it grows by 32MB and begins writing log records to the first VLF of the newly allocated space. <\/li>\n
    2. The writes to the log files do not happen at the same time, they occur sequentially as the engine writes log records into each file, filling the VLF\u2019s and has to move to the next file, or circle back to the beginning of the log when it reaches the end of the last log file. <\/li>\n
    3. Whatever results were seen in the original thread by opening the log file with Apex tools were incorrect and misleading to the original poster, since log files were actually written to all of the files during the operation.  I have a couple of theories as to what could have happened that made the Apex tool show no log records that I will discuss later in this thread. <\/li>\n<\/ol>\n<\/ol>\n
      What Events would we want to capture to look at what is happening in our transaction log files when running the same workload from the original post?  Since we are going to be executing a number of statements, the sqlserver.sql_statement_starting and sqlserver.sql_statement_completed Events seem like a good starting point, and since we want to know what statement was executing, we\u2019ll add the sql_text Action to these Events.  Since we are dealing with the transaction log files, the sqlserver.databases_log_file_size_changed, sqlserver.databases_log_file_used_size_changed, sqlserver.databases_log_flush_wait, sqlserver.databases_log_flush, sqlserver.databases_log_growth, and sqlserver.databases_log_truncation Events should probably be included to so we can track what\u2019s going on with our log specifically, and to ensure that these Events only fire for our test database, we\u2019ll dynamically build in a Predicate on the sqlserver.database_id Predicate source using the output of DB_ID() inside the testing database.  <\/p>\n
      Since the log is a file, we also will want to collect the file operation related events such as sqlserver.flush_file_buffers, sqlserver.file_read, sqlserver.file_written, sqlserver.file_read_completed, and sqlserver.file_write_completed, and we\u2019ll dynamically set a database_id Predicate on these Events as well.  If you recall back to Friday of last week, I talked about a number of trace flags that provide further information about Backup, Restore and file operations in my blog post A Look at Backup Internals and How to Track Backup and Restore Throughput (Part 1)<\/a>.  One of those was Trace Flag 3004, which writes file zeroing information to the trace print output whenever a zeroing operation occurs.  I previously used this trace flag in my blog post Does the tempdb Log file get Zero Initialized at Startup?<\/a>  Since the log files grew in the original tests, we can turn this trace flag on to track the file growths, and use the sqlserver.trace_print Event to capture the file operation messages, and to keep this Event focused to our tests only, we\u2019ll dynamically set a Predicate for the current session_id using the sqlserver.session_id Predicate Source and the output of @@SPID.  Finally since this is all happening inside of an explicit transaction, we\u2019ll also capture the sqlserver.database_transaction_begin and sqlserver.database_transaction_end events for the current database_id.<\/p>\n
      We have quite a large list of Events associated with this Event Session, and to ensure that we can perform analysis over all of the Event data from our tests, we\u2019ll use the package0.asynchronous_file_target to hold our Event information.  We\u2019ll also increase our buffer memory from the default 4MB to 8MB and set the Event Session up to ALLOW_SINGLE_EVENT_LOSS, which does exactly what it sounds like it does, and to correlate cause and effect we\u2019ll also turn TRACK_CAUSALITY to ON for the session.<\/p>\n
      \n
      -- Create our Event Session dynamically\r\n<\/span>DECLARE <\/span>@sqlcmd nvarchar<\/span>(<\/span>2000) = <\/span>'\r\nCREATE EVENT SESSION TransactionLogUsage\r\nON SERVER\r\n--ADD EVENT sqlserver.sql_statement_starting\r\n--( ACTION(sqlserver.sql_text)\r\n--  WHERE (sqlserver.database_id = '<\/span>+ <\/span>cast<\/span>(<\/span>DB_ID<\/span>() <\/span>as varchar<\/span>(<\/span>3))+<\/span>')),\r\n--ADD EVENT sqlserver.sql_statement_completed\r\n--( ACTION(sqlserver.sql_text)\r\n--  WHERE (sqlserver.database_id = '<\/span>+ <\/span>cast<\/span>(<\/span>DB_ID<\/span>() <\/span>as varchar<\/span>(<\/span>3))+<\/span>')),\r\nADD EVENT sqlserver.databases_log_file_size_changed\r\n( WHERE (sqlserver.database_id = '<\/span>+ <\/span>cast<\/span>(<\/span>DB_ID<\/span>() <\/span>as varchar<\/span>(<\/span>3))+<\/span>')),\r\nADD EVENT sqlserver.databases_log_file_used_size_changed\r\n( WHERE (sqlserver.database_id = '<\/span>+ <\/span>cast<\/span>(<\/span>DB_ID<\/span>() <\/span>as varchar<\/span>(<\/span>3))+<\/span>')),\r\nADD EVENT sqlserver.databases_log_flush_wait\r\n( WHERE (sqlserver.database_id = '<\/span>+ <\/span>cast<\/span>(<\/span>DB_ID<\/span>() <\/span>as varchar<\/span>(<\/span>3))+<\/span>')),\r\nADD EVENT sqlserver.databases_log_flush\r\n( WHERE (sqlserver.database_id = '<\/span>+ <\/span>cast<\/span>(<\/span>DB_ID<\/span>() <\/span>as varchar<\/span>(<\/span>3))+<\/span>')),\r\nADD EVENT sqlserver.databases_log_growth\r\n( WHERE (sqlserver.database_id = '<\/span>+ <\/span>cast<\/span>(<\/span>DB_ID<\/span>() <\/span>as varchar<\/span>(<\/span>3))+<\/span>')),\r\nADD EVENT sqlserver.databases_log_truncation\r\n( WHERE (sqlserver.database_id = '<\/span>+ <\/span>cast<\/span>(<\/span>DB_ID<\/span>() <\/span>as varchar<\/span>(<\/span>3))+<\/span>')),\r\nADD EVENT sqlserver.flush_file_buffers\r\n( WHERE (sqlserver.database_id = '<\/span>+ <\/span>cast<\/span>(<\/span>DB_ID<\/span>() <\/span>as varchar<\/span>(<\/span>3))+<\/span>')),\r\nADD EVENT sqlserver.file_read\r\n( WHERE (sqlserver.database_id = '<\/span>+ <\/span>cast<\/span>(<\/span>DB_ID<\/span>() <\/span>as varchar<\/span>(<\/span>3))+<\/span>')),\r\nADD EVENT sqlserver.file_written\r\n( WHERE (sqlserver.database_id = '<\/span>+ <\/span>cast<\/span>(<\/span>DB_ID<\/span>() <\/span>as varchar<\/span>(<\/span>3))+<\/span>')),\r\nADD EVENT sqlserver.file_read_completed\r\n( WHERE (sqlserver.database_id = '<\/span>+ <\/span>cast<\/span>(<\/span>DB_ID<\/span>() <\/span>as varchar<\/span>(<\/span>3))+<\/span>')),\r\nADD EVENT sqlserver.file_write_completed\r\n( WHERE (sqlserver.database_id = '<\/span>+ <\/span>cast<\/span>(<\/span>DB_ID<\/span>() <\/span>as varchar<\/span>(<\/span>3))+<\/span>')),\r\nADD EVENT sqlserver.trace_print\r\n(   WHERE (sqlserver.session_id = '<\/span>+ <\/span>cast<\/span>(<\/span>@@SPID <\/span>as varchar<\/span>(<\/span>4))+<\/span>')),\r\nADD EVENT sqlserver.database_transaction_begin\r\n( WHERE (sqlserver.database_id = '<\/span>+ <\/span>cast<\/span>(<\/span>DB_ID<\/span>() <\/span>as varchar<\/span>(<\/span>3))+<\/span>')),\r\nADD EVENT sqlserver.database_transaction_end\r\n( WHERE (sqlserver.database_id = '<\/span>+ <\/span>cast<\/span>(<\/span>DB_ID<\/span>() <\/span>as varchar<\/span>(<\/span>3))+<\/span>'))\r\nADD TARGET package0.asynchronous_file_target(\r\n     SET filename=''C:\\SQLBlog\\TransactionLogUsage.xel'',\r\n         metadatafile=''C:\\SQLBlog\\TransactionLogUsage.xem'')\r\nWITH (MAX_MEMORY = 8MB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, TRACK_CAUSALITY = ON )'\r\n<\/span>EXEC <\/span>(<\/span>@sqlcmd)\r\n<\/span>GO<\/span><\/pre>\n
        <\/a><\/p><\/blockquote>\n
      If you notice above, I have commented out the sqlserver.sql_statement_starting and sqlserver.sql_statement_completed Events in the Event Session.  It turns out that these two events are not needed in the Event Session to prove the points being made in this blog post.  Including these two events makes the time to process the 240K+ Events run in the 10-15 minute range on my 16 core test server, so its not likely something that you are going to do on a laptop VM, but they were included in my initial Event Session for this, and I wanted to show the thought process I followed to get from A to B and ultimately C.<\/p>\n
      With our Event Session created, we can finish setting up our environment to run the actual tests.  To do this we\u2019ll create a table named LogTable with two columns that are, as best as I can tell from the limited information provided about the test table, the same as the table used in the original post.  We\u2019ll then CHECKPOINT the database to cause log truncation to occur (you did create the database in SIMPLE recovery right?), turn on Trace Flag 3004 for our session, and then start the Event Session so that it collects the data from our Events during our test.<\/p>\n
      \n
      -- Create our Test Table\r\n<\/span>CREATE TABLE <\/span>LogTable (<\/span>RowID decimal<\/span>(<\/span>10,<\/span>4), <\/span>Data char<\/span>(<\/span>1024))\r\n<\/span>GO\r\n<\/span>-- Checkpoint the database to truncate and clear the log.\r\n<\/span>CHECKPOINT\r\nGO\r\n<\/span>-- Turn on Trace Flag 3004 so we can see file zeroing ops.\r\n<\/span>DBCC <\/span>TRACEON(<\/span>3004)\r\n<\/span>GO\r\n<\/span>-- Start the Event Session\r\n<\/span>ALTER EVENT SESSION <\/span>TransactionLogUsage\r\nON SERVER\r\nSTATE<\/span>=<\/span>START\r\nGO<\/span><\/pre>\n
        <\/a><\/p><\/blockquote>\n
      With the Event Session started and all our setup work completed we can now run the test script that was used in the original post to generate our test workload.  When the tests complete, we\u2019ll dump out our VLF information again with DBCC LOGINFO, then ROLLBACK the transaction, switch to master and DROP our test database and the Extended Events Session from the server since they are no longer needed.<\/p>\n
      \n
      -- Run our tests\r\n<\/span>SET NOCOUNT ON\r\n\r\nDECLARE <\/span>@cnt decimal<\/span>(<\/span>10,<\/span>4)=<\/span>0\r\nDECLARE <\/span>@rows int<\/span>=<\/span>0\r\nBEGIN TRAN\r\nWHILE <\/span>1=<\/span>1\r\nBEGIN\r\n    INSERT INTO <\/span>LogTable VALUES <\/span>(<\/span>ROUND<\/span>((<\/span>RAND<\/span>()* <\/span>1000000),<\/span>0), <\/span>SPACE<\/span>(<\/span>1024))\r\n    \r\n    <\/span>SELECT <\/span>@rows+=<\/span>1\r\n        \r\n    SELECT <\/span>@cnt = (<\/span>size * <\/span>1.0 * <\/span>8.0)\/<\/span>1024.0 \r\n    FROM  <\/span>Test.<\/span>sys<\/span>.<\/span>database_files\r\n    <\/span>WHERE <\/span>data_space_id = <\/span>0\r\n    AND <\/span>[FILE_ID]=<\/span>5\r\n    \r\n    IF <\/span>@cnt><\/span>1.0\r\n            BREAK\r\nEND\r\n\r\nSELECT <\/span>@rows;\r\n<\/span>GO\r\n<\/span>-- Pull Log VLF usage again\r\n<\/span>DBCC <\/span>LOGINFO\r\nGO\r\n<\/span>-- Rollback our transaction\r\n<\/span>ROLLBACK\r\nGO\r\nUSE master\r\nGO\r\n<\/span>-- Kill any connection to Test database\r\n<\/span>ALTER DATABASE <\/span>[Test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE<\/span>;\r\n<\/span>GO\r\n<\/span>-- Drop the Test database\r\n<\/span>DROP DATABASE <\/span>[Test]\r\nGO\r\n<\/span>-- Drop the Event Session\r\n<\/span>DROP EVENT SESSION <\/span>TransactionLogUsage\r\nON SERVER\r\nGO<\/span><\/pre>\n<\/blockquote>\n
      \"image\"<\/a><\/p>\n<\/p>\n
      There is a reason that I dump out the VLF information before performing the ROLLBACK of the transaction.  As long as the transaction remains active, the VLF\u2019s containing the active transaction can not be truncated and cleared.  In order to see the allocated VLF\u2019s, we need the transaction active still.  Once again, I have highlighted each of the individual log files separately, and from the DBCC LOGINFO output we can look at the FileId and Status columns and see that our transaction log wrote information into all 4 of the files, filling them, and the wrapped back to the first file which had to be grown, and each of the subsequent log files were also grown by the database engine.  However, if you look at the CreateLSN information for the growth portion of each log file, you will notice that each file has its own Create LSN value for the second set of VLF\u2019s, meaning that they were grown separately and at different times.  Still not convinced by DBCC LOGINFO?  Well we have the data to validate this and prove it unequivocally, but before we can look at the data, we need to retrieve it from the asynchronous_file_target files and shred the XML using XQuery.<\/p>\n
      \n
      -- Create our Analysis Database\r\n<\/span>CREATE DATABASE <\/span>TLogUsageTestResults\r\nGO\r\n<\/span>-- Switch to our Analysis Database\r\n<\/span>USE <\/span>[TLogUsageTestResults]\r\nGO\r\n<\/span>-- Create intermediate temp table for raw event data\r\n<\/span>CREATE TABLE <\/span>RawEventData\r\n(<\/span>Rowid int identity primary key<\/span>, <\/span>event_data xml<\/span>)\r\n\r\n<\/span>-- Create final results table for parsed event data\r\n<\/span>CREATE TABLE <\/span>TestResults\r\n(<\/span>[Rowid] int primary key<\/span>, <\/span>[event_name] varchar<\/span>(<\/span>50), <\/span>[package_name] varchar<\/span>(<\/span>50),\r\n <\/span>[timestamp] datetime2<\/span>, <\/span>[count] bigint<\/span>, <\/span>[increment] bigint<\/span>, <\/span>[database_id] int<\/span>, \r\n <\/span>[mode] nvarchar<\/span>(<\/span>4000), <\/span>[file_handle] nvarchar<\/span>(<\/span>4000), <\/span>[offset] bigint<\/span>, \r\n <\/span>[file_id] int<\/span>, <\/span>[file_group_id] int<\/span>, <\/span>[path] nvarchar<\/span>(<\/span>4000), <\/span>[duration] bigint<\/span>, \r\n <\/span>[io_data] nvarchar<\/span>(<\/span>4000), <\/span>[succeeded] nvarchar<\/span>(<\/span>4000), <\/span>[sql_text] nvarchar<\/span>(<\/span>4000), \r\n <\/span>[trace_message] nvarchar<\/span>(<\/span>4000), <\/span>[source_database_id] int<\/span>, <\/span>[object_id] int<\/span>, \r\n <\/span>[object_type] int<\/span>, <\/span>[cpu] int<\/span>, <\/span>[reads] bigint<\/span>, <\/span>[writes] bigint<\/span>, \r\n <\/span>[state] nvarchar<\/span>(<\/span>4000), <\/span>[offset_end] int<\/span>, <\/span>[nest_level] int<\/span>, \r\n <\/span>[activity_id] uniqueidentifier<\/span>, <\/span>[event_sequence] int <\/span>)\r\n\r\n<\/span>-- Read the file data into intermediate temp table\r\n<\/span>INSERT INTO <\/span>RawEventData (<\/span>event_data)\r\n<\/span>SELECT\r\n    <\/span>CAST<\/span>(<\/span>event_data AS XML<\/span>) <\/span>AS <\/span>event_data\r\nFROM <\/span>sys<\/span>.<\/span>fn_xe_file_target_read_file(<\/span>'C:\\SQLBlog\\TransactionLogUsage*.xel'<\/span>, \r\n                                     <\/span>'C:\\SQLBlog\\TransactionLogUsage*.xem'<\/span>, \r\n                                     null, null)\r\n\r\n<\/span>-- Query the Event data from the Target.\r\n<\/span>INSERT INTO <\/span>TestResults\r\n(<\/span>[Rowid], <\/span>[event_name], <\/span>[timestamp], <\/span>[database_id], <\/span>[count], <\/span>[increment], \r\n <\/span>[mode], <\/span>[file_handle], <\/span>[offset], <\/span>[file_id], <\/span>[file_group_id], <\/span>[path], \r\n <\/span>[duration], <\/span>[io_data], <\/span>[succeeded], <\/span>[sql_text], <\/span>[trace_message], <\/span>[source_database_id], \r\n <\/span>[object_id], <\/span>[object_type], <\/span>[cpu], <\/span>[reads], <\/span>[writes], <\/span>[state], <\/span>[offset_end], \r\n <\/span>[nest_level], <\/span>[activity_id], <\/span>[event_sequence])\r\n\r\n<\/span>-- Fetch the Event Data from the Event Session Target\r\n<\/span>SELECT \r\n    <\/span>RowID,\r\n    <\/span>event_data.<\/span>value(<\/span>'(event\/@name)[1]'<\/span>, <\/span>'varchar(50)'<\/span>) <\/span>AS <\/span>event_name,\r\n    <\/span>DATEADD<\/span>(<\/span>hh, \r\n            <\/span>DATEDIFF<\/span>(<\/span>hh, <\/span>GETUTCDATE<\/span>(), <\/span>CURRENT_TIMESTAMP<\/span>), \r\n            <\/span>event_data.<\/span>value(<\/span>'(event\/@timestamp)[1]'<\/span>, <\/span>'datetime2'<\/span>)) <\/span>AS <\/span>[timestamp],\r\n    <\/span>COALESCE<\/span>(<\/span>event_data.<\/span>value(<\/span>'(event\/data[@name="database_id"]\/value)[1]'<\/span>, <\/span>'int'<\/span>), \r\n             <\/span>event_data.<\/span>value(<\/span>'(event\/action[@name="database_id"]\/value)[1]'<\/span>, <\/span>'int'<\/span>)) <\/span>AS <\/span>database_id,\r\n    <\/span>event_data.<\/span>value(<\/span>'(event\/data[@name="count"]\/value)[1]'<\/span>, <\/span>'bigint'<\/span>) <\/span>AS <\/span>[count],\r\n    <\/span>event_data.<\/span>value(<\/span>'(event\/data[@name="increment"]\/value)[1]'<\/span>, <\/span>'bigint'<\/span>) <\/span>AS <\/span>[increment],\r\n    <\/span>event_data.<\/span>value(<\/span>'(event\/data[@name="mode"]\/text)[1]'<\/span>, <\/span>'nvarchar(4000)'<\/span>) <\/span>AS <\/span>[mode],\r\n    <\/span>event_data.<\/span>value(<\/span>'(event\/data[@name="file_handle"]\/value)[1]'<\/span>, <\/span>'nvarchar(4000)'<\/span>) <\/span>AS <\/span>[file_handle],\r\n    <\/span>event_data.<\/span>value(<\/span>'(event\/data[@name="offset"]\/value)[1]'<\/span>, <\/span>'bigint'<\/span>) <\/span>AS <\/span>[offset],\r\n    <\/span>event_data.<\/span>value(<\/span>'(event\/data[@name="file_id"]\/value)[1]'<\/span>, <\/span>'int'<\/span>) <\/span>AS <\/span>[file_id],\r\n    <\/span>event_data.<\/span>value(<\/span>'(event\/data[@name="file_group_id"]\/value)[1]'<\/span>, <\/span>'int'<\/span>) <\/span>AS <\/span>[file_group_id],\r\n    <\/span>event_data.<\/span>value(<\/span>'(event\/data[@name="path"]\/value)[1]'<\/span>, <\/span>'nvarchar(4000)'<\/span>) <\/span>AS <\/span>[path],\r\n    <\/span>event_data.<\/span>value(<\/span>'(event\/data[@name="duration"]\/value)[1]'<\/span>, <\/span>'bigint'<\/span>) <\/span>AS <\/span>[duration],\r\n    <\/span>event_data.<\/span>value(<\/span>'(event\/data[@name="io_data"]\/value)[1]'<\/span>, <\/span>'nvarchar(4000)'<\/span>) <\/span>AS <\/span>[io_data],\r\n    <\/span>event_data.<\/span>value(<\/span>'(event\/data[@name="succeeded"]\/value)[1]'<\/span>, <\/span>'nvarchar(4000)'<\/span>) <\/span>AS <\/span>[succeeded],\r\n    <\/span>event_data.<\/span>value(<\/span>'(event\/action[@name="sql_text"]\/value)[1]'<\/span>, <\/span>'nvarchar(4000)'<\/span>) <\/span>AS <\/span>[sql_text],\r\n    <\/span>event_data.<\/span>value(<\/span>'(event\/data[@name="message"]\/value)[1]'<\/span>, <\/span>'nvarchar(4000)'<\/span>) <\/span>AS <\/span>[trace_message],\r\n    <\/span>event_data.<\/span>value(<\/span>'(event\/data[@name="source_database_id"]\/value)[1]'<\/span>, <\/span>'int'<\/span>) <\/span>AS <\/span>[source_database_id],\r\n    <\/span>event_data.<\/span>value(<\/span>'(event\/data[@name="object_id"]\/value)[1]'<\/span>, <\/span>'int'<\/span>) <\/span>AS <\/span>[object_id],\r\n    <\/span>event_data.<\/span>value(<\/span>'(event\/data[@name="object_type"]\/value)[1]'<\/span>, <\/span>'int'<\/span>) <\/span>AS <\/span>[object_type],\r\n    <\/span>event_data.<\/span>value(<\/span>'(event\/data[@name="cpu"]\/value)[1]'<\/span>, <\/span>'int'<\/span>) <\/span>AS <\/span>[cpu],\r\n    <\/span>event_data.<\/span>value(<\/span>'(event\/data[@name="reads"]\/value)[1]'<\/span>, <\/span>'bigint'<\/span>) <\/span>AS <\/span>[reads],\r\n    <\/span>event_data.<\/span>value(<\/span>'(event\/data[@name="writes"]\/value)[1]'<\/span>, <\/span>'bigint'<\/span>) <\/span>AS <\/span>[writes],\r\n    <\/span>event_data.<\/span>value(<\/span>'(event\/data[@name="state"]\/text)[1]'<\/span>, <\/span>'nvarchar(4000)'<\/span>) <\/span>AS <\/span>[state],\r\n    <\/span>event_data.<\/span>value(<\/span>'(event\/data[@name="offset_end"]\/value)[1]'<\/span>, <\/span>'int'<\/span>) <\/span>AS <\/span>[offset_end],\r\n    <\/span>event_data.<\/span>value(<\/span>'(event\/data[@name="nest_level"]\/value)[1]'<\/span>, <\/span>'int'<\/span>) <\/span>AS <\/span>[nest_level],\r\n    <\/span>CAST<\/span>(<\/span>SUBSTRING<\/span>(<\/span>event_data.<\/span>value(<\/span>'(event\/action[@name="attach_activity_id"]\/value)[1]'<\/span>, <\/span>'varchar(50)'<\/span>), <\/span>1, <\/span>36) <\/span>AS uniqueidentifier<\/span>) <\/span>as <\/span>activity_id,\r\n    <\/span>CAST<\/span>(<\/span>SUBSTRING<\/span>(<\/span>event_data.<\/span>value(<\/span>'(event\/action[@name="attach_activity_id"]\/value)[1]'<\/span>, <\/span>'varchar(50)'<\/span>), <\/span>38, <\/span>10) <\/span>AS int<\/span>) <\/span>as <\/span>event_sequence\r\nFROM <\/span>RawEventData\r\nORDER BY <\/span>Rowid\r\nGO\r\n\r\n<\/span>-- Return our results\r\n<\/span>SELECT <\/span>* \r\n<\/span>FROM <\/span>TestResults\r\nWHERE <\/span>event_name NOT IN (<\/span>'sql_statement_starting' <\/span>, <\/span>'sql_statement_completed'<\/span>)\r\n<\/span>ORDER BY <\/span>RowID<\/pre>\n
        <\/a><\/p><\/blockquote>\n
      If you scroll through the results you can see the writes occurring sequentially through each of the log files, and while FileId=4 is being written to, the Database Engine begins the growth of FileId=2 by 32MB.  If we change our query to only focus on the file_write_completed, databases_log_growth, and trace_print Events, we can see this a little easier.<\/p>\n
      \n
      -- Return our results\r\n<\/span>SELECT \r\n    <\/span>Rowid, \r\n    <\/span>event_name, \r\n    <\/span>[timestamp], \r\n    <\/span>[count], \r\n    <\/span>database_id, \r\n    <\/span>mode, \r\n    <\/span>offset, \r\n    <\/span>file_id<\/span>, \r\n    <\/span>duration, \r\n    <\/span>trace_message \r\nFROM <\/span>TestResults\r\nWHERE <\/span>event_name  IN (<\/span>'file_write_completed' <\/span>, <\/span>'databases_log_growth'<\/span>, <\/span>'trace_print'<\/span>)\r\n<\/span>ORDER BY <\/span>RowID<\/pre>\n
        <\/a><\/p><\/blockquote>\n
      \n\n\n\n\n
      \"image\"<\/a> <\/td>\n   <\/td>\n\"image\"<\/a> <\/td>\n<\/tr>\n
      Log Rollover from Log1 to Log2<\/strong><\/td>\n <\/td>\nLog Rollover from Log2 to Log3<\/strong><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
       <\/p>\n
      \n\n\n\n\n
      \n
      \"image\"<\/a> <\/p>\n<\/td>\n
      		   <\/td>\n\"image\"<\/a> <\/td>\n<\/tr>\n
      Autogrow of Log1<\/strong><\/td>\n <\/td>\nLog Rollover from Log3 to Log4<\/strong><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
       <\/p>\n
      \n\n\n\n\n
      \"image\"<\/a><\/td>\n   <\/td>\n\"image\"<\/a> <\/td>\n<\/tr>\n
      Log Rollover from Log4 back to Log1<\/strong><\/td>\n <\/td>\nAutogrow of Log2<\/strong><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
       <\/p>\n
      \n\n\n\n\n
      \"image\"<\/a> <\/td>\n   <\/td>\n\"image\"<\/a> <\/td>\n<\/tr>\n
      Autogrow of Log3<\/strong><\/td>\n <\/td>\nAutogrow of Log4<\/strong><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
      \n  
      We can see the first log file, FileID=2, grows before the log rollover from Log3, FileID=4, occurs to Log4, FileID=5, making space available in the first log file for the rollover when FileID=5 becomes full.  Log records are written all four of the log files before the log wraps back around to the first log file, debunking point numbers two and four of the conclusion.  The timestamps of the events shows that the additional log files are written to serially and not at the same time debunking point number three of the conclusion.  The reason that only a fraction of the log records are written to the three additional log files is proportionate to the difference in the autogrowth settings between the first log file at 32MB and the three additional log files at 1MB.  If the first log file was set to grow at 1MB, the majority of the log records would not be in the first log file.<\/p>\n
      Its been well documented that there is no performance benefit to having multiple log files in a database, and Paul Randal\u2019s blog post, Importance of proper transaction log size management<\/a>, was linked to in the original blog post that ultimately triggered this post.  The behavior demonstrated in this post isn\u2019t a mystery, its documented in the Books Online (Transaction Log Physical Architecture<\/a>), but sometime empirical evidence like this helps solidify that fact.  <\/p>\n","protected":false},"excerpt":{"rendered":"
      While working on yesterday\u2019s blog post The Future \u2013 fn_dblog() No More? Tracking Transaction Log Activity in Denali I did a quick Google search to find a specific blog post by Paul Randal to use it as a reference, and in the results returned another blog post titled, Investigating Multiple Transaction Log Files in SQL […]<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[23,38,45],"tags":[],"class_list":["post-541","post","type-post","status-publish","format-standard","hentry","category-extended-events","category-sql-server-2008","category-xevent-a-day-series"],"yoast_head":"\nAn XEvent a Day (23 of 31) \u2013 How it Works \u2013 Multiple Transaction Log Files - Jonathan Kehayias<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-23-of-31-how-it-works-multiple-transaction-log-files\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"An XEvent a Day (23 of 31) \u2013 How it Works \u2013 Multiple Transaction Log Files - Jonathan Kehayias\" \/>\n<meta property=\"og:description\" content=\"While working on yesterday\u2019s blog post The Future \u2013 fn_dblog() No More? Tracking Transaction Log Activity in Denali I did a quick Google search to find a specific blog post by Paul Randal to use it as a reference, and in the results returned another blog post titled, Investigating Multiple Transaction Log Files in SQL […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-23-of-31-how-it-works-multiple-transaction-log-files\/\" \/>\n<meta property=\"og:site_name\" content=\"Jonathan Kehayias\" \/>\n<meta property=\"article:published_time\" content=\"2010-12-23T19:33:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T16:18:28+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.SQLskills.com\/blogs\/jonathan\/wp-content\/uploads\/windowslivewriter\/070b533d037c\/6e91e8be\/image_thumb.png\" \/>\n<meta name=\"author\" content=\"Jonathan Kehayias\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Jonathan Kehayias\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"18 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-23-of-31-how-it-works-multiple-transaction-log-files\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-23-of-31-how-it-works-multiple-transaction-log-files\\\/\"},\"author\":{\"name\":\"Jonathan Kehayias\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\"},\"headline\":\"An XEvent a Day (23 of 31) \u2013 How it Works \u2013 Multiple Transaction Log Files\",\"datePublished\":\"2010-12-23T19:33:00+00:00\",\"dateModified\":\"2017-04-13T16:18:28+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-23-of-31-how-it-works-multiple-transaction-log-files\\\/\"},\"wordCount\":2261,\"commentCount\":2,\"image\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-23-of-31-how-it-works-multiple-transaction-log-files\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.SQLskills.com\\\/blogs\\\/jonathan\\\/wp-content\\\/uploads\\\/windowslivewriter\\\/070b533d037c\\\/6e91e8be\\\/image_thumb.png\",\"articleSection\":[\"Extended Events\",\"SQL Server 2008\",\"XEvent a Day Series\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-23-of-31-how-it-works-multiple-transaction-log-files\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-23-of-31-how-it-works-multiple-transaction-log-files\\\/\",\"url\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-23-of-31-how-it-works-multiple-transaction-log-files\\\/\",\"name\":\"An XEvent a Day (23 of 31) \u2013 How it Works \u2013 Multiple Transaction Log Files - Jonathan Kehayias\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-23-of-31-how-it-works-multiple-transaction-log-files\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-23-of-31-how-it-works-multiple-transaction-log-files\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.SQLskills.com\\\/blogs\\\/jonathan\\\/wp-content\\\/uploads\\\/windowslivewriter\\\/070b533d037c\\\/6e91e8be\\\/image_thumb.png\",\"datePublished\":\"2010-12-23T19:33:00+00:00\",\"dateModified\":\"2017-04-13T16:18:28+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-23-of-31-how-it-works-multiple-transaction-log-files\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-23-of-31-how-it-works-multiple-transaction-log-files\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-23-of-31-how-it-works-multiple-transaction-log-files\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.SQLskills.com\\\/blogs\\\/jonathan\\\/wp-content\\\/uploads\\\/windowslivewriter\\\/070b533d037c\\\/6e91e8be\\\/image_thumb.png\",\"contentUrl\":\"https:\\\/\\\/www.SQLskills.com\\\/blogs\\\/jonathan\\\/wp-content\\\/uploads\\\/windowslivewriter\\\/070b533d037c\\\/6e91e8be\\\/image_thumb.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-23-of-31-how-it-works-multiple-transaction-log-files\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Extended Events\",\"item\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/category\\\/extended-events\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"An XEvent a Day (23 of 31) \u2013 How it Works \u2013 Multiple Transaction Log Files\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#website\",\"url\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/\",\"name\":\"Jonathan Kehayias - The Rambling DBA\",\"description\":\"The Rambling DBA\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\",\"name\":\"Jonathan Kehayias\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"caption\":\"Jonathan Kehayias\"},\"sameAs\":[\"http:\\\/\\\/3.209.169.194\\\/blogs\\\/jonathan\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"An XEvent a Day (23 of 31) \u2013 How it Works \u2013 Multiple Transaction Log Files - Jonathan Kehayias","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-23-of-31-how-it-works-multiple-transaction-log-files\/","og_locale":"en_US","og_type":"article","og_title":"An XEvent a Day (23 of 31) \u2013 How it Works \u2013 Multiple Transaction Log Files - Jonathan Kehayias","og_description":"While working on yesterday\u2019s blog post The Future \u2013 fn_dblog() No More? Tracking Transaction Log Activity in Denali I did a quick Google search to find a specific blog post by Paul Randal to use it as a reference, and in the results returned another blog post titled, Investigating Multiple Transaction Log Files in SQL […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-23-of-31-how-it-works-multiple-transaction-log-files\/","og_site_name":"Jonathan Kehayias","article_published_time":"2010-12-23T19:33:00+00:00","article_modified_time":"2017-04-13T16:18:28+00:00","og_image":[{"url":"https:\/\/www.SQLskills.com\/blogs\/jonathan\/wp-content\/uploads\/windowslivewriter\/070b533d037c\/6e91e8be\/image_thumb.png","type":"","width":"","height":""}],"author":"Jonathan Kehayias","twitter_misc":{"Written by":"Jonathan Kehayias","Est. reading time":"18 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-23-of-31-how-it-works-multiple-transaction-log-files\/#article","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-23-of-31-how-it-works-multiple-transaction-log-files\/"},"author":{"name":"Jonathan Kehayias","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c"},"headline":"An XEvent a Day (23 of 31) \u2013 How it Works \u2013 Multiple Transaction Log Files","datePublished":"2010-12-23T19:33:00+00:00","dateModified":"2017-04-13T16:18:28+00:00","mainEntityOfPage":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-23-of-31-how-it-works-multiple-transaction-log-files\/"},"wordCount":2261,"commentCount":2,"image":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-23-of-31-how-it-works-multiple-transaction-log-files\/#primaryimage"},"thumbnailUrl":"https:\/\/www.SQLskills.com\/blogs\/jonathan\/wp-content\/uploads\/windowslivewriter\/070b533d037c\/6e91e8be\/image_thumb.png","articleSection":["Extended Events","SQL Server 2008","XEvent a Day Series"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-23-of-31-how-it-works-multiple-transaction-log-files\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-23-of-31-how-it-works-multiple-transaction-log-files\/","url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-23-of-31-how-it-works-multiple-transaction-log-files\/","name":"An XEvent a Day (23 of 31) \u2013 How it Works \u2013 Multiple Transaction Log Files - Jonathan Kehayias","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-23-of-31-how-it-works-multiple-transaction-log-files\/#primaryimage"},"image":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-23-of-31-how-it-works-multiple-transaction-log-files\/#primaryimage"},"thumbnailUrl":"https:\/\/www.SQLskills.com\/blogs\/jonathan\/wp-content\/uploads\/windowslivewriter\/070b533d037c\/6e91e8be\/image_thumb.png","datePublished":"2010-12-23T19:33:00+00:00","dateModified":"2017-04-13T16:18:28+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-23-of-31-how-it-works-multiple-transaction-log-files\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-23-of-31-how-it-works-multiple-transaction-log-files\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-23-of-31-how-it-works-multiple-transaction-log-files\/#primaryimage","url":"https:\/\/www.SQLskills.com\/blogs\/jonathan\/wp-content\/uploads\/windowslivewriter\/070b533d037c\/6e91e8be\/image_thumb.png","contentUrl":"https:\/\/www.SQLskills.com\/blogs\/jonathan\/wp-content\/uploads\/windowslivewriter\/070b533d037c\/6e91e8be\/image_thumb.png"},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-23-of-31-how-it-works-multiple-transaction-log-files\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/"},{"@type":"ListItem","position":2,"name":"Extended Events","item":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/category\/extended-events\/"},{"@type":"ListItem","position":3,"name":"An XEvent a Day (23 of 31) \u2013 How it Works \u2013 Multiple Transaction Log Files"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/","name":"Jonathan Kehayias - The Rambling DBA","description":"The Rambling DBA","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c","name":"Jonathan Kehayias","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","caption":"Jonathan Kehayias"},"sameAs":["http:\/\/3.209.169.194\/blogs\/jonathan"]}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts\/541","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/comments?post=541"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts\/541\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/media?parent=541"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/categories?post=541"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/tags?post=541"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}