Archive for May, 2012

Reading the Transaction Log Using fn_dblog

May 25, 2012 3 comments

One of my favorite sections of the SQLskills Internals Immersion Event was logging and recovery. I've enjoyed internals since I started working as a COBOL programmer. Figuring out what SQL Server is doing under the covers makes me happy.

We covered a number of methods of interrogating the transaction log, and I have been working to put them together. In my next few blog posts, I will cover two of them and detail the challenges I faced joining the results.

I was familiar with the table-based function fn_dblog from reading SQL Server 2008 Internals. It takes two optional parameters, start and end log sequence number (lsn). Running this code:


will return the entire contents of the transaction log. Replacing the NULLs with an lsn will limit the result set. That query returns 116 columns, which is much more information than I need. Adding the following select list returned the data I was interested in:

SPID,                         -- The process ID for the transaction
[Begin Time],                 -- The start time for the transaction
[Current LSN],                -- The lsn of this record            
[Previous LSN],               -- Previous lsn for this record
Operation,                    -- The operation performed by the log record
Context,                      -- The resource affected by the operation
[Log Reserve],                -- The space reserved for rollback in bytes
AllocUnitName,                -- The name a the affected allocation unit
[Page ID],                    -- The page ID of the affected page
[Number of Locks],            -- The number of locks held by the transaction
[Lock Information]            -- Information about the locks held and the resources locked
FROM fn_dblog(NULL, NULL) 

To generate the result set below, I ran a CHECKPOINT on a database in SIMPLE recovery model to clear out the transaction log. I then ran the following query:

INSERT INTO loginsert

I've split the results into two parts. The first set contains everything up to the lock information:

fn_dblog results part one


You can see that SPID and Begin Time are only be populated for a BEGIN TRANSACTION (LOP_BEGIN_XACT in the log). The LOP_INSERT_ROWS operation shows the table inserted to and the page affected.

Finally, the Lock Information column demonstrates SQL Server's lock escalation behavior. In the second half of the result set:

fn_dblog result part two


you can see SQL Server acquiring three locks. First, an intent exclusive lock is taken on a table. Second, an intent exclusive lock on a page. Finally, an exclusive lock is taken on a key. I thought this was a very cool representation of lock escalation.

My next posts will cover the following topics:

  • Using sys.dm_tran_database_transactions to see what transactions are executing on the system
  • More information than you thought possible about convert lsns
  • Joining those results to fn_dblog to the log records associated with each active transaction

A SQL Weekend – SQL Friends and SQL Saturday #119

May 22, 2012 4 comments

I had the pleasure of spending the weekend with some of the best and brightest folks in the SQL Server world. Friday I attended the second SQLFriends lunch with Aaron Lowe (b|t), Jes Borland (b|t), Ted Krueger (b|t) and a number of other great people. It was three hours of good food and better conversation. Any opportunity to spend time with Jes is welcome, as her energy is infectious. I had not met Ted Krueger before but it was a privilege to talk to him at the lunch and throughout the weekend. Many thanks to Aaron for organizing the event.

After lunch, I joined Aaron, Ted and others at DeVry University in Addison to help set up for SQL Saturday #119. After several hours of stuffing bags, I was off to the speaker dinner at Dave & Buster's. It was a wonderful evening of conversation and camaraderie with the likes of Jes, Ted, Aaron, Brent Ozar (b|t), Tim Ford (b|t), Ben DeBow (b|t), Allan Hirt (b|t) and Bill Lescher (t). It was another reminder of the strength of the SQL community. The willingness of these experts to share their time and knowledge is really inspiring.

SQL Saturday 119 First Session 085
David Klee and Andy Galbraith on Virtualizing SQL Server

I was back at DeVry at 8 a.m. Saturday, having lost an epic battle with my snooze bar. By that point, other volunteers had registration under control so I spent some time catching up with people before the first session. It was Virtualizing Business Critical SQL Servers by David Klee (b|t) and Andy Galbraith (b|t) of House of Brick Technologies. Andy and David were in the SQLskills Immersion Event with me in April. Also, I worked with David and House of Brick on a VMWare proof of concept last month, so David asked me to talk about my experience.

For the next session, I planned on seeing Ted Krueger present on Query Tuning for Developers and DBAs. By the time I made it to the room it was standing-room-only. Instead, I went to hear Christina Leo (b|t) present on What's Buried in the Plan Cache. Christina connected a number of concepts I was aware of, providing some very powerful tools for investigating and troubleshooting issues. After the session, I talked to Christina about the upcoming SQL Saturday she is running in Nashville October 13. You can find information about all of the upcoming SQL Saturdays here.

SQL Saturday 119 Fourth Session 014
Ted Krueger Talking About Installing SQL Server

After lunch, I went to the computer lab. To thank DeVry for donating its campus for SQL Saturday Chicago the past two years, three sessions were scheduled for DeVry students and alumni. Ted Krueger presented on installing SQL Server and the components of SQL Server (Analysis Services, Intergration Services and Reporting Services) and I talked about building your first database. I arrived in time to hear the second session while making sure everything was set for my presentation.

SQL Saturday 119 Fifth Session 146
Me Talking About ACID Properties

I got started around 4:15 p.m. and spoke for about an hour. I think I did alright, but I was so fried by the end of the day I don't remember much. It was my first time presenting on SQL Server outside of work, and I am glad to have that under my belt. I am already looking at upcoming SQL Saturdays in the area with the idea of submitting abstracts.

I've come a long way since attending my first SQL Saturday last year. I am really happy to give back to the community that helped me get here.

What Not to Do When Using GO with a Count

Recently, I learned you can put a GO statement after a block of SQL code followed by a count. This will execute the code count number of times. It is a handy way to quickly replace a WHILE loop.

I was running a test this morning, needing to insert data into a table and begin an explicit transaction. To do this, I executed the following code (don't try this at home):

GO 1000000

What I didn't consider is all of the statements above the GO would be executed 1 million times, including the BEGIN TRANSACTION. I figured it out when I tried to run a SELECT against the temp table and it hung. I went back to the INSERT statement, issued a commit and the SELECT was still hung. After about 5 more COMMIT statements, I realized each execution of the GO opened an explicit transaction. Thankfully, the following statement solved my problem:

GO 1000000

And that is one to grow on.

Categories: SQL Server, T-SQL Tags: ,