DB size after index deletion has not been changed?

Question

DB size after index deletion has not been changed?

Michal Mokros

Hall of Fame

Points: 3259
More actions
August 15, 2007 at 1:00 am

#119965

Hi everybody,
we (our company) have decided, that our database is overindexed (600 indexes). I have just deleted 100 indexes (mostly on datetime and int columns, that are used for auditing purposes). Is it normal, that database size has not changed? Even after shrink I have noticed no change.
Thanks for your explanation,
Michal

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

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

michaela SSCertifiable Points: 5649 More actions · Answer 1

I would say to check whether your db is in full recovery mode; if it is then take a tran log backup then shrink it / or shrink the index file only. It should work.

About deleting the indexes, this is pretty much tricky. I am sure you know better your db there, otherwise the script below will check the unused indexes:

--lists any index not used since the last time SQL Server was recycled.

select

object_name(i.object_id), i.name

from

sys.indexes i, sys.objects o

where

i.index_id NOT IN (select s.index_id

from sys.dm_db_index_usage_stats s

where s.object_id=i.object_id and

i

.index_id=s.index_id and

database_id

= db_id('yourDB') )

and

o.type = 'U'

and

o.object_id = i.object_id

order

by object_name(i.object_id) asc

go

--Unused indexes will not appear in sys.dm_db_index_usage_stats.

Another usefull script:

--lists rarely-used indexes. Because the number and type of accesses are tracked in dmvs, this

--procedure can find indexes that are rarely useful. Because the cost of these indexes is incurred

--during maintenance (e.g. insert, update, and delete operations), the write costs of rarely-used

--indexes may outweigh the benefits

declare

@dbid int

select

@dbid = db_id()

select

objectname=object_name(s.object_id), s.object_id

, indexname=i.name, i.index_id

, user_seeks, user_scans, user_lookups, user_updates

from

sys.dm_db_index_usage_stats s,

sys.indexes i

where

database_id = @dbid

and

objectproperty(s.object_id,'IsUserTable') = 1

and

i.object_id = s.object_id

and

i.index_id = s.index_id

order

by (user_seeks + user_scans + user_lookups + user_updates) asc

Michal Mokros Hall of Fame Points: 3259 More actions · Answer 2

I know these methods to find unused indexes. I have no problem with choosing indexes to delete, but I have expected reducing DB size.

Recovery model is Simple.

Greg Walker Old Hand Points: 369 More actions · Answer 3

I've sometimes run into this issue when scrubbing our production DB for the developers. You may need to do an index rebuild on the affected tables to see a space reduction.

To test, run:

execute sp_mstablespace 'tablename'

against one of the tables in question, then do:

alter index all on tablename rebuild

and re-run teh sp_mstablespace proc. If you see a reduction in space, you may want to do this to the other tables.

Be careful doing this on a production server, it locks the tables while it rebuilds the indexes.

I have seen DBs that refuse to shrink the MDF/NDF files until you've run this on the tables to defrag and organize the tables.

As always, take the advice with a grain of salt and test before doing this to your production servers.

Greg Walker
DBA, ExpenseWatch.com

Michal Mokros Hall of Fame Points: 3259 More actions · Answer 4

Thanks Greg. I tried to rebuild indexes of one table before but nothing changed. But this was probably because of too little data in this table. I have tried it now and space was reduced, so I will do it for all affected tables.

Michal