• Before you do a shrink and a index defrag, ask yourself what has to happen while the index is running. Work through in your own mind what space is used within the database, and how previously used space might get reused.

    If you still want to go ahead with the shrink and defrag, then fine, but do not be surprised if you end up with much the same space used and free space.

    The question of why the database may be larger in test than production needs to be answered. Are you copying production to test, and if so how do you deal with issues of data protection.

    It is very seldom that the most effective way you deal with testing is to have a copy of production data. While production data may have a wide range of data values, most of this is not beneficial to testing and often just serves to slow down the test process. Also it will not have the edge cases that you need to include in your testing.

    These issues cannot be solved overnight, but if you do not start planning how to deal with them then code with bugs will find its way to production, and you will need to dedicate a few 100GB more disk space to your testing than is needed.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara