db size for all db's across servers from cms

Question

db size for all db's across servers from cms

mtz676

SSCrazy Eights

Points: 9174
More actions
October 9, 2020 at 6:41 pm

#3795580

how do I calculate db sizes of all databases across all servers from cms
declare @dbsizes TABLE (servername varchar(max),dbname varchar(max),dbsize varchar(max));
insert into @dbsizes
SELECT    @@servername as ServerName,  sys.databases.name,
CONVERT(VARCHAR,SUM(size)*8/1024)+' MB' AS [Total disk space]  FROM
sys.databases   JOIN        sys.master_files  ON
sys.databases.database_id=sys.master_files.database_id  GROUP BY    sys.databases.name  ORDER BY    sys.databases.name;
select * from @dbsizes db
the above code works fine on individual server but when run in cms throws an error.
Incorrect syntax near ' '-

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 1

I can't speak to the problem you're having with CMS (I don't use it) but it's a bit crazy having both of the columns in your table variable defined as LOBs. Max number of bytes for a database name is 128 characters. It's also not likely that you have a database larger than 2.4 BILLION megabytes (2.2 PetaBytes) so it's unlikely that you need more than 10 digits to display the number of MB in any given database. It's also going to plague you to have the megabytes listed as a VARCHAR() that is followed by a space and the letters "MB". You should just add MB to the column name and leave the datatype of the column as an INT.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Michael L John One Orange Chip Points: 27038 More actions · Answer 2

Do you have multiple versions of SQL in your CMS? That is likely the cause of your issue.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

dataEnthusiast SSC Rookie Points: 44 More actions · Answer 3

I am not sure about CMS either. But if you are looking for getting sizes of all databases from a single SQL server, I would use dynamic sql. I have something like this I use.

DECLARE @sqlscript VARCHAR(4000)

DECLARE @DB_sizes TABLE

(ServerName VARCHAR(200)

,DatabaseName varchar(200)

,FilesizeMB DECIMAL (10,2))

Select @sqlscript = 'Use ['+ '?' + '] Select @@servername as Servername,

'+ '''' + '?' + '''' + ' AS DatabaseName ,

convert(decimal(12,2),round(a.size/128.000,2)) as FilesizeMB

from dbo.sysfiles a'

Insert into @DB_sizes

Exec sp_MSForEachDB @sqlscript

mtz676 SSCrazy Eights Points: 9174 More actions · Answer 4

Thanks for the answer..I had it working using table variables in my script and it worked fine.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 5

mtz676 wrote:

Thanks for the answer..I had it working using table variables in my script and it worked fine.

Two way street here, please. Can you share your code? Someone else might benefit (probably me).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

david.edwards 76768 Ten Centuries Points: 1246 More actions · Answer 6

Michael L John wrote:

Do you have multiple versions of SQL in your CMS? That is likely the cause of your issue.

I'd concur with this. I split the servers into a hierarchy of SQL versions in CMS for this reason (not that I use CMS for much these days)

@mtz676: Having said that, I have just run your posted code, as is, against the top level and it works against all servers from 2008 to 2016 - do you have any 2005 left? Possibly something there that 2017+ does not like, but I've not had exposure to that yet.

Bearing in mind this and some of your other posts, you might find this useful (or not), I used to run it once a month using CMS and export to a simple spreadsheet just as a simple - "here's what we have" . Greybeards please excuse any naivety, I wrote shortly after becoming Accidental DBA!

-- Database Details
SELECT
--SERVERPROPERTY('ServerName') AS ServerName --Uncomment for 2005 group only
name
,database_id
,recovery_model_desc
,compatibility_level
,page_verify_option_desc 
,is_auto_update_stats_on 
--,is_auto_update_stats_async_on 
--,is_local_cursor_default
,REPLACE(@@version, CHAR(10), ' ') AS SQLVersion
,SERVERPROPERTY('productVersion') AS Build
,SERVERPROPERTY('productlevel') AS SPLevel
FROM sys.databases


-- SQL Server details
SELECT
@@version AS SQLVersion
,CASE SERVERPROPERTY('EngineEdition')
WHEN 1 THEN 'Personal or Desktop' 
WHEN 2 THEN 'Standard'
WHEN 3 THEN 'Enterprise'
WHEN 4 THEN 'Express'
END AS Edition
,SERVERPROPERTY('productlevel') AS SPLevel
,SERVERPROPERTY('productVersion') AS Build

This reply was modified 3 years, 5 months ago by david.edwards 76768.

"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?