Size of a single Record ? SQL

Question

I have a scenario where every user has been allocated 2 MB of database space. Now I have to show Percenatge usage of their allocated space, to do so I need to know the size of single record in the Table.

I have tried to use sp_spaceused and even wrote a custom procedure making use of datalength .. but they show vast difference in their result.

Is there any way around to get this thing right.

Answer 1

The data is saved on disk in directories named for the database. That includes everything, including what you're not measuring (indices, etc.). Measure that.

Answer 2

You can calculate the size a row (of number of rows) with a fairly complex formula. See Books Online (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/81fd5ec9-ce0f-4c2c-8ba0-6c483cea6c75.htm) for the exact details.

In short:

Take the number of columns and determine the size of each column by its datatype for fixed-length datatypes.

Calculate the space used for any nullable columns Then calculate the rowspace:

Row_Size = Fixed_Data_Size + Variable_Data_Size + Null_Bitmap + 4

-Edo

Answer 3

Run DBCC SHOWCONTIG with your table name

dbcc showcontig ('TableName') with tableresults

then look at max min and average record size

Answer 4

The following query will tell you the percentage of @SpaceAllotted that is being used by the data by looking at how many pages are allocated by the database's objects.

It's not worth attempting to measure usage at the row-level, since SQL Server allocates all space at the extent-level. Each extent is composed of eight 8KB data pages. So if your database only had one row, and that row had 4 bytes of data, an entire extent would still need to be allocated to hold that 4 bytes (or an existing extent with unallocated pages would be used. This is called a mixed extent).

DECLARE @SpaceAllotted   FLOAT
-- 2MB converted to kilobytes...
SET  @SpaceAllotted = 2048

SELECT 
    -- Allocation is done on the extent-level.
    -- Each extent contains eight 8KB data pages.
    ((1 / (@SpaceAllotted)) * CEILING(CAST(SUM([ips].[page_count]) AS FLOAT) / 8) * 64) * 100 AS PercentageUsed
FROM
    [sys].[dm_db_index_physical_stats](DB_ID(), NULL, NULL, NULL, NULL) ips
    -- This will allow us to retrieve the page count of all tables in the
    -- current database, regardless of whether or not they have clustered
    -- indexes and/or non-clustered indexes.
INNER JOIN
    [sys].[indexes] i
    ON
     [ips].[object_id] = [i].[object_id]
    AND [ips].[index_id] = [i].[index_id]

Since we do have the possibility of mixed extents, and no good way (there are ways, but they're not going to be pretty) of determining what pages are allocated to what extents, this isn't 100% accurate. Additionally, extents may even have free pages (that are reserved and therefore still taking up disk space), so generally, this estimate will always come in low. However, it's probably the best you're going to get without writing something to inspect the database at the page-level.

Ah yes, looking at the other answers, this is another option. This will basically look at all the current size of the data files in pages, and determine the percentage of the space they have consumed. Now, there are caveats here as well...

• If there's no maximum size specified for the database (autogrowth is enabled and unrestricted), this won't fly, since *max_size* will be returned as -1.
• Again, we can't accurately determine how much space is used by the actual data. Here we're looking at how much space is actually used on the file system.
• We're not looking at log file space. Yes, that does still consume disk space.

Hope one of these solves your problem.

SELECT 
    ((1 / CAST(SUM([df].[max_size]) AS FLOAT)) * CAST(SUM([df].[size]) AS FLOAT)) * 100 AS PercentUsed
FROM 
    [sys].dm_io_virtual_file_stats(DB_ID(), NULL)   vfs
INNER JOIN 
    [sys].[database_files]  df
    ON 
        [vfs].[file_id] = [df].[file_id]
WHERE 
    [df].[type] = 0