tempdb..syscolumns growing in size without obvious reason

• AivarsH

  Mr or Mrs. 500

  Points: 577

  More actions

  November 10, 2015 at 3:05 am

  #100211

  Hi all,

  Does anybody have ever seen tempdb..syscolumns table growing in size while maintaining ~300 entries?

  select cast(object_name(id) as varchar(10)) as name,status,dpages,reserved,used,rowcnt from sysindexes

  where object_name(id) = 'syscolumns'

  name| status| dpages| reserved| used| rowcnt|

  ---------- ----------- ----------- -----------

  syscolumns| 16402| 403334| 631854| 594834| 35593|

  syscolumns| 2 | 188710| 80561| 78437| 75|

  select count(*) from [dbo].[syscolumns]

  -----------

  278

  DBCC SHOWCONTIG (syscolumns)

  -----------

  DBCC SHOWCONTIG scanning 'syscolumns' table...

  Table: 'syscolumns' (3); index ID: 1, database ID: 2

  TABLE level scan performed.

  - Pages Scanned................................: 397609

  - Extents Scanned..............................: 55244

  - Extent Switches..............................: 224894

  - Avg. Pages per Extent........................: 7.2

  - Scan Density [Best Count:Actual Count].......: 22.10% [49702:224895]

  - Logical Scan Fragmentation ..................: 26.88%

  - Extent Scan Fragmentation ...................: 30.11%

  - Avg. Bytes Free per Page.....................: 4048.6

  - Avg. Page Density (full).....................: 49.98%

  The table is growing with approx speed 1GB/hour

  This is happening on SQL 2000 Ent (8.00.2066)

  Any help or advise is very appreciated!