How to know if a Delete query is fired on SQL Database(Express version on personal computer) through C# FileSystemWatcher ?

Question

In case of Insert, Update query on SQL Database(Express version on personal computer), the application creates simple .txt in some folder and I can get notified through FileSystemWatcher.

However, in case of Delete query, the application doesn't create any file and even last modified datetime of .mdf file and .ldf file of SQL Database is not changed same.

So, I cannot be notified through FileSystemWatcher.

The application is operated by other organization. And I have to find a way silently without informing them.

My directory structure is like C:\the application\SQLdatabase\DB.mdf.

I set the filepath 'C:\the application' of FileSystemWatcher with IncludeSubdirectories = true option but fail.

Through Microsoft official document, I understand that FileSystemWatcher can catch last access time but I don't know why it's not working.

Why is the datetime of .mdf and .ldf file of SQL database not changed(updated) although some data in the database is inserted, updated, deleted?

I used SQLTableDependency(trigger, queue, message, contract, procedure) with sysadmin role but now I only have dbreader role(read permission) and I cannot use SQLTableDependency anymore.

Thank you !

Answer 1

Why is the datetime of .mdf and .ldf file of SQL database not changed(updated) although some data in the database is inserted, updated, deleted?

By several reason.
For performance SQL Server access the database file directly, not via OS API.
And all DML operation changes the data first in memory, a lazy backgroup process writes the changes somehow later to disk.

Answer 2

Hi @Kay_Lee ，

Themodified dates change when SQL Server closes the files(SQL Server is shut down or the database is detached), or when the file is grown, all other times, SQL Server essentially bypasses the file system when performing write operations, so the modification date is not updated

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b7db6744-cd7b-42b5-a84e-5e511a5e8e59/mdf-amp-ldf-files-last-modified-date-not-increasing-as-expected

For FileSystemWatcher, i am not familiar with it, maybe you can create a log file, and when someone opeartes database, you can write relevant messages into log file, then you can use the FileSystemWatcher to monitor the log file, you cannot use the FileSystemWatcher to monitor a table but can monitor the database file

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e925a712-9a36-43a8-88ab-6e5e23c3fb50/about-filesystemwatcher

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

I think that was one of the more cumbersome methods I've heard of to detect changes. There are some more straightforward ways to do this, all depending on the database design. You have already asked about this in earlier threads, and we have answered as good as we can.

If you want to use FileSystemWatcher, you will have to change the application to write files on deletes as well. But it's a very awkward design.