SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Auto-shrink, shrinkfile and other horrors
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 01/15/2013 :  06:30:47  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2870 Posts

Posted - 01/15/2013 :  07:31:40  Show Profile  Reply with Quote
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 - 01/15/2013 :  07:54:37  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 01/15/2013 :  10:16:06  Show Profile  Reply with Quote
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 - 01/15/2013 :  10:18:19  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 01/15/2013 :  10:37:03  Show Profile  Reply with Quote
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.

Edited by - sodeep on 01/15/2013 10:37:16
Go to Top of Page

lopez
Starting Member

USA
8 Posts

Posted - 02/08/2013 :  06:08:36  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000