SQL 2000 Table Size Reserved Space Issue

  • Hello,

    I have a database from a canned CRM application which is growing exponentially (6 gig in 7 months). I looked up the code to view table sizes trying to see if there was an issue. Table 1 is 622MG but has a total reserved space of almost 5 gig. table 2 has 291 mg used and 1.2 gig reserved. Table 1 has an image field and table 2 has a ntext data type field. Are these what is causing this issue and wether that's the case or not is there anything I can do to reduce the unused space in these tables? Thanks for any help you can provide.

    Here is the code I used to retrieve the table sizes

    Exec sp_helpdb

    declare @idint

    declare @typecharacter(2)

    declare@pagesint

    declare @dbname sysname

    declare @dbsize dec(15,0)

    declare @bytesperpagedec(15,0)

    declare @pagesperMBdec(15,0)

    create table #spt_space

    (

    objidint null,

    rowsint null,

    reserveddec(15) null,

    datadec(15) null,

    indexpdec(15) null,

    unuseddec(15) null

    )

    set nocount on

    -- Create a cursor to loop through the user tables

    declare c_tables cursor for

    selectid

    fromsysobjects

    wherextype = 'U'

    open c_tables

    fetch next from c_tables

    into @id

    while @@fetch_status = 0

    begin

    /* Code from sp_spaceused */

    insert into #spt_space (objid, reserved)

    select objid = @id, sum(reserved)

    from sysindexes

    where indid in (0, 1, 255)

    and id = @id

    select @pages = sum(dpages)

    from sysindexes

    where indid < 2

    and id = @id

    select @pages = @pages + isnull(sum(used), 0)

    from sysindexes

    where indid = 255

    and id = @id

    update #spt_space

    set data = @pages

    where objid = @id

    /* index: sum(used) where indid in (0, 1, 255) - data */

    update #spt_space

    set indexp = (select sum(used)

    from sysindexes

    where indid in (0, 1, 255)

    and id = @id)

    - data

    where objid = @id

    /* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */

    update #spt_space

    set unused = reserved

    - (select sum(used)

    from sysindexes

    where indid in (0, 1, 255)

    and id = @id)

    where objid = @id

    update #spt_space

    set rows = i.rows

    from sysindexes i

    where i.indid < 2

    and i.id = @id

    and objid = @id

    fetch next from c_tables

    into @id

    end

    select TableName = (select left(name,60) from sysobjects where id = objid),

    Rows = convert(char(11), rows),

    ReservedKB = ltrim(str(reserved * d.low / 1024.,15,0) + ' ' + 'KB'),

    DataKB = ltrim(str(data * d.low / 1024.,15,0) + ' ' + 'KB'),

    IndexSizeKB = ltrim(str(indexp * d.low / 1024.,15,0) + ' ' + 'KB'),

    UnusedKB = ltrim(str(unused * d.low / 1024.,15,0))

    from #spt_space, master.dbo.spt_values d

    where d.number = 1

    and d.type = 'E'

    order by unused desc

    drop table #spt_space

    close c_tables

    deallocate c_tables

  • Yes, it true. Storage size for the image and ntext data type, in bytes, is two times the number of characters entered.

    Note: ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

    MJ

Viewing 2 posts - 1 through 1 (of 1 total)

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