May 29, 2009 at 8:10 am
I like to know what script is used to determine the space usage values for the data and log-files as shown when opening the Reports>Disk Usage. I like to use that info in a script.
Any1?
Greetz,
Hans Brouwer
May 29, 2009 at 8:36 am
Hi Hans
If you run profiler you can see exactly what the report runs.
It gets the values from DBCC SHOWFILESTATS and sys.data_spaces, DBCC SQLPERF ( LOGSPACE ), sysfiles, sys.partitions, sys.allocation_units and sys.internal_tables.
Best you run profiler and get the rest of the code as well.
I run the following to capture disk space to a table to build audit and trend information:
--Script to Collect File Usage for every DB
EXECUTE sp_msforeachdb '
set nocount on
use ?
--Insert into Administration.dbo.filespace
select
[Database] = ''?'',
[Type] =
case when a.groupid is null then '''' when a.groupid = 0 then ''Log'' else ''Data'' end,
[FileSizeMB]=
convert(numeric(10,2),sum(round(a.size/128.,2))),
[UnusedSpaceMB]=
convert(numeric(10,2),sum(round((a.size-fileproperty( a.name,''SpaceUsed''))/128.,2))) ,
[UsedSpaceMB]=
convert(numeric(10,2),sum(round(fileproperty( a.name,''SpaceUsed'')/128.,2)))
,[DBFileName]= isnull(a.name,''*** Total for all files ***''),
[Date] = Getdate(),
[DBID] = db_id()
from
sysfiles a
group by
groupid,
a.name
with rollup
having
a.groupid is null or
a.name is not null
order by
case when a.groupid is null then 99 when a.groupid = 0 then 0 else 1 end,
a.groupid,
case when a.name is null then 99 else 0 end,
a.name
'
------
Hope this helps.
Stef.
May 29, 2009 at 8:40 am
Stefano has the right idea. Attached is the sql I got from Profiler.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 30, 2009 at 12:46 pm
Tnx for answering all. Found out about SQLPERF already, but tnx anyways.
Greetz,
Hans Brouwer
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply