SQL Database is gradually decreasing in size

Question

I have an SQL Database connected to a SCADA software and it was running completely fine for almost a month then I have noticed that my SQL Database size is gradually decreasing, what could be the reason for this Occurrence?

.mdf file size of my database is decreasing and .ldf file size is increasing at an abnormal rate, what could be the reason for this?

Previous data in my SQL Database is also missing, is it possible that I can restore my previous data and what could be the steps?

Answer 1

There are several possible reasons for the gradual decrease in size of the .mdf file and the increase in size of the .ldf file:

1. Automatic database shrinkage: The SQL Server automatically shrinks the database if there is a lot of free space. This process releases unused space and reduces the size of the .mdf file.
2. Transaction log backups: If transaction log backups are not being taken regularly, the .ldf file can grow significantly. This can be resolved by scheduling regular log backups.
3. Data purging: Data purging from the database can also cause the .mdf file to decrease in size.
4. Corruption: The .mdf and .ldf files may be corrupted. In this case, you may need to restore from a recent backup.

To restore previous data, you can use the SQL Server Management Studio (SSMS) to restore a database backup. The steps to restore a database backup are:

1. In SSMS, connect to the SQL Server instance that contains the database.
2. Right-click on the Databases folder and select Restore Database.
3. In the Restore Database dialog box, select the backup file and click OK.
4. Select the options that you want and click OK.
5. The restore process will start and the database will be restored to its previous state.

It's important to keep in mind that restoring a database will overwrite any data that is currently in the database, so make sure you have a backup of your current data before proceeding.

Answer 2

Hi

As Tasadduq already mentioned, there must be some automatism in your application that deletes "old" data after a specific time and makes sure that the file also shrinks (this is no automatism built-in SQL Server by default)

So, if your application deletes data, this deletion is a transaction that has to be logged in the log file (ldf). So many deletions leading to a decrease in mdf and an increase in ldf... without more details/insights into your application, we can't tell you why.

I would recommend looking into any automatism your application is doing... the decrease/increase process is normal if there are such "cleanup" tasks.

To get those data back... you have to restore your database to a point in time before this decrease started.

I hope my answer is helpful to you,

Your

Bjoern Peters

If the reply was helpful, please upvote and/or accept it as an answer, as this helps others in the community with similar questions. Thanks!

Answer 3

Please note that people here are not familiar with SCADA. I this forum you can get generic answers, but use an answer like the ones from Tassaduq and Björn, you may need some general knowledge about SQL Server.

To me it sounds like SCADA comes with a purge job that deletes old data, and somehow SCADA also regularly schedules shrinking of the data file. (Or employs the autoshrink feature in SQL Server.)

You would get a lot better help if you find a support forum for SCADA, where people should know what SCADA is up to.

Answer 4

Hi TALHA AHMED,

This reminds me of a previous case I encountered, which described a similar problem to yours. I checked it out and it was also raised by your current account.

https://learn.microsoft.com/en-us/answers/questions/1144389/what-are-the-possible-reasons-for-automatic-data-l

Was your problem not solved? So, I'm wondering if it's a SCADA software issue. It would require you to get in touch with SCADA tech support.

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".