April 11, 2016 at 2:55 am
Hello Fellow DBAs,
I have a production server with 50 GB DB . We used to have a archiving service which used to delete unwanted data and shift 6 months old data to a archive DB . Because of new application release we had to stop archiving for 10 days which resulted in DB size increasing to 150 GB . We deployed an updated archiving service which cleared all the unwanted data and moved old data to archive DB . But the DB size is still 150 GB .The option i can see is to shrink the MDF files , but can't do it on a live DB since it will have adverse impact on the application .
Could you please suggest a way out ? Appreciate your help in advance.
April 11, 2016 at 2:57 am
What adverse impact do you expect it to have on the application? Do it at a quiet time, and you shouldn't have any problems.
John
April 11, 2016 at 3:11 am
Hi John ,
Many thanks for your response. The DB services requests from all over the world and unfortunately i have a small window of 2 hours which i think will not be enough. The last time i tried on a smaller DB it took almost 1 hour for the operation to complete . Also the prod DB is constantly under memory pressure with memory utilization running at 85 %. Just want to make sure that any other alternative exists.
April 11, 2016 at 3:13 am
Why do you need to shrink the files?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 11, 2016 at 3:23 am
Restore the database to a test server, then compare how long it takes to shrink down to your target size in one go versus how long it takes doing it in, say, 10GB chunks. You may find that you can do the shrinking over a period of several maintenance windows.
John
April 11, 2016 at 3:31 am
Hi Gail ,
The MDF size had increased exponentially after the archiving was stopped . Once the data was archived the space allocation is still there , hence the need for to shrink the mdf file.
April 11, 2016 at 3:35 am
Yes, the allocated space will still be there. Why does that mean that you must shrink the file?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 11, 2016 at 3:37 am
Hi John ,
Will try your suggestion .Checking on the test server will give me exact timelines and will help to lessen the impact on prod server. Thanks for your valuable suggestion.
April 11, 2016 at 3:38 am
rohan1424 (4/11/2016)
Hi John ,Will try your suggestion .Checking on the test server will give me exact timelines and will help to lessen the impact on prod server. Thanks for your valuable suggestion.
Add in the time to rebuild fragmented indexes after each shrink (because that's a side-effect of shrink)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 11, 2016 at 3:46 am
Hi Gail,
The size the DB was initially 50 GB , not it has grown to 150 GB even after deleting the unnecessary data. Managing the DB with 150 DB is getting difficult with the drive sizes and the backups.
April 11, 2016 at 3:51 am
It's not going to affect your backup sizes. Backups only contain the data in the DB, not the empty space. A 500GB database containing 100GB of data would have a backup size around 100GB.
If the file's 150 GB, then it won't be growing again unless you have a similar thing happen and the data size goes back towards 150GB. As it is, the free space in the file will be used first, hence it shouldn't be causing worries around file size. If it fits now, it'll be fine. It's not going to grow.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply