Tell multiple DB sizes at once

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

General SQL Server Forums

New to SQL Server Programming

Tell multiple DB sizes at once

Author

Topic

stumbling
Posting Yak Master

104 Posts

Posted - 2008-10-17 : 01:27:30

Hi there i was wondering if anyone had any ideas on how to find the size of the ALL database and log file accross the board when you have over 40 databases using SQL 2000?
Cheers
Phil

AShehzad
Starting Member

8 Posts

Posted - 2008-10-17 : 03:54:05

USE master
GO
SELECT name, filename, [size]
FROM sysaltfiles
order by name asc
GO

Atif Shehzad
DBA Pakistan Revenue Automation Limited

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-10-17 : 10:19:05

If S2K, something like this...

EXEC master..sp_MSForeachdb '
USE

IF DB_ID(''?'')>4
BEGIN

insert into DBMaint..DBSizeUsed
SELECT name AS [File], filename as File_Name
, CAST(size/128.0 as DECIMAL(10,2)) AS Size_in_MB
, CAST(FILEPROPERTY(name, ''SpaceUsed'')/128.0 as DECIMAL(10,2)) as Space_Used
, CAST(size/128.0-(FILEPROPERTY(name, ''SpaceUsed'')/128.0) AS DECIMAL(10,2)) AS Available_Space
, getdate() as RunDate
FROM SYSFILES

--BEGIN
--PRINT ''?''
--END
END'

This actually inserts into a table and I use it to track growth over time..Simply drop the insert line to show within QA.

Terry

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-17 : 10:23:46

i think you can use
sp_databases

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-10-17 : 11:32:51

This script gets the file information for every database on a server, inserts it into temp table, and queries it multiple ways to give various levels of analysis of file space usage.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89058

CODO ERGO SUM

stumbling
Posting Yak Master

104 Posts

Posted - 2008-10-17 : 20:21:50

Thank you very much for your help on this, i appreciate the skill srt that this forum has.
Cheers
Phil

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources