Help? Fragmentation still high after drop and re-create indexes...

Hello, i am encountering a strange behaviour on SQL 2005 with dropping and re-creating my indexes not affecting my fragmentation.

I have many tables with the same structure, and on each table i have 3 indexes defined, one clustered and 2 other indexes. After some time, they get pretty fragmented. When i drop the indexes, and re-create across all my tables, some of my tables fragmentation % is still very high, over 60%. Yet some other tables report fragmentations of 0 across my 3 indexes.

I use this query to find out fragmentation:

SELECT a.index_id, name, avg_fragmentation_in_percent, index_type_desc
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('NS_Time_Daily'),
NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id

I also checked my physical disk fragmentation and it has no issue, i.e. it does not recommend a defrag (im on win server 2003)

Why would this be happening?

Any thoughts will be greatly appreciated!! Thanks, Jeff

CREATE TABLE [dbo].[NS_Time_Daily]
(
[MemberId] [bigint] NOT NULL,
[lft] [bigint] NULL,
[rgt] [bigint] NULL,
[modelsiteID] [bigint] NOT NULL,
[depth] [bigint] NOT NULL,
[lvl] [bigint] NOT NULL,
[lvlPosition] [bigint] NOT NULL
)

DROP INDEX [NS_Time_Daily$IDX_LP] ON [NS_Time_Daily]
DROP INDEX [NS_Time_Daily$IDX_LR] ON [NS_Time_Daily]
DROP INDEX [NS_Time_Daily$IDX_MM] ON [NS_Time_Daily]

CREATE CLUSTERED INDEX [NS_Time_Daily$IDX_MM] ON [NS_Time_Daily](ModelSiteId, MemberId)
CREATE UNIQUE INDEX [NS_Time_Daily$IDX_LP] ON [NS_Time_Daily](ModelSiteId, Lvl, LvlPosition)
CREATE UNIQUE INDEX [NS_Time_Daily$IDX_LR] ON [NS_Time_Daily](ModelSiteId, Lft, Rgt)

Hi Jeff,

How big is the table mate?

Could you send us the output of the following statement before and after you do the re-index?

selectobject_name(object_id)as TableName, index_id, index_type_desc,

avg_fragmentation_in_percent, fragment_count, avg_fragment_size_in_pages, page_count,

avg_page_space_used_in_percent

from sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('NS_Time_Daily'),

NULL,NULL,NULL)

regards

Jag Sandhu

Help: trouble dropping master key

It could be trivial question.

I have trouble dropping master key. when i execute the following query..

open master key decryption by password = 'pass1234$'

alter master key drop encryption by password = 'pass1234$'

close master key

drop master key

go

I get the following result..

Msg 15558, Level 16, State 1, Line 3

Cannot drop encryption by password '********'.

Msg 15580, Level 16, State 1, Line 5

Cannot drop master key because certificate 'BackofficeServiceAccount' is encrypted by it.

But 'BackofficeServiceAccount' was already dropped.

Can any one help me how to drop master key?

I appreciate any input..

regards...

I realized the script was creating login and certificate with the same name.

Dropping the certificate first, helped dropping the master key...