December 3, 2002 at 8:44 am
I wrote the following stored procedure to check the databases' space. first, I put it in the master databases, then if I run it from the user database, it gives me the size of master database!
Then if I create the stored procedure in the user databases, it works fine.
I think the stored procedure start with name sp_xxx and put it in master database, then you can execute it from user database and it should work in the way as it was in user database?! Is there anything wrong with this?
create procedure sp_userdb_usage
as
declare @idint-- The object id of @objname.
declare @typecharacter(2) -- The object type.
declare@pagesint-- Working variable for size calc.
declare @dbname sysname
declare @dbsize dec(15,0)
declare @logsize dec(15)
declare @bytesperpagedec(15,0)
declare @pagesperMBdec(15,0)
declare @usedspace dec(15,0)
dbcc updateusage(0) with no_infomsgs
set nocount on
/*
** If @id is null, then we want summary data.
*/
/*Space used calculated in the following way
**@dbsize = Pages used
**@bytesperpage = d.low (where d = master.dbo.spt_values) is
**the # of bytes per page when d.type = 'E' and
**d.number = 1.
**Size = @dbsize * d.low / (1048576 (OR 1 MB))
*/
begin
select @dbsize = sum(convert(dec(15),size))
from sysfiles
where (status & 64 = 0)
select @logsize = sum(convert(dec(15),size))
from sysfiles
where (status & 64 <> 0)
select @bytesperpage = low
from master.dbo.spt_values
where number = 1
and type = 'E'
select @pagesperMB = 1048576 / @bytesperpage
select @usedspace = sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255)
select check_date = convert(char(20), getdate(),0),
database_name = db_name(),
data_size =ltrim(str((@dbsize) / @pagesperMB,15,2) + ' MB'),
log_size =ltrim(str((@logsize) / @pagesperMB,15,2) + ' MB'),
'unallocated space' =ltrim(str((@dbsize - @usedspace) / @pagesperMB,15,2)+ ' MB'),
percentage = ltrim(str(@usedspace/@dbsize *100,15,2)+'%')
end
December 3, 2002 at 2:30 pm
I would set the @dbname variable = to the database your are in. Then I would create a string variable that would look something like this:
declare @cmd varchar(1000)
set @cmd = 'select @dbsize = sum(convert(dec(15),size))from' + @dbname + '.dbo.sysfiles
where (status & 64 = 0)'
exec (@cmd)
That should force the variable to store the data from the database you are interested in.
Remember this when a developer tells you it will just be temporary. Temporary = Permanent.
December 6, 2002 at 1:38 pm
You need to call sp_MS_MarkSystemObject @objectname on your stored procedure. Provide the name of your procedure as the value for @objectname.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply