Archive for the ‘Bug’ Category

Size (KB) in database information (Tables)

Monday, January 18th, 2010

Some months ago, i was 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…Navision calculation, sums up also the sift data, then we can pair the Size (KB) amount of nav and the Size calculated from SQL Script(s):
Size (KB) = “table data” + “table indexes” + “unused space of the table” + “sum of all sift data” + “sum of all sift index(es)” + “sum of all sift unused space”
It appears that the Size (KB) field reads also the “unused space” of the table, while the “File->Database->Information->Database Size (KB)” does not. Moreover, that “unused space” is not even allocated by sql: it appears that only navision “size (kb)” fields considers it.

If you notice a large amount of this “unused space”, be careful and check your navision build, because you could be affected by this bug. If you see negative values, just close&reopen the client