I was wondering if there is a sql statement to get the current size of all the databases on your server instead of right clicking and going to properties for each one.
If you need to check a single database, you can quickly find the SQL Server database sizein SQL Server Management Studio (SSMS): Right-click the database and then click Reports -> Standard Reports -> Disk Usage. Alternatively, you can use stored procedures like exec sp_spaceused to get database size.
To estimate the size of a database, estimate the size of each table individually and then add the values obtained. The size of a table depends on whether the table has indexes and, if they do, what type of indexes.
If you can use stored procedures, this should work:
exec sp_msforeachdb 'use ? exec sp_spaceused'
Check out the sys.master_files table.
This query will give you total size of everything in your instance:
SELECT SUM(size*8192.0) AS totalsize
FROM sys.master_files;
The simplest way is to use:
exec [sys].[sp_databases]
go
Which produces something like:
Name Size Remarks
mydatabase1 29888 NULL
mydatabase2 13760 NULL
mydatabase3 11776 NULL
master 5376 NULL
model 3008 NULL
msdb 7616 NULL
tempdb 2560 NULL
For the size of the transaction logs associated with a database, use:
dbcc sqlperf(logspace)
go
Which produces something like:
Name Log Size (MB) Log Space Used (%) Status
master 1.242188 50.9434 0
tempdb 0.7421875 61.25 0
model 0.7421875 38.94737 0
msdb 1.992188 35.88235 0
mydatabase1 5.554688 18.55661 0
mydatabase2 2.742188 32.9594 0
mydatabase3 8.992188 26.58015 0
exec sp_helpdb
Lists out the size of data, owner, creation date, etc for all databases on a server in one nice table.
If you then want to go delving into a particular database to see the individual table sizes you can use
use MyFunkyDb
go
EXECUTE sp_MSforeachtable 'EXECUTE sp_spaceused [?]'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With