patternsqlMinor
DB backups have suddenly gotten smaller
Viewed 0 times
gottenbackupssuddenlysmallerhave
Problem
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
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 ed
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 ed
Solution
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.
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.
Context
StackExchange Database Administrators Q#294478, answer score: 9
Revisions (0)
No revisions yet.