How to get the Databases Filegroup Size?

HI Guys,

I am facing some problem in creating reports about the Databases
I am handling 42 user databases accross 4 database Servers.

I have to maintain, the Database size, Database file sizes (MDF, LDF and NDF) in day today basis.

I is easy to get the database size if I use "sp_helpdb" from QA.

But to get the file size of each database is time consuming even if I do it from QA or EM.

Any each solution like stored_procedure or views for this?

I could create 3 or 4 views to achive this and call the main view to get the result in QA. I have pasted the views below.

*****************************************

FileSize_VIEW
=============
CREATE VIEW dbo.FileSize_VIEW
AS
SELECT TOP 100 PERCENT MV.dbid, SD.name, MV.MDFSize, LV.LDFSize
FROM dbo.MDFFileSize_VIEW MV INNER JOIN
dbo.LDFFileSize_VIEW LV ON MV.dbid = LV.dbid INNER JOIN
dbo.sysdatabases SD ON MV.dbid = SD.dbid
ORDER BY SD.name


MDFFileSize_VIEW
================
CREATE VIEW dbo.MDFFileSize_VIEW
AS
SELECT dbid, ROUND([size] * 8 / 1024, 1)+1 AS MDFSize, name
FROM dbo.sysaltfiles
WHERE (fileid = 1)

LDFFileSize_VIEW
=================
CREATE VIEW dbo.LDFFileSize_VIEW
AS
SELECT dbid, ROUND([size] * 8 / 1024, 1)+1 AS LDFSize, name
FROM dbo.sysaltfiles
WHERE (fileid = 2)

*****************************

I call the main view "FileSize_VIEW" from QA to get the result.

But there is some problem arises in this too, the "tempdb" database doesn't show the size which is showing in EM.

Anyone can reply this?

Thanks in advance
-Johnson

hi Hemantgiri,

yes, its possible to get these info database one by one, either USing the database in QA or selecting the properties through EM.

I want the info like

dbidDatabase NameMDF FileLDF File
1master 16 3
8ABC 514 2
7ABC_OLD 514 2
3model 1 2
4msdb 13 3
6Northwind 3 2
5pubs 2 1
2tempdb 9 1

The Views which I have pasted, will give me this out put.
But the problem is, the data of "tempdb" doesn't show the correct info.

I have taken these values form "sysaltfiles", I think, this table in the "master" database won't reflect unless untill SQL Server restarts. Is there any way to refresh this table with latest value??

-Johnson

ok..<br />I thought, I work will be easier in this way, if I get a solution.<br /><br />Anyway, never mind.. we should do some manual work, otherwise we will forget to do our work <img src='/community/emoticons/emotion-5.gif' alt='' /><br /><br />-Johnson

Hi,

Try this script

set nocount on

-- Database Size
print '*** Database Size ***'

create table #db_size (dbno int identity,
dbdate datetime,
dbname nvarchar(50),
datasize float,
logsize float(53),
actual float)

declare @datasize float
declare @logsize float
declare @usedspace float
declare @dbsize float
declare @datacmd nvarchar (100)
declare @logcmd nvarchar (100)
declare @usedspacecmd nvarchar (1000)
declare @dbsizecmd nvarchar (1000)
declare @actual nvarchar (100)
declare @dbname char (25)

declare c1 cursor for select name from master..sysdatabases
open c1
fetch c1 into @dbname

while @@fetch_status = 0
begin


set @datacmd='select @datasize=size from ['+@dbname+'].dbo.sysfiles where fileid=1'
exec sp_executesql @datacmd, N'@datasize float output', @datasize output
set @datasize=round(@datasize/128,0)

set @logcmd='select @logsize=size from ['+@dbname+'].dbo.sysfiles where fileid=2'
exec sp_executesql @logcmd, N'@logsize float output', @logsize output
set @logsize=round(@logsize/128,0)

