February 23, 2006 at 10:47 am
I'm in the market for a T-SQL Script to render the following for a given SQL Server 2000 Server:
All DB Names along with their allocated space & used space. I know sp_helpfile will do this for a SINGLE DB. I'm looking for a script to render this for all DB's on a single server in a single invocation.
SAMPLE:
DBName Allocated Space (MB) Utilized Space (MB)
Customer 15000 13000
Sales 40 10
Products 210 24
etc...
etc ....
thx in advance!
February 23, 2006 at 10:53 am
Here is what I run every Sunday evening versus over 100 servers that is at the Database and File group level:
set nocount on
create table #DBSpace
(DbName sysname
,FileGroupName sysname
,AllocatedMb integer
,UsedMb integer
,FreeMb integer
,FreePercent integer
,AutoGrowMb decimal(12,2)
,AutoGrowMaxSize integer
)
exec master.dbo.sp_MSForEachDb @command1 =
'dbcc updateusage([?]) with no_infomsgs
insert into #DBSpace
select ''?''
, sysFileGroups.groupname
, F.AllocatedPages / 128 as AllocatedMb
, U.UsedPages / 128 as UsedMb
, (F.AllocatedPages - U.UsedPages) / 128 as FreeMb
, (F.AllocatedPages - U.UsedPages) * 100
/ F.AllocatedPages as FreePercent
, CAST(F.GrowthPages / 128.0 as decimal(12,2) ) as AutoGrowMb
, F.MaxSize as AutoGrowMaxSize
FROM (select GroupId
, sum( cast( used as bigint) )
from [?].dbo.sysindexes
where indid in (0,1,255)
group by GroupId
) as U ( GroupId , UsedPages)
join (select GroupId
, sum(cast( size as bigint) )
, Max(growth)
, MAX(case status & 0x100000
WHEN 0x100000 then Growth * Size / 100 -- Increase in Percentage
else Growth -- Increase in Pages
end)
, MAX( case maxsize when -1 then 2147483647 else maxsize end)
from [?].dbo.sysfiles
where (status & 64 = 0)
group by GroupId
) as F (GroupId , AllocatedPages, Growth, GrowthPages, maxsize)
on F.GroupId = U.groupid
join sysFileGroups
on sysFileGroups.GroupId = U.groupId
'
select CAST( SERVERPROPERTY ('MachineName') as nvarchar(128) ) AS MachineName
, COALESCE ( CAST( SERVERPROPERTY ('InstanceName') as nvarchar(128) ) , 'Default') AS InstanceName
, DbName
, FileGroupName
, AllocatedMb
, UsedMb
, FreeMb
, FreePercent
, AutoGrowMb
, AutoGrowMaxSize
from #DBSpace
SQL = Scarcely Qualifies as a Language
February 23, 2006 at 12:24 pm
What about
sp_msforeachdb "?..sp_helpfile"
That would give you the results for every db on that server.
-- Cory
February 23, 2006 at 1:07 pm
I just tested Carl's script. The result is correct if the data file consists of a single MDF file. For multi-datafiles database it will only report the first file.
February 23, 2006 at 2:57 pm
Why does it not work for multi-datafiles ?
Please note that it reports by FileGroup, not by File.
Also, everything is rounded to MBs not Kbs.
Here is a test case:
create database SizeTest
go
use SizeTest
go
select *
into Foo1
from syscolumns
go
exec sp_spaceused @updateusage = 'true'
go
exec sp_helpfile
go
exec sp_helpfilegroup
go
alter database SizeTest
ADD FILE ( NAME = SizeTest_2,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL\data\SizeTest_2.ndf' ,
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP [PRIMARY]
go
SQL = Scarcely Qualifies as a Language
February 24, 2006 at 5:41 am
I ran this as well on one of my servers, nearly all of my DB's have more than one datafile, and I got one datafile to come back as well. ?
-- Cory
February 24, 2006 at 7:28 pm
There is no way to report space used by datafile as this information is not in the system tables, only in the space management pages.
One may get object space usage by file by running DBCC CHECKALLOC but the output would need to be reformated and then translated (object ids into object names and file ids into file names).
That is why the SQL summarizes by FileGroup.
Here is the format of the output of CHECKALLOC:
File 1. Number of extents = 52, used pages = 375, reserved pages = 405.
File 1 (number of mixed extents = 36, mixed pages = 277).
Object ID 1, Index ID 0, data extents 0, pages 6, mixed extent pages 6.
Object ID 1, Index ID 2, index extents 0, pages 4, mixed extent pages 4.
Object ID 1, Index ID 3, index extents 0, pages 2, mixed extent pages 2.
Object ID 2, Index ID 0, data extents 0, pages 6, mixed extent pages 6.
Object ID 2, Index ID 255, index extents 3, pages 23, mixed extent pages 9.
Object ID 3, Index ID 0, data extents 1, pages 15, mixed extent pages 9.
SQL = Scarcely Qualifies as a Language
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply