Find the size of all databases at once | SansSQL

Sunday, May 24, 2009

Find the size of all databases at once

You may run into cases where you have to find the size of all the databases in a server in less time...
This will be easy and quick when you have less databases on the box.
What happens if the box has more number of databases??? Here is a quick solution for it...

Run the below Query and get your results in less time and in one shot.

EXEC sp_msforeachdb 'Use ?

Declare @dbsize float
Declare @logsize float
select @dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end))
, @logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))
from dbo.sysfiles
select ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize))
* 8192 / 1048576,15,2) + '' MB'') AS [Size of ?]'

2 comments:

Anonymous said...

It is trowing errors

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '3'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '3'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '3'.
Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'ama'. No entry found with that name. Make sure that the name is entered correctly.

and errors contin...

Sandesh Segu said...

This error means, that the db name has white space or special characters. To resolve this just replace "Use ?" with "Use [?]"

Post a Comment