SQL Server Performance Forum – Threads Archive
fn_dblog and get Object Name
Ok, experts, please investigate. In SQL 2000 I could query this function and get which object (DDL) changed and when,but in SQL 2005, it seems that I’m hard pressed to find out the object name/id. When you make a DML statement (update, insert,delete), it tells you which object it is making it against, but not so for DDL (at least not explicitly) Anyone out there do this? Here’s the base query: select * from ::fn_dblog(null,null)
TIA
One of the posts in MSDN forums MSFT
quote:
The transaction log contains physical information that are often just blocks of bytes which are meaningless in terms of the DDL or DML that instigated them. The log was never intended for audit purposes and really should not be used that way.
The transaction log contains physical information that are often just blocks of bytes which are meaningless in terms of the DDL or DML that instigated them. The log was never intended for audit purposes and really should not be used that way.
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
i guess that won’t stop me from trying <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /> BTW, I think I saw that somewhere. Not necessarily looking for the binary stuff, there is Lock info that actually does have this, but needs to be parsed. Looks like:<br />ACQUIRE_LOCK_SCH_M OBJECT: 9:354100302:0 – so, we have a Schema/DLL change, 9 is the database, and 354100302 is the object_id. I guess I may have to use this.<br /><br />From the same post I believe, talks about this.<br /><br />"Which table is not actually stored in the log record in SQL 2005 and later. This is due to partitioning. The partition has to be linked through the catalog metadata back to its base table and index. This is attempted by fn_dblog() itself and shows up as other columns in the output. DDL can make this lookup fail."<br /><br />Follow-up thoughts?
Yes that is agreed on the part of partitioning, due to the changes from SQL 2000 to 2005 you wouldn’t get much far even if we get in to MS SQL dev. team for an information. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
]]>