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.
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.
