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.
Here is a simple TSQL to simplify the work:
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]
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