Skip to main content
Our Tech Ideas

Find database size when it is offline

Hi, Tech Giants! What new to me today? Yeah, We did a SQL Server migration activity or we can say a side-by-side up-gradation activity. From our DBA side, it’s almost done 90%, 10% percent is pending. We have some offline databases in our source server and the decision-making process is going between our big bosses. All of a sudden one of our big bosses asks the total size of all offline databases. Ohhh!! this task I have not done before !! I start googling and found two articles with two TSQL queries. But before googling I was thinking to go to the physical location of the database files and check the size of the files one by one. So I tried all three possibilities and the result shown below.

Solution

Here is a simple TSQL to simplify the work:

Query 1

The first article I found here at sqlservercentral.com

-- Retrieve the name of the database and calculate its size in megabytes
SELECT 
    DB_NAME(db.database_id) AS [DB Name], -- Alias for the database name column
    CONVERT(DECIMAL(6, 3), SUM((CONVERT(DECIMAL(20, 5), mf.size) * 8) / 1024)) AS [DB Size in MB] -- Alias for the size column
    
-- From the sys.databases table and left join with sys.master_files table
FROM sys.databases db
LEFT JOIN sys.master_files mf ON db.database_id = mf.database_id

-- Filter only the offline databases
WHERE db.state_desc = 'OFFLINE'

-- Group the results by database name
GROUP BY DB_NAME(db.database_id)

-- Sort the results in ascending order of database name
ORDER BY [DB Name]

Steps

To run a script on a Microsoft SQL Server using SSMS.

  • Open Microsoft SQL Server Management Studio
  • Select [New Query] from the toolbar
  • Copy the ‘Example Query’
  • Select the database to run the query against
  • Paste the ‘Example Query’ into the query window
  • Click the [Execute] button

Result