sql server replication: I have a table on sql2k under replication as below (it is a huge job to
change schema you know that). the table has 500K+ rows.

now I need to add one column using script
exec sp_repladdcolumn 'TheTable', 'AllowRebuy', 'tinyint NULL', 'all'
and the script took 5+ minutes to run; can any one explain why a nullable
column should take that long time to go and is there any way to reduce the
cost?


----Table definition here:
CREATE TABLE [dbo].[TheTable] (
[TheTableId] [int] IDENTITY (1, 1) NOT NULL ,
[TblName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PWord] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT
[DF_TheTable_PWord] DEFAULT (null),
[PWordRequired] [tinyint] NOT NULL CONSTRAINT [DF_TheTable_PWordRequired]
DEFAULT (0),
[TblTypeId] [int] NOT NULL ,
[LoginTypeId] [tinyint] NOT NULL ,
[GameTypeId] [int] NOT NULL ,
[RoomId] [smallint] NOT NULL ,
[StartRegistration_GMT] [datetime] NULL ,
[EndRegistration_GMT] [datetime] NULL ,
[StartTheTable_GMT] [datetime] NULL ,
[BreakTime_SEC] [int] NOT NULL CONSTRAINT [DF_TheTable_BreakTime_SEC]
DEFAULT (0),
[BreakFrequency_SEC] [int] NOT NULL CONSTRAINT
[DF_TheTable_BreakFrequency_SEC] DEFAULT (0),
[MinParticipating] [int] NOT NULL ,
[MaxParticipating] [int] NOT NULL ,
[StartingChips] [money] NOT NULL ,
[EntryFee] [smallmoney] NOT NULL ,
[BuyInFee] [smallmoney] NOT NULL CONSTRAINT [DF_TheTable_UserFeeToPool]
DEFAULT (0),
[TblCurrency] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AllInAllowed] [smallint] NOT NULL ,
[TblCurrentParticipants] [int] NOT NULL CONSTRAINT
[DF_TheTable_TblCurrentParticipants] DEFAULT (0),
[TblBreakNumber] [int] NOT NULL CONSTRAINT [DF_TheTable_TblBreakNumber]
DEFAULT (0),
[TblStatusTypeId] [tinyint] NOT NULL CONSTRAINT [DF_TheTable_TblStatusId]
DEFAULT (0),
[Pool] [money] NOT NULL CONSTRAINT [DF_TheTable_Prize] DEFAULT (0),
[NumberOfRegistered] [int] NOT NULL CONSTRAINT
[DF_TheTable_NumberOfRegistered] DEFAULT (0),
[SiteId] [tinyint] NOT NULL CONSTRAINT [DF_TheTable_SiteId] DEFAULT (7),
[SeatsPerTable] [smallint] NOT NULL ,
[DateTime_GMT] [datetime] NOT NULL ,
[Requester] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[HousePoolContribution] [money] NOT NULL CONSTRAINT
[DF_TheTable_HousePoolContribution] DEFAULT (0),
[DisplayDuration_Sec] [int] NOT NULL ,
[FillInDuration_Sec] [int] NOT NULL ,
[LevelDuration_Sec] [int] NOT NULL CONSTRAINT
[DF_TheTable_LevelDuration_Sec_1] DEFAULT (600),
[LevelDurationTOrH] [tinyint] NOT NULL CONSTRAINT
[DF_TheTable_LevelDurationTOrH_1] DEFAULT (2),
[ToInformGS] [tinyint] NOT NULL CONSTRAINT [DF_TheTable_ToInformGS] DEFAULT
(1),
[FixedOperationMode] [tinyint] NOT NULL CONSTRAINT
[DF_TheTable_FixedOperationMode] DEFAULT (2),
[TblPayOutSchemeId] [int] NOT NULL ,
[RefundTypeId] [tinyint] NOT NULL CONSTRAINT [DF_TheTable_RefundTypeId]
DEFAULT (0),
[LastRegTypeId] [tinyint] NULL ,
[LastRegTicketId] [bigint] NULL ,
[EliminatedAtCancel] [int] NULL ,
[TblLevelSchemeId] [int] NOT NULL ,
[Reschedule] [tinyint] NOT NULL CONSTRAINT [DF_TheTable_Reschedule] DEFAULT
(1),
[MerchantGroupId] [smallint] NOT NULL CONSTRAINT
[DF_TheTable_MerchantGroupId] DEFAULT (99),
[TemplateId] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ExternalNotify] [tinyint] NOT NULL CONSTRAINT [DF__Tbl__Exter__4873D87E]
DEFAULT (0),
[BetLimitType] [tinyint] NOT NULL CONSTRAINT [DF__Tbl__BetLi__16926427]
DEFAULT (0),
[AccessLimit] [money] NOT NULL CONSTRAINT [DF__Tbl__Acces__1A62F50B]
DEFAULT (0),
[MoneySchemeId] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [DF__Tbl__Money__1E3385EF] DEFAULT ('RM:RM'),
[UserPoolContribution] [money] NOT NULL CONSTRAINT
[DF__Tbl__UserP__220416D3] DEFAULT (0),
[PCurrency] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF__Tbl__PCurr__25D4A7B7] DEFAULT ('USD'),
[ThemeId] [smallint] NOT NULL CONSTRAINT [DF__Tbl__Theme__27DCEBBA] DEFAULT
(0),
[ScheduleId] [int] NULL CONSTRAINT [DF__Tbl__Sched__751C61C3] DEFAULT (null),
[SatelliteLevelId] [int] NULL CONSTRAINT [DF__Tbl__Satel__78ECF2A7] DEFAULT
(null),
[TheTableIdNextLevel] [int] NULL CONSTRAINT [DF__Tbl__Tbln__7CBD838B]
DEFAULT (null),
[ChangeMask] [int] NOT NULL CONSTRAINT [DF__Tbl__Chang__008E146F] DEFAULT
(0),
[PayFeeMax] [smallint] NOT NULL CONSTRAINT [DF__Tbl__PayFe__045EA553]
DEFAULT (0),
[PayFeeNumber] [smallint] NOT NULL CONSTRAINT [DF__Tbl__PayFe__082F3637]
DEFAULT (0),
[Active] [tinyint] NULL CONSTRAINT [DF__Tbl__Activ__0BFFC71B] DEFAULT (0),
[SatelliteInstance] [int] NULL CONSTRAINT [DF__Tbl__Satel__0FD057FF]
DEFAULT (null),
[StartTOrigin_GMT] [datetime] NULL CONSTRAINT [DF__Tbl__Start__13A0E8E3]
DEFAULT (null),
[TblText] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF__Tbl__TblT__177179C7] DEFAULT (null),
[CellSize] [int] NULL CONSTRAINT [DF__Tbl__CellS__1B420AAB] DEFAULT (0),
[OutOfCellQualified] [tinyint] NOT NULL CONSTRAINT
[DF__Tbl__OutOf__6352B743] DEFAULT (0),
[IsOriginal] [int] NOT NULL ,
[ScheduleIdOrigin] [int] NULL ,
[ShowupTime_GMT] [datetime] NULL CONSTRAINT [Def_TheTable_ShowupTime_GMT]
DEFAULT (getdate()),
[HandSpeed] [smallint] NULL ,
[RestrictionFlag] [tinyint] NULL ,
[CashToQualifiers] [money] NOT NULL CONSTRAINT [DF__Tbl__CashT__0EA3FC40]
DEFAULT (0),
[DurHFH] [int] NOT NULL CONSTRAINT [DF__Tbl__DurHF__0F982079] DEFAULT (0),
[AccumulationType] [tinyint] NOT NULL CONSTRAINT [DF__Tbl__Accum__108C44B2]
DEFAULT (0),
[TimeBank] [smallint] NULL ,
[PPFee] [smallmoney] NULL ,
[Shootout] [tinyint] NULL ,
[HP] [smallmoney] NULL ,
[FP] [smallmoney] NULL ,
[CP] [smallmoney] NULL ,
[HHOrBounty] [tinyint] NULL CONSTRAINT [DF_TheTable_HHOrBounty] DEFAULT (0),
[RestrictionsAdjustDate] [tinyint] NULL ,
CONSTRAINT [PK_TheTable] PRIMARY KEY CLUSTERED
(
[TheTableId]
) ON [PRIMARY] ,
CONSTRAINT [FK_TheTable_GameType] FOREIGN KEY
(
[GameTypeId]
) REFERENCES [dbo].[GameType] (
[GameTypeId]
),
CONSTRAINT [FK_TheTable_LoginType] FOREIGN KEY
(
[LoginTypeId]
) REFERENCES [dbo].[LoginType] (
[LoginTypeId]
),
CONSTRAINT [FK_TheTable_MerchantGroupType] FOREIGN KEY
(
[MerchantGroupId]
) REFERENCES [dbo].[MerchantGroupType] (
[MerchantGroupId]
),
CONSTRAINT [FK_TheTable_Rooms] FOREIGN KEY
(
[RoomId]
) REFERENCES [dbo].[Rooms] (
[RoomId]
),
CONSTRAINT [FK_TheTable_SatelliteInstances] FOREIGN KEY
(
[SatelliteInstance]
) REFERENCES [dbo].[SatelliteInstances] (
[SatelliteInstance]
),
CONSTRAINT [FK_TheTable_SatelliteLevels] FOREIGN KEY
(
[SatelliteLevelId]
) REFERENCES [dbo].[SatelliteLevels] (
[SatelliteLevelId]
),
CONSTRAINT [FK_TheTable_Schedules] FOREIGN KEY
(
[ScheduleId]
) REFERENCES [dbo].[Schedules] (

That's a big table! IIRC it has to place a table lock on the table and then
generate a snapshot.

Depending on what is going on in your database at the time it could be a
lengthy process.

--
RelevantNoise.com - dedicated to mining blogs for business intelligence.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
[quoted text, click to view]