Hi Jason,

The reason i felt getting sleeping ones are also better is because we would increase the chances of finding the actual queries. And at times if we have a system which has large number of short transactions , it can be fairly effective.

Second point from me is use fileproperty function instead of just the perfmon counter. Reason is it can monitor the Data file used and left and log file used and left.

USE [dbadb]

GO

/****** Object: StoredProcedure [dbo].[get_db_sizes] Script Date: 03/11/2010 15:19:28 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[get_db_sizes]

AS

if exists ( select * from tempdb.dbo.sysobjects o

where o.xtype in ('U') and o.id = object_id( N'tempdb..#DB_FILE_INFO' ))

drop table #DB_FILE_INFO

set nocount on

create table #DB_FILE_INFO (

[ID]intnot null

identity (1, 1) primary key clustered ,

[DATABASE_NAME]sysnamenot null ,

[FILEGROUP_TYPE]nvarchar(4)not null ,

[FILEGROUP_ID]smallintnot null ,

[FILEGROUP]sysnamenot null ,

[FILEID]smallintnot null ,

[FILENAME]sysnamenot null ,

[DISK]nvarchar(1)not null ,

[FILEPATH]nvarchar(260)not null ,

[MAX_FILE_SIZE]intnull ,

[FILE_SIZE]intnot null ,

[FILE_SIZE_USED]intnot null ,

[FILE_SIZE_UNUSED]intnot null ,

[DATA_SIZE]intnot null ,

[DATA_SIZE_USED]intnot null ,

[DATA_SIZE_UNUSED]intnot null ,

[LOG_SIZE]intnot null ,

[LOG_SIZE_USED]intnot null ,

[LOG_SIZE_UNUSED]intnot null ,

)

declare @sqlnvarchar(4000)

set @sql =

'use ['+'?'+'] ;

if db_name() <> N''?'' goto Error_Exit

insert into #DB_FILE_INFO

(

[DATABASE_NAME],

[FILEGROUP_TYPE],

[FILEGROUP_ID],

[FILEGROUP],

[FILEID],

[FILENAME],

[DISK],

[FILEPATH],

[MAX_FILE_SIZE],

[FILE_SIZE],

[FILE_SIZE_USED],

[FILE_SIZE_UNUSED],

[DATA_SIZE],

[DATA_SIZE_USED],

[DATA_SIZE_UNUSED],

[LOG_SIZE],

[LOG_SIZE_USED],

[LOG_SIZE_UNUSED]

)

selecttop 100 percent

[DATABASE_NAME] = db_name(),

[FILEGROUP_TYPE]= case when a.groupid = 0 then ''Log'' else ''Data'' end,

[FILEGROUP_ID]= a.groupid,

a.[FILEGROUP],

[FILEID]= a.fileid,

[FILENAME]= a.name,

[DISK]= upper(substring(a.filename,1,1)),

[FILEPATH]= a.filename,

[MAX_FILE_SIZE] =

convert(int,round(

(case a.maxsize when -1 then null else a.maxsize end*1.000)/128.000

,0)),

[FILE_SIZE]= a.[fl_size],

[FILE_SIZE_USED] = a.[fl_used],

[FILE_SIZE_UNUSED] = a.[fl_unused],

[DATA_SIZE]= case when a.groupid <> 0 then a.[fl_size] else 0 end,

[DATA_SIZE_USED]= case when a.groupid <> 0 then a.[fl_used] else 0 end,

[DATA_SIZE_UNUSED] = case when a.groupid <> 0 then a.[fl_unused] else 0 end,

[LOG_SIZE] = case when a.groupid = 0 then a.[fl_size] else 0 end,

[LOG_SIZE_USED] = case when a.groupid = 0 then a.[fl_used] else 0 end,

[LOG_SIZE_UNUSED] = case when a.groupid = 0 then a.[fl_unused] else 0 end

from

(

Select

aa.*,

[FILEGROUP]= isnull(bb.groupname,''''),

-- All sizes are calculated in MB

[fl_size]=

convert(int,round((aa.size*1.000)/128.000,0)),

[fl_used]=

convert(int,round(fileproperty(aa.name,''SpaceUsed'')/128.000,0)),

[fl_unused]=

convert(int,round((aa.size-fileproperty(aa.name,''SpaceUsed''))/128.000,0))

from

dbo.sysfiles aa

left join

dbo.sysfilegroups bb

on ( aa.groupid = bb.groupid )

) a

order by

case when a.groupid = 0 then 0 else 1 end,

a.[FILEGROUP],

a.name

Error_Exit:

'

exec sp_msforeachdb @sql

select database_name,filegroup_type,filename,filepath,file_size,file_size_used

from #DB_FILE_INFO

Agreed that its not the greatest piece of code, but yes effective.