Episode 133
| May 02, 2018
| 36 minutes
Shrinking Files
While the ability to shrink data files is available in SSMS, the conventional wisdom is don’t do it. I was extremely curious when I read an email from our guest suggesting the opposite. In this episode we explain the ‘real world’ and discuss why you might consider shrinking files and the how it will affect you. Nigel has a great sense of humor and I know you will enjoy today’s episode.
Episode Quotes
“We have a set percentage that we need to leave free on a disc, and that causes problems. I do tend to find that discs can be a bit like teenager’s bedrooms in that if there’s space available, it will be filled.”
“It’s an awful process. I think that’s why it’s become such a controversial or taboo subject, whereas, I live in the real world and from time to time, I do have to do this process.”
“I’ve been in situations whereby you’ve got systems that cannot be expanded for various reasons, and it’s a bit like doing a three-points turn with a petrol tanker in the children’s playground. You’re very limite
Listen to Learn
00:40 Intro
01:27 SQL Server in the News
02:11 Intro to the guest and topic
04:30 Compression might make sense depending on the amount of space you’ll free up
08:31 Do you compress if the growth is temporary?
10:20 What is the tipping point to determine if you need to reclaim space?
13:19 Recovering from the shrink database process
16:07 You set parameters, warnings and limitations and then proceed with caution
18:11 There’s more than one way to shrink
21:13 Cleanup and rebuild, then the shrinkfile will go more quickly
22:57 There are circumstances where shrinking may be necessary and we should learn more
24:21 Shrinking the transaction log
27:14 SQL Family Questions
34:28 Closing Thoughts
Our Guest
Nigel Foulkes-Nock
Nigel Foulkes-Nock is a SQL Server DBA who supports and embraces technology, recognizing the need to provide true Business value while maintaining technical stability.
He enjoys bringing order out of chaos and embraces the power of education through self-learning and helping others.
Based in (old) South Wales, Nigel is a regular attendee at the Cardiff MSFT Stack and also the SQLBristol User Groups.
Nigel’s blog: https://nigelDBA.com. He can be reached on Twitter using @nigelDBA.
Meet the Hosts
Carlos Chacon
With more than 10 years of working with SQL Server, Carlos helps businesses ensure their SQL Server environments meet their users’ expectations. He can provide insights on performance, migrations, and disaster recovery. He is also active in the SQL Server community and regularly speaks at user group meetings and conferences. He helps support the free database monitoring tool found at databasehealth.com and provides training through SQL Trail events.
Eugene Meidinger
Eugene works as an independent BI consultant and Pluralsight author, specializing in Power BI and the Azure Data Platform. He has been working with data for over 8 years and speaks regularly at user groups and conferences. He also helps run the GroupBy online conference.
Kevin Feasel
Kevin is a Microsoft Data Platform MVP and proprietor of Catallaxy Services, LLC, where he specializes in T-SQL development, machine learning, and pulling rabbits out of hats on demand. He is the lead contributor to Curated SQL, president of the Triangle Area SQL Server Users Group, and author of the books PolyBase Revealed (Apress, 2020) and Finding Ghosts in Your Data: Anomaly Detection Techniques with Examples in Python (Apress, 2022). A resident of Durham, North Carolina, he can be found cycling the trails along the triangle whenever the weather's nice enough.
Want to Submit Some Feedback?
Did we miss something or not quite get it right? Want to be a guest or suggest a guest/topic for the podcast?