locked
Filesize and SpaceUsed is not displaying for all the database other than master RRS feed

  • Question

  • Hi,

    I executed the below query and getting the capacity values only for master database.All other DB shows NULL values for spaceused.

    I'm acutally looking for a query to get all the capacity information other than using temp table and the procedures.

    Is there any way using SQL query ONLY.

    select
    db.[dbid] as 'DB ID',
    db.[name] as 'Database Name',
    af.[name] as 'Logical Name',
    convert(decimal(12,2),round(size/128.000,2)) as FileSizeMB, 
    convert(decimal(12,2),round(fileproperty(db.name,'SpaceUsed')/128.000,2)) as SpaceUsedMB
    from sys.sysdatabases db
    inner join sys.sysaltfiles af
    on db.dbid = af.dbid

    Below is the output

    DB ID Database Name Logical Name FileSizeMB SpaceUsedMB
    1 master master 4.00 3.44
    1 master mastlog 2.00 3.44
    2 tempdb tempdev 8.00 NULL
    2 tempdb templog 0.50 NULL
    3 model modeldev 2.19 NULL
    3 model modellog 0.75 NULL
    4 msdb MSDBData 14.13 NULL
    4 msdb MSDBLog 0.75 NULL
    5 ReportServer ReportServer 5.19 NULL
    5 ReportServer ReportServer_log 6.88 NULL
    6 ReportServerTempDB ReportServerTempDB 4.19 NULL
    6 ReportServerTempDB ReportServerTempDB_log 1.06 NULL
    7 MyAdventureWorks AdventureWorks2012_Data 205.00 NULL
    7 MyAdventureWorks AdventureWorks2012_Log 1.00 NULL

    Wednesday, August 26, 2015 9:30 AM

Answers

  • There is no way to accomplish this through a SELECT, to the best of my knowledge. I.e., without using a cursor, temptable, dynamic SQL or similar constructs.

    Tibor Karaszi, SQL Server MVP | web | blog

    Monday, August 31, 2015 12:00 PM

All replies

  • Dont use sysaltfiles its deprecated. use sys.database_files or sys.master_files

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Wednesday, August 26, 2015 9:37 AM
  • Tried with sys.database_files--This only shows master db info,none of the other db capacities displayed.

    sys.master_files shows all the db,but doesnt show values for Available Space except master DB,All other db shows NULL values

    Thursday, August 27, 2015 8:16 AM
  • For your first script, you are using the FILEPROPERTY function incorrectly. It expects a filename, and you pass the database name. Also, it only work for the current database.

    Sys.master_files do not have file size. And FILEPROPRTY will work just ad badly for sys.master_files as for master..sysaltfiles - it only work for current database.

    So, you do need several queries, potentially encapsulated in a stored procedure. I use my own sp_dbinfo, but that do not show file size, only db (data and log separated):

    http://www.karaszi.com/SQLServer/util_sp_dbinfo.asp


    Tibor Karaszi, SQL Server MVP | web | blog

    Thursday, August 27, 2015 10:50 AM
  • My use case is without using a stored procedure and creation of table.

    By using ONLY SQL query is there any way to get these info 

    Monday, August 31, 2015 4:29 AM
  • There is no way to accomplish this through a SELECT, to the best of my knowledge. I.e., without using a cursor, temptable, dynamic SQL or similar constructs.

    Tibor Karaszi, SQL Server MVP | web | blog

    Monday, August 31, 2015 12:00 PM