Post reply

Check .mdf and .ldf file size using T-SQL

  • December 31, 2009 at 4:03 am

    #156544

    Hello,

    Please tell me a query which i can use to get a result like below table

    Database_NameDataType Size in MB

    DB1 Data 2550

    DB1 Log 1345

    please remember I'm asking about .mdf and .ldf file size not .bak and .trn.

    I tried DBCC sqlperf(logspace) - that gives me the correcct .ldf file size but doesn't provide .mdf file size.

    I also tried Sp_helpdb - but that doesn't categories between .mdf and .ldf. It just gives the DB size, which is not what i want.

    Please suggest.

    Rgds,
    Pankaj

  • December 31, 2009 at 4:21 am

    #1097821

    Try this.

    sp_helpdb 'database Name'

    -Vikas Bindra

  • December 31, 2009 at 4:37 am

    #1097830

    Thanks Vikas,

    Rgds,
    Pankaj

  • January 4, 2010 at 6:35 am

    #1098376

    You can also query sys.database_files (must be run in the context of the database you are interested in). You do need to convert the size from 8kb pages to MB. Here's a query that does it:

    SELECT

    [file_id],

    [type],

    type_desc,

    data_space_id,

    [name],

    physical_name,

    state,

    state_desc,

    size,

    size * 8 / 1024.00 AS size_in_mb

    FROM

    sys.database_files AS DF

    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 4 posts - 1 through 3 (of 3 total)

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