Search results matching tag 'transaction log'

Geek City: What Triggered This Post?

Kalen Delaney — Tue, 01 Jan 2013 00:00:00 GMT

I’d really like to get another post up onto my much neglected blog before the end of 2012. This will also start one of my New Year’s resolutions, which is to write at least one blog post a month. I’m going to tell you about a change in SQL Server that wasn’t announced in any “What’s New” list that I ever saw, perhaps because it was just a chance in internal behavior, and nothing that required any change in user applications.

Do you retest what you know is true for every new version? When I update my books, I do test all the scripts, but if there isn’t a script, I don’t retest every ‘fact’ that I have known for years is true. And sometimes, things change. And sometimes my reviewers notice those unreported changes, and sometimes they don’t.

You might be aware of the fact that SQL Server can perform UPDATE operations in two different ways. The UPDATE can be performed as a two-step process: delete the old row and then insert a whole new row, or, the UPDATE can be performed (much more efficiently) as an update-in-place. When the two-step UPDATE is performed, it is a LOT more work. Not only does SQL Server have to log the entire old row and the entire new row, but each nonclustered index is also modified twice, and each of those index changes also has to be logged. So it’s nice when an update-in-place is done, because only the bytes changed are logged, and only indexes on the updated columns are affected.

Prior to SQL Server 7, there were actually four different ways that UPDATE could be done. The two-step UPDATE had some variations that could make it even slower in some cases! But that was a long time ago, so I’m not going to go into the details now. But I will say that back then, in order to get an update-in-place to occur, there was a big long list of prerequisites that had to be met and if you missed just one, you’d get one of the slower UPDATE operations.

As of SQL Server 7, update-in-place became the default. The only time it doesn’t happen is when the row can’t stay in the same location (such as when you update a clustered index key column) or when SQL Server really needs the old and new versions of the row.

In SQL 7, one of the places that SQL needed the old and new version of the updates rows was when processing triggers. Triggers need the transaction log to get the contents for the DELETED and INSERTED pseudo-tables. And because triggers needed the entire old and new versions of the updated rows, the UPDATE was performed as a two-step operation. DELETE the old row, log the entire old row, and the INSERT the new row with the new values, and log the entire new row.

But as of 2005, we now have the version store, primarily used for SNAPSHOT isolation, but available for other uses as well. In SNAPSHOT isolation, the version stores stores ‘old versions’ of rows that have been updated or deleted. I knew that the version store was also used for triggers, but it only occurred to me just recently that maybe, because the old and new versions of the row were not needed from the log, perhaps UPDATEs did not always need to be performed internally as a two-step UPDATE.

So I decided to test it out.

-- DEMO: If there is an UPDATE trigger, are updates logged as DELETE + INSERT?
-- First build a new database.

USE master;
GO
IF (SELECT db_id('TestTrigger')) IS NOT NULL
DROP DATABASE TestTrigger;
GO
CREATE DATABASE TestTrigger;
GO
ALTER DATABASE TestTrigger SET RECOVERY SIMPLE;
GO
SELECT db_id('TestTrigger');
GO

USE TestTrigger;
GO

-- Just for a warmup, look at the function fn_dblog, which works in the current database

SELECT * FROM fn_dblog(null, null);
GO

-- Create a new table to work with
IF (SELECT object_id('objects')) IS NOT NULL
DROP TABLE objects;
GO
SELECT TOP 100 * INTO objects FROM sys.objects;
GO

-- Create a clustered index on the table
CREATE CLUSTERED INDEX objects_clustered on objects(name);
GO

-- First examine an update we know is NOT done in place,
-- i.e. updating a clustered key value

UPDATE objects SET name = 'newrowsets' WHERE name = 'sysrowsets';
GO

-- Look at last 10 rows; notice a LOP_DELETE_ROWS and LOP_INSERT_ROWS
-- The AllocUniteName column shows the object affected is the clustered index on dbo.objects
SELECT Operation, [Transaction ID], AllocUnitName FROM fn_dblog(null, null);
GO

-- Now examine an update we know is done in place,
-- i.e. updating an unindexed column on a table with no triggers
UPDATE objects SET parent_object_id = 1 WHERE name = 'sysfiles1';
GO

-- Look at last 3 rows; notice a LOP_MODIFY_ROW on the dbo.objects allocation unit
SELECT Operation, [Transaction ID], AllocUnitName FROM fn_dblog(null, null);
GO

-- Create an update trigger
-- Will the update be done with the siple LOP_MODIFY_ROW or with the LOP_DELETE_ROWS and LOP_INSERT_ROWS
CREATE TRIGGER trg_update_objects ON objects FOR UPDATE
as
SELECT * FROM DELETED; SELECT * FROM INSERTED;
RETURN;
GO

-- Now perform update again
UPDATE objects SET parent_object_id = 10 WHERE name = 'sysfiles1';
GO

-- Look at last 3 rows; notice a LOP_MODIFY_ROW
SELECT * FROM fn_dblog(null, null);
GO

Since the database is in SIMPLE recovery model, you can issue a CHECKPOINT before each UPDATE if you want to reduce the number of rows in the log to make it easier to examine.

So it seems that I need to update my course and some of my writings. There might also be special cases that still require that an two-step UPDATE be performed in the presence of triggers, but it seems like a two-step UPDATE is not ALWAYS required anymore. That is very good news!

I hope you all have a wonder-filled and joyous New Year!

~Kalen

Follow-up Answers for my Australia Classes

Kalen Delaney — Wed, 04 Apr 2012 22:56:00 GMT

I was out of the country for the last two weeks of March, delivering classes in Brisbane and Sydney, which were organized by WardyIT. It was a great visit and there were 24 terrific students!

As is sometimes (perhaps often?) the case, there were questions posed that I couldn’t answer during class, so here are a couple of follow-up answers.

1. I brought up the fact that SQLS 2012 generates a warning message when there are ‘too many’ Virtual Log Files (VLFs) in a database. (It turns out the message will be generated when a database has more than 10,000 VLFs but the error says that the database has more than 1000 VLFs! Which is of course, technically true, but the message is a little misleading.) A student reported hearing that the Best Practices Analyzer also had a warning about the number of VLFs, but didn’t know what that limit was, and asked if I knew. I didn’t then, but I do now. A message will be generated by BPA when there are 1000 or more VLFs. The message is actually focused on the performance problems that can arise when there are two many VLFs. The boot message is more concerned with the impact on database restore, rollback, backup and recovery of too many VLFs.

2. When discussing locks, I briefly mention the BU lock that is acquired by request (with a hint) during bulk load operations, for the purpose of allowing multiple processes to load into the same table concurrently. Someone asked whether any SSIS components used the BU lock, and I thought it was probably true, but I since I don’t work with SSIS, I couldn’t comment any deeper. I was able to discover, thanks to SQL Server MVPs Paul White and Arthur Zubarev that it definitely is possible for both SQL Destinations and OLE DB Destinations. The destination table must be a heap, and you can specify TABLOCK as an option for the FastLoadOptions property, as shown:

3. I very briefly mention Extended Events on the very last day, and mention that in SQL Server 2008 one of the drawbacks is that there is no GUI option to set up an XEvents session, but that has changed in SQL Server 2012. I was asked if the 2012 Management Studio could connect to a SQL Server 2008 instance and set up an XEvents session on that instance. My friend and fellow MVP Ben Miller was able to verify that when connecting from the SQL 2012 GUI to a 2008 instance, the option for creating an XEvents session does not even show up. You could try scripting a session created for a 2012 instance and running it on a 2008 instance, but there are no guarantees.

So now I’ll get back to working on my new book …

~Kalen

Page splits, extended events, index page allocation and all the fish

mz1313 — Mon, 25 Apr 2011 19:49:45 GMT

2 years ago I wrote about monitoring page splits with Extended Events. Only 2 bloggers explored Extended Events feature at that time, so my post was more of a learning than for any practical matter. Yet one question remained open: according to straightforward calculations, after 3 splits of the first page, next split should have occurred on another page - but trace with extended events clearly showed 4 splits of initial page.

Recently Pavel Nefyodov has drawn my attention to another curious fact: initially we had 1 page; after all those splits we had 10. But trace showed 10 splits, not 9. So where did additional split come from? And finally I've realized that there is another simple way to trace those splits - via transaction log. If we manually checkpoint the system before and perform insert in transaction afer - our transaction log will containg exactly the relevant data; and it'll remain this way all the time transaction stays open. We'll query transaction log using undocumented but widely known fn_dblog function.

OK, Let's see some code (mostly taken from my old post which in turn used page split demo from Kalen Delaney's blog). Initial configuration (including trace definition):

USE tempdb; GO CREATE TABLE split_page (id INT IDENTITY(0,2) PRIMARY KEY, id2 bigint DEFAULT 0, data1 VARCHAR(33) NULL, data2 VARCHAR(8000) NULL); GO INSERT INTO split_page DEFAULT VALUES; GO 385 CREATE EVENT SESSION MonitorPageSplits ON SERVER ADD EVENT sqlserver.page_split ( ACTION (sqlserver.database_id, sqlserver.sql_text) WHERE sqlserver.database_id = 2 ) ADD TARGET package0.asynchronous_file_target ( SET filename = N'c:\temp\MonitorPageSplits.etx', metadatafile = N'c:\temp\MonitorPageSplits.mta' ); GO CHECKPOINT GO

Short glance at our table's allocation inside data file:

DBCC IND(tempdb, split_page, -1); GO

PagePID	IndexID	iam_chain_type	PageType	IndexLevel
175	1	In-row data	10	NULL
148	1	In-row data	1	0

So we have 1 IAM page and 1 data page. Now the split itself and - remember, we should query transaction log before committing transaction if we don't want accidental checkpoint to ruine all our efforts.

ALTER EVENT SESSION MonitorPageSplits ON SERVER STATE = start; GO SET IDENTITY_INSERT split_page ON; GO BEGIN TRAN INSERT INTO split_page (id, id2, data1, data2) SELECT 111, 0, REPLICATE('a', 33), REPLICATE('b', 8000); GO SET IDENTITY_INSERT split_page OFF; GO SELECT [Current LSN], Operation, Context, [Page ID], [Description] FROM fn_dblog(NULL, NULL) COMMIT GO ALTER EVENT SESSION MonitorPageSplits ON SERVER STATE = STOP; GO DROP EVENT SESSION MonitorPageSplits ON SERVER; GO SELECT split.value('(/event/data[@name=''file_id'']/value)[1]','int') AS [file_id], split.value('(/event/data[@name=''page_id'']/value)[1]','int') AS [page_id], split.value('(/event[@name=''page_split'']/@timestamp)[1]','datetime') AS [event_time], split.value('(/event/action[@name=''sql_text'']/value)[1]','varchar(max)') AS [sql_text] FROM ( SELECT CAST(event_data AS XML) AS split FROM sys.fn_xe_file_target_read_file('c:\temp\MonitorPageSplits*.etx', 'c:\temp\MonitorPageSplits*.mta', NULL, NULL) ) AS t ORDER BY [event_time]

First let's take a look at our table now:

PagePID	IndexID	iam_chain_type	PageType	IndexLevel
175	1	In-row data	10	NULL
148	1	In-row data	1	0
184	1	In-row data	2	1
185	1	In-row data	1	0
187	1	In-row data	1	0
188	1	In-row data	1	0
189	1	In-row data	1	0
190	1	In-row data	1	0
191	1	In-row data	1	0
392	1	In-row data	1	0
393	1	In-row data	1	0
394	1	In-row data	1	0

Transaction log contains more rows than we are interested in, so I cleaned irrelevant ones out from the table below and correlated transaction log with extended events trace. I also transformed hexadecimal page numbers from transaction log to decimal ones - like we see in extended events trace.

So we can see that indeed there have been 3 splits of the first page. From transaction log we see that first action was actually allocating of page 184 which is - look at the allocation table above - index page. According to transaction log this page is allocated - not splitted from any other page. Still extended events trace shows as if it has been splitted from the first data page of our table. Don't know whether this behavior can be considered as bug - probably not. Anyway - both unanswered question from my old post are resolved: first page indeed has been splitted 3 times and we had only 9 data page splits while 10th split belongs to index page allocation.

Have a nice week!

Geek City: What gets logged for SELECT INTO operations?

Kalen Delaney — Tue, 15 Mar 2011 19:41:00 GMT

Last week, I wrote about logging for index rebuild operations. I wanted to publish the result of that testing as soon as I could, because that dealt with a specific question I was trying to answer. However, I actually started out my testing by looking at the logging that was done for a different operation, and ended up generating some new questions for myself.

Before I started testing the index rebuilds, I thought I would just get warmed up by observing the logging for SELECT INTO. I thought I knew what got logged, but I was wrong. My understanding was that as a minimally logged operation, in FULL recovery, SELECT INTO would log each 8K page as it was written, not each row like a normal INSERT would. In BULK_LOGGED, SELECT INTO would create a log record for each page, but it would not log the entire 8K page. And of course, it would also not log every row. However, my first test turned up some surprising results.

Here's what I did: First, I made sure the AdventureWorks database was in FULL recovery, and dropped the Details table if it existed. Then I created a table called SELECTINTOLoggingData to hold the results of calling fn_dblog. As in last week's script, the following information was captured:

Max log record size
Sum of log record sizes
Number of log records
Number of log records larger than 8K

Finally, I backed up the AdventureWorks database to give me a foundation for log backups. So here's the first part of the script:

ALTER DATABASE AdventureWorks SET RECOVERY FULL
GO
USE AdventureWorks;
GO
IF EXISTS (SELECT 1 FROM sys.tables
            WHERE name = 'Details')
       DROP TABLE Details;

IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'SELECTINTOLoggingData')
    DROP TABLE SELECTINTOLoggingData;
GO

CREATE TABLE SELECTINTOLoggingData
(DBRecModel CHAR(12), MaxLogRowSize INT, NumLogRows INT, NumLargeRows INT);
GO

BACKUP DATABASE AdventureWorks TO DISK = 'c:\backups\AW.bak' WITH INIT;
GO

The actual testing was be done first in FULL recovery, then in BULK_LOGGED. I backed up up the transaction log, then created the Details table by executing a SELECT INTO from the Sales.SalesOrderDetail table. The Details table has 1495 pages and 121,317 rows. Finally, I inserted the relevant values from fn_dblog into the SELECTINTOLoggingData table.

---- FULL Recovery

BACKUP LOG AdventureWorks TO DISK = 'c:\backups\AW_log.bak' WITH INIT;
SELECT COUNT(*) FROM fn_dblog(null, null);
GO
SELECT * INTO Details
FROM Sales.SalesOrderDetail;
GO

INSERT INTO SELECTINTOLoggingData
SELECT 'FULL',
        MAX([Log Record Length]),
        COUNT(*),
        (SELECT COUNT(*) FROM fn_dblog(null, null) WHERE [Log Record Length] >8000)
        FROM fn_dblog(null, null);
GO

DROP TABLE Details;
GO

-- BULK_LOGGED Recovery

ALTER DATABASE AdventureWorks SET RECOVERY BULK_LOGGED;
BACKUP LOG AdventureWorks TO DISK = 'c:\backups\AW_log.bak' WITH INIT;
SELECT COUNT(*) FROM fn_dblog(null, null);
GO

SELECT * INTO Details
FROM Sales.SalesOrderDetail;
GO

INSERT INTO SELECTINTOLoggingData
SELECT 'BULK_LOGGED',
        MAX([Log Record Length]),
        COUNT(*),
        (SELECT COUNT(*) FROM fn_dblog(null, null) WHERE [Log Record Length] >8000)
        FROM fn_dblog(null, null);
GO

Finally, I looked at the data I collected:

SELECT * FROM SELECTINTOLoggingData;
GO

Here are my results:

