![]() Use that space to build the new copy of our index.SQL Server needs enough empty space in the database to build an entirely new copy of the index, so it’s going to: Guess what happens when you rebuild the index? In this case, with 98% fragmentation, I bet you’re going to want to rebuild that index. So what do you do to fix this? I’m going to hazard a guess that you have a nightly job set up to reorganize or rebuild indexes when they get heavily fragmented. Remember how our object used to be perfectly in order? Well, now it’s in reverse order because SQL Server took the stuff at the end and put it at the beginning. ![]() To do its work, SHRINKDATABASE goes to the end of the file, picks up pages there, and moves them to the first open available spaces in our data file. Suddenly, we have a new problem: our table is 98% fragmented. ( DB_ID ( N 'WorldOfHurt' ), OBJECT_ID ( N 'dbo.Messages' ), NULL, NULL, 'DETAILED' ) Īnd the results: After the shrink, 98% fragmented. + CASE is_percent_growth WHEN 1 THEN ' ' ELSE '' ENDįROM sys.database_files A LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id + CASE max_size WHEN 0 THEN 'DISABLED' WHEN -1 THEN ' Unrestricted'ĮLSE ' Restricted to ' + CAST(max_size/(128*1024) AS VARCHAR(10)) + ' GB' END WHEN 1 THEN CAST(growth AS VARCHAR(10)) + '% -' ELSE '' END , = 'By ' + CASE is_percent_growth WHEN 0 THEN CAST(growth/128 AS VARCHAR(10)) + ' MB -'
0 Comments
Leave a Reply. |