February 9, 2009 at 2:26 am
Hello
I need to get the file space used for each file in the database without using fileproperty(). Is there a table that I can get this from in MSSQL 2000 and MSSQL 2005.:unsure:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
February 9, 2009 at 2:33 am
I think sysfiles should help you out. select * from sysfiles
Sriram
February 9, 2009 at 2:40 am
Hello
I did us it but it only gives the size of the file not the used space.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
February 9, 2009 at 4:15 am
hello, did you see sp_helpfile?
Sriram
February 9, 2009 at 4:50 am
Hello
Yes these figures I can get from sysfiles. My problem is linking the sysindex table to the sysfiles table so that I can work out the amount of unused space in the database per file. To get this from a server localy I use fileproperty() but in this case I can not use it.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
February 9, 2009 at 4:23 pm
Try this:
Create TABLE #db_file_information(
fileid integer
, theFileGroup integer
, Total_Extents integer
, Used_Extents integer
, db varchar(30)
, file_Path_name varchar(300))
-- Get the size of the datafiles
insert into #db_file_information
( fileid
, theFileGroup
, Total_Extents
, Used_Extents
, db
, file_Path_name )
exec sp_MSForEachDB 'Use ?; DBCC showfilestats'
-- add two columns to the temp table
alter table #db_file_information add PercentFree as
((Total_Extents-Used_Extents)*100/(Total_extents))
alter table #db_file_information add TotalSpace_MB as
((Total_Extents*64)/1024)
alter table #db_file_information add UsedSpace_MB as
((Used_Extents*64)/1024)
alter table #db_file_information add FreeSpace_MB as
((Total_Extents*64)/1024-(Used_Extents*64)/1024)
select * from #db_file_information
drop table #db_file_information
MJ
February 9, 2009 at 9:38 pm
You can also get this information using SMO and the properties DataSpaceUsage and IndexSpaceUsage.
Not sure if you can use that, since you can't use fileproperty().
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
February 15, 2009 at 4:30 pm
Or this one :-
USE DatabaseName
SELECT [name] as PhysicalFileName, SUM(size) / 128.00 AS [Total (Mb)],
SUM(ISNULL(FILEPROPERTY([name], 'SpaceUsed') / 128.00, 0)) AS [Used (Mb)],
CASE status & 0x40 WHEN 0x40 THEN 'Log' ELSE 'Data' END AS Usage
FROM sysfiles
GROUP BY [name], status & 0x40
February 16, 2009 at 6:06 am
On SQL 2000 the space used figures are not maintained very well. You should plan to do a DBCC UpdateUsage maybe once per week to re-sync the figures.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 16, 2009 at 6:17 am
Hello
Thanks for all the replies. At the end of the day I created a SP on the remote servers that populates a table in the remote mater DB with the figures. I executed the SP from the local Server and then read the data from the table in the remote master DB. This is a very dodgy way of doing it but was the only way I could find for SQL 2000.
Thanks again
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
February 16, 2009 at 7:51 am
Hi,
No need to break your head.Already a smart script is there only you need to modify it.
This script was done by Nikhil:
http://qa.sqlservercentral.com/Forums/FindPost657106.aspx
This will help you to get the details.
I will suggest change the script by removing the cursor.
Thanks,
Debojyoti
TechAnalyst
June 6, 2014 at 9:04 am
Hey OomBoom!
I am also stuck with the same problem.. "without using fileproperty()". Could you give some idea about getting space used by a database file without going into each db of an instance, like not using sp_msforeachdb.
Thanks.
June 9, 2014 at 1:06 am
I did not find any other way than DB by DB, file by file. This is what I did. Not the easiest/best way but it works. Hope it helps.
This is not the full script so to use it you will need to patch the holes.
set @sql = 'select *
from OPENROWSET(''SQLOLEDB'',
'''+@InstanceName+''';''SQLUser'';''SQLUserPassword'',
''select name from sys.databases'')'
insert into #tmpDBList
exec sp_executesql @sql
declare dblist cursor for select * from #tmpDBList
open dblist
fetch next from dblist into @DBname
while @@FETCH_STATUS = 0
begin
set @sql = 'select '''+ @InstanceName + ''' InstanceName,'''+ @DBname +''' DBname,GETDATE() CheckDT,*
from OPENROWSET(''SQLOLEDB'',
'''+@InstanceName+''';''SQLUser'';''SQLUserPassword'',
''
select a.FILEID,
[FILE_SIZE_MB] = convert(decimal(12,2),round(a.size/128.000,2)),
NAME = left(a.NAME,15),
FILENAME = left(a.FILENAME,30)
from ['+@DBname+'].dbo.sysfiles a'')'
insert into StatCollection.dbo.DBFileSizes
exec sp_executesql @sql
fetch next from dblist into @DBname
end
drop table #tmpDBList
close dblist
deallocate dblist
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply