-1

I want to know if there is any way to find out when the latest event was writing to a table in SQL Server.

Assuming the table does not have a created_at/updated_at columns and CDC and Change Capture are NOT enabled on the table.

I don't want to stream data out of this approach; I know I can use CDC or CT to do so, but I'm trying to find a way to check if table X is active and have inserts/updates for monitoring purposes only. I don't want the actual data that was writing I just want a timestamp indicating the table was affected with insert or update.

I've been checking sys.tables and I found columns such as create_date and modify_date but its related to table existence and schema changes and not the underlying data but it gave me hope that it might be available in one of SQL Server undocumented system tables.

Thank you.

1
  • Presumably INSERTs and UPDATEs are considered writes, do you also consider DELETEs too?...what about TRUNCATEs?
    – J.D.
    Nov 29 '21 at 23:22
0

If it is like a high-watermark you want, then one option might be reading the transaction log. This isn't straight-forward since the log isn't meant to be used for these purposes. You also have to consider the recovery model for the database and how frequently you empty the log.

A polling solution would be required. I.e., a scheduled job that reads the transaction log that grabs the relevant information into your own tables. Below article can serve as a starting point regarding reading the transaction log using fn_dblog().

https://www.mssqltips.com/sqlservertip/3076/how-to-read-the-sql-server-database-transaction-log/

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