Best way to monitor datafile and log file grow

  • Hi,

    what is the best way to monitor datafile and lofile grow ?

    With custom scripts ?

    Does perfmon can do that ?

    regards

    C

  • Hi,

    you can use sql scripts from dbWarden. Here you got an email if there is an autogrowth on your database.

    Or you can configure alerts in the SSMS.

  • There are built in utilities that let you monitor drive space on the system. Here's an example[/url] on how to use it.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Hi,

    do you have scripts examples of configure alerts in the SSMS ?

    thanks !

  • Hi,

    here is a link how to create alerts.

    http://technet.microsoft.com/en-us/library/ms366332.aspx

    If you had created them, you can script them yourself. It is very easy

    Andreas

  • I wrote this:

    USE [Monitor]

    GO

    /****** Object: StoredProcedure [dbo].[sp_DBSizing] Script Date: 12/06/2013 12:01:44 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE procedure [dbo].[sp_DBSizing]

    as

    begin transaction;

    EXECUTE master.sys.sp_MSforeachdb

    'USE [?];

    insert into monitor.dbo.tbl_DBSizing

    select CURRENT_TIMESTAMP

    , d.name

    , d.file_id

    , d.size

    , d.physical_name

    from sys.database_files d

    order by d.file_id, d.size;'

    commit;

    The information is logged to a holding table to be used in anticipating future storage requirements.

  • thanks for your script.

  • Hi

    You can use the performance monitor (perfmon.exe) by choosing "Log growths" and "Data file(s)" under the MSSQL$Instance:Databases for a specific database.

    Regards,

    IgorMi

    Igor Micev,My blog: www.igormicev.com

  • Great !

    Thanks for your answer !

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

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