March 29, 2007 at 9:40 am
Guys,
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.
Any ideas/suggestions?
Thanks a lot
March 29, 2007 at 1:51 pm
Check the size of the transaction log (using Enterprise Manager, right click on the database/Properties/Transaction Log. Check the size of the data file as well.
If the log is of a reasonable size, then do a "restore headeronly from disk=<path and name of backup file>" on the database backup. This will show you how many backups are stored in the file.
If either of these don't give you the answer, do a backup of the database with the "format" option and see if the size returns to normal.
Carlos
March 29, 2007 at 4:49 pm
Thanks for the advice.
The puzzle has been solved by using this tool: http://vyaskn.tripod.com/code/sp_show_huge_tables.txt
I identified that I had one table not purged for a long time ... once purged, backup is now 10GB in size ...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply