Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Find size of multiple databases in SQL Server 2005

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.

like image 943
Jason Avatar asked May 29 '09 18:05

Jason


People also ask

How do I get a list of databases and sizes in SQL Server?

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.

How do I determine the size of a SQL Server database?

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.


4 Answers

If you can use stored procedures, this should work:

exec sp_msforeachdb 'use ? exec sp_spaceused'
like image 69
Joel Marcey Avatar answered Oct 09 '22 17:10

Joel Marcey


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;
like image 44
Derek Swingley Avatar answered Oct 09 '22 16:10

Derek Swingley


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
like image 30
Thomas Bratt Avatar answered Oct 09 '22 16:10

Thomas Bratt


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 [?]'
like image 42
pjp Avatar answered Oct 09 '22 17:10

pjp