Although the number of large (8K) log rows was as expected, I was quite surprised to see the total number of log records! (And yes, I know the column names use the term "Log Rows", but I'm calling them "Log Records". I guess I just wanted a shorter column name.)

I then ran the same test on a different version of the Sales.SalesOrderDetail table that I use in some of my class examples. Here is the script to create the Sales.SalesOrderDetail2 table:

CREATE TABLE Sales.SalesOrderDetail2 (
    [SalesOrderID] [int] NOT NULL,
    [SalesOrderDetailID] [int] NOT NULL,
    [CarrierTrackingNumber] [nvarchar](25) NULL,
    [OrderQty] [smallint] NOT NULL,
    [ProductID] [int] NOT NULL,
    [SpecialOfferID] [int] NOT NULL,
    [UnitPrice] [money] NOT NULL,
    [UnitPriceDiscount] [money] NOT NULL ,
CONSTRAINT [PK_SalesOrderDetail2_SalesOrderID_SalesOrderDetailID] PRIMARY KEY CLUSTERED
(
    [SalesOrderID] ASC,
    [SalesOrderDetailID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY];
GO

INSERT INTO Sales.SalesOrderDetail2 SELECT [SalesOrderID]
      ,[SalesOrderDetailID]
      ,[CarrierTrackingNumber]
      ,[OrderQty]
      ,[ProductID]
      ,[SpecialOfferID]
      ,[UnitPrice]
      ,[UnitPriceDiscount]
FROM AdventureWorks.Sales.SalesOrderDetail;
GO

After setting the AdventureWorks database back to FULL recovery, deleting the Details table and truncating the SELECTINTOLoggingData table, I run the same test as above (starting at the "Full Recovery" comment, substituting Sales.SalesOrderDetail2 for Sales.SalesOrderDetail.

ALTER DATABASE AdventureWorks SET RECOVERY FULL;
GO
DROP TABLE Details;
GO
TRUNCATE TABLE SELECTINTOLoggingData;
GO

Here are my new results:

So these are much more like the results I expected from a SELECT INTO operation!

Why did the first example, selecting from Sales.SalesOrderDetail, do so much more logging?

It turns out that if you have a IDENTITY column in the source table, SELECT INTO must then log every row as the IDENTITY value is generated.

Does this mean you should rethink the beneift of using IDENTITY columns? Only if you're doing a lot of SELECT INTO operations to copy a table with IDENTITY columns. Normal INSERT statements, which is how IDENTITY values are usually generated, are going to have the same amount of logging whether or not the table has an IDENTITY column. (Let me know if you think otherwise.)

So now you know.

~Kalen

p.s. I would like to thank Sunil Agarwal for his brilliant insights and encouragement. Thanks for everything, Sunil!

Geek City: What gets logged for index rebuild operations?

Kalen Delaney — Tue, 08 Mar 2011 18:54:00 GMT

This blog post was inspired by a question from a future student. Someone who was already booked for my SQL Server Internals class in June asked for some information on a current problem he was having with transaction log writes causing excessive wait times during index rebuild operations when run in ONLINE mode. He wanted to know if switching to BULK_LOGGED recovery could help.

I knew the difference between ALTER INDEX in FULL vs BULK_LOGGED recovery when doing normal OFFLINE rebuilds, but I wasn't sure about ONLINE. So I dug out some old scripts, massaged them a bit, and ran some tests.

A minimally logged operation is one that does not always log every single row. In FULL mode, SQL Server will log the entire page as each page is filled, so that we end up with individual log records of over 8K. In BULK_LOGGED mode, SQL Server just logs the fact that a new page was generated (and some information about the page) and doesn't log the full 8K. So the log space used is quite a bit less.

I will use Microsoft's AdventureWorks database that you can download from codeplex. I used the SQL Server 2005 version.

After making sure the database is set to FULL recovery, I make a copy of the Sales.SalesOrderDetail table (which I call Details) and build a clustered index on it. The table has about 1580 pages and 121,317 rows.

ALTER DATABASE AdventureWorks SET RECOVERY FULL
GO
USE AdventureWorks;
GO
IF EXISTS (SELECT 1 FROM sys.tables
            WHERE name = 'Details')
       DROP TABLE Details;


SELECT * INTO Details
FROM Sales.SalesOrderDetail;
GO

CREATE CLUSTERED INDEX CL_details_indx ON Details(SalesOrderID);
GO

I then build a table to store log information that I will gather after the different index rebuild operations. After that I back up the database to make sure I have a base for making log backups.

IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'AlterIndexLoggingData')
DROP TABLE AlterIndexLoggingData;
GO

CREATE TABLE AlterIndexLoggingData
(DBRecModel CHAR(12), IndexBuildMode CHAR(3), MaxLogRowSize INT, SumLogSize BIGINT, NumLogRows INT, NumLargeRows INT);
GO
BACKUP DATABASE AdventureWorks TO DISK = 'c:\backups\AW.bak' WITH INIT;
GO

Now starts the testing. There are four sections, each of which basically does the same thing, with the four permutations of FULL vs BULK_LOGGED recovery, and ONLINE vs OFFLINE index builds. Each section backs up the transaction log to truncate it, and then reports the number of rows in the log, just to verify we really have (logically) cleared it out. It then performs a rebuild of the index on the Details table. Finally, it captures the following information from the log using fn_dblog:

Max log record size
Sum of log record sizes
Number of log records
Number of log records larger than 8K

---- FULL Recovery, ONLINE = OFF

BACKUP LOG AdventureWorks TO DISK = 'c:\backups\AW_log.bak' WITH INIT;
SELECT COUNT(*) FROM fn_dblog(null, null);
GO
ALTER INDEX CL_details_indx ON Details REBUILD
GO

INSERT INTO AlterIndexLoggingData
SELECT 'FULL', 'OFF',
        MAX([Log Record Length]),
        SUM([Log Record Length]),
        COUNT(*),
        (SELECT COUNT(*) FROM fn_dblog(null, null) WHERE [Log Record Length] > 8000)
        FROM fn_dblog(null, null);
GO

--- FULL Recovery, ONLINE = ON

BACKUP LOG AdventureWorks TO DISK = 'c:\backups\AW_log.bak' WITH INIT;
SELECT COUNT(*) FROM fn_dblog(null, null);
GO

ALTER INDEX CL_details_indx ON Details REBUILD
WITH (ONLINE = ON)
GO

INSERT INTO AlterIndexLoggingData
SELECT 'FULL', 'ON',
        MAX([Log Record Length]),
        SUM([Log Record Length]),
        COUNT(*),
        (SELECT COUNT(*) FROM fn_dblog(null, null) WHERE [Log Record Length] > 8000)
        FROM fn_dblog(null, null);
GO
--SELECT * FROM AlterIndexLoggingData;
GO

--- BULK_LOGGED Recovery; ONLINE = OFF

ALTER DATABASE AdventureWorks SET RECOVERY BULK_LOGGED;
BACKUP LOG AdventureWorks TO DISK = 'c:\backups\AW_log.bak' WITH INIT;
SELECT COUNT(*) FROM fn_dblog(null, null);
GO

ALTER INDEX CL_details_indx ON Details REBUILD
GO

INSERT INTO AlterIndexLoggingData
SELECT 'BULK_LOGGED', 'OFF',
        MAX([Log Record Length]),
        SUM([Log Record Length]),
        COUNT(*),
        (SELECT COUNT(*) FROM fn_dblog(null, null) WHERE [Log Record Length] > 8000)
        FROM fn_dblog(null, null);
GO
--SELECT * FROM AlterIndexLoggingData;
GO

--- BULK_LOGGED Recovery, ONLINE = ON

BACKUP LOG AdventureWorks TO DISK = 'c:\backups\AW_log.bak' WITH INIT;
SELECT COUNT(*) FROM fn_dblog(null, null);
GO

ALTER INDEX CL_details_indx ON Details REBUILD WITH (ONLINE = ON)
GO

INSERT INTO AlterIndexLoggingData
SELECT 'BULK_LOGGED', 'ON',
        MAX([Log Record Length]),
        SUM([Log Record Length]),
        COUNT(*),
        (SELECT COUNT(*) FROM fn_dblog(null, null) WHERE [Log Record Length] > 8000)
        FROM fn_dblog(null, null);
GO
SELECT * FROM AlterIndexLoggingData;
GO

Here are my results:

Notice the following:

Only FULL recovery with an OFFLINE rebuild logged the 8K pages.
FULL recovery with an ONLINE rebuild actually did log every single row; in fact, it looks like at least two log records were generated for each row. There is a LOT of logging for this set of options and by far, the largest amount of log space used.
BULK_LOGGED mode used substantially less log space than FULL, but the difference between ONLINE and OFFLINE is even more pronounced. In FULL recovery, ONLINE used about twice the log space, in BULK_LOGGED, the logging required more than 8 times the space!

I'm sure there are more fun facts you can pull out of the results from fn_dblog, but for my first look at the logging differences between ONLINE and OFFLINE index rebuilds, this is what I wanted to know. It's not a complete answer to the student's question, but it does indicate there is a lot more logging with ONLINE index builds, so it should not be surprising if there are a lot more log write waits. And I would assume that switch to BULK_LOGGED recovery could make a noticeable difference.

I hope this is useful!

~Kalen

Point-in-time restore of database backup?

TiborKaraszi — Tue, 28 Dec 2010 16:42:00 GMT

SQL Server 2005 added the STOPAT option for the RESTORE DATABASE command. This sounds great - we can stop at some point in time during the database backup process was running! Or? No, we can't. Here follows some tech stuff why not, and then what the option is really meant for:

A database backup includes all used extents and also all log records that were produced while the backup process was running (possibly older as well, to handle open transactions). When you restore such a backup, SQL Server will from backup media copy the data pages to mdf and ndf file(s), log records to ldf file(s) and then do REDO (roll forward the log records) and possibly also UNDO (rollback open transactions - this is skipped if you specify NORECOVERY option). Why does it need the log records? Because you can do modifications in the database while the database backup is running. After backup process was started (and before it finishes) you can both modify pages not yet copied to backup media, but also pages already copied to backup media. So, the data pages in themselves do not present a consistent state of the database. At restore time, SQL Server uses the log records included in the database backup to "sync" the modifications that were performed while the backup process were running. So, what you end up with is what the database looked like at the end time of the backup process. I'm sure this is well documented somewhere in Books Online so I won't dive further into the tech stuff here.

So, what is the purpose of the STOPAT option for RESTORE DATABASE?

It is to give you an early warning, in case you try to restore a database backup which is too late for a subsequent STOPAT operation for a log restore. Here's an example:

13:00 db backup
13:10 log backup
13:20 log backup
13:30 db backup
13:40 log backup
13:50 log backup

Say you now want to restore to 13:15. Intuitively, you say that you will restore 1, 2 and for 3 you do STOPAT 13:15. Yes, that is right.

But say you are under stress, and perhaps even used some tool which confuses things for you? So, you end up restore 4, 5 and for 6 you try STOPAT 13:15. Clearly, you can't stopat 13:15 for backup number 6 (it only covers 13:40 to 13:50). I.e., restore of 6 gives you an error message. So, you have to re-start the restore process (1, 2 and 3), which could take a long time (if the backups are large). If you had specified STOPAT 13:15 when you restore backup 4, SQL Server would have given you an error immediately, so you wouldn't have wasted valuable time restoring unnecessary backups!

That is the purpose of STOPAT for RESTORE DATABASE.

Why we never want to trick SQL Server into rebuilding a log file

TiborKaraszi — Thu, 14 Oct 2010 17:04:00 GMT

"Never" is a strong word, so let me say that we really really want to avoid it, if at all humanly possible. In short, we will have a (potentially) broken database, both at the physical level and at the logical level. Read on.

Just to be clear, when I refer to a "log file" here, I'm talking about a transaction log file, an .ldf file. Ever so often we see in forums how log files are "lost", "disappears" or so. Now, SQL Server relies on the ldf file at startup to bring the database to a consistent state. This is known as "recovery", or "startup recovery". This is pretty well known, I for instance teach this at every intro level admin course. So, what if

The ldf file isn't there?
Or isn't accessible to the db engine?
Or is broken somehow?
Or is from a different point in time from the data file? (You'd be surprised to all the things one see over the years.)

Well, SQL Server will do the only reasonable, refuse us into the database and produce an error message (in eventlog etc).

What we see from time to time, is trying to "trick" SQL Server into re-creating an ldf file. So, why is this so bad? I will try to explain why. Let me first say that SQL Server doesn't do these things to be mean to us, or to prove a point. If SQL Server know that the ldf file is not necessary for recovery (the database was "cleanly shutdown"), then it can and will re-create a log file for us at startup. The problem is that it isn't these cases we see in forum. The cases we see in the forums is when this didn't happen. SQL Server relied on the ldf file in order to bring the database to a consistent state.

Enough beating around the bush, here is an example of why we don't want to trick SQL Server to forcefully re-create a log file:

Say you have a transaction in which you add a row to the order header table (oh) and five rows to the order details table (od). Physically, each command is reflected in the tlog, the page is modified (but still cached) and at commit, the log records are written to the ldf file. There are lots written about these algorithms, but the concepts are pretty simple. For more details, read this.

Say that we forcefully stop SQL Server, delete the ldf file, start SQL Server, see that the database isn't accessible and somehow "trick" SQL Server into creating an ldf file for us. What is the problem? Why is this so bad? The problem is that you can have no confidence in the state of your data, both at the physical level and at the logical level. Let me start explaining what I mean by the logical level and problems at this level:

The logical level
By the logical level, I consider the user data. The rows in the tables, quite simply. We inserted one row in the oh table and five rows in the od table. These can be inserted into a total of two pages or 6 pages (in reality more, since each table is likely to have indexes etc, I'll touch on this when we get to the logical level). Say that three of order details rows have been written to disk, but not the order header row, and not the other two order details rows. This is just an example; you can pick and choose any combo you want. It can get just as bad as you can imagine! You might think that the pages are in the end written in the same sequence as we modified them. No, that is now how the cookie crumbles (read the article I posted link to). You can probably imagine other scenarios, closer to your data. For instance, we will not do some certain modification to a table unless some other prior modification was also performed. In essence, rebuilding a log file leave us with no confidence in the data. Are you prepared to go through your database and manually verify all the data in there? Majority of you will say "no", and it might not even be doable (data volumes, lack of information to verify etc). So, logical inconsistencies are bad. Really bad. We don't want this. Ok?

The physical level
This log is not only used for "user-data". It i also used for system data. Tables has indexes, where each row in a table is reflected by a row in each index. We have allocation pages stating what storage is used. We have IAM pages and PFS pages. We have linked lists. And whatnot. You probably realize that these structures also require that some modification is performed in full or not at all. (an extent allocation will be reflected in both the GAM or SGAM page and also in th extent data itself, etc). What do you think is used to protect this? Yes, you got it, the ldf file. Trick SQL Server into re-creating an ldf file and you have all sorts of physical inconsistencies. Actually, physical inconsistencies are a bit better than logical since we do have a way to check for these. I'm of course talking about the mighty DBCC CHECKDB command, a command with lots of smarts (right Paul?) to check that the db is consistent at the physical level. And what if it isn't? CHECKDB spits out errors. Sure it has repair options, but those generally mean (unless you are the type of person who wins are lotteries) that the repair will rip out whatever makes the db inconsistent resulting in data loss (which also has no knowledge of what you might consider logical consistent data).

So, what to do?
I hope the answer is obvious. Don't get into this situation in the first place. Don't go deleting ldf files for instance. Do take backups frequently enough so you don't end up in a situation like "I need to rebuild the log or I'm toast.". If something strange happens, don't start mucking about with the system unless you know what you are doing. Hire a specialist, call MS support or so. If I had a brand new Ferrari, I wouldn't disassemble the engine in case I hear weird noise from the it. Heck, I barely pop the hood of my Beamer!

And no, I won't get into what commands can be used to re-build the ldf file. I expect all readers of this to not get into a situation where it is needed. :-)

(I understand one can encounter a machine with no backups and something happens to the ldf file, and such scnearios. Of course I do. I feel really really bad every time I read about such a situation, because there is no graceful way to handle it. That is why I have such a harsh tone above. I don't want this to happen to anybody. One would hope that this is obvious, but nothing ever is on the Net. So I've learned. So, please leave such unconstructive comments out of the discussions!)

Restore database to the point of disaster

TiborKaraszi — Sat, 27 Mar 2010 09:50:00 GMT

This is really basic, but so often overlooked and misunderstood. Basically, we have a database, and something goes south. Can we restore all the way up to that point? I.e., even if the last backup (db or log) is earlier than the disaster?
Yes, of course we can (unless for more extreme cases, read on), but many don't realize/do that, for some strange reason.

This blog post was inspired from a thread in the MSDN forums, which exposed just this misunderstanding. Basically the scenario was that they do db backup and only log backup once a day. Now, doing log backup that infrequent is of course a bit weird, but that is beside the point. The point is that you can recover all the way up to the point of disaster. Of course, it depends on what the disaster is (don't expect too much if the planet blows up, for instance).

Since "log backup only once a day" was mentioned, I will first elaborate a bit on frequency for database vs log backups. For the sake of discussion, say we do both db and log backup once a day. You say:
"What? Both db backup and log backup once a day - why would anybody do that way? Wouldn't one do log backup more frequently than db backup?"
Yes, of course (but I actually see such weird implementations from time to time). But again, that doesn't change the topic at hand, but I will first elaborate on this; just so we don't see blurring comments later arguing this irrelevant argument.

So, lets first sort out two different cases:

A) Log backup before the db backup
1: db backup
...
2: log backup
3: db backup
crash
Here we will use backup 3 when we later will restore.

B) Db backup before log backup
1: db backup
...
2: db backup
3: log backup
crash
Here we will use backup 2 and 3 when we later will restore.

You see that A) and B) are really the same thing? What is relevant is that we have all log records available (in ldf file/log backups) since the db backup we chose to use as starting point for the restore. Actually, for A), we could might as well use backup 1 and 2 (and skip 3)!

"Hang on", you say, "we're not done yet. What about the modifications since the last log backup! Gotcha!"
No worries, this is where it gets interesting, and below is really the heart of the topic. Clearly, we need to get the log records out of the ldf file into a log backup (file). If we can do that, then we will call this backup number 4, and use as the last backup for our restore. After doing that restore, we have no data loss!

So, how do we produce a log backup after a disaster?
It depends on the disaster! Let's discuss a few scenarios:

a) Planet Earth blows up.
No can do. I doubt that anyone of you has mirrored data centers on Moon or March; and also people stationed off-Earth for these situations. Of course, I'm being silly. But my point is that you can always have a disaster such that you can't produce that last log backup. No matter how much you mirror: if the disaster takes out all mirrors, then you are toast. Remember that when you talk SLA's. That fact is not popular, but it can't be argued. It is all about limiting the risk exposure - not eliminating it. Anybody who believes we can eliminate risk exposure is dreaming. Agreed? Good. Let's move on to (hopefully) more realistic scenarios:

