CODEWITHSUNDEEP
sqlsql-server
adriano
adriano

Reputation: 17

Check DB size but exclude system DB

I've been asked a "simple task" of pulling all used space of all our DBs (about 50) but exclude the system DBs in each instance. My code below throws errors with my where clause. What did I forget?

SELECT [Database Name] = DB_NAME(database_id),
       [Size in MB] = CONCAT(CAST( ((SUM(Size)* 8) / 1024.0) AS DECIMAL(18,2) ) AS VARCHAR(20)),' MB') AS [database_size]

FROM   sys.master_files


WHERE      database_id NOT IN (‘master’, ‘tempdev’,’tempdb’……etc………)


GROUP BY      GROUPING SETS
              (
                (DB_NAME(database_id), Type_Desc),
                (DB_NAME(database_id))
              )
ORDER BY      DB_NAME(database_id), Type_Desc DESC
GO

Upvotes: 0

Views: 193

Answers (1)

Uday Dodiya
Uday Dodiya

Reputation: 354

try following query, you get exact output, add database name in where condition as per your requirment

SELECT [Database Name] = DB_NAME(database_id),
   [Size in MB] = CONCAT(CAST(((SUM(Size)* 8) / 1024.0) AS DECIMAL(18,2)),' 
MB') 

FROM   sys.master_files

WHERE  DB_NAME(database_id) NOT IN ('master', 'tempdev','tempdb')

GROUP BY      GROUPING SETS
          (
            (DB_NAME(database_id), Type_Desc),
            (DB_NAME(database_id))
          )
ORDER BY      DB_NAME(database_id), Type_Desc DESC
GO

Upvotes: 1

Related Questions