SQL Server Performance

How to get the Databases Filegroup Size?

Discussion in 'General Developer Questions' started by johnson_ef, Sep 10, 2005.

  1. johnson_ef Member

    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
  2. ghemant Moderator

    hi,<br />as per BOL :<br /> <br /> <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>sp_helpfilegroup <br />Returns the names and attributes of filegroups associated with the current database.</i><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />and <br /><br /> <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>sp_helpfile<br />Returns the physical names and attributes of files associated with the current database. Use this stored procedure to determine the names of files to attach to or detach from the server. </i> <hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"> <br /><br /><br />moreover refer BOL for sp_helpfilegroup and sp_helpfile<br /><br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />Regards<br /><br />Hemantgiri S. Goswami<br />ghemant@gmail.com<br />"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri Goswami<br />
  3. johnson_ef Member

    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
  4. ghemant Moderator

    no AFAIK theirs no way to refresh this tables


    Regards

    Hemantgiri S. Goswami
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri Goswami
  5. johnson_ef Member

    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
  6. riya New Member

    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
  7. johnson_ef Member

    Great!!! Its working..

    Thanks a lot...

    -Johnson
  8. TRACEYSQL New Member

    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..
  9. Bredsox New Member

    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
  10. druer New Member

    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.
  11. druer New Member

    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.
  12. Bredsox New Member

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

    Dan
  13. druer New Member

    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.

Share This Page