insert into #db_size (dbdate,dbname,datasize,logsize,actual)
values (getdate(),@dbname,@datasize,@logsize,@usedspace)
fetch next from c1 into @dbname
end
select
cast(dbno as varchar(5)) "DBno",
cast(dbdate as varchar(20)) "DBDate",
cast(dbname as varchar(20)) "DBName",
cast(datasize as varchar(10)) "datasize",
cast(logsize as varchar(10)) "logsize"
from #db_size
drop table #db_size
close c1
deallocate c1






Riya

Great!!! Its working..

Thanks a lot...

-Johnson

im trying to modify this to get growth

create table #db_size (dbno int identity,
dbdate datetime,
dbname nvarchar(50),
datasize float,
data_growth int,
logsize float(53),
log_growth int,
actual float)

declare @datasize float
declare @logsize float
declare @usedspace float
declare @dbsize float
declare @datacmd nvarchar (100)
declare @logcmd nvarchar (100)
declare @usedspacecmd nvarchar (1000)
declare @dbsizecmd nvarchar (1000)
declare @actual nvarchar (100)
declare @dbname char (25)
declare @data_growth int
declare @log_growth int

declare c1 cursor for select name from master..sysdatabases
open c1
fetch c1 into @dbname

while @@fetch_status = 0
begin


set @datacmd='select @datasize=size,
@data_growth=growth from ['+@dbname+'].dbo.sysfiles where fileid=1'
exec sp_executesql @datacmd, N'@datasize float output', @datasize output
set @datasize=round(@datasize/128,0)

set @logcmd='select @logsize=size, @log_growth=growth from ['+@dbname+'].dbo.sysfiles where fileid=2'
exec sp_executesql @logcmd, N'@logsize float output', @logsize output
set @logsize=round(@logsize/128,0)

insert into #db_size (dbdate,dbname,datasize, data_growth,logsize, log_growth,actual)
values (getdate(),@dbname,@datasize,@data_growth, @logsize,@log_growth, @usedspace)
fetch next from c1 into @dbname
end
select
cast(dbno as varchar(5)) "DBno",
cast(dbdate as varchar(20)) "DBDate",
cast(dbname as varchar(20)) "DBName",
cast(datasize as varchar(10)) "datasize",
cast(logsize as varchar(10)) "logsize"
from #db_size
drop table #db_size
close c1
deallocate c1


but i keep getting error on variable undefined..

Also, if some databases are offline within that server, it will show the error message saying:
Msg 942, Level 14, State 4, Line 1
Database 'DBName' cannot be opened because it is offline.

Need to tweek a little bit i guess.

Dan

If you run the following command you'll get the filesizes of all of of the files for all of the databases on any 1 server:

exec master.dbo.sp_MSforeachdb 'use ? select db_name() as dbname, * from sysfiles'

If you create a table to retain the information, you can alter that command to select only the details you want from the sysfiles table, and then you could say "Insert into Table (field, field, fied) exec ....." to have the data pumped into that table from a job on a nightly basis.

Hope it helps,
Dalton

Blessings aren't so much a matter of recieving them as they are a matter of recognizing what you have received.

I wanted to follow up on this just to have a record of it:

The following command will create a table that will contain the information :

create table DatabaseSizes (dbname varchar(255), fileid int, filename varchar(255), name varchar(255), size_mb int, reporting_date datetime)
The following is the code that you can use in a job to gather the data each day and have it populate the table, then you can issue selects against the table afterwards:
insert into DatabaseSizes(dbname, fileid, filename, name, size_mb, reporting_date)
exec master.dbo.sp_MSforeachdb 'use ? select db_name() as dbname, fileid, filename, name, size/128, getdate() from sysfiles'



Hope it helps,
Dalton

Blessings aren't so much a matter of recieving them as they are a matter of recognizing what you have received.

Druer,
That was great. I ran the script creating #temp table instead of permanent table and it works great.

Dan

Dan, Glad it helped you. The permanent table is just in case you wanted to run the command daily and retain the data on an ongoing basis so that you could track the size as things grow. I generally update management quarterly with the rates at which the database is growing and with the table it makes that easy.

Hope it helps,
Dalton

Blessings aren't so much a matter of recieving them as they are a matter of recognizing what you have received.