January 5, 2009 at 12:09 am
To identify which tables are being hit by DELETE operation and how many times, I used:
SELECT AllocUnitName, COUNT([Current LSN])
FROM ::fn_dblog(NULL,NULL )
WHERE Operation = N'LOP_DELETE_ROWS'
GROUP BY AllocUnitName
Is it possible to determine the actual content deleted(in this case, the row that is deleted).
The same question goes for insert & update queries.
If I am updating few records, how can I view the exact changes performed.
Hira Masood
January 5, 2009 at 7:43 am
It is very hard to understand the output of dbcc log or fn_dblog. Some might say that it is impossible:-). Although there is no much information available on the net to help us understand the info that we get from dbcc log (or fn_dblog function), there are some third party tools that can do it. You can use Google to search information about log explorer, sql log rescue and other tools that are available.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 5, 2009 at 9:49 am
What are you trying to accomplish here? Wouldn't a log table and the OUTPUT clause help you with the statements? Or a trigger?
January 5, 2009 at 10:17 pm
Yes Adi, final idea is supposed to be the 3rd party tool.
what I am trying to accomplish is just that if we don't use any third party tool, are we able to extract the query posed by the user.
e.g.
using fndblog( , ) we can get the operation performed (insert/update/delete) & we get the allocation unit.
But to know the exact query posed
(UPDATE tableName SET column1=value1 WHERE column1=value2) can fndblog be helpful?
Further I have tried APEXSQL didn't find anyway to get the QUERY posed.
N.B. This is just for information, if I get a little clue about it I won't use the third party tool! You can say I am trying to do what a third party tool do for us
January 6, 2009 at 1:02 am
It is better that you’ll answer Steve’s question because there is a good chance that you are not on the right track at all. As I wrote before there is not much published information about the product of dbcc log and fn_dblog function. If you’ll get this information, you’ll be able to extract that data just like the third party tools do, but this is one big if, and you also don’t know how much development is involved with this kind of solution. If you want to see information such as who issued a query, the query it self, etc’ you can accomplish it in other ways such as using triggers that log it, preventing direct DML on the table, and using stored procedure to log it, working with profiler or any other way. The point is that as long as no one knows what you need to do, no one can really give you a good advice. We can however tell you that trying to work on your own with dbcc log or fn_dblog doesn’t seem like a good direction to fallow.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 6, 2009 at 1:22 am
If you want to see information such as who issued a query, the query it self, etc’ you can accomplish it in other ways such as using triggers that log it, preventing direct DML on the table, and using stored procedure to log it, working with profiler or any other way. The point is that as long as no one knows what you need to do, no one can really give you a good advice. We can however tell you that trying to work on your own with dbcc log or fn_dblog doesn’t seem like a good direction to fallow.
May be I did not clear to you people that I am trying to perform audit of my SQL databases moreover you can say trying to audit the transaction logs.
I got enough information using dbcc, fndblog. Next what I tried was to get the complete query itself that brought change to my DB table.
Ok now I got what can be the solution for "getting the query itself". Right now looking Triggers/OUTPUT clause as Steve said.
Hira Masood
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply