Sharmarke Afgab Posted July 28, 2017 Share Posted July 28, 2017 Sometimes you run out of SQL storage space and it is tempting and the easy option to shrink your databases. While this practice on a small scale, addresses your immediate needs, it is something that eventually becomes a zero sum game. Shrinking a data file causes index fragmentation, takes up resources, and ultimately leads to performance issues. It should not be part of your regular maintenance exercise as you get into a vicious shrink/grow cycle. If you shrink and you have log back up part of your maintenance plan, this information is backed up and thus takes up unnecessary space in your backup drive. As you process data or users perform additional work in your review case, the database will auto-grow which leads you back to the shrinking again. My advice: allocate the proper amount of storage based on usage, add more storage when needed, and STOP shrinking. Link to comment Share on other sites More sharing options...
Sharmarke Afgab Posted July 28, 2017 Author Share Posted July 28, 2017 Sometimes you run out of SQL storage space and it is tempting and the easy option to shrink your databases. While this practice on a small scale, addresses your immediate needs, it is something that eventually becomes a zero sum game. Shrinking a data file causes index fragmentation, takes up resources, and ultimately leads to performance issues. It should not be part of your regular maintenance exercise as you get into a vicious shrink/grow cycle. If you shrink and you have log back up part of your maintenance plan, this information is backed up and thus takes up unnecessary space in your backup drive. As you process data or users perform additional work in your review case, the database will auto-grow which leads you back to the shrinking again. My advice: allocate the proper amount of storage based on usage, add more storage when needed, and STOP shrinking. Link to comment Share on other sites More sharing options...
IPRO Employee choltzworth Posted July 28, 2017 IPRO Employee Share Posted July 28, 2017 Here are a few resources to read up further on why you should not shrink your data files. https://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/ https://straightpathsql.com/archives/2009/01/dont-touch-that-shrink-button/ https://straightpathsql.com/archives/2009/01/shrinking-is-a-popular-topic/ https://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/ http://www.sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/ https://thomaslarock.com/2009/03/when-to-use-auto-shrink/ Link to comment Share on other sites More sharing options...
IPRO Employee choltzworth Posted July 28, 2017 IPRO Employee Share Posted July 28, 2017 Here are a few resources to read up further on why you should not shrink your data files. https://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/ https://straightpathsql.com/archives/2009/01/dont-touch-that-shrink-button/ https://straightpathsql.com/archives/2009/01/shrinking-is-a-popular-topic/ https://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/ http://www.sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/ https://thomaslarock.com/2009/03/when-to-use-auto-shrink/ Link to comment Share on other sites More sharing options...
Lukeyluke Posted November 15, 2017 Share Posted November 15, 2017 What are your 5 best tips for maintaining SQL, environments that just run eCap and/or eclipse? thanks, L Link to comment Share on other sites More sharing options...
Lukeyluke Posted November 15, 2017 Share Posted November 15, 2017 What are your 5 best tips for maintaining SQL, environments that just run eCap and/or eclipse? thanks, L Link to comment Share on other sites More sharing options...
Lukeyluke Posted November 15, 2017 Share Posted November 15, 2017 What are your 5 best tips for maintaining SQL, environments that just run eCap and/or eclipse? thanks, L Link to comment Share on other sites More sharing options...
Cloud Solutions Architect Barrett Gardner Posted November 28, 2017 Cloud Solutions Architect Share Posted November 28, 2017 Hi Luke, We don't have a "Top 5 SQL tips" for running eCapture/Eclipse per se, however we provide a SQL best practices document available for download on myipro.iprotech.com. This is listed under Resources > ADD Workflow > Best Practice & Implementation documents. This lists our recommendations for install, configuration, data/log file management, maintenance, backup/restore and alerts monitoring. Link to comment Share on other sites More sharing options...
Cloud Solutions Architect Barrett Gardner Posted November 28, 2017 Cloud Solutions Architect Share Posted November 28, 2017 Hi Luke, We don't have a "Top 5 SQL tips" for running eCapture/Eclipse per se, however we provide a SQL best practices document available for download on myipro.iprotech.com. This is listed under Resources > ADD Workflow > Best Practice & Implementation documents. This lists our recommendations for install, configuration, data/log file management, maintenance, backup/restore and alerts monitoring. Link to comment Share on other sites More sharing options...
Cloud Solutions Architect Barrett Gardner Posted November 28, 2017 Cloud Solutions Architect Share Posted November 28, 2017 Hi Luke, We don't have a "Top 5 SQL tips" for running eCapture/Eclipse per se, however we provide a SQL best practices document available for download on myipro.iprotech.com. This is listed under Resources > ADD Workflow > Best Practice & Implementation documents. This lists our recommendations for install, configuration, data/log file management, maintenance, backup/restore and alerts monitoring. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.