Indexes Blew up after migration from SQL 2000 to SQL 2005

i migrated sql 2000 sql 20005 client using backup/restore method. evverything perfect until weekend. on weekend had scheduled rebuild index job. after (i presume caused issue) database blew 18gb 80gb! 65% index. main issue table mhgroup.docmaster holds documents. (this document management system intervowen). basicly there 5 million records , 16gb of data , 46gb of indexes. table huge , there 33 non-clustered indexes. 1 clustered index created via key constraint. suspecting forwarded records wrong. thoughts?? below table sctipted.

use [mbdocs]  go  /****** object: table [mhgroup].[docmaster] script date: 09/02/2009 10:49:32 ******/  set ansi_nulls on  go  set quoted_identifier on  go  set ansi_padding on  go  create table [mhgroup].[docmaster](  [docname] [varchar](254) null,  [docnum] [float] not null,  [docsize] [float] null,  [editwhen] [datetime] null,  [docinuse] [char](1) null,  [entrywhen] [datetime] null,  [editprofilewhen] [datetime] null,  [indexed] [char](1) null,  [version] [int] not null,  [docloc] [varchar](254) null,  [author] [varchar](64) null,  [operator] [varchar](64) null,  [alias] [varchar](32) null,  [checkedout] [char](1) null,  [comments] [text] null,  [comindex] [char](1) null,  [archive_req] [char](1) null,  [t_alias] [varchar](32) null,  [c_alias] [varchar](32) null,  [subclass_alias] [varchar](32) null,  [archived] [char](1) null,  [inuseby] [varchar](64) null,  [indexable] [char](1) null,  [isrelated] [char](1) null,  [c1alias] [varchar](32) null,  [c2alias] [varchar](32) null,  [c3alias] [varchar](32) null,  [c4alias] [varchar](32) null,  [c5alias] [varchar](32) null,  [c6alias] [varchar](32) null,  [c7alias] [varchar](32) null,  [c8alias] [varchar](32) null,  [c9alias] [varchar](32) null,  [c10alias] [varchar](32) null,  [c11alias] [varchar](32) null,  [c12alias] [varchar](32) null,  [c13alias] [varchar](96) null,  [c14alias] [varchar](96) null,  [c15alias] [varchar](96) null,  [c16alias] [varchar](96) null,  [c29alias] [varchar](32) null,  [c30alias] [varchar](32) null,  [c31alias] [varchar](32) null,  [cdbl1] [float] null,  [cdbl2] [float] null,  [cdbl3] [float] null,  [cdbl4] [float] null,  [cbool1] [char](1) null,  [cbool2] [char](1) null,  [cbool3] [char](1) null,  [cbool4] [char](1) null,  [cdate1] [datetime] null,  [cdate2] [datetime] null,  [cdate3] [datetime] null,  [cdate4] [datetime] null,  [pending_close] [char](1) null,  [pending_irm] [int] null,  [irm] [int] null,  [archv_id] [float] null,  [lastuser] [varchar](64) null,  [retain] [int] null,  [lognodeaddr] [varchar](32) null,  [default_security] [char](1) null,  [is_secured] [char](1) null,  [type] [char](1) not null constraint [df__docmaster__type__25869641] default ('d'),  [msg_id] [varchar](512) null,  [extrnl_as_nrml] [char](1) not null constraint [df__docmaster__extrn__793dffaf] default ('n'),  [is_external] [char](1) null,  [has_attachment] [char](1) not null constraint [df__docmaster__has_a__0a688bb1] default ('n'),  [declarewhen] [datetime] null,  constraint [pk_docmaster] primary key nonclustered   (  [docnum] asc,  [version] asc  )with (pad_index = on, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on, fillfactor = 10) on [primary]  ) on [primary] textimage_on [primary]  go  set ansi_padding off  go  alter table [mhgroup].[docmaster] check add constraint [fk_dcmstr_archvtbl] foreign key([archv_id])  references [mhgroup].[archivetbl] ([archv_id])  go  alter table [mhgroup].[docmaster] check constraint [fk_dcmstr_archvtbl]  go  alter table [mhgroup].[docmaster] check add constraint [fk_dcmstr_custom1] foreign key([c1alias])  references [mhgroup].[custom1] ([custom_alias])  go  alter table [mhgroup].[docmaster] check constraint [fk_dcmstr_custom1]  go  alter table [mhgroup].[docmaster] check add constraint [fk_dcmstr_custom10] foreign key([c10alias])  references [mhgroup].[custom10] ([custom_alias])  go  alter table [mhgroup].[docmaster] check constraint [fk_dcmstr_custom10]  go  alter table [mhgroup].[docmaster] check add constraint [fk_dcmstr_custom11] foreign key([c11alias])  references [mhgroup].[custom11] ([custom_alias])  go  alter table [mhgroup].[docmaster] check constraint [fk_dcmstr_custom11]  go  alter table [mhgroup].[docmaster] check add constraint [fk_dcmstr_custom12] foreign key([c12alias])  references [mhgroup].[custom12] ([custom_alias])  go  alter table [mhgroup].[docmaster] check constraint [fk_dcmstr_custom12]  go  alter table [mhgroup].[docmaster] check add constraint [fk_dcmstr_custom2] foreign key([c1alias], [c2alias])  references [mhgroup].[custom2] ([cparent_alias], [custom_alias])  go  alter table [mhgroup].[docmaster] check constraint [fk_dcmstr_custom2]  go  alter table [mhgroup].[docmaster] check add constraint [fk_dcmstr_custom29] foreign key([c29alias])  references [mhgroup].[custom29] ([custom_alias])  go  alter table [mhgroup].[docmaster] check constraint [fk_dcmstr_custom29]  go  alter table [mhgroup].[docmaster] check add constraint [fk_dcmstr_custom3] foreign key([c3alias])  references [mhgroup].[custom3] ([custom_alias])  go  alter table [mhgroup].[docmaster] check constraint [fk_dcmstr_custom3]  go  alter table [mhgroup].[docmaster] check add constraint [fk_dcmstr_custom30] foreign key([c29alias], [c30alias])  references [mhgroup].[custom30] ([cparent_alias], [custom_alias])  go  alter table [mhgroup].[docmaster] check constraint [fk_dcmstr_custom30]  go  alter table [mhgroup].[docmaster] check add constraint [fk_dcmstr_custom31] foreign key([c29alias], [c30alias], [c31alias])  references [mhgroup].[custom31] ([cgrandparent_alias], [cparent_alias], [custom_alias])  go  alter table [mhgroup].[docmaster] check constraint [fk_dcmstr_custom31]  go  alter table [mhgroup].[docmaster] check add constraint [fk_dcmstr_custom4] foreign key([c4alias])  references [mhgroup].[custom4] ([custom_alias])  go  alter table [mhgroup].[docmaster] check constraint [fk_dcmstr_custom4]  go  alter table [mhgroup].[docmaster] check add constraint [fk_dcmstr_custom5] foreign key([c5alias])  references [mhgroup].[custom5] ([custom_alias])  go  alter table [mhgroup].[docmaster] check constraint [fk_dcmstr_custom5]  go  alter table [mhgroup].[docmaster] check add constraint [fk_dcmstr_custom6] foreign key([c6alias])  references [mhgroup].[custom6] ([custom_alias])  go  alter table [mhgroup].[docmaster] check constraint [fk_dcmstr_custom6]  go  alter table [mhgroup].[docmaster] check add constraint [fk_dcmstr_custom7] foreign key([c7alias])  references [mhgroup].[custom7] ([custom_alias])  go  alter table [mhgroup].[docmaster] check constraint [fk_dcmstr_custom7]  go  alter table [mhgroup].[docmaster] check add constraint [fk_dcmstr_custom8] foreign key([c8alias])  references [mhgroup].[custom8] ([custom_alias])  go  alter table [mhgroup].[docmaster] check constraint [fk_dcmstr_custom8]  go  alter table [mhgroup].[docmaster] check add constraint [fk_dcmstr_custom9] foreign key([c9alias])  references [mhgroup].[custom9] ([custom_alias])  go  alter table [mhgroup].[docmaster] check constraint [fk_dcmstr_custom9]  go  alter table [mhgroup].[docmaster] check add constraint [fk_dcmstr_dcsbclss] foreign key([c_alias], [subclass_alias])  references [mhgroup].[docsubclasses] ([c_alias], [subclass_alias])  go  alter table [mhgroup].[docmaster] check constraint [fk_dcmstr_dcsbclss]  go  alter table [mhgroup].[docmaster] check add constraint [fk_dcmstr_docclass] foreign key([c_alias])  references [mhgroup].[docclasses] ([c_alias])  go  alter table [mhgroup].[docmaster] check constraint [fk_dcmstr_docclass]  go  alter table [mhgroup].[docmaster] check add constraint [fk_dcmstr_doctypes] foreign key([t_alias])  references [mhgroup].[doctypes] ([t_alias])  go  alter table [mhgroup].[docmaster] check constraint [fk_dcmstr_doctypes]  

i don't see clustered index or non-clustered indexes in scripts provided.  have lot of constraints , single nonclustered index on composite primary key.  i'd suspect forwarded records problem, if there no clustered index on table.  output of:


select * sys.dm_db_index_physical_stats(db_id(), object_id('mhgroup.docmaster'), null, null, 'detailed')

---

jonathan kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.twitter.com/sqlsarg
http://www.sqlclr.net/
please click mark answer button if post solves problem!

SQL Server  >  SQL Server Database Engine