June 27, 2006 at 8:38 am
Is there any way to find out total size of all the user/sys DBs and Data space used (in % or MB)? Just like DBCC SQLPERF (LOGSPACE)
June 27, 2006 at 11:38 am
Here is the script to get DB size and available DB space.
Declare @cmd1 varchar(500)
Set @cmd1 ='SELECT Name as DBName,Filename as Path ,(Size*8)/1024 as MB FROM ' +'?'+'..sysfiles '
Exec sp_MSforeachdb @command1=@cmd1
For Available Space
SELECT NAME AS NameOfFile,
CAST
((SIZE/128.0 -CAST(FILEPROPERTY(NAME, 'SpaceUsed' )AS INT)/128.0)AS INT) AS AvailableSpaceInMB FROM DBNAME..SYSFILES
Hope this helps,
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com
June 27, 2006 at 11:54 am
Getting following error when I tried to run 2nd script:
/*-----------------------------
SELECT NAME AS NameOfFile,
CAST
((SIZE/128.0 -CAST(FILEPROPERTY(NAME, 'SpaceUsed' )AS INT)/128.0)AS INT) AS AvailableSpaceInMB FROM DBNAME..SYSFILES
-----------------------------*/
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'DBNAME..SYSFILES'.
June 28, 2006 at 6:29 am
You need to edit where it says "DBNAME" to the name of the database you want to see the data for. Also if you collation is set to case sensitivity you need to change where it says "SYSFILES" to "sysfiles"
June 30, 2006 at 2:11 am
you just need to run the following script on the database you need this information
SELECT F.name AS NameOfFile,
G.groupname,
F.size/128.0 SpaceinMB,
F.size/128.0 -CAST(FILEPROPERTY(name, 'SpaceUsed' )AS int)/128.0 AS AvailableSpaceInMB,
((F.size/128.0 -CAST(FILEPROPERTY(name, 'SpaceUsed' )AS int)/128.0)*100)/(F.size/128.0) AvailableSpaceInPerc,
f.FileName
FROM dbo.SYSFILES F
join dbo.sysfilegroups G
on F.groupid=G.groupid
go
SELECT G.groupname,
sum (F.size/128.0) SpaceinMB,
sum(F.size/128.0 -CAST(FILEPROPERTY(name, 'SpaceUsed' )AS int)/128.0) AS AvailableSpaceInMB,
(sum((F.size/128.0 -CAST(FILEPROPERTY(name, 'SpaceUsed' )AS int)/128.0)*100))/(sum(F.size/128.0)) AvailableSpaceInPerc
FROM dbo.SYSFILES F
join dbo.sysfilegroups G
on F.groupid=G.groupid
group by G.groupname
go
master..xp_fixeddrives
go
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply