Find database size on several servers

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Find database size on several servers

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-03-23 : 08:21:52

Peter writes "I'm trying to write a query to create a list of databases and their size AND do this for several servers. I have the following which works on a single server but I need this point at several servers.

SET NOCOUNT ON
DECLARE @counter SMALLINT
DECLARE @counter1 SMALLINT
DECLARE @dbname VARCHAR(100)
DECLARE @size INT
DECLARE @size1 DECIMAL(15,2)
SET @size1=0.0

SELECT @counter=MAX(dbid) FROM master..sysdatabases
IF EXISTS(SELECT name FROM sysobjects WHERE name='sizeinfo')
DROP TABLE sizeinfo
CREATE TABLE sizeinfo(fileid SMALLINT, filesize DECIMAL(15,2), filename VARCHAR(1000))
CREATE TABLE #temp3 (DBNAME CHAR(250), filesize DECIMAL(15,2))
WHILE @counter > 0
BEGIN
SELECT @dbname=name FROM master..sysdatabases WHERE dbid=@counter
TRUNCATE TABLE sizeinfo
EXEC ('INSERT INTO sizeinfo SELECT fileid,size,filename FROM '+ @dbname +'..SYSFILES')
SELECT @counter1=MAX(fileid) FROM sizeinfo
WHILE @counter1>0
BEGIN
SELECT @size=filesize FROM sizeinfo WHERE fileid=@counter1
SET @size1=@size1+@size
SET @counter1=@counter1-1
END
SET @counter=@counter-1
insert into #temp3
SELECT @dbname AS DBNAME,CAST(((@size1)*0.0078125) AS DECIMAL(15,2)) AS [DBSIZE(MB)]

SET @size1=0.0
END
SET NOCOUNT OFF
select * from #temp3 order by filesize desc
drop table #temp3"

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-03-23 : 08:55:00

If you can, create an admin database on each server, output the results to a table in this database (adding the server name to an extra field), then use a DTS package to get this data and run it into a local table..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources