Reading Transaction Log Backups

Like it a business, where advances in technology fuel greater efficiencies, advances in fishing technology make for greater fishing opportunities. Just look at all the different ways to rig live bait for examples. My preferred method for fishing close to, but not right on, the bottom is a Lindy Rig. This rig is great when there isn’t much vegetation – it has a small bobber between a weight and the hook which lifts the bait off the bottom. The weight is usually a walking slip sinker weight, which has a large hole for the line to flow through so it doesn’t get hung up. The rig’s made so the fish can grab the bait and swim away with it without feeling any resistance. Then when the fish stops to eat the bait, you set the hook. It’s great fun to sit there with your bail open and a finger on the line, trying to feel the fish make some initial nibble on the bait before grabbing it and running. Next comes the excitement as line peels off your reel while the fish runs. Lastly, once it stops you reel in steadily until you feel the weight of the fish on the end, then set the hook sharply. Using a Lindy Rig is a fun and exciting way to fish with live bait.
Last week we ran into a situation where some user had changed a large amount of data in our OMS – but we didn’t know who or why. We weren’t even sure if the changes had been made through the OMS UI or via a direct database insert. Obviously having change tracking turned on who had made the investigation easier, but that doesn’t help you after the fact. The change had taken place the previous day, so it was my job to try to figure out who made it. For this, my only option was to read the Transaction Log Backups. There is an undocumented system stored procedure in SQL Server called fn_dump_dblog which allow you to get some basic information from the transaction log. Calling the function is a bit interesting – the first two parameters are the starting and ending LSN, which can usually just be left NULL to read the whole log backup. The third paramter is either DISK or TAPE indicating where your backup is stored. The fourth parameter is the backup number within the file. For most of us this will be 1 since we’re not using multi-backup media sets. Lastly, the file path and name is the fifth parameter. After that there are 63 nonoptional DEFAULT parameters! So when you do an initial call, it will look like this:

SELECT *
FROM fn_dump_dblog (NULL, NULL, N'DISK', 1, N'\\NASServer\SQLBackup\INSTANCEServer\Database\LOG\InstanceServer_database_LOG_20180306_120001.trn',
                    DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
                    DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
                    DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
                    DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
                    DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)

For my particular issue, I needed to find all INSERTS into a specific table. I used the fn_dump_dblog function in a common table expression and filtered the operation on inserts and the PartitionId on the table I was interested in. Then I joined that CTE to another SELECT from the function on the Transaction ID and filtered that table on the LOP_BEGIN_XACT operation to get when the INSERT began. I was able to use the SUSER_SNAME function on the Transaction SID to reveal the database user who had made the INSERTs:

WITH CTE
AS
       (SELECT [Transaction ID], COUNT(*) AS InsertedRows
       FROM fn_dump_dblog (NULL, NULL, N'DISK', 1, N'\\NASServer\SQLBackup\InstanceServer\DatabaseName\LOG\InstanceServer_DatabaseName_LOG_20180306_120001.trn',
       DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
       DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
       DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
       DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
       DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)
       WHERE Operation = ('LOP_INSERT_ROWS')
       AND [PartitionId] IN (SELECT sp.partition_id
                            FROM DatabaseName.sys.objects so
                            INNER JOIN DatabaseName.sys.partitions sp ON so.OBJECT_ID = sp.OBJECT_ID
                            WHERE name = 'InsertedOnTable'
                           AND sp.index_id = 1)
       GROUP BY [Transaction ID]
       )
SELECT [Current LSN], a.[Transaction ID], [Transaction Name], [Operation], [Begin Time], SUSER_SNAME([TRANSACTION SID]) AS LoginName, CTE.InsertedRows
FROM fn_dump_dblog (NULL, NULL, N'DISK', 1, N'\\NASServer\SQLBackup\InstanceServer\DatabaseName\LOG\InstanceServer_DatabaseName_LOG_20180306_120001.trn',
   DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
   DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
   DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
   DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
   DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT) AS a
INNER JOIN cte ON a.[Transaction ID] = cte.[Transaction ID]
WHERE Operation = ('LOP_BEGIN_XACT')

20180315 Query Tlog Backup
Change tracking would give more detail and be easier to query, but at least for now I was able to see that the user making the change was the login that the application uses to connect to the database, so I was able to see that the change was indeed made through the UI.

Advertisements

Documentation – The ‘Hit-by-a-Bus’ Handbook

A huge part of success in fishing is preparation. Without careful preparation fishing will be slow, you can lose or break equipment, and you can even put yourself in danger. Several of my previous posts have detailed how preparing by looking at weather, depths, fishing reports, etc. can improve your fishing “luck”. Another part of preparation is inspecting your fishing equipment before use. I can remember at least three times where I’ve casted my lure right off the end of my line. It’s great to see a bomb cast really sail out there, but it’s not so great the moment you realize it’s no longer attached to the end of your line. Lastly, let’s not forget the thought of unhooking that deep hookset on a northern pike when you neglected to make sure your pliers was in your tackle box.
I was considering preparation this week after I read this great post by Monica Rathbun on how to survive as a Lone DBA. One of the great tips she has is to create a ‘Hit-by-the-Bus’ handbook. This would have all the information you need to pass on to the next DBA if you were to suddenly leave the job. With this documentation, the next DBA should be able to step right in without missing a beat. As I deeply considered my current environment, it became clear to me that, were I to suddenly leave my position, the next DBA would have a lot of questions that he or she would have to do some deep and time consuming investigation to answer. As such, I’ve decided to make a major commitment to improving my documentation. From a DBA perspective, this will include detailed documentation on:

  • Daily/Weekly/Monthly/Ad-Hoc tasks. How to do them and why they are done.
  • SQL Agent Jobs. What are they doing and how to troubleshoot if they break. How urgently do they need to be fixed?
  • SSIS packages. What are they doing at a quick glance? Stepping through an SSIS package to figure out what it is doing can be all kinds of frusterating.
  • Backups. What is our strategy? Where are backups going and how would I address various restore situations?

I believe this documentation will bring value both for myself (because my memory is not great) and for my successor, whenever that day may come.