Clever Geek Handbook

SQL Server - Database Size Difference

I'm trying to programmatically control the size of a SQL Server database so that my admin section of my web application can report this and I can use it to do some cleanup chips, to clean up log files, etc.

I am using the following code to calculate the size of tables, as recommended by SO:

CREATE TABLE #t (name SYSNAME, rows CHAR(11), reserved VARCHAR(18), data VARCHAR(18), index_size VARCHAR(18), unused VARCHAR(18))EXEC sp_msforeachtable 'INSERT INTO #t EXEC sp_spaceused ''?'''-- SELECT * FROM #t ORDER BY name-- SELECT name, CONVERT(INT, SUBSTRING(data, 1, LEN(data)-3)) FROM #t ORDER BY nameSELECT SUM(CONVERT(INT, SUBSTRING(data, 1, LEN(data)-3))) FROM
#tDROP TABLE #t

When I run this against my small database of examples I get a total of ~ 6.8 megabytes. When I look at my database properties it shows 15.5 megabytes.

I have the following questions:
1. What else might matter?
2. Can this difference be described as "overhead" that will only grow by a fraction of the rate as other data grows (a rough estimate is all I need to do this).
3. Are stored procedures, functions, views, triggers in this "overhead" space? Is there a way to calculate them?
4. Is there another way to get the size of the WHOLE base? I just need an easy way to get the REAL size.

0


source to share


3 answers


The difference I think is due to the fact that the size you see on the Properties page is calculated by querying the .sys.database_files table, which counts the number of 8KB pages allocated for each database file.

To get the same result, just run the following query (SQL Server 2005):

SELECT
  SUM ([size] * 8) / 1024 AS DB_Size - Field "size" contains the number of 8 KB pages contained in each file
FROM
  [db_name_here] .sys.database_files


More details about sys.database_files on the MSDN website.

Hope this helps. :)

Diego

+3


source


Why not just check the size of the file (or files) on disk? assuming you know the name of the file (s), you can simply check their size through the filesystem.



+1


source


Simple observation, your script seems to be summarizing the sp_spaceused data column and not the reserved column (Reserved = data + index + unused). Also sp_msforeachtable does not include system tables.

Also from the SQL Server online books for sp_spaceused

If objname is not specified, results are returned for the entire database.

Also from the same page in books in line

database_size will always be greater than the sum of reserved + unallocated because it includes the size of the log files, but is reserved and unallocated_space only takes into account page data.

One more thing you may need to run DBCC UPDATEUSAGE to get the exact numbers.

See Also " SQL Server Books Online sp_spaceused (Transact-SQL)"

0


source






More articles:


All Articles