July 2, 2020 at 8:19 am
Hello from Portugal.
I manage a database for a customer, and this database has been developed and used from a fistful of developers and many programs, since it is living for about 209 years now.
This database has a main table, where many information are in. We now have faced the fact, that anything is happening from time to time (sometimes once in two weeks, sometimes twice in an hour), which deletes all entries of a specific type. We clearly need to know, what happens. What I did so far: I placed a trigger onto this table, which notices this action, sends an email and then rolls back. So at least the records won't be deleted. My hope was, that if we immediately know, that it happened, we can ask the users what they did the last minute and if they had an error (due to the rollback and error-throwing). It comes as it was clear it would - nobody did anything or noticed anything strange. To just get the username will not help, since many users/connections use the same user (most parts running from within an IIS). So I thought of getting as much information from within this trigger as possible. What command forced the trigger? Maybe a stacktrace? Maybe the id of the connection and any information connected to this id? Anything? Everything?
Normally it could be enough to just prevent this deleting, but I want to know who does what to force this accidently? Because noone knows, what more traps are around...
Many thanks
July 2, 2020 at 10:48 am
since it is living for about 209 years now
RDBMS, let alone SQL Server, weren't around in 1811, so your database must have been really advanced back then!
As for what you're after, have you considered using extended events to capture the events details as well? I assume your application has some logging in it as well, so if you use the trigger or XE to find out when the event happens, you can consult the application logs as well and see what was happening at that point too. That would be where I would start anyway.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply