I am a bit puzzled. Our database backup grew from the usual size of ~27GB to ~40GB, all of a sudden. Nothing special happened in the last few days - nothing major to cause such increase.
I found out about this, because we suddenly had the backups failing, and when I explored, I saw that this was due to the lack of space on the hard-disk.
I do know that we need additional hard disk space. In the meantime, however, I'd like to be able to identify what exactly could cause such growth.
As far as I understand, for the backup to grow, the database needs to grow in a similar proportion. My only theory is that when the backup failed a few times, each time, somehow, it resulted in the database growth. Does this make any sense?
Another clue is that the backup job, which usually runs ~ 30 minutes has been running for 6 hours already, the file has grown to 40GB, and the backup job is still running ...
What is the best way for me to explore what exactly happened? Are there some system tables containing history of table counts or something - so that I can see who grew when and by how much?
I ran a query to see which objects were added in the past few days - that did not give me any clues - all looks normal.
In general, a SQL Server backup only collects the pages with data on them. You can get a rough estimate of how large a backup will be by running the following query:
select sum(fileproperty(name, 'SpaceUsed')/128)
where groupid > 0
This will give you a "base size" for the backup. If you have large numbers of transactions running during the backup, then you will have to add in the transaction log for the duration of the backup. Without historical information, there is no good way of telling whether the backups are growing because of data growth, or because of some other factor.
One thing to try would be to run the backup manually from QA, with the "stats=" parameter. Something like:
backup database [DBName] to disk = 'E:\Backups\Filename' with init, stats = 5
This will give you an estimate of how far along the backup is progressing, which you may not get from the backup job. Somehow the first 5% is always the slowest, so don't worry too much over that. This will also give you an idea if the problem is in the database (likely) or in the backup job. The length of the backup job may be affected by using the same disk for the backup as the datafiles of the database are on. You would be reading and writing to the same physical disk, which would cause a bottleneck.
When I ran (select sum(fileproperty(name, 'SpaceUsed')/128) from sysfiles where groupid > 0), I got (42605). If this is in megabytes, then the backup size appears to be similar. What is puzzling, however, is why it grew from 27GB to 40GB in 1 day, all of a sudden. This is something I'd like to explore.
Another puzzle is the length of the time the backup job is running. I will try to cancel the job and perform a manual a backup with stats option you suggested.
Also, we don't back up to the same drive where the data file sit. It is a completely separate file. The backing up could also be slow because we are writting to a compressed folder ... but then again, we always did that.
And the question is still - why did the data file grow all of a sudden? How can I explore this?
I did so, and found out that I have a table, which has not been purged since January and is taking up about 29GB of space. I am not sure why the problem only surfaced now, as this table has constantly been growing ... In any case, I purged it, and a few others. Now my size - to be backed up - is 10GB ... all the way down from 40GB.
I cancelled the old backup job which has been running for over 9 hours, and am currently running a new one ... if that does not work, i will just not use maintenance plan to perform backups ...