Posted by: Colin Smith
SQL Admin, SQL Administration, SQL Administration. MS SQL, SQL Server
In most cases we would like to reduce fragmentation and try to come up with ways, like reducing fill factor, to minimize the amount of fragmentation that occurs when doing inserts and updates. I , however, need to create fragmentation. I am sure that you are asking yourself why I might need to do this. That is a great question and the answer is because I need to test some re-indexing scripts and get timings on how long they are going to run. To accomplish this, I am going to use another feature of SQL Server that I try to NEVER use. The dreaded Shrink Database. The reason I am using shrink database is that it, by its nature, causes fragmentation. So today is just a day of worst practices for me. I am doing this is a sandbox testing environment and I would not recommend ever using Shrink Database Unless you absolutely have to. If you do run Shrink Database for any reason, just know that you are going to need to clean up your indexes after and that is going to grow your database again.