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.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Auto-shrink, shrinkfile and other horrors

Author  Topic 

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2013-01-15 : 06:30:47
Hi all

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.

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.aspx

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

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?
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

lopez
Starting Member

8 Posts

Posted - 2013-02-08 : 06:08:36
Turn Auto shrink off it could be the main cause of your performance degradation
check out some article regarding this for more detail
http://blogs.msdn.com/b/sqlserverstorageengine/archive/2007/03/28/turn-auto-shrink-off.aspx
http://www.brentozar.com/blitz/auto-shrink-enabled/
Go to Top of Page
   

- Advertisement -