b) Something happens with the data file(s), lost, corrupt or so. Ldf file is still there.
This is the easy case, but so often overlooked. What you do now is to backup the log of the damaged database, using the NO_TRUNCATE option. Something like:
BACKUP LOG dbname TO DISK = 'C:\dbname.trn' WITH NO_TRUNCATE
Yes, it really is that simple. Then restore backups from above, including this last log backup. Don't believe me? Test it.

Create database and table
Insert some data
Do db backup (1)
Insert some more data
Do log backup (2)
Insert some more data
Stop SQL Server
Delete mdf file
Start SQL Server
Do log backup using NO_TRUNCATE (3)
Restore 1, 2 and 3.

c) Something happens with the database. Ldf file is NOT still there.
Clearly, if the ldf file is really gone, we can't do a log backup - how much as we might want to. Remember the old days, when redundancy for disks (RAID) wasn't as common as today? "If there's anywhere you want redundancy, it is for the transaction log files!"

d) Something happens with the data file(s), lost, corrupt or so. Ldf file is still there. The installation is toast - we can't start SQL Server.
This seems a bit more nerve-wracking, right? Not to worry, just do the right steps and you will be fine. You probably ask now:

"But how can we backup the transaction log when our SQL Server won't start?"

That is a good question. You need to get that ldf file to a healthy SQL Server, and make SQL Server believe this is the ldf file for a broken database on that instance. It is not really complicated. Just use a dummy database on that SQL Server as intermediate - to get the right meta-data into that SQL Server, so in turn it will allow you to produce this last log backup. I will show just that:

