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 ?]'
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:
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...
This error means, that the db name has white space or special characters. To resolve this just replace "Use ?" with "Use [?]"
Post a Comment