Why is this DB so big? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Why is this DB so big?

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.
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.
Recovery model = full. The transaction log (LDF) is currently 1 MB on the hard disk.
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.
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.
]]>