I have two instances on my machine (named "a" and "b"). I will create and damage a database on instance a, and then produce a log backup for that orphaned ldf file a different instance, b. I will pretend these are on two different machines, using separate folders for the database files "C:\a" and "C:\b". Here's the T-SQL, starting with instance a:

IF DB_ID('x') IS NOT NULL DROP DATABASE x 
GO 
CREATE DATABASE x 
ON PRIMARY  
(NAME = N'x', FILENAME = N'C:\a\x.mdf', SIZE = 10MB, FILEGROWTH = 10MB) 
LOG ON  
(NAME = N'x_log', FILENAME = N'C:\a\x_log.ldf', SIZE = 5MB, FILEGROWTH = 5MB) 
GO 
ALTER DATABASE x SET RECOVERY FULL 
CREATE TABLE x.dbo.t(c1 INT IDENTITY) 
INSERT INTO x.dbo.t DEFAULT VALUES --1 
BACKUP DATABASE x TO DISK = 'C:\x.bak' WITH INIT 
INSERT INTO x.dbo.t DEFAULT VALUES --2 
BACKUP LOG x TO DISK = 'C:\x1.trn' WITH INIT 
INSERT INTO x.dbo.t DEFAULT VALUES --3 
SELECT * FROM x.dbo.t 

--Stop SQL Server and delete below file 
--C:\a\x.mdf 
--Start SQL Server 

--Oops, damaged database...: 
SELECT * FROM x.dbo.t 

--Stop SQL Server, pretend installation is toast

Do we agree that we have a damaged database, and there has been done modifications since the last log backup? Ok, fine. We now pretend that SQL Server instance "a" doesn't start anymore. So, I will try to produce a log backup from that ldf file on instance "b":

IF DB_ID('x2') IS NOT NULL DROP DATABASE x2 
IF DB_ID('x') IS NOT NULL DROP DATABASE x 
GO 
CREATE DATABASE x2 
ON PRIMARY  
(NAME = N'x2', FILENAME = N'C:\b\x2.mdf', SIZE = 9MB, FILEGROWTH = 8MB) 
LOG ON  
(NAME = N'x2_log', FILENAME = N'C:\b\x2_log.ldf', SIZE = 6MB, FILEGROWTH = 7MB) 
GO 

--Stop SQL Server and delete below files 
--C:\b\x2.mdf 
--C:\b\x2_log.ldf 

--Copy the C:\a\x_log.ldf to C:\b\x2_log.ldf 

--Start SQL Server 

--Produce our last log backup: 
BACKUP LOG x2 TO DISK = 'C:\x2.trn' WITH INIT, NO_TRUNCATE 

