Simple script to monitor dB size 3

[RSeymour]

This is a script that I have found very helpful when it comes to managing a SQL server without any fancy tools.

It returns a list of the dBs in an instance with the size of the associated MDF and LDF files, as well as their location.

====================

declare @sql varchar(4000)
declare @db varchar(64)
set @db=''
SELECT @db=min(name)
FROM sysdatabases
WHERE name NOT IN ('master','msdb','tempdb') -- all system databases
and name > @db
while @db is not null
begin
set @sql='use '+@db+'
SELECT ''' + @db + ''' as DBName, sysfiles.size * 8/1024 AS SIZE_kb, sysfiles.filename
FROM sysfiles'
exec (@sql)
SELECT @db=min(name)
FROM sysdatabases
WHERE name NOT IN ('master','msdb','tempdb')
and name > @db
end

====================

 

[JohnBates]

Short and simple. That's how I like it. Thanks.

 

[gmmastros]

RSeymour,

Nice script. There are some potential problems with it, though.

1. If a database is offline, you get an error.
2. if you have a database name with a space, or - or perhaps other weird characters, you get an error.
3. If you're going to exclude master, msdb and tempdb, then you might as well exclude the 4th system db too (model).
4. finally, you can use the undocumented sp_MSForEachDB to make this be a one-liner, like this:

sp_msforeachdb 'If ''?'' Not In (''master'',''msdb'',''tempdb'',''model'')
                   Select ''?'' As DBName, 
                          Size * 8 / 1024 As Size_MB, 
                          FileName 
                   From   [?].dbo.sysfiles'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[JohnBates]

Even sweeter, gmmastros. It does omit OFFLINE databases though I don't know how....

John

 

[RSeymour]

Very nice gmmastros, I will add it to my collection!

Rob