February 14, 2003 at 1:59 pm
I wrote a stored proc called sp_DBA_spaceused to traverse all databases retrieving spaceused information. For some reason I can only access sysfiles on master and not my other databases, yet I am able to retrieve data from other system tables without a problem. I have one procedure which retrieves all DB names and builds and EXEC statement to fire a second stored proc that retrieves the spaceused data. Both stored procs were created in master and have the name sp_ so they should be able to run from any database.
Even the simple logic below won't work.
-- Create in master database
create procedure sp_DBA_test
as
begin
select * from sysfiles
end
-- Create in master database
create procedure sp_DBA_test2
as
begin
select * from sysobjects
end
-- From master execute this code against database DBATest
exec DBATest.dbo.sp_DBA_test
exec DBATest.dbo.sp_DBA_test2
The first EXEC statement appears executes sp_DBA_test1 within DBATest, but retrieves sysfiles data from master.
The second EXEC statement executes sp_DBA_test2 from within DBATest and retrieves sysobjects information from DBATest.
What gives? Why can't I access sysfiles? I must be missing something obvious.
Thanks, Dave
February 15, 2003 at 4:51 pm
Dave,
I'm not sure why you can't query sysfiles database but if you replace sysfiles to sysfiles1,it would work.It did work for me. BOL says that sysfiles is a virtual table and i believe sysfiles1 is a physical table. I think that might be the cause. Hope someone else can also throw some more light on this.
February 16, 2003 at 11:22 am
Thanks. I'll try using sysaltfiles. I need retrieve the data and log file names, location and size. I believe sysaltfiles should do the job.
Dave
February 18, 2003 at 11:44 am
Well that didn't work. I thought sysaltfiles would give me the size of the data and log files, but the data does not match the data in sysfiles.
Why does the data in the "size" column in sysfiles not match the data contained in the "size" column in sysaltfiles? I did notice that sysfiles is automatically updated whenver a size changes and that sysaltfiles is only updated when the server is restarted. For this reason I wish to avoid referencing sysaltfiles. Do you know where I can retrieve the size information for a data and log file besides going to each database and running Select * from sysfiles?
I want to traverse all databases, creating a single report for database space usage.
Thanks, Dave
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply