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.
The first article I found here at sqlservercentral.com
SELECT DB_NAME(db.database_id) as [DB Name],
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]
The second article I found here seniordba.wordpress.com
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);
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.
Scenario We have an interesting situation after configuring a mirror setup. We have configured mirroring between a list of databases…
LinkedIn Learning offering a huge list of video courses. Instantly we can access more than 15,000 courses in different languages…