Is there any way to get original query based on lsn

Posted on

Question :

Is there any way or tool to get original query against the lsn.

For example, I have a lsn range which cause the data set modified. Can I get the actual query against the lsn. Is there any mapping between them in SQL Server ?

Answer :

Is there any way or tool to get original query against the lsn.

No, there isn’t a way to get the “original query text”. I quote this, because the log records record what changes happen in that database. Since select statements, cte’s, etc., do not generate* log records there is no way to get that information. Additionally, the log records describe the changes that have happened, of which a query could be generated to create the same changes again, reverse them, or show what was changed… however there is no way to find what the larger part – if any – it may play in the batch.

Let’s take a quick and simple example:

Suppose we wanted to find all of the accounts using gift cards and give them an extra $1 if they made a purchase over $5 in the last 7 days. The log records would show the $1 increase to any of the accounts that met the above logic, but there is no place for us to see what that predicate was… we’d just see that some accounts (if any) were updated to have 1 added to a column (or whatever your model was).

Thus we could re-create the UPDATE of the individual record, but no way to figure out what the original query was, however we could look at the entire transaction and make a logically equal batch.

What if you wanted to do this in the future – to know all of the queries that have happened?

Native to SQL Server there are various ways to log all of the queries that happen. Additionally there are 3rd party services and applications that can log this as well.

Leave a Reply

Your email address will not be published. Required fields are marked *