The server I've in herited has had Auto-shrink enabled for some databases, DBCCShrinkdatabase run on all databases except tempdb and various other bits and pieces.
Now, everything is running slow and no-one seems to know why.
From some research I've done (courtesy of google), it seems that all of the above will cause index fragmentation which slow down both writing to tables (done once a day) and reads (done after all the writes have completed).
Now, I'm after a bit of advice here.
We're running SQL Server 2008 R2 and I'm not sure whether to rebuild the indexes on all tables (that could take a while) or do an index defrag. I'm also not sure of the state of play for any of the tables or indexes with regards to fragmentation.
Thanks for the link. Do you know if this will work in 2008 R2? Also, I want to get a list of all tables in all databases and the fragmentation levels so I can work on the worst ones first. Is that possible?
Look at Tara's script. it will do what you are looking for. You should not enable AUTO SHRINK and shrink database unless it's a last resort.Performance will be horrible as you said.
Thanks for that. I need to shrink the databases to save disk space (there's no more room on the server and it won't take any more drives). I'm going to set it up so that the indexes are rebuilt/re-organised after that taking the most defragmented first.
Auto-shrink, shrinkfile and other horrors
Posted - 01/15/2013 : 06:30:47
