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