Index defrag and shrinkDB

Question

Index defrag and shrinkDB

terry999

SSCertifiable

Points: 5036
More actions
August 5, 2010 at 3:41 am

#222125

Hi have a large table in mssql 2005
I've noticed there are several non clustered indexes which are not needed or can be combined.
There is one clustered index on a datetime field which is not unique - however almost all queries use a date range.
OK to use offline mode because disk space is an issue and want it to be quick
I intend to
[font="Courier New"]1. Drop redundant indexes
2. alter index ALL on mytable rebuild with online=OFF
3. create new non-clustered index[/font]
I will then have lots of space left in the DB file.
4. shrink DB to return some of the space.
I'm confused about ShrinkDB using dialogue box if I "choose Reorganize files before releasing unused space" = DBCC SHRINKDATABASE with TRUNCATEONLY. It releases space at the end end without moving pages.
Whereas DBCC SHRINKDATABASE NOTRUNCATE reorganizes the pages - so actually size doesn't go down.
So would it make sense to do
[font="Courier New"]1. DBCC SHRINKDATABASE NOTRUNCATE and then
2. DBCC SHRINKDATABASE with TRUNCATEONLY[/font]
What effects will these 2 options have on the resulting DB.
thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Don't shrink!

Shrinking causes massive index fragmentation. It's going to undo all the work that your index defrag did, will probably leave the indexes more fragmented than they were before the rebuild.

Unused space in the data file is not a problem. It's necessary for SQL's operation. Leave it alone.

See http://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/ and everything it links to.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

terry999 SSCertifiable Points: 5036 More actions · Answer 2

Thanks you've saved me alot of time and fragmentation.

In my situation the DB has received a huge data import, several years of data its now going to grow much slower as it gets daily much smaller amounts.

Its a write at night read only during daytime DB.

The current situation is for every 1GB stored in this table there are 1.2 GB off indexes, dropping these redundant indexes and creating just 2 new ones turns this into 1GB data with 0.4GB index.

I'm going to be left with huge amount of space which is unlikely to be filled in my lifetime.

1. drop redundant indexes

2. shrinkDB and leave growth space

3. reindex table (ALTER INDEX myTable ALL)

4. Create new index(s)

With using ALter Index (offline) - will the file still grow?

After what I read reorganise won't do because the indexes will be too fragmented after the shrink for it to be useful.

Just quite confused read alot of articles and got info overload.

Thanks Terry

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Drop the redundant indexes.

Do a once-off shrink, shrink the database to a reasonable size (leave enough free space for index rebuilds and a couple months of growth).

Ensure there's a reasonable growth size for the files

Rebuild all your indexes (online or offline, both will cause the DB to grow if there isn't enough free space)

Once that's done, leave what free space is there alone.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

terry999 SSCertifiable Points: 5036 More actions · Answer 4

Thanks..

Really saved me alot of hassle on this one.