sp_ from master not returning expected results...... 1

[dbmsguy]

for your consideration

I am in the midst of writing a process to monitor database stats for capactiy planning reasons.

senario 1.

in query analyzer I run:

use spacetest
go
select db_name(),db_id(),coalesce(a.groupname,'log') , a.groupid,
b.fileid, (convert(dec(12,2),b.size) *8/1024) , (b.maxsize *8/1024), b.growth, b.name, b.filename
from sysfiles b left outer join sysfilegroups a
on a.groupid = b.groupid

go

select db_name(),db_id(),groupid, convert(dec(12,2),sum(reserved))* 8/1024 as reserved,
convert(dec(12,2),sum(used)) * 8/1024 as used from sysindexes
where (indid < 2 or indid = 255) and groupid > 0 group by groupid


and get:
groupid fileid growth name filename
-------------------------------------------------------------------------------------------------------------------------------- ------ -------------------------------------------------------------------------------------------------------------------------------- ------- ------ --------------------- ----------- ----------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
spacetest 9 PRIMARY 1 1 100.6875000 0 10 spacetest_data c:\mssql7\data\spacetest_data.mdf
spacetest 9 log NULL 2 30.0000000 0 10 spacetest_log c:\mssql7\data\spacetest_log.ldf

(2 row(s) affected)

groupid reserved used
-------------------------------------------------------------------------------------------------------------------------------- ------ ------- --------------------- ---------------------
spacetest 9 1 68.2500000 68.1484375

(1 row(s) affected)


senario 2.

I put the exact query in a stored procedrue in master and call it sp_blabla

I run:

use spacetest
go
sp_blabla

and get:

groupid fileid growth name filename
-------------------------------------------------------------------------------------------------------------------------------- ------ -------------------------------------------------------------------------------------------------------------------------------- ------- ------ --------------------- ----------- ----------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
spacetest 9 PRIMARY 1 1 15.0000000 0 10 master C:\MSSQL7\data\master.mdf
spacetest 9 log NULL 2 1.2500000 0 10 mastlog C:\MSSQL7\data\mastlog.ldf

(2 row(s) affected)

groupid reserved used
-------------------------------------------------------------------------------------------------------------------------------- ------ ------- --------------------- ---------------------
spacetest 9 1 68.2500000 68.1484375

(1 row(s) affected)


excuse the volumous output, but to summarize

from senario 1 and 2 i get different results....

from senario 1 the results are correct

from senario 2, the first query returns results for the master files, the second query returns results for the database context specified (spacetest)...

looking for an explanation
Paul

http://www.dbmsguy.com

 

[tlbroadbent]

You've encountered a &quot;feature&quot; of SQL Server. Sysfiles is a virtual table and doesn't act like other tables. The behavior you see is to be expected in SQL 7 and 2000.

Normally, when a stored procedure, starting with &quot;sp_&quot; is placed in master, it can be referenced using another database name (i.e., spacetest..sp_blabla) and all operations occur in context of the named database. This is not so with the sysfiles table.

No word from MS if it will be corrected at some later date. Terry

http://members.home.net/tlbroadbent/prog.htm

_____________________________________
Man's mind stretched to a new idea never goes back to its original dimensions. - Oliver Wendell Holmes