How to find database size when it is offline in SQL server
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
|
1 2 3 4 5 6 7 |
SELECT DB_NAME(db.database_id) AS [DB Name] ,CONVERT(DECIMAL(6, 3), SUM((CONVERT(DECIMAL(20, 5), mf.size) * 8) / 1024)) AS [DB Size in MB] FROM sys.databases db LEFT JOIN sys.master_files mf ON db.database_id = mf.database_id WHERE db.state_desc = 'OFFLINE' GROUP BY DB_NAME(db.database_id) 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
Solution
Here is a simple TSQL to simplify the work:
Query 2
The second article I found here seniordba.wordpress.com
|
1 2 3 4 5 6 7 |
SELECT LEFT(physical_name, 2) AS Drive ,SUM(size * 8) / 1024 AS Tota_In_MB ,SUM(size * 8) / 1024 / 1024.0 AS Total_In_GB FROM sys.master_files mf INNER JOIN sys.databases sd ON sd.database_id = mf.database_id WHERE sd.state_desc = 'offline' GROUP BY LEFT(physical_name, 2); |
Result
And the third method is straightforward. I visit the physical location of the database files and check the size of the files one by one.
I’m having the following error when trying to execute Query1:
Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.
Kindly try with updated SSMS