--Restore the database, up to last transaction. 
--Investigate logical file names for MOVE options first: 
RESTORE FILELISTONLY FROM DISK = 'C:\x.bak'  

RESTORE DATABASE x FROM DISK = 'C:\x.bak'  
WITH 
 NORECOVERY 
,MOVE 'x' TO 'C:\b\x.mdf' 
,MOVE 'x_log' TO 'C:\b\x_log.ldf' 

RESTORE LOG x FROM DISK = 'C:\x1.trn' WITH NORECOVERY 
RESTORE LOG x FROM DISK = 'C:\x2.trn' WITH RECOVERY 

--Data there? 
SELECT * FROM x.dbo.t 
--See? That wasn't so difficult.

Note how I even named the dummy database differently on instance b, with different physical file names and different file sizes (all compared to what we had on instance a). Typically, you will use same database name and same filename, but I want to show that we don't really have to know a whole lot about the damaged database in order to produce a log backup from the ldf file!

Case closed.

Are log records removed from ldf file for rollbacks?

TiborKaraszi — Mon, 22 Mar 2010 09:34:00 GMT

Seems like a simple enough question, right? This question (but more targeted, read on) was raised in an MCT forum. While the discussion was on-going and and I tried to come up with answers, I realized that this question are really several questions. First, what is a rollback? I can see three different types of rollbacks (there might be more, of course):

Regular rollback, as in ROLLBACK TRAN (or lost/terminated connection)
Rollback done by restore recovery. I.e., end-time of backup included some transaciton which wasn't committed and you restore using RECOVERY, so SQL Server need to rollback this transaction (UNDO).
Rollback done by crash (restart) recovery. I.e. SQL Server was shut down with some open transaction.

I'm going to try to show whether log records are removed or still present for these three types of rollback situations. I will use the fn_dblog function. This isn't documented or supported, but search the internet and you will find how to use it. The result isn't documented either, of course, so we have to guess a bit what the various values mean...

The TSQL script has some common parts (the same part executed repeatedly, once for each test):

Create a database
Make sure it is in full recovery
Do a database backup
Create a table (myTable)
Insert a row into myTable. This last operation generates 5 log records for the myTable table: one for the PFS page, two for IAM pages, one format page for the heap and the last one is a LOP_INSERT_ROWS.
Start a transaction
Insert one more row into myTable. We now have one more log record for myTable (LOP_INSERT_ROWS). Looking at the transaction id for this last insert, we see two log records (one LOP_BEGIN_XACT and the LOP_INSERT_ROWS). Note that this transaction is now open!

Here is the above mentioned first part of the script:

USE master 
IF DB_ID('x') IS NOT NULL DROP DATABASE x 
GO 
CREATE DATABASE x 
GO 
ALTER DATABASE x SET RECOVERY FULL 
BACKUP DATABASE x TO DISK = 'C:\x.bak' WITH INIT 

USE x 
CREATE TABLE myTable(c1 INT IDENTITY) 
INSERT INTO myTable DEFAULT VALUES 

SELECT * FROM fn_dblog(NULL, NULL) WHERE AllocUnitName LIKE '%myTable%' 
--5 rows 

BEGIN TRAN 
INSERT INTO myTable DEFAULT VALUES 

SELECT * FROM fn_dblog(NULL, NULL) WHERE AllocUnitName LIKE '%myTable%' 
--6 rows 

SELECT * FROM fn_dblog(NULL, NULL)  
WHERE [Transaction ID] = (SELECT TOP(1) [Transaction ID]  
                          FROM fn_dblog(NULL, NULL) 
                          WHERE AllocUnitName LIKE '%myTable%' 
                          ORDER BY [Current LSN] DESC) 
--2 rows

Now, on to the different cases:

1. Regular rollback, as in ROLLBACK TRAN.
It seems reasonable to me that SQL Server will just add some "rollback transaction" log record here. So, let's try that (continuing on above first part)... We now have 7 log records for myTable, with an added LOP_DELETE_ROWS which undo the previously insert. And for our transaction ID, we have 4 rows, with added two rows being LOP_DELETE_ROWS (compensation log record) and a LOP_ABORT_XACT.

--Regular ROLLBACK 
ROLLBACK TRAN 

SELECT * FROM fn_dblog(NULL, NULL) WHERE AllocUnitName LIKE '%myTable%' 
--7 rows 

SELECT * FROM fn_dblog(NULL, NULL)  
WHERE [Transaction ID] = (SELECT TOP(1) [Transaction ID]  
                          FROM fn_dblog(NULL, NULL) 
                          WHERE AllocUnitName LIKE '%myTable%' 
                          ORDER BY [Current LSN] DESC) 
--4 rows

2. Restore recovery
Now, what does this really mean? Restoring a transaction log backup is a good example. SQL Server read log records from the transaction log backup file and writes them into the LDF file. This is what we call the "data copy" phase. Then SQL Server performs REDO (a.k.a. roll forward). And finally, SQL Server performs UNDO (roll back), assuming we don't do the restore using the NORECOVERY option. Restoring from a database backup isn't any differene except the log records are of course read from the database backup file.

Here it seems likely that SQL Server will wipe more or less anything from the LDF file as soon as the database is restored and brought on-line. Why? The log in this newly restored database can't serve as a starting point for a restore operation for this database. You first need a database backup. So, no use hanging onto log records either! Let's see if we can verify that:

--Restore recovery 

--Do this from different connection 
BACKUP LOG x TO DISK = 'C:\x.bak' 
--We now have open transaction in database! 

--Perform RESTORE 
ROLLBACK 
USE master 
RESTORE DATABASE x FROM DISK = 'C:\x.bak' WITH REPLACE, NORECOVERY 
RESTORE LOG x FROM DISK = 'C:\x.bak' WITH FILE = 2, RECOVERY 

--Rollback was done, and database berought online.  
--What log records do we have? 

SELECT * FROM fn_dblog(NULL, NULL) WHERE AllocUnitName LIKE '%myTable%' 
--0 rows 

--Use the transaction ID from earlier SELECT 
SELECT * FROM fn_dblog(NULL, NULL)  
WHERE [Transaction ID] = '0000:00000203' 
--0 rows

So, SQL Server will remove user-defined stuff from LDF file after restore recovery was performed. Makes sense.

3. Crash recovery (a.k.a. restart or startup recovery)
I couldn't really guess here. So, lets give it a spin immediately and see:

--Crash recovery 

--Do below from separate connection 
SHUTDOWN WITH NOWAIT 

-- startup SQL Server and examine the log records: 

