SQL Rebuild Index After Shrink?

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
LVL 1
TBSupportAsked:
Who is Participating?
 
lcohanConnect With a Mentor Database AnalystCommented:
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
 
magarityConnect With a Mentor Commented:
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
 
lcohanDatabase AnalystCommented:
Lot more about reindexing and maintenance at: http://ola.hallengren.com/
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
magarityCommented:
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
 
TBSupportAuthor Commented:
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
 
magarityCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.