I love the script. Do you have a script that will force management to
buy more hard drives for the SAN so I can stop shrinking all the log
files every day to keep the server from maxing out the disk and killing
all of our customer's websites?
Me: We still need more drives.
Mgmt: I know.
Me: When are we getting more drives?
Mgmt: When we get the money for it?
Me (thinking): I'm not coming in to fix any broken websites Saturday
night at 2 AM.
Mgmt (thinking): We don't need hard drives as long as he keeps fixing
the websites at 2 AM every Saturday.
P.S. Handfuls of hair for sale ..... Cheap!!!
Robert Davis
-----Original Message-----
From: Michael_Beadles
[mailto:mssqldba-ezmlmshield-x79394743.[Email address protected]
Sent: Thursday, October 27, 2005 4:11 PM
To: LazyDBA Discussion
Subject: DB Datafile size script for each database on server ...
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
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
MS Sql Server LazyDBA home page