SELECT * FROM fn_dblog(NULL, NULL) WHERE AllocUnitName LIKE '%myTable%' 
--5 rows 

--Use the transaction ID from earlier SELECT 
SELECT * FROM fn_dblog(NULL, NULL)  
WHERE [Transaction ID] = '0000:00000204' 
--0 rows

Hmm, so SQL Server removes the log record after the rollback was performed. Makes sense.

But this got me thinking some more. How can this be done... physically? SQL Server would just "reverse" the head of the log a bit. But what if we have later transactions for other connections, which has been committed? SQL Server can't ignore those, of course. These need to be kept in the LDF file for subsequent log backups. OTOH, I doubt that SQL Server will somehow physically delete things "in the middle" of an ldf file. Time for yet another test:

--Crash recovery, part two 

--Do below from separate connection 
--First something which creates more recent log records 
CREATE TABLE y(c1 INT) INSERT INTO y(c1) VALUES(1) 
SHUTDOWN WITH NOWAIT 

-- startup SQL Server and examine the log records: 

SELECT * FROM fn_dblog(NULL, NULL) WHERE AllocUnitName LIKE '%myTable%' 
--7 rows 

--Use the transaction ID from earlier SELECT 
SELECT * FROM fn_dblog(NULL, NULL)  
WHERE [Transaction ID] = (SELECT TOP(1) [Transaction ID]  
                          FROM fn_dblog(NULL, NULL) 
                          WHERE AllocUnitName LIKE '%myTable%' 
                          ORDER BY [Current LSN] DESC) 
--4 rows

Now the log records for our rolled back transaction are still there! So, just as when we did a regular rollback, SQL Server inserted a LOP_DELETE_ROWS to reflect the undo of the INSERT, and then a LOP_ABORT_XACT.

Conclusion
Isn't it beautiful when it all makes sense? Here are my conclusions, whether log records are kept or removed from transaction log file (LDF) for various types of rollback scenarios:

Regular rollback. Log records are not removed. Compensation log records are logged, reflecting undo of the modifications, and then an LOP_ABORT_XACT is logged.
Restore recovery. Log records are removed.
Crash recovery. It depdends. If you have a transaction which is at the very head of the log, then those log records can be removed. If there are other, subsequent committed transactions, then compensation log records are logged, reflecting undo of the modifications, and then a LOP_ABORT_XACT is logged.

Geek City: Exploring the Transaction Log Structure

Kalen Delaney — Tue, 22 Dec 2009 00:48:00 GMT

As I mentioned last October, my PASS preconference seminar was a whole day about the transaction log. I told the attendees all kinds of things about the structure of the log, and told them about one of the best tools for exploring the structure: the command DBCC LOGINFO. This command is officially undocumented, but numerous bloggers have discussed it in some detail, including here. I'm not going to go into all the details here, but I'll be writing some related blog posts over the next month or so. To understand DBCC LOGINFO you need to know about VLFs (Virtual Log Files) and one of the best places to read about VLFs is in Paul Randal's TechNet article. The DBCC LOGINFO command returns one row per VLF including the following columns:

FileId

If you only have one physical log file, this value will be the same in every row. If you have multiple log files, you can use this value to explore the order that the SQL Server will fill up VLFs in multiple files. (More details in a future post.)

FileSize

This value is in bytes. If you have your log file growth set to a percentage (which, unfortunately is the default), you should notice this value getting progressively larger as more VLFs are added to your log file.

StartOffset

This value is also in bytes, and is used as the sort column for the output. That is, the output from DBCC LOGINFO (if you only have single physical file) will be ordered by StartOffset. Notice that the first VLF always starts at offset 8192, which is the number of bytes in a page. The first page of the physical file is the file header, and does not store log records.

FSeqNo

The File Sequence Number indicates the order of usage of the VLFs. The row with the highest FSeqNo value is the VLF where current log records are being written. Notice that the FSeqNo values are not always consistent with the StartOffset, as VLFs are reusable after the log has been truncate. I'll discuss this value more in a future post, or you can get some info from Paul's article on how and when a VLF is reused. A value of 0 for the FSeqNo means that this VLF has never been used at all yet.

Status

Status has two possible values: 0 and 2. I'll be discussing these values in a lot more detail later. For now, the simple definition is that a value of 2 means the VLF is not reusable and a value of 0 means it is.

Parity

Parity has two possible values: 64 and 128. Every time a VLF is reused, the parity value is switched. Paul Randal discusses the parity value in more detail in this article.

CreateLSN

This value indicates when the VLF was created. If the CreateLSN is 0, it means the VLF was original equipment, created when the database was created. You can use this value to see how many VLFs were added each time the log grew. VLFs with the same CreateLSN value were created at the same time.

The whole point of describing all these columns was just to provide a lead-in to my little holiday gift to you. Here is a table that can be created in the master database, and then populated with the output of DBCC LOGINFO. The name of the table starts with sp_, which means you can use it from any database. Once you have captured all the rows from DBCC LOGINFO, you can query them using any TSQL queries.

Here the table creation script:

USE master
GO
IF EXISTS (SELECT 1 FROM sys.tables
WHERE name = 'sp_LOGINFO')
DROP TABLE sp_loginfo;
GO
CREATE TABLE sp_LOGINFO
(FileId tinyint,
FileSize bigint,
StartOffset bigint,
FSeqNo int,
Status tinyint,
Parity tinyint,
CreateLSN numeric(25,0) );
GO

And here is how to populate it. I will use the AdventureWorks database for the example:

USE AdventureWorks;
GO

TRUNCATE TABLE sp_LOGINFO;
INSERT INTO sp_LOGINFO
EXEC ('DBCC LOGINFO');

You can select the VLFs from most recently used to the least:

SELECT * FROM sp_LOGINFO
ORDER BY FSeqNo DESC;

You can count how many VLFs have each status value:

SELECT status, COUNT(*) FROM sp_LOGINFO
GROUP BY status;

If you don't have a lot of VLFs, you can try to generate more by running SELECT INTO repeatedly. Make sure you are in FULL recovery to get the most log growth:

SELECT * INTO dbo.Orders FROM AdventureWorks.Sales.SalesOrderDetail;
DROP TABLE dbo.Orders;
GO 5

TRUNCATE TABLE sp_LOGINFO;
INSERT INTO sp_LOGINFO
EXEC ('DBCC LOGINFO');

SELECT status, COUNT(*) FROM sp_LOGINFO
GROUP BY status WITH ROLLUP;

You may be able to figure out other queries that provide useful or interesting information for you. I'll refer back to this table in my future discussions on DBCC LOGINFO and VLFs.

Enjoy!

~Kalen