September 9, 2010 at 6:22 am
Hi,
I am having a database with databse size og 1.5 GB and when i check the all table size by using sp_spaceused it hardly gives 570MB how is it possible. Or is there any other process to find the size of objects or is there anything else which get calculated in over all size of database.
September 12, 2010 at 1:20 am
Don't forget that if you run sp_spaceused on the database, it shows you the size of the whole database, including the log file and free space. It could be that 1GB of your database is in the log file for instance, and therefore wouldn't show up in table size calculations.
To find out file sizes and free space I use the following query (I can't remember where I got it):
SELECT a.Name AS LogicalName, RTRIM(a.Filename) AS PhysicalName,
CONVERT(DECIMAL(12,2), ROUND(a.Size/128.000, 2)) AS FileSizeMB,
CONVERT(DECIMAL(12,2), ROUND(FILEPROPERTY(a.Name,'SpaceUsed')/128.000, 2)) AS SpaceUsedMB,
CONVERT(DECIMAL(12,2), ROUND((a.Size-FILEPROPERTY(a.Name,'SpaceUsed'))/128.000,2)) AS FreeSpaceMB,
CONVERT(DECIMAL(4,2), ROUND(100 * (CONVERT(DECIMAL(12,3), FILEPROPERTY(a.Name,'SpaceUsed'))/CONVERT(DECIMAL(12,3), a.Size)), 2)) AS SpaceUsedPercent,
CONVERT(DECIMAL(4,2), ROUND(100 * (1 - CONVERT(DECIMAL(12,3), FILEPROPERTY(a.Name,'SpaceUsed'))/CONVERT(DECIMAL(12,3), a.Size)), 2)) AS FreeSpacePercent
FROM dbo.sysfiles a
Hope that helps
Duncan
September 12, 2010 at 2:24 am
If you're interested, I also have a few queries up on my site that can help with finding the the size and free space.
http://jasonbrimhall.info/2010/05/05/sql-2005-tablespace/
As Duncan said, you probably have some free space in a data file or it is being calculated from the log file.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply