Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 979
  • Last Modified:

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.
0
motioneye
Asked:
motioneye
1 Solution
 
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
 
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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
 
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
 
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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now