Post reply

Script to find the datafile size and logfile size?

  • December 2, 2008 at 10:27 pm

    #198430

    hi,

    is there any script to get the actual datafile size, logfile size

    thank you

  • December 2, 2008 at 10:57 pm

    #906655

    Is this what you want?

    --===== Declare local files

    --Jeff Moden

    DECLARE @DBID INT

    DECLARE @DBName VARCHAR(30)

    DECLARE @Text NVARCHAR(300)

    --===== Get the first database ID

    SELECT @DBID = MIN(DBID) FROM Master.dbo.SysDataBases

    --===== Loop until we run out of database ID's

    WHILE @DBID IS NOT NULL

    BEGIN

    --===== Get the database name for the current DBID

    SELECT @DBName = NAME FROM Master.dbo.SysDataBases WHERE DBID = @DBID

    --===== Set the dynamic SQL to use the database name and get the info

    SET @Text = N'USE '+ @DBName + N' EXEC sp_HelpFile'

    --===== Exec the dynamic SQL

    EXEC dbo.sp_ExecuteSql @Text

    --===== Get the next larger DBID

    SELECT @DBID = MIN(DBID) FROM Master..SysDataBases WHERE DBID > @DBID

    END

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 2, 2008 at 11:21 pm

    #906656

    thanks Jeff,

    This script is giving the mdf and ndf file. But I would like to get the actual space allocated for datafile and the available space inorder to monitor .Based on the output of the script, increase the data file size when ever the available space comes to a low value to aviod the application hanging situations when the datafile size increasing which is set to autogrow(which is usaully resource intensive process). and also the size should be in MB.

    Thanks in advance

  • December 3, 2008 at 12:12 am

    #906666

    This might be what you are looking for, even if it is not; it does give you a pretty good idea of how to start working on it 🙂

    http://www.sqlservercentral.com/scripts/Miscellaneous/30031/

    Regards,

    -Hope is a heuristic search :smooooth: ~Hemanth

  • December 3, 2008 at 7:29 pm

    #907300

    madhu.arda (12/2/2008)

    thanks Jeff,

    This script is giving the mdf and ndf file. But I would like to get the actual space allocated for datafile and the available space inorder to monitor .Based on the output of the script, increase the data file size when ever the available space comes to a low value to aviod the application hanging situations when the datafile size increasing which is set to autogrow(which is usaully resource intensive process). and also the size should be in MB.

    Thanks in advance

    I'm thinking you can probably handle the math to chang KB to MB. Lemme look and see what I've got in my goodie-locker for the rest.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 3, 2008 at 10:54 pm

    #907362

    For Data File Space run

    USE [DatabaseName]

    DBCC SHOWFILESTATS

    Result Computation

    DB Size - (totalextents*64)/1024

    DB Used - (usedextents*64)/1024

    DB Space - (DB Size) - (DB Used)

    For Log File Space run

    DBCC SQLPERF(LOGSPACE)

    "-=Still Learning=-"

    Lester Policarpio

  • December 5, 2008 at 10:07 am

    #908469

    Hi,

    here is the script to find the datafile size and logfile size.But it is giving for the database in which we run it.

    Could you plz make it work for all the databases in an instance.

    select Name,

    (convert(float,size)) * (8192.0/1048576) File_Size,

    (convert(float,fileproperty(name,'SpaceUsed'))) * (8192.0/1048576) MB_Used,

    ((convert(float,size)) * (8192.0/1048576) - (convert(float,fileproperty(name,'SpaceUsed'))) * (8192.0/1048576)) MB_Free

    from sysfiles

    order by

    fileproperty(name,'IsLogFile')

    Thanks

  • December 5, 2008 at 3:53 pm

    #908712

    This should work:

    sp_msforeachdb 'USE ?

    select Name,

    (convert(float,size)) * (8192.0/1048576) File_Size,

    (convert(float,fileproperty(name,''SpaceUsed''))) * (8192.0/1048576) MB_Used,

    ((convert(float,size)) * (8192.0/1048576) - (convert(float,fileproperty(name,''SpaceUsed''))) * (8192.0/1048576)) MB_Free

    from sysfiles

    order by

    fileproperty(name,''IsLogFile'')'

  • December 5, 2008 at 6:33 pm

    #908739

    Thanks Edogg....

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply