SQL Server: Data removed from table without any trace

Sudip Bhatt 2,271 Reputation points
2021-01-28T17:04:32.817+00:00

I have one SP which first remove data and then insert data into a particular table. when i am testing that SP works fine. all DML operation done under Begin Tran and COMMIT Tran as a result if any error occur then inserted data will be rollback. some time i am seeing data missing from a specific table.

i do not have DBA knowledge so not being able to capture the reason. there are many other developer in team which who login as sa and they also can delete data.

i can create trigger on that table to capture host name and app name but if a any developer removing data then they can disable trigger.

so please guide me how can i safe guard the data and also share some knowledge as a result i can capture how data is removed?

I am using SQL server 2017 and my version details -> Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 2017 17:04:49 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: )

i have no idea how to see & query the transaction log to get deleted info.

please share some best idea to handle this situation.

Thanks in advance.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,732 questions
Accepted answer
  1. Erland Sommarskog 101K Reputation points MVP
    2021-01-29T22:13:42.18+00:00

    then a user can first disable trigger and do the operation.

    I have already addressed this, but maybe I was not clear enough. The answer to your question is that you minimise the number of people who can disable the trigger. In production database, only the DBA team should have those permissions.

    That is not always good enough, and in that case SQL Server Audit may also be worth considering. Audit can be turned off, but at least the audit log will have information about that. However, setting up an Audit is a DBA task, and nothing you would do as a developer.

    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 101K Reputation points MVP
    2021-01-28T22:55:01.51+00:00

    There is no supported way to read the transaction log. There are some undocumented functions to read the log, but the output is not easily interpreted. I am not going to give any explanations of the simple reason, I have not worked much with the output, and don't understand much of it myself.

    For a production system, you would have auditing set up, or you wold use a trigger for auditing. And in that case, you would not permit developers on the machine, and if you did you would make sure that they have permissions to disable triggers.

    2 people found this answer helpful.
    0 comments
  2. Tom Phillips 17,716 Reputation points
    2021-01-29T16:40:11.963+00:00

    Lets start over.

    Your original post says you "have one SP which first remove data and then insert data into a particular table.". Is this process not working? If so, we need to see your code.

    If that code is working, and you believe something else is deleting data after the SP runs, there is nothing built into SQL Server which tracks deletes of records. You can track that by several methods, including a trigger, turning on CDC tracking for the table, or attempting to use SQL Profiler to find it. I would suggest a trigger would be the simplest.

    1 person found this answer helpful.
    0 comments
  3. Tom Phillips 17,716 Reputation points
    2021-01-28T17:19:37.22+00:00

    Without seeing your code it is impossible to guess as to the problem.

    Completely unrelated, you are running the RTM version. You will need to update to a current patch level.
    https://support.microsoft.com/en-us/topic/kb4047329-sql-server-2017-build-versions-346e8fcd-c07c-5eeb-e10b-e3411ba8d8dd