Post reply

Query - Database

  • September 28, 2011 at 2:44 pm

    #258024

    I know I can get the information from sys.master_files and sp_heldb but how can I write a query to get the data in a sing line per database for the below columns?

    server

    database

    dbid

    dbsize

    dbfreespace

    mdfpath

    ldfpath

    recovery

    Sample out needed:

    server db dbid dbsize dbfreespace mdfpath ldfpath recovery

    testserver test1 1 2.5 1.5 e:\test1.mdf g:\test1.ldf full

    testserver test2 2 5.3 2.5 f:\test1.mdf g:\test2.ldf simple

    Thanks.

    The_SQL_DBA
    MCTS

    "Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

  • September 28, 2011 at 2:58 pm

    #1388110

    you have to join master-files to itself.

    this will work, but if you have any ndf files, the are not taken into account.

    select @@SERVERNAME,

    db_name(main.database_id),

    main.size AS mdfsize,

    logs.size AS logssize,

    main.physical_name AS mdfpath,

    logs.physical_name AS ldfpath,

    dbs.recovery_model_desc

    FROM sys.master_files main

    inner join sys.master_files logs

    on main.database_id = logs.database_id

    AND main.file_id <> logs.file_id

    inner join sys.databases dbs

    on main.database_id = dbs.database_id

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • September 28, 2011 at 3:06 pm

    #1388116

    Thanks Lowell. But the result from your query gives .ldf under mdfpath and .ndf under the ldfpath for some databases, maybe because I have multiple ndf files? Also, how do I get the actual db size and free space similar to the output of sp_helpdb for a given database?

    The_SQL_DBA
    MCTS

    "Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

  • September 29, 2011 at 1:27 am

    #1388203

    Hi the below query will solve u r problem.it shows mdf file mdf path and ldf in ldf path

    with cte_dbmdfinfo(dbname,dbid,type_desc,filename,mdfpath,mdfsize)

    as

    (select DB_NAME(database_id),database_id,type_desc,name,physical_name,size from sys.master_files where type_desc='rows'),

    cte_dbldfinfo(dbname,dbid,type_desc,filename,ldfpath,ldfsize)

    as

    (select DB_NAME(database_id),database_id,type_desc,name,physical_name,size from sys.master_files where type_desc='log'),

    cte_recovery(database_id,recovery_model)

    as

    (select database_id,recovery_model_desc from sys.databases)

    select @@servername as ServerName,a.dbname,a.dbid,a.filename,a.mdfsize+b.ldfsize as Size,a.mdfpath,b.ldfpath,c.recovery_model from cte_dbmdfinfo a inner join cte_dbldfinfo b

    on a.dbid=b.dbid inner join cte_recovery c on a.dbid=c.database_id

Viewing 4 posts - 1 through 3 (of 3 total)

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