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 |
|
jbates99
Constraint Violating Yak Guru
396 Posts |
 Posted - 2010-07-28 : 17:44:16
|
| hi experts,I cancelled a database backup job for this (2005) 40 GB database today.Now this database is a size of 60 GB.I just now ran a full backup and the .bak size is 60 GB. Has anyone else experienced such an abnormal growth after cancelling a backup in mid-stream?Will any DBCC command adjust the size back to normal.Note- I'm not referring to log size. The .mdf grew by 50 %.Thanks, John |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-07-28 : 17:55:49
|
| It is unlikely that the backup job or cancelling the backup job caused this. I have a strong suspicion that something else caused it. Have you checked the data file growth events? If you right click on the database, go to reports, and then select the disk usage report, there's a section that you expand to show the times of when the files grew.You can use DBCC SHRINKFILE to shrink it down, provided that you've got free space inside it. If you don't have free space, then you should check fragmentation levels of your indexes. You can reclaim space by rebuilding indexes that are heavily fragmented.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
jbates99
Constraint Violating Yak Guru
396 Posts |
Posted - 2010-07-28 : 21:07:37
|
| The database was locked down during the backup. We were about to install some updates from the software provider so I always get everyone out and perform a full backup just prior to installing the updates.The backup job was running longer than usual, so I stopped the job. I immediately noticed the datafile had exploded in size.Because the backup job had exclusive use of the db, I have to attribute the size increase to the cancellation of the backup. It took awhile, couple minutes, for the job to terminate.There is no free space - I checked earlier.Not knowing what else to do, I ran an UPDATEUSAGE on this db. No effect on file size.DBCC CHECKDB shows no allocation or consistency erros.We have a type of mirroring in place (Falconstor) and the mirror server database is size 38 GB.I guess it's possible that this db suddenly developed severely fragmented indexes. My maint plan will run Sunday. Hopefully that will reduce the size but I have my doubts.Thanks for your suggestion. i will check checked the data file growth events, Tara.John |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-29 : 01:59:06
|
| "the backup job had exclusive use of the db"Users were kicked off, are you sure no scheduled jobs (or external connections) ran anything?We put the database into single user mode / DBO when we do this kind of task to make sure nothing unexpected "creeps in" behind our backs! |
|
|
|
jbates99
Constraint Violating Yak Guru
396 Posts |
Posted - 2010-07-29 : 14:31:25
|
| Aha! That's a very cool report, Tara. it shows that the sudden growth occurred during the installation of the software updates. Not during the backup job. It also shows that the size of indexes is much larger than the same db that exists on another server. maybe my weekend maint plan will clean up the index issue. But the db size will remain the same unless I shrink it, right? I don't like to shrink the files but this may be one of those times...Thanks for everyone's ideas.John |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-30 : 06:00:07
|
Do you need to get the disk space back? Is there a chance that the DB might grow again? If No+Yes then I'd leave it alone! otherwise, yup, its "one of those times" and permission is granted for a one-time shrink  |
|
|
|
jbates99
Constraint Violating Yak Guru
396 Posts |
Posted - 2010-08-01 : 21:57:10
|
| Thanks, Kristen.As I had hoped, my weekend maint plan recovered 41 % of the space (I could now shrink it by that much) so I suppose that was a lot of jacked-up indexes. So to review: the installation of a software update failed part-way through and then I noticed the db size had grown by almost 50 percent.No, I do not need to claim that disk space and I will not perform the shrink until that situation changes. All is well now :)thanks for everyone's advice. John |
|
|
|
|
|
|
|
|
Database Size Grew by 50 % After Cancelling Backup