question

KayLee-8582 avatar image
0 Votes"
KayLee-8582 asked KayLee-8582 commented

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

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 !

sql-server-general
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


If you have read permission, maybe you can check periodically the contents of the tables.

See also the modify_date column of sys.tables view.


0 Votes 0 ·

See also the modify_date column of sys.tables view.

Useless for the requirement, the "modify_date" changes only if one changes the table design, not the table content.
0 Votes 0 ·

Can the application use the same approach for Delete as it currently uses for Insert or Update (e.g. creating a simple file)?

0 Votes 0 ·

The application is operated by other organization. And I have to find a way silently without informing them. I'll
struggle more on this. Thanks for your comment.

0 Votes 0 ·
OlafHelper-2800 avatar image
1 Vote"
OlafHelper-2800 answered KayLee-8582 commented

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.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Many thanks for your insightful answer.

Then is there any class like FileSystemWatcher to only catch the tiny fact that some Delete query executed through memory ?

0 Votes 0 ·

Then is there any class like FileSystemWatcher to only catch the tiny fact that some Delete query executed through memory ?

Sorry, no way.
0 Votes 0 ·

Answer from excellent someone is enough to finish my journey. Thanks for your time and kindness.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered KayLee-8582 commented

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.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Sorry, you might be confused. I never asked about this before.

0 Votes 0 ·

I got this link from your profile:
https://docs.microsoft.com/en-us/answers/questions/824092/how-to-know-databasetable-changed-with-only-dbread.html
Yes, that question did not ask about DELETE, but it is obviously the same overall problem.

Not until now you tell us:

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

This is essential information, you should have included in your first post.



0 Votes 0 ·
KayLee-8582 avatar image KayLee-8582 ErlandSommarskog ·

The situation you linked has continued to this question utilizing 'FileSystemWatcher'.
Half success with Insert, Update but half fail with Delete. So, I'm asking. Not asked before.
By the way, I accepted your suggestion to add to first post.

0 Votes 0 ·
Yufeishao-msft avatar image
1 Vote"
Yufeishao-msft answered KayLee-8582 commented

Hi @KayLee-8582

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.




· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

+1, Thanks for your excellence !

0 Votes 0 ·