Database size (KB) and Unused Space

Belias

hi everyone,
i am almost monopolying this section's new threads, but i hope my questions can be useful for anyone else.
Back to topic, i am trying to reduce the space of a 40SP1 database ,and i was wondering why navision holds strange values in File->database->information->tables->Size(KB)
Example: i have Item Ledger Entry table = 27GB in navision, but when i go to the properties of the table through SSMS (2005) i have Index = 2gb, Data = 900MB.
I think to be smart in math, and i think that 2gb + 900mb = nearly 3gb...
after this, i tried to find out a script in order to retrieve the size of the tables through sql, and i found this

--**************************************
-- Name: Get SQL Table Size - Table and Index Space - Row Count
-- By: Zxtreme
--
--
-- Inputs:See commented code
--
-- Returns:See commented code
--
--Assumes:None
--
--Side Effects:none
--This code is copyrighted and has limited warranties.
--Please see http://www.Planet-Source-Code.com/xq/ASP/txtCodeId.732/lngWId.5/qx/vb/scripts/ShowCode.htm
--for details.
--**************************************

SET NOCOUNT ON
/*DATABASE TABLE SPY SCRIPT
Micheal Soelter
1/24/03
DESCRIPTION
Returns Table Size Information 
SORTING USAGE
@Sort bit values
0 = Alphabetically by table name
1 = Sorted by total space used by table
*/
DECLARE @cmdstr varchar(100)
DECLARE @Sort bit
SELECT @Sort = 0 /* Edit this value for sorting options */
/* DO NOT EDIT ANY CODE BELOW THIS LINE */ 
--Create Temporary Table
CREATE TABLE #TempTable 
(	[Table_Name] varchar(50),
	Row_Count int,
	Table_Size varchar(50),
	Data_Space_Used varchar(50),
	Index_Space_Used varchar(50),
	Unused_Space varchar(50)
)
--Create Stored Procedure String
SELECT @cmdstr = 'sp_msforeachtable ''sp_spaceused "?"'''
--Populate Tempoary Table
INSERT INTO #TempTable EXEC(@cmdstr)
--Determine sorting method
IF @Sort = 0 
    BEGIN 
    --Retrieve Table Data and Sort Alphabetically
    SELECT * FROM #TempTable ORDER BY Table_Name
END
ELSE
    BEGIN
    /*Retrieve Table Data and Sort by the size of the Table*/
    SELECT * FROM #TempTable ORDER BY Table_Size DESC
END
--Delete Temporay Table
DROP TABLE #TempTable

and i found that Item Ledger entry hase 15GB(!) of unused space... :shock: :shock: :shock: :shock:
then, supposing that navision take into account also the sift data, we have the correct amount of data:
Size (KB) = table data + table index + unused space of the table + sum of all sift table data + sum of all sift table index + sum of all sift table unused space
(All the sifts related to the table)

After this explanation, i have a question: what can cause such a HUGE unused space for a table?
Thanks in advance

EDIT: (!!!!!) Moreover, if i sum up the "unused space" data for every tables (with excel), i retrieve 58GB...but my database size is 45GB!! should i stop to believe to "nothing is created nothing is destroyed"?

EDIT2: i have the clustered index bug to be fixed; if i manually correct it for a table, his unused space drops down dramatically to <1MB....i think that this happens by simply rebuilding the index, anyway

EDIT3: i tried to run DBCC DBREINDEX on my PK index in order to verify my "EDIT2"...the unused space of the table is not reduced! :-k :-k :-k
Note that the rebuilt index is NOT clustered, and if i "cluster" it, table's unused space falls down to <1MB (actually it is >1GB)
Is this correct?

Belias

just find out that heap tables does not release unused space while clustered does...Has someone got a link to an article that explains this feature?

ara3n

Nav also includes the SIFT tables.

File->database->Information->Tables->Optimize

will delete the zero buckets in the SIFT tables.

Belias

ara3n wrote:

Nav also includes the SIFT tables.

File->database->Information->Tables->Optimize

will delete the zero buckets in the SIFT tables.

yes i know...for this reason i wrote

Size (KB) = table data + table index + unused space of the table + sum of all sift table data + sum of all sift table index + sum of all sift table unused space
(All the sifts related to the table)

And i already summed up that data too...i already verified the calculation i just quoted. As i said, I think my problem is that "heaps" (all tables in my case) does not release unused space when their index is rebuilt.

and i also know that

will delete the zero buckets in the SIFT tables.

But this is not my problem, because deleting zero buckets should only decrease SIFT table size, not the "REAL" table size...
I am also really doubtful to have 15GB of 0 Sifts, anyway

ara3n

This was a bug in Nav that it forgot to set the PK to clustered index. There is a fix for it that you need to run.

Belias

ara3n wrote:

This was a bug in Nav that it forgot to set the PK to clustered index. There is a fix for it that you need to run.

i also know this

EDIT2: i have the clustered index bug to be fixed

Thanks for suggestions, anyway

Belias

substantially, my last question is:
Has someone got a link to an article that explains this feature? (i only found a forum post that says "heap tables does not release unused space" or something like this...it's not too much, because i want to understand why does this happen)
Thanks in advance

oh, yeah....and also

EDIT: (!!!!!) Moreover, if i sum up the "unused space" data for every tables (with excel), i retrieve 58GB...but my database size is 45GB!! should i stop to believe to "nothing is created nothing is destroyed"?

kine

The table size in NAV was strange for me from beginning. I saw already negative sizes there etc. I am not taking them seriously. For me the base things i the table is record count....

Belias

I saw already negative sizes there etc

Me too! i've seen it one time...i was worried that i've created a black hole somewhere...
Close&reopen the client solved the problem, anyway...
I've never took the value seriously, too...but it was so absurd in my database, that i checked it with the query i posted before...now i trust this value more, it's a useful value afterall, but it's not documented anywhere and no one could imagine such a strange calculation (i didn't even know that there were "unused space" for tables :oops: )

Belias

Ok, now i'm getting a little disappointed...
I optimized the key of item ledger entry; as i said, the unused space of that key ffell dow from 15 GB to 1 or 2 MB...
Then i optimized the table and truncated the transaction log (again, i'm on my test db )
my db size decrease by more or less 3 GB?!?! where in the hell are these other 9GB vanished?
I supposed they were allocated to the db...maybe they aren't, instead?

Belias

Sorry to be redundant with this post, but i cannot explain myself why i released 12GB of unused space, but my db size decreased by only 3GB or so...(see previous post)
Is there anyone who know why?

EDIT: FYI, here's the output of query "exec sp_spaceused"