Where has all the space disappeared?

[NemoMaximus]

I'm running SQL Server 2005 and one of my databases is eating up space and I have no idea why... My calculations of the required space is just not saying the same thing as the SQL Server reports. Have a look at this:

EXEC sp_spaceused MyTableName

Returns
-------
Name = MyTableName
Rows = 14674175
Reserved = 22898464 KB
Data = 18169480 KB
Index_size = 4669704 KB
Unused = 59280 KB

Now, have a look at the table structure:

field_01 Type=uniqueidentifier Length=16
field_02 Type=uniqueidentifier Length=16
field_03 Type=uniqueidentifier Length=16
field_04 Type=datetime Length=8
field_05 Type=datetime Length=8
field_06 Type=numeric Length=13
field_07 Type=char Length=1
field_08 Type=char Length=1
field_09 Type=char Length=3
field_10 Type=char Length=1
field_11 Type=char Length=1
field_12 Type=numeric Length=5
field_13 Type=numeric Length=13
field_14 Type=char Length=1
field_15 Type=bit Length=1
field_16 Type=timestamp Length=8
field_17 Type=_deduction numeric Length=13
field_18 Type=_deducted char Length=1
field_19 Type=char Length=3
field_20 Type=numeric Length=5
field_21 Type=numeric Length=13
field_22 Type=numeric Length=13
field_23 Type=numeric Length=13
field_24 Type=int Length=4
field_25 Type=numeric Length=5
field_26 Type=numeric Length=13

Total length = 195 bytes

So according to my calculations the space used for the table should be:

14674175 x 195 = 2,861,464,125 bytes or 2,861,464 KB

But sp_spaceused is reporting the the space used is 18,169,480 KB, more than 6 times what it should be according to my calculations. How is this possible?

One thought that I have... I'm deleting about 700000 of these records every day and recalculates them. Could it be that the deleted records are still there taking up space? And if so, how do I get rid of them?

Please help

 

[ptheriault]

run DBCC UPDATEUSAGE then check your size. I also know there is a bug in SQL Server 2005 when you upgrades it causes your databases to grow by a large amount. I am looking for it now. If I find it I will post it for you.


- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[NemoMaximus]

I have already done DBCC UPDATEUSAGE, didn't help...

 

[ptheriault]

I found the bug that was reported in SQL 2005 and SP1. It is fixed in sp2. (Or as just released sp2a)

http://support.microsoft.com/kb/919611/

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[NemoMaximus]

Thanks! I will check into this, but I already have the SP2 CTP installed so I don't know if this will solve it.

But still, automatic grows gives you more unused space, my problem is that it reports that I have 18,2 Gb of used data when it should only be about 2,9 Gb according to my calculations.

 

[ptheriault]

Nemo,
After a little bit of research I believe you are calculating your table size incorrectly. Look up "Estimating the size of a table" in BOL. Also here is a link to a sp that will do it for you.

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=4924&#88621

It's for SQL2000 but should still work in 2005/

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[NemoMaximus]

I knew that my calculation was not fully accurate, but still, it should give some kind of indication where we are headed. Anyway, I ran the script that you linked to and that showed that the table size should be about 5.5 GB, still a lot less then the 18.2 GB that SQL Server 2005 reports.

I'm going crazy here

 

[ptheriault]

interesting... Maybe it's time for a phone call to Microsoft.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[SQLBill]

Nemo,

Something you should be aware of: SP2 CTP was the BETA version and I believe Microsoft said it should not be applied to Production systems. The Production SP2 may have additional fixes that SP2 CTP did not have, or it may have left out fixes that caused problems in the CTP version.

-SQLBill

Posting advice: FAQ481-4875

 

[NemoMaximus]

Yes, I know about that for SP2 CTP... I will upgrade to SP2a later this week...

Anyway, I did a test, copied all the records to another database, truncated the table and copied everything back, which resulted in:

Rows = 14673912
Reserved = 4512600 KB
Data = 3010040 KB
Index size = 1501888 KB
Unused = 672 KB

We are down to 3 GB of data, as it should be I'm going to monitor this table on a daily basis and see how the growth is... Hopefully this fixed everything.

 

[SQLBill]

Just out of curiosity....how are you going to upgrade to SP2a? If SQL Server 2005 already has a SP2 version installed, installing a second version will not work (per Microsoft's SP2a readme).

And you can't uninstall SP2.

-SQLBill

Posting advice: FAQ481-4875

 

[NemoMaximus]

I didn't know that... Microcrap... I hope the upgrade works anyway...

The table starts to grow directly, this is what have happen tonight:

2007-03-07
----------
Rows = 14673912
Reserved = 4512600 KB
Data = 3010040 KB
Index size = 1501888 KB
Unused = 672 KB

2007-03-08
----------
Rows = 14693550
Reserved = 5007136 KB
Data = 3164880 KB
Index size = 1841336 KB
Unused = 920 KB

Another 500 Mb added although it shouldn't have been more then 5 Mb...

 

[ptheriault]

are all your other tables being reported correctly?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[NemoMaximus]

Yes, as far as I know all other tables are fine, at least the total size of the database is resonable when I exclude this table.

But this is the only table as well that I delete about 700000 records every night and recalculates them, and to me it feels like the deleted records is left in the table. But that's just my guess...

 

[AlexCuse]

>>>I hope the upgrade works anyway...

This is doubtful at best. At least back up all your db's if you are seriously going to try it. I remember getting an email from the local SQL user's group saying that if you install SP2 and need to go back to the original state, it requires a fresh installation. Sorry dude!

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[NemoMaximus]

Well, I found this on Microsofts web site:

http://www.microsoft.com/downloads/...a1-ecc4-4c49-8f65-daa6b7800eec&displaylang=en

Seems to be just a small update for SP2a for those who already have SP2...

 

[ptheriault]

The only thing that I can think of here is that those data pages are being held onto after your delete. But DBCC UPDATEUSAGE is supposed to correct those inaccuracies.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[SQLBill]

SP issue:
Yes, but that is showing the difference between the release version of SP2 and the release version of SP2a. They don't have to let you know the difference between a beta (CTP) version and a release version.

Size issue:
Enterprise Manager is known to have issues with showing proper sizes of tables/databases, especially when large transactions take place. You should always run DBCC UPDATEUSAGE after large transactions.

-SQLBill

Posting advice: FAQ481-4875

 

[NemoMaximus]

Well, I have done DBCC UPDATEUSAGE, no difference. And its not just that SQL Server has problem showing the correct size of the table, the physical size of the database is wrong. Yesterday, after my exercise of coping and truncating the table the database size went from 28 GB to about 5 Gb...

 

[SQLBill]

Check what you have AUTOGROW set for on that database for both the data and log files.

-SQLBill

Posting advice: FAQ481-4875