Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sql_er
Constraint Violating Yak Guru
267 Posts |
 Posted - 2007-03-29 : 11:37:58
|
| 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 |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2007-03-29 : 11:47:15
|
| Are you using standard MSSQL backups....or 3rd party tools. Some of the latter might bypass the (failing) problem, as they use large-scale compression. See some recommendations from other members here. |
 |
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2007-03-29 : 12:35:15
|
| I am using a maintenance plan. I know it is bad ... I just never got time to implement a job another way ... now I am paying the price ... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-04-02 : 04:07:53
|
| The backup job will put the transactions onto the end of the backup file, won't it? (i.e. the Backup is "complete" as of the END of the backup, not the START), so perhaps something is generating transactions faster than Backup can save them?!Kristen |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2007-04-03 : 10:13:25
|
| The OP found an answer on his own. Guess he never mentioned over here.http://www.dbforums.com/showthread.php?t=1616675 |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-04-03 : 13:55:57
|
| "The OP found an answer on his own"IMHO I don't reckon the OP did. The OP purged some stale data from a table and reduced the backup size.The OP cancelled the Backup an started a new one, and that worked.All sounds circumstantial to me ....Kristen |
|
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2007-04-04 : 12:34:22
|
| I think that someone changed the code of the application inserting error data into that "problematic table". The change resulted in more data to be written to that table. And since the purge job was not set up properly, we had an exponential growth of that table, which got noticed in the backup file size.Now that the purge job is corrected, we truncate that table daily, and all is back to normal.To be 100% sure, I would have to inspect that table before truncating it, to see that the growth was really sudden in the past week. However, I truncated it without performing this inspection. As such, I'll never know now.The lesson here, however, is at least 2-fold:1. Whenever setting up a table which keeps data bound to grow a lot, set up a purge job as well and test to ensure that it works properly2. Periodically, check the table sizes of your largest tables (i.e. how much disk space they are eating up) by using the following sp: http://vyaskn.tripod.com/code/sp_show_huge_tables.txtThanks |
|
|
|
|
|
|
|
|
Surprising backup file growth - puzzle!!!