List all databases which are offline:
SELECT
‘DB_NAME’ = db.name,
‘FILE_NAME’ = mf.name,
‘FILE_TYPE’ = mf.type_desc,
‘FILE_PATH’ = mf.physical_name
FROM
sys.databases db
INNER JOIN sys.master_files mf
ON db.database_id = mf.database_id
WHERE
db.state = 6 — OFFLINE
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT
m.physical_name + ‘\’ + m.name AS [file_path]
FROM
sys.databases AS d
INNER JOIN sys.master_files AS m ON d.database_id = m.database_id
WHERE
d.state_desc = ‘OFFLINE’
–AND m.type_desc = ‘ROWS’
GROUP BY
m.physical_name + ‘\’ + m.name
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
select * from sys.databases where state_desc=’OFFLINE’
=========================================================================
List all Physical and Logical names of databases and their paths:
SELECT d.name DatabaseName, f.name LogicalName,
f.physical_name AS PhysicalName,
f.type_desc TypeofFile
FROM sys.master_files f
INNER JOIN sys.databases d ON d.database_id = f.database_id
GO
=========================================================================
Get a list of database files with size for all databases in SQL Server:
SELECT DB_NAME(database_id) AS database_name,
type_desc,
name AS FileName,
size/128.0 AS CurrentSizeMB
FROM sys.master_files
WHERE database_id > 6 AND type IN (0,1)
=========================================================================
6 Ways to Check the Size of a Database in SQL Server using T-SQL
The sp_spaceused Stored Procedure
This is a system stored procedure that displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database, or displays the disk space reserved and used by the whole database.
To use it, simply switch to the relevant database and execute the procedure. Like this:
USE WideWorldImporters;
EXEC sp_spaceused;
Result:
database_name database_size unallocated space
------------------ ------------- -----------------
WideWorldImporters 3172.00 MB 2511.76 MB
1 row(s) returned
reserved data index_size unused
--------- --------- ---------- -------
573688 KB 461728 KB 104120 KB 7840 KB
1 row(s) returned
This returns two result sets that provide the relevant information.
You can also provide an object name to return data on a specific object within the database. In this case, only one result set will be returned.
Example:
USE WideWorldImporters;
EXEC sp_spaceused N'Application.Cities';
Result:
name rows reserved data index_size unused
------ -------------------- -------- ------- ---------- ------
Cities 37940 4880 KB 3960 KB 896 KB 24 KB
In this example we return information about the Cities table only.
The sp_helpdb Stored Procedure
Another system stored procedure is sp_helpdb.
Here’s an example of calling that:
EXEC sp_helpdb N'WideWorldImporters';
Result:
name fileid filename filegroup size maxsize growth usage
------------ ------ ---------------- --------- ---------- ------------- -------- ---------
WWI_Primary 1 /data/WWI.mdf PRIMARY 1048576 KB Unlimited 65536 KB data only
WWI_Log 2 /data/WWI.ldf null 102400 KB 2147483648 KB 65536 KB log only
WWI_UserData 3 /data/WWI_UD.ndf USERDATA 2097152 KB Unlimited 65536 KB data only
In this case, we pass the name of the database as an argument. We can also call sp_helpdb without providing an argument. If we do this, it will return information on all databases in the sys.databases catalog view.
The sp_databases Stored Procedure
Yet another option is the sp_databases system stored procedure. This stored procedure lists databases that either reside in an instance of the SQL Server or are accessible through a database gateway.
Here’s how to execute it:
EXEC sp_databases;
Result:
DATABASE_NAME DATABASE_SIZE REMARKS
------------------ ------------- -------
master 6848 null
model 16384 null
msdb 15616 null
Music 16384 null
Nature 16384 null
Solutions 47104 null
tempdb 16384 null
Test 16384 null
WideWorldImporters 3248128 null
world 16384 null
WorldData 16384 null
The sys.master_files View
The above stored procedure queries the sys.master_files view. So an alternative is to go straight to the view and cherry pick your columns:
SELECT
name,
size,
size * 8/1024 'Size (MB)',
max_size
FROM sys.master_files
WHERE DB_NAME(database_id) = 'WideWorldImporters';
Result:
name size Size (MB) max_size
------------ ------ --------- ---------
WWI_Primary 131072 1024 -1
WWI_Log 12800 100 268435456
WWI_UserData 262144 2048 -1
In this case we can see the size of each data file and log file, as they’re listed separately. You’ll also notice that I perform a calculation on the size column to convert the value into megabytes (MB).
The sys.database_files View
There’s also a system view called sys.database_files. We can use this view to return the same info as the previous example:
USE WideWorldImporters;
SELECT
name,
size,
size * 8/1024 'Size (MB)',
max_size
FROM sys.database_files;
Result:
name size Size (MB) max_size
------------ ------ --------- ---------
WWI_Primary 131072 1024 -1
WWI_Log 12800 100 268435456
WWI_UserData 262144 2048 -1
Use a Window Function
One potential issue with the previous two examples is that they list out the size of each file separately. This could be seen as a positive or a negative depending on what you want to achieve.
It could also be argued that the first three solutions on this page are problematic, because they only provide the sum total of all files – they don’t list out each individual file along with its size.
So what if you want to see both the size of each individual file, and the total of all files for each database?
You could use the OVER clause to do exactly that.
Here’s an example:
SELECT
d.name AS 'Database',
m.name AS 'File',
m.size,
m.size * 8/1024 'Size (MB)',
SUM(m.size * 8/1024) OVER (PARTITION BY d.name) AS 'Database Total',
m.max_size
FROM sys.master_files m
INNER JOIN sys.databases d ON
d.database_id = m.database_id;
Result:
Database File Size (MB) Database Total
------------------ --------------- --------- --------------
master master 4 6
master mastlog 2 6
model modeldev 8 16
model modellog 8 16
msdb MSDBData 14 14
msdb MSDBLog 0 14
Music Music 8 16
Music Music_log 8 16
Nature Nature 8 16
Nature Nature_log 8 16
Solutions Solutions 8 46
Solutions Solutions_log 8 46
Solutions Solutions_dat_2 10 46
Solutions Solutions_dat_3 10 46
Solutions Solutions_log_2 10 46
tempdb tempdev 8 16
tempdb templog 8 16
WideWorldImporters WWI_Primary 1024 3172
WideWorldImporters WWI_Log 100 3172
WideWorldImporters WWI_UserData 2048 3172
world world 8 16
world world_log 8 16
This lists out each database, the files for each database, the file size for each file, as well as the total of all files for each database. This requires that each database (and their total size) is listed multiple times (once for each file).
Ref: https://database.guide/6-ways-to-check-the-size-of-a-database-in-sql-server-using-t-sql/#:~:text=If%20you’re%20using%20a,and%20then%20click%20Disk%20Usage).
Welcome to My World 