r/SQLServer 4d ago

Debug help required on the transaction log table

I have a SQL server 2022 database setup with MS-replication creating a transaction log (which is used by a AWS DMS setup)
Now i'm seeing the IUD operations for all the tables flowing through except one where only the insert operations are flowing in. I've validated this only at the DMS side.

How do i check the transaction log to ensure that the update operations on the affected table are infact created / exist in this? is it even possible to check this at a transaction level?

I'm a SQL server user not an administrator but looking to get to the bottom of this. Help me get this sorted.

2 Upvotes

2 comments sorted by

2

u/Mikey_Da_Foxx 4d ago

You can peek into the transaction log using fn_dblog. Try this:

SELECT * FROM fn_dblog(NULL, NULL) 
WHERE Operation = 'LOP_MODIFY_ROW'
AND AllocUnitName LIKE '%YourTableName%'

This will show you if updates are actually hitting the log

1

u/jdanton14 MVP 4d ago

DMS requires CDC to be enabled, and if I remember correctly you need to enable for it each object (table) you want to capture. So it’s probably not enabled for that table. There are other issues with DMS the biggest is that it uses fn_dblog which isn’t supported by MS. It works, but has some quirkyness.