mdf size

18 May 2009 3:36 PM

Paulo

select * from sysfiles

It shows me a size column. But it's not the file size in KB or MG or GB...!
How do I get it?

Using MS SQL 2000... Thanks!

18 May 2009 6:07 PM

DXC

Here is what I use.
Note: Used space may not be 100% correct but close.

CREATE TABLE #temptable (
    [fileid] smallint NULL ,
    [filegroup] smallint NULL ,
    [total_extends] int NULL ,
    [used_extends] decimal (10, 2) NULL ,
    [db_name] varchar (30) ,
    [filename] varchar (150)
    ) ON [PRIMARY]
GO

Insert into #temptable
EXEC master.dbo.sp_MSforeachdb 'Use ? DBCC SHOWFILESTATS'
GO

update #temptable
set total_extends = ((total_extends * 64)/1024)
GO

update #temptable
set used_extends = ((used_extends * 64)/1024)
GO

Select (CONVERT(CHAR(10), GETDATE(), 112)) as [date], [db_name],
used_extends as used_size_in_mb, total_extends as total_size_in_mb,
[filename] from #temptable
GO

Drop table #temptable
GO

Coskun

Show quoteHide quote

"Paulo" wrote:

> select * from sysfiles
>
> It shows me a size column. But it's not the file size in KB or MG or GB...!
> How do I get it?
>
> Using MS SQL 2000... Thanks!
>
>
>

21 May 2009 1:35 PM

Jelle

Hi Coskun and Paulo,

When you run this script you get this error (I'm using SQL Server 2008):

    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near ';'.

I changed this line from:

    EXEC master.dbo.sp_MSforeachdb 'Use ?; DBCC SHOWFILESTATS'

to:

    EXEC master.dbo.sp_MSforeachdb 'Use [?]; DBCC SHOWFILESTATS'

On the topic of calculating database space, you may find it better to sum
the size in sysfiles along with sysindexes. I believe there is an article in
PC Magazine (but I can not remember off the top of my head).

I hope this helps.

Jelle

Show quoteHide quote

"DXC" wrote:

> Here is what I use.
> Note: Used space may not be 100% correct but close.
>
> CREATE TABLE #temptable (
>     [fileid] smallint NULL ,
>     [filegroup] smallint NULL ,
>     [total_extends] int NULL ,
>     [used_extends] decimal (10, 2) NULL ,
>     [db_name] varchar (30) ,
>     [filename] varchar (150)
>     ) ON [PRIMARY]
> GO
>
>
> Insert into #temptable
> EXEC master.dbo.sp_MSforeachdb 'Use ? DBCC SHOWFILESTATS'
> GO
>
> update #temptable
> set total_extends = ((total_extends * 64)/1024)
> GO
>
> update #temptable
> set used_extends = ((used_extends * 64)/1024)
> GO
>
> Select (CONVERT(CHAR(10), GETDATE(), 112)) as [date], [db_name],
> used_extends as used_size_in_mb, total_extends as total_size_in_mb,
> [filename] from #temptable
> GO
>
> Drop table #temptable
> GO
>
>
> Coskun
>
>
>
>
> "Paulo" wrote:
>
> > select * from sysfiles
> >
> > It shows me a size column. But it's not the file size in KB or MG or GB...!
> > How do I get it?
> >
> > Using MS SQL 2000... Thanks!
> >
> >
> >

Thread options