0

I'm working on an application, which uses different DLLs, each of them doing something on an MS-SQL database. After a while, some entries in the database don't look as I would want them, and I would like to monitor all the modifications, which have led to this situation, something like:

at 12:00:01.234 : modify "Status" from 2 to 3
at 12:00:02.345 : modify "Destination" from "ABC01" to "ABC02".
at 12:00:02.456 : modity "Status" from 3 to 4

Because currently, I see that "Status" is 4 and that "Destination" is "ABC02", but I have no idea about when and how this has happened. (My question is similar to asking to read the archive logs of an MS-SQL database, if this makes any sense :-) )

Thanks in advance

Edit: some clarification:
On the internet, there are quite some explanations on database monitoring, but in almost all cases, the idea is to measure and to improve the performance. For my question, this is not the case: I just want to understand, piece by piece, how the data in one particular table go from one value to the other one.

Edit: fn_dblog
In the meantime, I've started using fn_dblog, as follows:

SELECT * 
  FROM fn_dblog(null,null) 
  WHERE UPPER(AllocUnitName) LIKE '%TABLENAME_STATUS%'

... but I have no idea how to see the timestamp. Does anybody have an idea?

Edit2 about fn_dblog:
As far as specific fn_dblog information is concerned, I've created a new question for this subject.

6
  • @J.D.: I've added the SQL-server version and in the meantime I've started looking into fn_dblog; this looks promising but the timestamps are either missing or ununderstandable.
    – Dominique
    Jul 11 at 12:23
  • I started this question, having no idea where to find information. Until now, all answers say something like "You need to prepare something and do your thing again.". Although this might be true, I have the impression that fn_dblog contains the information I'm looking for and I don't need to restart all the things I've done already. Oh yes, why the downvote? Is something wrong with fn_dblog?
    – Dominique
    Jul 11 at 12:57
  • Your edit suffers from XY problem you are more interested in your attempted solution than in answers to your question.
    – Zikato
    Jul 11 at 13:34
  • 1
    You should ask about the fn_dblog in a separate question. To ask about two different things (albeit semi-related) inflates the scope of the question.
    – Zikato
    Jul 11 at 13:43
  • 1
    @Zikato: You're right: dba.stackexchange.com/questions/314272
    – Dominique
    Jul 11 at 13:56

3 Answers 3

2

As things stand today, you're going to have to use the existing tools like Extended Events to capture data changes. When SQL Server 2022 is released, that changes quite a lot with SQL Ledger.

Today, you're looking at using the Audit tool (Extended Events under the covers) in combination with Extended Events. The query performance metrics also include parameter values in the case of prepared statements, and the full query string in the case of batches. Between all this, you can see how the data was changed over time. Until 2022 gives us ledger, this is the way to get this done.

0
2

I've used a combination of Trigger and Extended Events (XE) to track changes at a cell (row/column) combination.

  • The upside is it's efficient, and you only capture relevant operations without the need for additional filtering.

  • The downside is you don't see the change in the values - only what code caused them. That can be changed with a customization

How

Create a Trigger on your target table that only checks for your conditions (e.g. column value has changed) and Return on success.

Then you set up the XE session with event sp_starting, filter for the Trigger's object Name and track an empty query hash (the RETURN statement) along with any auditing columns.

You could also swap the RETURN statement for an insert into a logging table. But, of course, you would then have to track the new query hash.

More info here:
https://straightforwardsql.com/posts/how-to-audit-data-modifications-with-surgical-precision/

The other options are

  1. SQL Server Audit - uses XE in the background.
  2. Change Data Capture (CDC), which reads from the Transaction log. It doesn't tell you about the source of the change.
  3. Change Tracking - more lightweight than CDC. By default, it only tracks row changes. You need to enable column updates separately. It doesn't tell you about the source of the change.
  4. Temporal Tables - tracks all changes to the table in a separate history table. It has some limitations with other SQL features. It doesn't tell you about the source of the change.
2

If all you care about is when the change happened, what the old value was, and what the new value is, there's a multitude of features available for you, natively in SQL Server, to choose from. Two of the most commonly used features are:

  1. Change Data Capture:

Change data capture (CDC) uses the SQL Server agent to record insert, update, and delete activity that applies to a table. This makes the details of the changes available in an easily consumed relational format. Column information and the metadata that is required to apply the changes to a target environment is captured for the modified rows and stored in change tables that mirror the column structure of the tracked source tables.

  1. Change Tracking:

Change tracking is a lightweight solution that provides an efficient change tracking mechanism for applications. Typically, to enable applications to query for changes to data in a database and access information that is related to the changes, application developers had to implement custom change tracking mechanisms. Creating these mechanisms usually involved a lot of work and frequently involved using a combination of triggers, timestamp columns, new tables to store tracking information, and custom cleanup processes.

Also see this general document from Microsoft that discussed both of the aforementioned features, called Track Data Changes:

SQL Server 2019 (15.x) provides two features that track changes to data in a database: change data capture and change tracking. These features enable applications to determine the DML changes (insert, update, and delete operations) that were made to user tables in a database.

Additionally, please see my related answer on tracking table history which mentions a few other native features for tracking changes such as Temporal Tables.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.