How to monitor all database size

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 Development (2000)

How to monitor all database size

Author

Topic

luga
Starting Member

1 Post

Posted - 2008-04-03 : 13:33:12

Hello,

I need to put in a table the allocated space, used space and free space for all datafiles and transaction logs all databases in a specific server.

The code is :

EXEC tempdb.dbo.spSpaceUsed

CREATE PROCEDURE spSpaceUsed AS

CREATE TABLE tempdb.dbo.DBAloc(
rowid int identity (1,1),
DbId Int,
DbName varchar (100),
Name varchar (100),
FileName varchar (200),
Alocated float,
FreeSpace float,
SpaceUsed float,
)

CREATE TABLE tempdb.dbo.Databases(
rowid int identity (1,1),
dbid int,
dbname varchar(100) )

INSERT INTO Databases (dbid, dbname)
select dbid, sd.name
from master.dbo.sysdatabases sd

--
declare @dbCount int
declare @dbName varchar(100)
declare @ssql varchar (100)
select @dbCount = @@rowcount

--count down all the rowids to loop on all databases
WHILE @dbCount > 0
BEGIN

SELECT @DBName = ltrim(rtrim([Dbname]))
FROM Databases
WHERE rowid = @DbCount

SET @ssql = 'USE ' + @DBName

-- doen't seem to work :

EXEC(@ssql)

INSERT INTO dbAloc ( Name , FileName , Alocated , FreeSpace , SpaceUsed )
select name, filename, size/128.0 as alocated, (size - fileproperty (name, 'SpaceUsed'))/128.0 as freespace, fileproperty (name, 'SpaceUsed')/128.0 as spaceused from northwind..sysfiles

SET @dbcount = @dbcount - 1

END

GO

The problem I have with this code is the context of fileproperty is allways the current database it doen't seems to respect the use statement.

How to accomplish my objective ?

All the best,
Luga.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-04-03 : 13:38:01

Get Server Database File Information:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89058

CODO ERGO SUM

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-03 : 13:41:11

To monitor Disk spaces used by database for Trend-Analysis,
Here you go:

http://www.sqlservercentral.com/articles/Administering/monitoringdiskspaceandsendingalertswithtsql/1114/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources