What are the possible reasons for Automatic Data loss inside SQL Server database.

Question

I was running SQL Database connected to a SCADA software for almost 2 months and it was running completely fine, after that all the data inside SQL database got deleted automatically. I have checked SQL server logs, Antivirus Logs and System logs but no reason was given for this sudden data loss.

Please guide me if had done anything wrong while creating the database which might have caused this sudden data loss and why wasn't there anything mentioned in the log regarding data loss.

Answer 1

Hi,

after that all the data inside SQL database got deleted automatically.

deleted automatically by whom?!? If your app delete the data then the issue is in your app and if your people delete it then speak with them.

Do you really think that a database server will delete data by itself ? Who will ever use such database if it does not store the data ?!?

(1) Check your app, (2) check if your have jobs in the SQL Server that clean the data, (3) check yourself and your teammet activities....

I have checked SQL server logs

I am really wondering which logs you checked. Please clarify in which logs did you suspected to see such actions logged.

Note: there is option to check the transaction log files and their backup files in order to find actions that were executed on the data, but this is a level 500 and not documented and can take a long time even for experts.

For example in the following post I find from the transaction log file who was the last one that deleted data from specific table. In the link after it you can see how I find who created the database. In the same way you can find other activities like drop table and delete actions from the history.

https://ariely.info/Blog/tabid/83/EntryId/154/SQL-Server-Who-deleted-my-record-last-time.aspx

https://ariely.info/Blog/tabid/83/EntryId/303/SQL-Server-Who-created-the-database.aspx

What are the possible reasons for Automatic Data loss inside SQL Server database.

Non

What are the possible reasons for Data loss inside SQL Server database.

Many, including: (1) command from your applications, (2) Jobs in the SQL Server that clean the data, (3) external schedule actions by application that run on the data, (4) people activities

----------

Ronen Ariely
Personal Site | Blog | Facebook | Linkedin

Answer 2

SQL Server never deletes users data on it's own, so it also don't "log data lossing".

Only applications & users delete data; head for them instead.

Answer 3

Hi @TALHA AHMED ,

First of all, I don't know SCADA, I can only answer from SQL Server's point of view. SQL Server does not cause data loss under normal circumstances. There are some cases that may cause data loss, such as corruption of the disk where the data files are stored, or database corruption.
You can restore the latest backup to recover the data to the maximum extent. In addition, DBCC CHECKDB can help you check the health of your database.

Best regards,
Seeya

---

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 4

In this case we need more detailed information. What does "data inside SQL database got deleted automatically" mean? Does the database not contain tables that previously existed or do the tables still exist but do not contain data? Although this is an unlikely scenario, data may have been removed if memory optimized tables that have SCHEMA_ONLY durability are used and SQL Server or the database has been restarted. Of course, SQL Server does not automatically delete data in any case. If the deletion occurred, it is due to application or user intervention, application code error, or some type of attack. If there is data or database corruption, queries against the damaged tables usually end in error and the damaged database is in a specific Suspect or Recovering state. In the event of database damage, it is possible to proceed with an integrity check (DBCC CHECKDB), which will reveal the extent of the damage, or by restoring the database from a backup.