About 8 GB has been trimmed off of the size of our main database. This is reflected in backups and when we look at free vs available space.
Everything seems to be working and we can't find any sign that data is missing but we've never seen this before. We're a little worried that something untoward might have happened.
Can anyone tell us how 8 GB of data can disappear? If this is a normal thing, we'd like to understand how it happened as being able to halve the size of your database backups is actually somewhat useful.
We are running SQL Server Enterprise version 14.0.3391.2 on Windows Server 2019
Our main database is backed up every day just after midnight. The backup destination is a local disk. A new file is created for each database every time a backup runs. Our recovery mode is SIMPLE. Backups are not compressed. For at least the last couple of years, the backups have been around 15 GB each. Since 11 June, the file size has shrunk to just over 7 GB.
Doing some research, one thread on DBA StackExchange related to someone who was accidentally creating multiple backups in a single file.
RESTORE HEADERONLY FROM DISK against backups from 10 June (15 GB) and 11 June (7 GB). Both appear to contain a single backup. I also didn't see any other noteworthy differences.
Upon restoring the database, I observed the total size to be around 30 GB on both the current and restored database. The free space on the current database was around 19 GB, while on the restored database it was around 11 GB. A difference of 8 GB, like the backups. Our backups are not compressed.
We haven't noticed any data missing in our app which we rely on quite heavily. I've used SSDT to compare schema and data from the current database and the restored database.
The application that uses this database is under constant development so there were a few columns and tables that were added to the current database to support new features, but none removed.
Likewise there were some rows edited or deleted in the current database, but the associated tables account for well under 100 MB in the restored database, so even if they were totally deleted that wouldn't result in about 8 GB of data vanishing.
We have one person developing the application. Database administration is shared between that person and me. Neither of us believes we've done anything that would cause this change.
There haven't been any significant changes in how we use the database and if anything the backups should be getting bigger.
We're a small company and it's very unlikely that someone has done a data clean-up. Also that should have shown in data compare in SSDT.
I found nothing in the log files to suggest unauthorised access or a failed backup.
I'm currently restoring the backup from 11 June (7 GB) to see if that tells me anything though I'm not expecting much insight from that.
We don't auto-delete backups, we do it manually, which is when we spotted the size difference.
I don't believe any indices have been removed - anyone have a suggestion on the best way to check? Defragmentation seems an entirely possible cause - is there a way I can check?
Hasn't been run as far as I'm aware. Ran this morning. Didn't see any errors, and finished with this message: CHECKDB found 0 allocation errors and 0 consistency errors in database 'MyDatabase'.
Table size report
The results are pretty similar, the most notable difference being a table which I think is new. In the smaller, live database, that table is 900 MB. In the larger, restored database, that table's only 6 MB. I'm wondering if we've crossed some threshold of x% database usage which has triggered some sort of maintenance operation.