March 19, 2012 at 2:56 pm
SQL 2005
Database: Simple mode
Every night I backup the SQL Server using a backup tool called SQLBackup and FTP.
For the last 30 nights, I have had a database size of 750,000 KB. Last night, the backup size was 122,000 KB.
What might have caused this size reduction?
There are no maintenance plans for shrinking the database. No compression changes on the file being downloaded. There is a rebuild and reorganize index that takes place. It ran for the first time last night at 4am. The backup started and completed by 2:03am, so that's not it.
March 19, 2012 at 3:25 pm
The only obvious answer is that there was a bunch of data deleted.
Since this is SQL 2005 I don't think backup compression is available in any version.
CEWII
March 19, 2012 at 3:30 pm
That's what I thought as well. But this was on a Sunday night and no one worked yesterday. I also spoke with the customer and he mentioned that everything was great and nothing was missing from the data. I guess I'll restore Saturday and Sunday and start comparing the differences... Thank you
March 19, 2012 at 3:46 pm
SQL Backup is a third party tool for backups, correct? Is it configured to compress the backup files?
March 19, 2012 at 4:10 pm
Either a significant amount of data was deleted or the backup was changed to use compression.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 19, 2012 at 4:15 pm
You are correct, it is a third party tool. It was setup of normal compression and it has been that way for a while now.
I also have a Maintenance Plan that does a backup as well. They were both smaller.
Let me correct something from the original post.
the SQLBackup and FTP file is normally 159,000 KB and last night was 122,000KB (77% difference)
The SQL Server Maintenance Plan Backup is usually 732,000 KB and last night was about 629,000KB (85% difference)
March 19, 2012 at 4:18 pm
Then some data was removed.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 19, 2012 at 4:35 pm
I'm going to start a conversation with the third party to see if they have any advice as to what might be going on.
I do own a handful of Red Gate tools like SQL Data Compare. It tells me out of the 323 tables that I have in my database, 323 have identical row counts between the Saturday 159000KB and the Sunday 122000KB database.
Plus, when they are restored, they are the exact same size in SQL...
March 19, 2012 at 4:52 pm
Possible some of the rebuilds of indexes (clustered/nonclustered) freed space in the database when pages were consolidated. The database itself will retain its size unless explicitly shrunk.
Depending on how fragmented some of the indexes may have been, it may account for the change in size.
March 20, 2012 at 2:45 am
Also check the indexes on the before and after databases.
Someone could have dropped an index (or two), which would account for the reduction in size.
March 20, 2012 at 6:02 am
That's absolutely a new one to me. I wouldn't think that defragmenting the indexes would change the size of the backup.
Did you maybe update SQL Backup to a new version? It might have changed the compression algorithm internally, not your settings, which might have resulted in a change in the size of the backup.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply