Delete and Shrink Database
Have you ever gotten to the point where your Database was getting very big and needed a shrink and you searched for forums telling you the best way to shrink and the only thing you found were warnings not to shrink, under any circumstances! Was quite helpful right?
I would like to share with you my experience with shrinking in our production DB, and hope it can help you.
In general it's a true statement, not to shrink (look at http://www.sqlskills.com/
Our production database reached 500gb and was keeping getting bigger and bigger. We didn't anticipate that will have such a growth rate in such a small amount of time. The size of the DB caused performance problems, backups took too long (3-4 hours and were getting longer), in short it was a mess.
So the first step was to create an Archive database and then delete from the operational DB the old data.
To delete the old data we looked according to the primary key to find all rows to delete and put the PK into a temp table. Then we had to experience what would be the perfect batch size to delete, as it is an operational DB we can't take it offline and if you delete too much you can cause deadlocks. After some time we found the perfect batch size and started deleting everyday for 6 hours. After 3 months we finished and came up with a DB of 500 GB that has 30% to shrink, sounds good, right?
But we decided to go a bit further. We first looked at the index fragmentation to see how bad it was because of all the deletes. We were not disappointed, we found a lot of indexes with high fragmentation, over 60%,
so we started to rebuild the indexes!
Yes i know what you think to yourself, why rebuild indexes if you are going to shrink the database, after you shrink you screw the indexes up again and end up with same fragmentation!
That's true, but wanted to bring the size down of the DB and by rebuilding indexes and then shrinking we managed to reduce the size of the DB by 75% so now our DB is 120GB big. So yes we have fragmentation, but it's not too bad and if there is a very bad index will rebuild it, but the main thing is that the size is down and we are not going to let the DB grow back to the monstrous amount is used to be. We achieve that by having daily maintenance job that copied old file to the archive and then deletes them from the DB. By doing this daily it doesn't take long and has no performance affect on the DB.
Shrinking is not a great thing to do, but sometimes necessary, but you have to plan accordingly and take a lot of things into account. Again, if you really hate to shrink you still have Paul's option(mentioned above), creating a new database.
So the first step was to create an Archive database and then delete from the operational DB the old data.
To delete the old data we looked according to the primary key to find all rows to delete and put the PK into a temp table. Then we had to experience what would be the perfect batch size to delete, as it is an operational DB we can't take it offline and if you delete too much you can cause deadlocks. After some time we found the perfect batch size and started deleting everyday for 6 hours. After 3 months we finished and came up with a DB of 500 GB that has 30% to shrink, sounds good, right?
But we decided to go a bit further. We first looked at the index fragmentation to see how bad it was because of all the deletes. We were not disappointed, we found a lot of indexes with high fragmentation, over 60%,
so we started to rebuild the indexes!
Yes i know what you think to yourself, why rebuild indexes if you are going to shrink the database, after you shrink you screw the indexes up again and end up with same fragmentation!
That's true, but wanted to bring the size down of the DB and by rebuilding indexes and then shrinking we managed to reduce the size of the DB by 75% so now our DB is 120GB big. So yes we have fragmentation, but it's not too bad and if there is a very bad index will rebuild it, but the main thing is that the size is down and we are not going to let the DB grow back to the monstrous amount is used to be. We achieve that by having daily maintenance job that copied old file to the archive and then deletes them from the DB. By doing this daily it doesn't take long and has no performance affect on the DB.
Shrinking is not a great thing to do, but sometimes necessary, but you have to plan accordingly and take a lot of things into account. Again, if you really hate to shrink you still have Paul's option(mentioned above), creating a new database.
