How to create a procedure from the dbcc showfilestats results

Hi,
I would like to create a stored procedure or maybe system function that will represents below results from dbcc showfilestats below, the procedure or function that will be create must be able to represent the data of used and free space on each data files in KB or MB. how do I start to create the sql statement to have this data to be represent?

Fileid      FileGroup   TotalExtents         UsedExtents          Name                         FileName
----------- ----------- -------------------- -------------------- -----------------------------------------------
1           1           48                   27                                   db_dat                      c:\partition\db.mdf
3           2           32                   1                                    FG1_dat                     c:\partition\FG1.ndf
4           3           32                   1                                    FG2_dat                     c:\partition\FG2.ndf
5           4           112                  35                                 FG3_dat                     c:\partition\FG3.ndf
6           5           32                   1                                    FG4_dat                     c:\partition\FG4.ndf

(5 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
motioneyeAsked:
Who is Participating?
 
motioneyeAuthor Commented:
Hi I find my answer how to get the dbcc showfilestats work as stored procedure and display the results as what EM does.

http://www.databasejournal.com/features/mssql/print.php/10894_3414111_2
0
 
SQL_SERVER_DBACommented:
use master
go
select * from sysaltfiles
0
 
David ToddSenior DBACommented:
Hi,

to illustrate doing this for one database - model in my example ...

use master
go

select * from sysaltfiles
where dbid = db_id( 'model' )

Regards
  David
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
motioneyeAuthor Commented:
Hi,
syslaltfiles will not showing u current used and free space for each data files, that is why I'm trying to have the stored procedure where the procedure will convert the values in dbcc showfilestats from the extent values to a KB or MB
0
 
David ToddSenior DBACommented:
Hi,

Have you looked at sp_spaceused?

Regards
  David
0
 
motioneyeAuthor Commented:
Yes but sp_spaceused will guve u an overall of the db size, what is need is current used and free space on each data file size, by using dbcc showfilestats it will give me an indicator but this dbcc commands will show me the results in an extent not as MB
0
 
David ToddSenior DBACommented:
Hi,

An extent is 8 pages, and a page is 8k, so the maths is pretty easy.

Extents * 8 * 8 * 1024 = bytes
Extents * 8 * 8 = kbytes
Extents * 8 / 1024 = Mbytes

HTH
  David
0
 
David ToddSenior DBACommented:
Hi,

Post a 0 point question in community support asking for PAQ with refund ... you get your points back, but the question remains for others to search.

Regards
  David
0
 
Vee_ModCommented:
Closed, 500 points refunded.
Vee_Mod
Community Support Moderator
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.