May 1, 2018 at 1:47 pm
Hi,
I am cleaning one of our production databases by getting rid of the old temporary tables that creates a lot of free space (over 30 percent) on the data file.
I want to shrink this file (if it is possible) not breaking the cluster. I did it in pre-production database that resides on a stand alone server and not utilizing the AlwaysOn cluster, and can see some performance improvements.
What would be the recommendations? And is there a way of shrinking the primary (secondary) replica without breaking the cluster?
Thank you.
May 1, 2018 at 2:01 pm
Creating more available space in the database file is what you accomplished when you got rid of tables. Shrinking that space out of the file is not necessarily a good idea. You are imposing the cost of allocating additional disk space that at the moment, it already has, and thus adding unnecessary overhead. You also then have a good chance at increasing fragmentation of objects within the file, causing even more performance issues. Probably not a good idea in the long run.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 1, 2018 at 2:18 pm
Thanks for your quick response, Steve
I understand that shrinking might be not a best idea. But every case is different and you never know until you've tried.
I am wondering if anybody tried to shrink the production database file in alwayson without bringing the database to a simple recovery mode and breaking a cluster?
May 1, 2018 at 2:23 pm
AER - Tuesday, May 1, 2018 2:18 PMThanks for your quick response, Steve
I understand that shrinking might be not a best idea. But every case is different and you never know until you've tried.I am wondering if anybody tried to shrink the production database file in alwayson without bringing the database to a simple recovery mode and breaking a cluster?
And when you break the cluster and have trouble getting it back in operating order, you let me know if the shrink was worth that level of pain. Honestly, I don't think it's worth the trouble. If you still want to do that, use a restored backup copy of Prod on a set of test servers and have at it...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 1, 2018 at 3:05 pm
Thanks Steve,
But this is still not the answer to my question.
Of course I am not going to do this right away in Production.
I was wondering if anybody had this experience of shrinking the database file without breaking a cluster and if it is doable at all.
I could not find valid articles about that.
May 3, 2018 at 4:54 am
AER - Tuesday, May 1, 2018 1:47 PMHi,I am cleaning one of our production databases by getting rid of the old temporary tables that creates a lot of free space (over 30 percent) on the data file.
I want to shrink this file (if it is possible) not breaking the cluster. I did it in pre-production database that resides on a stand alone server and not utilizing the AlwaysOn cluster, and can see some performance improvements.What would be the recommendations? And is there a way of shrinking the primary (secondary) replica without breaking the cluster?
Thank you.
Shrinking a user database does not break the cluster, I\O resources will be heavily consumed during a shrink that's the main point to watch
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 3, 2018 at 8:45 am
Thanks Perry
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply