I am looking for some ideas.  We have a table that is growing from about 7GB
to 40+GB but most of the space is unused.  I am reviewing the insert proc
that loads it.  The part that is puzzling we use message queing to initiate
this insert on two identical databases.  Only one is exhibiting this problem.
Any ideas on what to look for would be appreciated.

Here is the result for sp_spaceused for the table:
name      rows        reserved           data         index_size        
unused            
--------   ----------- ---------------- -------------- ------------------
------------------Rpt_D...  944256   46180576 KB   5859968 KB  81368 KB      
    40239240 KB

Any help on this would be greatly appreciated.

Steve

On Fri, 6 Jan 2006 14:29:02 -0800, Steve Hughes
<SteveHug***@discussions.microsoft.com> wrote:
Show quoteHide quoteDDL please!

Have to see the keys ... you might check to be sure they're the same
on both servers, especially any clustered key.

Nothing else is touching those tables on either server?

J.

The tables are identical in both dbs including the clusters.  This is
managed.  The insert is called from only one location in the app.  Both
tables should have the same data.

Table:
CREATE TABLE [Rpt_AssetTracking] (
    [Org_SID] [int] NOT NULL ,
    [Vehicle_SID] [int] NOT NULL ,
    [Site_SID] [int] NOT NULL ,
    [EventTag_SID] [int] NOT NULL ,
    [EventDate_SID] [smalldatetime] NOT NULL ,
    [EventTime_SID] [int] NOT NULL ,
    [SegmentStartDate_SID] [smalldatetime] NOT NULL ,
    [SegmentStartTime_SID] [int] NOT NULL ,
    [SegmentEndDate_SID] [smalldatetime] NOT NULL ,
    [SegmentEndTime_SID] [int] NOT NULL ,
    [Trip_ID] [int] NULL ,
    [MotionSegment_ID] [bigint] NULL ,
    [SegmentStartDateTime] [datetime] NULL ,
    [SegmentEndDateTime] [datetime] NULL ,
    [Latitude] [int] NULL ,
    [Longitude] [int] NULL ,
    [OdometerMiles] [decimal](9, 1) NULL ,
    [FuelMeterGallons] [decimal](12, 3) NULL ,
    [DistanceMiles] [decimal](9, 1) NULL ,
    [ConsumedFuelQtyGallons] [decimal](12, 3) NULL ,
    [DurationSeconds] [int] NULL ,
    [AvgSpeedMPH] [decimal](9, 4) NULL ,
    [AvgMileageMPG] [decimal](9, 4) NULL ,
    [EventDateTime] [datetime] NULL ,
    CONSTRAINT [fk_RptAssetTracking_DimEventTag] FOREIGN KEY
    (
        [EventTag_SID]
    ) REFERENCES [Dim_EventTag] (
        [EventTag_SID]
    ),
    CONSTRAINT [fk_RptAssetTracking_DimOrganization] FOREIGN KEY
    (
        [Org_SID]
    ) REFERENCES [Dim_Organization] (
        [Org_SID]
    ),
    CONSTRAINT [fk_RptAssetTracking_DimSite] FOREIGN KEY
    (
        [Site_SID]
    ) REFERENCES [Dim_Site] (
        [Site_SID]
    ),
    CONSTRAINT [fk_RptAssetTracking_DimVehicle] FOREIGN KEY
    (
        [Vehicle_SID]
    ) REFERENCES [Dim_Vehicle] (
        [Vehicle_SID]
    ),
    CONSTRAINT [fk_RptAssetTracking_EventDimDate] FOREIGN KEY
    (
        [EventDate_SID]
    ) REFERENCES [Dim_Date] (
        [Date_SID]
    ),
    CONSTRAINT [fk_RptAssetTracking_EventDimTime] FOREIGN KEY
    (
        [EventTime_SID]
    ) REFERENCES [Dim_Time] (
        [Time_SID]
    ),
    CONSTRAINT [fk_RptAssetTracking_SegmentEndDimDate] FOREIGN KEY
    (
        [SegmentEndDate_SID]
    ) REFERENCES [Dim_Date] (
        [Date_SID]
    ),
    CONSTRAINT [fk_RptAssetTracking_SegmentEndDimTime] FOREIGN KEY
    (
        [SegmentEndTime_SID]
    ) REFERENCES [Dim_Time] (
        [Time_SID]
    ),
    CONSTRAINT [fk_RptAssetTracking_SegmentStartDimDate] FOREIGN KEY
    (
        [SegmentStartDate_SID]
    ) REFERENCES [Dim_Date] (
        [Date_SID]
    ),
    CONSTRAINT [fk_RptAssetTracking_SegmentStartDimTime] FOREIGN KEY
    (
        [SegmentStartTime_SID]
    ) REFERENCES [Dim_Time] (
        [Time_SID]
    )
) ON [PRIMARY]
GO

Stored Proc:
IF ( OBJECTPROPERTY( OBJECT_ID( 'dbo.P_RptDiagnosticWarning_Insert' ),
'IsProcedure' ) = 1 )
BEGIN
    DROP PROCEDURE dbo.P_RptDiagnosticWarning_Insert
END
GO

CREATE PROCEDURE dbo.P_RptDiagnosticWarning_Insert
(
    @Org_ID                int,
    @Vehicle_ID            int,
    @Site_ID               int,
    @Trip_ID               int,
    @UTCDateTime           datetime,
    @MID                   int,
    @SID                   int,
    @PID                   int,
    @Fault                 int,
    @OccurrenceCount       int,
    @Latitude              int,
    @Longitude             int,
    @MotionDurationSeconds int,
    @MotionDistanceMiles   decimal(9,1),
    @MotionFuelGallons     decimal(12,3)
)
AS
BEGIN
    SET NOCOUNT ON
    SET XACT_ABORT ON

    DECLARE @Org_SID              int,
            @Vehicle_SID          int,
            @Site_SID             int,
            @UTCDate_SID          smalldatetime,
            @UTCTime_SID          int,
            @ComponentName        varchar(100),
            @MIDDesc              varchar(255),
            @FMIMessage           varchar(255)

    -- Lookup all of the SIDs

    SELECT @Vehicle_SID = DV.Vehicle_SID,
           @Org_ID      = ISNULL( @Org_ID, VehicleOrg_ID )
    FROM   dbo.Dim_Vehicle AS DV
    WHERE  DV.Vehicle_ID = @Vehicle_ID

    IF( @Vehicle_SID IS NULL )
    BEGIN
        RAISERROR( 'Unknown vehicle ID', 16, 1 )
        RETURN -1
    END

    SELECT @Org_SID = DO.Org_SID
    FROM   dbo.Dim_Organization AS DO
    WHERE  DO.Org_ID    = @Org_ID
    AND    DO.IsPrimary = 1

    IF( @Org_SID IS NULL )
    BEGIN
        RAISERROR( 'Unknown organization ID', 16, 1 )
        RETURN -1
    END

    SELECT @Site_SID = DS.Site_SID
    FROM   dbo.Dim_Site AS DS
    WHERE  DS.Site_ID = ISNULL( @Site_ID, -1 )

    IF( @Site_SID IS NULL )
    BEGIN
        RAISERROR( 'Unknown site ID', 16, 1 )
        RETURN -1
    END

    SELECT @UTCDate_SID = DD.Date_SID
    FROM   dbo.Dim_Date AS DD
    WHERE  DD.Date_SID = CAST( CONVERT( varchar, ISNULL( @UTCDateTime,
'1/1/1900' ), 101 ) AS smalldatetime )

    SET @UTCDate_SID = ISNULL( @UTCDate_SID, '1/2/1900' )

    SELECT @UTCTime_SID = DT.Time_SID
    FROM   dbo.Dim_Time AS DT
    WHERE  DT.Time_ID = ISNULL( LEFT( CONVERT( varchar, @UTCDateTime, 108 ), 5
), 'NA' )

    SELECT @MIDDesc = SM.MIDName
    FROM   dbo.Spt_MID AS SM
    WHERE  SM.MID = @MID

    SELECT @FMIMessage = SF.FMIMessage
    FROM   dbo.Spt_FMI AS SF
    WHERE  SF.FMICode = ( @Fault & 0x0F )

    SELECT @ComponentName = SS.SIDName
    FROM   dbo.Spt_SID AS SS
    WHERE  SS.SID = @SID
    AND    SS.MID = @MID

    IF ( @ComponentName IS NULL )
    BEGIN
        SELECT @ComponentName = SP.PIDName
        FROM   dbo.Spt_PID AS SP
        WHERE  SP.PID = @PID
    END

    INSERT INTO dbo.Rpt_DiagnosticWarning
    (
           Org_SID,
           Vehicle_SID,
           Site_SID,
           UTCDate_SID,
           UTCTime_SID,
           Trip_ID,
           UTCDateTime,
           MID,
           SID,
           PID,
           Fault,
           OccurrenceCount,
           Latitude,
           Longitude,
           MotionDurationSeconds,
           MotionDistanceMiles,
           MotionFuelGallons,
           ComponentName,
           MIDDesc,
           FMIMessage
    )
    VALUES
    (
           @Org_SID,
           @Vehicle_SID,
           @Site_SID,
           @UTCDate_SID,
           @UTCTime_SID,
           @Trip_ID,
           @UTCDateTime,
           @MID,
           @SID,
           @PID,
           @Fault,
           @OccurrenceCount,
           @Latitude,
           @Longitude,
           @MotionDurationSeconds,
           @MotionDistanceMiles,
           @MotionFuelGallons,
           @ComponentName,
           @MIDDesc,
           @FMIMessage
    )

    IF ( @@ROWCOUNT = 0 )
    BEGIN
        RAISERROR( 'No row was inserted', 16, 1 )
        RETURN -1
    END
END
GO

Steve Hughes (SteveHug***@discussions.microsoft.com) writes:
Show quoteHide quoteAre the indexes the same in both databases?

If all this happens from insertion, it would indicate that an excessive
page split is going on.

If rows also are deleted, this can cause fragmentation, particularly
if deletions are not aligned with the clustered index.

If you run DBCC DBREINDEX on the table, I expect that you would see a
decrease in size.

OPTIMIZER LOCK HINTS
Select most recent date time
What Are The Issues With MS Access Client to SQL Server DB
Column X is same, column Y is different?
Imbedded subroutine prefix error