Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rmg1
Constraint Violating Yak Guru
256 Posts |
 Posted - 2013-01-15 : 06:30:47
|
| Hi allThe 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.Any help gratefully received on this one. |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2013-01-15 : 07:31:40
|
| http://weblogs.sqlteam.com/tarad/archive/2007/11/27/60415.aspxJimEveryday I learn something that somebody else already knew |
 |
|
|
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2013-01-15 : 07:54:37
|
| 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? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2013-01-15 : 10:16:06
|
| 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. |
|
|
|
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2013-01-15 : 10:18:19
|
| 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.Looks like I've got a lot of work ahead of me. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2013-01-15 : 10:37:03
|
| If you are worried about performance I would suggest you get more drive space or cleanup space. Even Rebuilding index will need space for logs. |
|
|
|
lopez
Starting Member
8 Posts |
|
|
|
|
|
Auto-shrink, shrinkfile and other horrors