June 24, 2002 at 3:39 pm
Is there another way to find the amount of space used in a datafile besides dbcc showfilestats? I am trying to get this information into a query I am using for monitoring and I do not want to get the amount of space used in the database to include both the log files and datafiles. I guess I am looking for a query to extract the info. If not I will use dbcc showfilestats and place it in a temp table.
Thanks....
"Keep Your Stick On the Ice" ..Red Green
June 25, 2002 at 5:41 am
Did you try 'sp_helpfile'? It can be the answer for you.
June 25, 2002 at 5:43 am
This will give you the physical file size but not used space.
SELECT (size * 8.0) / 1024 AS MBsize FROM sysfiles
or for total size
select (SUM(size) * 8.0) / 1024 AS MBsize FROM sysfiles
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 25, 2002 at 6:21 am
Antares686
you got result in MB - sp_helpfile in KB
June 25, 2002 at 8:48 am
Just don't do the / 1024 part and will be KB.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 25, 2002 at 10:31 pm
Hi,
Try this stored procedure : "sp_spaceused". Maybe this will help u.
SaNaZ
June 26, 2002 at 9:55 am
Thanks everyone..but,
I am looking for the amount of space that is actually storing data(or indexes) in a datafile. Basically, I am looking for space utilization. DBCC SHOWFILESTATS provides this information, and I guess I am trying to bypass this. I can use it if necessary. It would be nice to be able to query the system tables and extract what I am looking for.
"Keep Your Stick On the Ice" ..Red Green
June 26, 2002 at 10:06 am
Better off using DBCC, it's supported. A service pack (unlikely) or server upgrade (likely) will break what you build.
Steve Jones
June 28, 2002 at 7:31 am
Where can I find more information on DBCC SHOWFILESTATS? I am trying to do the same thing. I can't get the results of sp_spaceused into a temp table, I assume because it produces two outputs from a database.
June 28, 2002 at 9:27 am
Books on line has more information. The two result sets are problematic. I'd pull them using ADO or isql and then parse the text file or insert the results from ADO into a temp table. DTS is a great way to schedule this.
Steve Jones
June 28, 2002 at 9:33 am
I can't find anything on DBCC SHOWFILESTATS in Books Online. Is it there? How do I search for it? Thanks.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply