Solved

SQL Rebuild Index After Shrink?

Posted on 2013-12-18
6
5,297 Views
Last Modified: 2013-12-20
Hello:

I read somewhere that it is a good idea to run a rebuild of the index after shrinking a database.  Is that correct?

Is "rebuild" the same thing as "reorganize"?

How can you run rebuild index against an entire database and just individual tables at a time?

TBSupport
0
Comment
Question by:TBSupport
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 40

Accepted Solution

by:
lcohan earned 250 total points
ID: 39727858
Why would you shrink a database first of all??
You should never do that in my opinion (and others) and to get T-log under control you need at least a daily backup full+t-log to be done.

http://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/

Rebuild is NOT the same as Reorganize and to reindex all tables in a DB you can use a SQL Script like:

http://www.mssqltips.com/sqlservertip/1367/sql-server-script-to-rebuild-all-indexes-for-all-tables-and-all-databases/

In SQL Enterprise Edition you can REBUILD an index with ONLINE=ON therefor not locking the table.
0
 
LVL 13

Assisted Solution

by:magarity
magarity earned 250 total points
ID: 39727875
So let's assume you mistakely shrank your database already (see other's post about not doing it in the first place).  There are two shrink options as I recall, one that simply lops off trailing empty space and a nuclear option that reorganizes so that all internal empty fragments are filled first then the trailing space lopped off.  
If you did the first, then you can probably go either way and rebuild or not.  Safer to just go ahead and do so.  If you did the second, your database is in pain and you need to rebuild all indexes if just to give the system some breathing space.  Reuild them again after some modest activity has taken place like in a day or so. Then read all the articles linked in the article linked by lcohan.
0
 
LVL 40

Expert Comment

by:lcohan
ID: 39727878
Lot more about reindexing and maintenance at: http://ola.hallengren.com/
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 13

Expert Comment

by:magarity
ID: 39727884
PS - here's a handy script that will rebuild all indexes (it may take some time to execute): http://www.mssqltips.com/sqlservertip/1367/sql-server-script-to-rebuild-all-indexes-for-all-tables-and-all-databases/
0
 
LVL 1

Author Comment

by:TBSupport
ID: 39728058
Whether shrink is used or not, I need to reduce the amount of space being taken up on a server by a 10GB database.  There is empty space in the database.  I'm trying to get rid of that empty space, reduce the database size, and therefore free up more space on the server.

If shrink is not the correct option, then please tell me the alternative, based on what I have outlined as my needs.

Much appreciated!

TBSupport
0
 
LVL 13

Expert Comment

by:magarity
ID: 39728095
I know corporate internal pricing for storage can be out of touch with reality but it's impressive that you're in capacity trouble at 10GB and your only recourse is shrinking the db. Have you asked your server administrator to grow the partition and been turned down?
You've done all the usual transaction log backup and removal?
You can also look at removing unneeded non-clustered indexes or moving them to another partition.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question