10

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. I've run 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?

CHECKDB

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.

Improve this question
2
  • Could you generate the database scripts with the advanced schema only option for each database? maybe there's a really big object that is not a table or index and it would cause the script of one database to be considerably larger than the other. And did you sum the size of all tables and indexes from table report? With that result we'll be able to figure out how much of the used space is due to them.
    – Ronaldo
    Jun 21, 2021 at 15:28
  • Restore the old version and check for differences, this here might help to find where the difference is: mssqltips.com/sqlservertip/1177/…
    – Spörri
    Jun 22, 2021 at 15:03

3 Answers 3

Reset to default
9

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.

You might find the difference by checking one of the standard reports of table size:

Right click the database > Reports > Standard Reports > Disk Usage by Table.

You can order this report by size and compare the reports for the original database and the restored backup.

Improve this answer
8

Some clues that may change things:

  1. Dropped some non-clustered indexes (NCIs).
  2. Added clustered columnstore indexes on some tables.
  3. A drop in row numbers reduces the size of indexes, especially if you have a lot of NCIs on it; in this way if you delete 100MB of data you can even free 1Gb from indexes.
  4. Defragmenting some heavily fragmented indexes. Demo script provided by Razvan Socol.
  5. Rebuilding a heap that had empty pages left behind by deletions.
Improve this answer
0
1

I can not tell how that space was released in your case but a way to find out is to restore the old database and report counts of your tables, indexes, snapshots, number of rows per table, avg row length per table and the standard deviations of the row lengths.

Compare that with a similar report of your current database and the differences must become clear.

Don’t forget to also check temporary structures and stored code.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.