Hi -

(By the way, being a lazy dba nerd, I guess y'all know I was rooting all
along for the White Sox ...
I'm just glad there isn't a baseball team called the Pocket Protectors or
I'd be very conflicted about what team to root for).

Just thought I'd throw this script out there - been working on this with an
eye to monitoring data growth rates and when it's time to grow the data
files.

Yeh yeh ... I know ... there are other scripts out there, but a lot of them
are cursor-based and some involved dynamically creating stored procedures,
and other things that I didn't want to add to production db's.

It's a work in progress, so there are a couple of select statements thrown
in here, while I make up my mind exactly what info I want.
I'm thinking of a secondary report that shows me how much disk space, per
drive, being used by SQL data/log files and non-SQL files - so that is why
there is an aggregated select with cube.

It comes very close, compared to EM, in displaying the db data file sizes,
space used in the file, space free in the file.
Since extents are used and converted to arrive at a close approximation of
what EM displays, there are minor rounding errors, as far as I can tell.

If you try it out - two things to be aware of.

1) It does a dbcc update useage on each db - so it's a little resource
intensive for a few seconds on each db, and probably best not to test this
out in production during business hours.

2) Watch the copy / paste line lengths - you may have to remove unnecessary
line breaks to make it work in Query Analyzer.



Create Table ##helpfile
(dbname varchar(100),
name varchar(100),
FileId int,
Filename varchar(100),
Filegroup varchar(50),
Size varchar(20),
maxsize varchar(20),
growth varchar(20),
usage varchar(20))

Create Table ##filestats
(Fileid int,
FileGroup int,
TotalSpace int,
UsedSpace int,
FreeSpace int,
Name varchar(100),
FileName varchar(100))

Exec sp_MSForeachDB

@command1 = 'Use ?; DBCC UPDATEUSAGE(0)',
@command2 = 'Use ?;Insert ##helpfile (name, fileid, filename, filegroup,
size, maxsize, growth, usage) Exec sp_helpfile; update ##helpfile set
dbname = ''?'' where dbname is null',
@command3 = 'Use ?;Insert ##filestats (Fileid, FileGroup, TotalSpace,
UsedSpace, Name, FileName) exec (''DBCC SHOWFILESTATS WITH TABLERESULTS'')'

Update ##filestats set totalspace = totalspace*64/1024, usedspace =
usedspace*64/1024
Update ##filestats set freespace = totalspace - usedspace

--cube
Select dbname, UPPER(Left(filename,1)) as [Drive Letter],
count(Left(filename,1)) AS [Count of DB Files],
sum (Cast(replace(size,' KB', '')as int)) As [Total OS File Size],
Usage from ##helpfile group by dbname, usage, UPPER(Left(filename,1))
with cube
order by dbname, [Drive Letter] , Usage desc

--filestats
Select * from ##filestats

-- add in the usedextents to the helpfile info
select a.dbname,
a.filename,
Cast(replace(a.size,' KB', '')as int)/1024 as DatafilesizeMB,
b.usedspace as UsedSpaceMB,
b.freespace as FreeSpaceMB
from ##helpfile a, ##filestats b where a.filename = b.filename

Drop table ##helpfile
Drop table ##filestats



MS Sql Server LazyDBA home page