asked
database delete
Hi, I am trying to bring our production DB down, right now we are on 480 GB. We started a delete process, every day in the morning and slowly its going down. But i have a few problems: when i check up sql reports and compare it to an backup before i started deletes,then even though i see that number of rows have gone down drasticly, still the Reserved amount of KB for the tables i do the delete on are actually increasing instead of decreasing?! Could this be because of fragmentation, but how does it become higher? After i finish my big deletes of 100's of millions of rows, i want to shrink the DB and then defragmant. But after doing a few tests on backups i see something very weird: If i try to shrink and then defragmant, then the shrink doesn't release a lot of space , only around 60 GB. But if i first do defragment (rebuild/reorder index) and then shrink, then i get almost 200 GB!! why is that? also the problem is that if i use option 2 then probably after the shrink i would again have to defragment! if anyone has experiences with such big deletes and could explain the mentioned problems, would be very much appreciated. thanks
The deletes themselves will not change the allocations within SQL Server. This is a least partly because you're probably not deleting from the leading edge of the data. When you shrink, if you just shrink without also telling SQL Server to reallocate the pages, you'll be only shrinking the pages that are not allocated, basically the last bit of growth. It really depends on how you're calling the command. Read up on it here in the [Books Online on MSDN][1]. The real question is, why are you shrinking over and over again? That leads to very serious defragmentation on the disk of your machine at the OS level. A one time shrink because you've radically changed data allocations is fine. Shrinking over and over is a serious problem. If you're going for the one time shrink, then complete all deletions before doing the shrink. If you're shrinking over and over, I'd suggest strongly that you stop. [1]:
http://msdn.microsoft.com/en-us/library/ms190488.aspx
No you misunderstood. i am planning to shrink only once after the deletes are finished. i am just experimenting with the shrink on a backup db. i am using DBCC SHRINKFILE (N'ShopAndCheckUsers' , 350000), i dont want to shrink in one shot too much it will take too long. But why do isee in the sql report that the data is increasing in the table even though teher aless rows in there?
If you're looking at allocations, that won't change because of the delete. Also, doing that shrink in multiple steps is going to lead to fragmentation on the OS. You sure need to know that going in.
Grant is right on what he says. The question is, why do you feel the need to shrink your DB to begin with? If you Delete data and just leave the DB as it is, then the allocated space will fill up with new data and the DB probably won't experience much growth. By shrinking, you're forcing it to grow every time new data is entered.
13 People are following this question.
