Post reply

monitoring DBsize, DBfree, DBGrowth, DBMax, LogSize, Logfree, LogGrowth, LogMax.

  • July 10, 2012 at 9:05 am

    #263982

    Hi, need help in monitoring DBsize, DBfree, DBGrowth, DBMax, LogSize, Logfree, LogGrowth, LogMax.

    In our environment we have SQL 2000/2008/R2/2012.

    Almost all the databases are in Restricted File Growth. Because of that we don’t have any notification alert on Autogrowth . Untill the developers notify the DBA about the space error.

    Our is aim is to create own tool for our sql servers File Growth.

    Can anyone help no how monitor the space. Like various options OR

    Can I get the scripts for it.

    Thanks in advance.

  • July 10, 2012 at 1:24 pm

    #1511162

    YOu can create a query or queries that use sys.database_files and the FILEPROPERTY function to determine space available. Something like this:

    SELECT

    name AS [file_name],

    D.type_desc,

    CONVERT(DECIMAL(10, 2), size / 128.0) AS size_in_mb,

    CONVERT(DECIMAL(10, 2), FILEPROPERTY(name, 'Spaceused') / 128.0) AS space_used_in_mb,

    CONVERT(DECIMAL(10, 2), size / 128.0) -

    CONVERT(DECIMAL(10, 2), FILEPROPERTY(name, 'Spaceused') / 128.0) AS space_available_in_mb,

    CONVERT(DECIMAL(10, 2), FILEPROPERTY(name, 'Spaceused') / 128.0) /

    CONVERT(DECIMAL(10, 2), size / 128.0) * 100 AS pct_filled

    FROM

    sys.database_files AS D

    You can then alert based on the results.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

Viewing 2 posts - 1 through 1 (of 1 total)

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