April 12, 2010 at 11:36 am
Hello everyone. My developers have provided a new version of their DB and although the speed is improved, it comes at the cost of increased storage. I have the previous and new versions of the software running and have configured them identical using separate SQL 2005 servers.
I already know how much space the LDF and MDF files are using and the total free space between the 2. What I would like to find out is how to find out how much free space is in each file and also where the additional space is being used (I.E. Tables, Indexes, etc.)
Thanks
Dave
April 12, 2010 at 2:03 pm
I have a couple of queries that I use for that kind of information. Please forgive the usage of deprecated objects in these scripts. I need to update them to SQL 2005 objects.
First Query: Find Table Size, Index Size, Free Space, FileGroupName
with tablesize as (
select so.Name as TableName
,TableSizeMB = convert(decimal(15,2),si.dpages *8 / 1024)
,IndexSizeMB = convert(decimal(15,2),sum(isnull(si2.used,0))*8 / 1024)
,TotalSizeMB = convert(decimal(15,2),(si.dpages * 8 /1024) + (sum(isnull(si2.used,0))*8 / 1024))
,TableFreeMB = convert(decimal(15,2),(si.reserved * 8 /1024) -(si.used * 8/1024))
,TableSizeKB = convert(decimal(15,2),si.dpages *8)
,IndexSizeKB = convert(decimal(15,2),sum(isnull(si2.used,0))*8)
,f.Name as FileGroupName
,d.physical_name as FGFileName
,(select convert(decimal(15,2),sum(reserved)* 8 /1024) from sysindexes where indid in (0,1)) as DBSize
from sysindexes si
Inner join sys.objects so
on so.object_id = si.id
and so.is_ms_shipped = 0
--and so.type = 'U'
and si.indid in (0,1)
and so.name <> 'sysdiagrams'
Inner join sysindexes si2
on so.object_id = si2.id
and si2.indid > 1
and si2.indid < 255
Inner Join sys.filegroups f
on f.data_space_id = si.groupid
Inner Join sys.database_files d
on f.data_space_id = d.data_space_id
group by so.Name,si.dpages,f.Name,d.physical_name,si.reserved,si.used
)
Select TableName,TableSizeMB,IndexSizeMB,TotalSizeMB,TableFreeMB,TableSizeKB,IndexSizeKB,FileGroupName,FGFileName,DBSize
,convert(decimal(15,12),(TableSizeMB + IndexSizeMB)/DBSize) * 100 as TablePercentofDB
from TableSize
Order by FileGroupName asc,TableSizeMB desc
Second Query: Find just filegroup Information
CREATE TABLE #FileDetails (
FileId int , FileGroupId int , TotalExtents int , UsedExtents int ,
"Name" nvarchar( 128 ) , "FileName" nvarchar( 500 ) ,
TotalSize AS ( ( TotalExtents * 64.0 ) / 1024 ) ,
UsedSize AS ( ( UsedExtents * 64.0 ) / 1024 )
)
--Data File Details
INSERT INTO #FileDetails (
FileId , FileGroupId , TotalExtents , UsedExtents , "Name" , "Filename"
)
EXECUTE( 'dbcc showfilestats with tableresults' )
SELECT FILEGROUP_NAME( FileGroupID ) AS FileGroupName ,
FileId ,
"Name" ,
"FileName" ,
(CONVERT(decimal(38,2),TotalSize)) AS FileSizeMB ,
(CONVERT(decimal(38,2),UsedSize)) AS CurrentSizeMB ,
(CONVERT(decimal(38,2),((UsedExtents*1.)/TotalExtents)*100)) AS "%Usage"
FROM #FileDetails
Select sum(CONVERT(decimal(38,2),UsedSize)) as CurrentDBSize,sum(CONVERT(decimal(38,2),TotalSize)) as CurrentDBAllocatedSize from #FileDetails
DROP TABLE #FileDetails
Final Query: Get LOB information
Select FileGroupName = filegroup_name(a.data_space_id)
,TableName = object_name(p.object_id)
,IndexName = i.name
From sys.allocation_units a
Inner Join sys.partitions p
On p.partition_id = a.container_id
And a.type = 2
Left Outer Join sys.indexes i
On i.object_id = p.object_id
And i.index_id = p.index_id
Where OBJECTPROPERTY(p.object_id,'IsMSShipped') = 0
And filegroup_name(a.data_space_id) = 'Primary'
Union
Select FileGroupName = filegroup_name(a.data_space_id)
,TableName = object_name(p.object_id)
,IndexName = i.name
From sys.allocation_units a
Inner Join sys.partitions p
On p.hobt_id = a.container_id
And a.type in (1, 3)
Left Outer Join sys.indexes i
On i.object_id = p.object_id
And i.index_id = p.index_id
Where OBJECTPROPERTY(p.object_id,'IsMSShipped') = 0
And filegroup_name(a.data_space_id) = 'Primary'
Order By TableName asc
These queries work, but in reviewing them I would make a few tweaks to make them better.
Edit: I modified the last query. This last query doesn't show you the sizes you are looking for, but is useful in determining where BLOB objects reside. The query could be modified to include the size.
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
April 13, 2010 at 8:29 am
Thank you for the scripts. This gave me the exact information I needed.
April 13, 2010 at 4:53 pm
Dave Heiler (4/13/2010)
Thank you for the scripts. This gave me the exact information I needed.
You're welcome.
I have updated that last script again. I posted information about it on my blog (see sig block).
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 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply