SQL Server Performance

Why is this DB so big?

Discussion in 'General DBA Questions' started by Zirco, Jun 3, 2005.

  1. Zirco New Member

    One of our new DB in Dev has a size of 140 MB and can be zipped down to 3.5 MB (compression ration 98%).

    I am very puzzled how this DB is so compressible. I've tried DBCC SHRINKDATABASE, DBCC SHRINKFILE, re-created clustered indexes in an attempt to eliminate some slack spaces. But that didn't help.

    The 2 busiest tables are (fill factor = 90%):

    Table1: has 15,000 rows. Among the 3 columns, 2 are nvarchar(2000). Each of them are filled by about 150 bytes and 750 bytes. Estimated total size = (750 + 150) * 15000 = 13.5 MB (real size = 27 MB because Unicode)

    Table2: has 230 rows, 7 columns (6 numeric and 1 ntext). The ntext column contains XML data. The average XML content is about 3.5 per XML. Estimated total size = 3.5K X 230 = 0.85 MB (real size = 2 MB max).

    What is the reason for this DB to take 135 MB? (the size of the backup file). Is there anyway to reduce this size?

    Thanks in advance for your help.

  2. Luis Martin Moderator

    What kind of recovery model do you have?
    How big is transanction log?


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  3. Zirco New Member

    Recovery model = full. The transaction log (LDF) is currently 1 MB on the hard disk.
  4. Luis Martin Moderator

    Run:
    Use yourdatabase
    sp_spaceused @updateusage = 'TRUE'

    to find out real size.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  5. satya Moderator

    Use of ntext column will have this kind of issues in reclaiming the space.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page