2

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?

geogeogeo
  • 121
  • 2
  • Pls Refer the Following Question I hope this helps for you http://dba.stackexchange.com/questions/18117/mitigate-negative-effects-of-shrinking. – Heisenberg Oct 31 '14 at 04:29
  • I've just been looking at similar things. I also use sp_blitz as well as Ola Hallengrens scripts. Have a look at https://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 08: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 08:21

1 Answers1

0

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.

Sir Swears-a-lot
  • 3,233
  • 2
  • 28
  • 48