I've been given the task of finding out why our server is slow. I ran a stored procedure I downloaded called sp_Blitz. It let me know that our database had auto-shrink enabled, and to disable it. So, I did that. I don't know how long it's been enabled, but I'd guess at least 3-4 years. When I checked the fragmentation percent of the indexes, a lot of them were 60% - 99%. I then ran a script to rebuild the indexes and update the statistics, but the fragmentation percent didn't really change for these indexes.

Is there something else I need to do to repair what auto-shrink has done?

Any advice for me?

  • Pls Refer the Following Question I hope this helps for you dba.stackexchange.com/questions/18117/…. – Heisenberg Oct 31 '14 at 4:29
  • I've just been looking at similar things. I also use sp_blitz as well as Ola Hallengrens scripts. Have a look at ola.hallengren.com. there are some great scripts for maintenence tasks like rebuilding indexes. The advice I've found is that shrinking a db leads to fragmentation, which convention wisdom says is bad. Because fragmented indexes hurt performance. However depending upon size of the table, type and usage of tge index, a little fragmentation will probably have little or no effect. Achieving zero fragmentation may be impossible. – Sir Swears-a-lot Oct 31 '14 at 8:12
  • The question is how much fragmentation is acceptable? Do you have a performance problem that indicates that index fragmentation is actually an issue? Assuming you do, one way to reduce fragmentation on large/critical tables is to create a new file group and move/rebuild the table into it. Another way although maybe not practical would be to export your data and reimport. It would remove fragmentation but I doubt it would be worth the time /effort /inconvenience for the gain you would achieve. – Sir Swears-a-lot Oct 31 '14 at 8:21

No auto shrink can't permanently damage a database. It may lead to highly fragmented tables and indexes which may hurt performance. The fragmentation could be difficult to remove completely, but minor fragmentation itself shouldn't be a concern. You need to assess whether this is really an issue for you worth spending time and effort to fix.

Not the answer you're looking for? Browse other questions tagged or ask your own question.