Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

At some point in the last 24 hours some changes were made to a customer SQL server 2008 (v 9.0 SP4) by one of a couple if possible applications that use the DB.

Its not particular critical, but it would represent a useful insight into wtf and how these applications are using the tables if I could dump out SQL statements corresponding to the time periods.

The tables I am interested in, do not change very often and hence transactions logs applying to those particular tables should not be very large in total rows.

I see the "fn_dblog" function and DBCC LOG('DataBaseName') commands, and seem to have quite a bit of related information, but I am not sure how to take the next step and get them to produce a report of the actual changes.

There are some details in this blog post; http://janiceclee.com/tag/fn_dump_dblog/

USE AdventureWorks
    GO
    SELECT [Transaction ID], count(*)
    FROM fn_dblog(DEFAULT, DEFAULT)
    where AllocUnitName LIKE '%Production.TransactionHistory%'--table name
    GROUP BY [Transaction ID]
    HAVING COUNT(*) >= 113443 --(estimated/actual) number of deleted records

However I don't have any details like that, just the table name (and the primary keys if necessary I can get that)

and another blog post that is using a similar example data given their table naming; http://www.sqlskills.com/blogs/paul/post/Search-Engine-QA-6-Using-fn_dblog-to-tell-if-a-transaction-is-contained-in-a-backup.aspx

    USE AdventureWorks;

    GO

    SELECT [Current LSN], Operation, [Transaction ID], AllocUnitName FROM fn_dblog (NULL, NULL);

    GO

The log record at that LSN is:

    00000058:00001870:0001  LOP_MODIFY_ROW     0000:00001338  HumanResources.Employee.PK_Employee_EmployeeID

This is obviously in the middle of my contrived transaction - showing that it isn't all in the full backup. The end of the transaction isn't until way later in the log:

    0000005e:00000628:01b1  LOP_MODIFY_ROW     0000:00001338  HumanResources.Employee.PK_Employee_EmployeeID
    0000005e:00000628:01b2  LOP_COMMIT_XACT    0000:00001338  NULL
share|improve this question
add comment

2 Answers 2

The transaction log does not not contain statements, it contains the physical changes occurred in the database. If you see a log record that indicate a delete you cannot know if this was a DELETE statement, a MERGE statement or a wide (split) UPDATE statement. If you see an operation indicating an INSERT you cannot know if it was an INSERT (...) VALUES (...) or it was an INSERT (...) SELECT (...) or it was an INSERT (...) EXEC or it was a MERGE or it was a wide (split) UPDATE. And so on and so forth. Specifically, the transaction log does not intend to substitute for an audit trace.

The transcriptional replication agent has means to reconstruct T-SQL operation with identical effect as those that changed a published article, but how it does it is not public information.

If you want to monitor data changes, use Change Tracking or Change Data Capture. If you want to monitor T-SQL activity, use profiler traces.

share|improve this answer
add comment

I don't think you can get the query from the transaction log itself - now I could be wrong but I have never seen it done - though I have never tried either. You can however get the data that was modified during a transaction.

The article below presents a stored procedure that will read the modified data from the transaction log; I haven't used it myself but a few chaps in our company say it works quite well. Please note that it only works on 2005 and above though:

http://raresql.com/2011/10/22/how-to-recover-deleted-data-from-sql-sever/

It might provide the insight that you are after - or not :-)

BTW there are a few commercial log reader applications that do a similar thing - however something to keep in mind is that they are only providing you the ability to do something you could do yourself with enough research.

If I may I would like to suggest a look at SQL Internals by Kalen Delaney which is an excelent book and will really help you understand a lot of what is going on in SQL Server:

http://www.microsoft.com/learning/en/us/book.aspx?id=12967&locale=en-us

Now this won't help for past transactions but going forward I would probably recommend using something like Change Data Capture or SQL Profiler to capture the information that you require - it is much easier than trying to trawl through the transaction log. ;-)

I hope this helps you.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.