Post reply

sql server database size and recovery model script.

  • July 9, 2012 at 8:44 am

    #263970

    Hi everyone.

    Can i get T-SQL script for SQL 2008R2/2012, which include database name, DB size & recovery model in single statement.

  • July 9, 2012 at 8:46 am

    #1510376

    select * from sys.databases

  • July 9, 2012 at 8:54 am

    #1510381

    Thanks surma,

    But I cant get the Database size info, form the statement [select * from sys.databases]

    I need database name, DB size & recovery model in single statement.

    Thanks for your help.

  • July 9, 2012 at 8:58 am

    #1510383

    Danzz (7/9/2012)

    Thanks surma,

    But I cant get the Database size info, form the statement [select * from sys.databases]

    I need database name, DB size & recovery model in single statement.

    Thanks for your help.

    SELECT m.name ,

    size * 8 / 1024 AS size ,

    recovery_model_desc

    FROM sys.databases AS s

    INNER JOIN sys.master_files AS m ON m.database_id = s.database_id

  • July 9, 2012 at 8:59 am

    #1510384

    Thanks clayman

  • July 9, 2012 at 9:11 am

    #1510393

    OK, just keep in mind that the above script returns the data file size only so if you need the database file itself (data+log) you would need to write something like this.

    ;

    WITH C AS ( SELECT database_id ,

    [name] ,

    type ,

    size * 8 / 1024 AS size

    FROM sys.master_files

    )

    SELECT [name] ,

    ( SELECT SUM(size)

    FROM C

    WHERE type = 0

    AND C.database_id = s.database_id

    ) + ( SELECT SUM(size)

    FROM C

    WHERE type = 1

    AND C.database_id = s.database_id

    ) AS ,

    recovery_model_desc

    FROM sys.databases s

  • February 13, 2018 at 8:28 am

    #1979592

    sp_helpdb

    qh

    [font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]

Viewing 7 posts - 1 through 6 (of 6 total)

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