October 22, 2007 at 9:33 am
I have a piece of code that was pulled from the sp_spaceused proc in sql2000 that I used to keep track of database space per week by database. The same code won't work in sql2005. Listed below is a portion of the code. Any help would be appreciated.
/**
*
* USAGE: Called by another stored procedure: gatherdatabaseuseinfo
*
* DESC:
*
* Revision History:
* -----------------
*
* --/--/---- ??? Created.
* 01/20/2000 sgs Edited for SQL 7.0.
* 04/05/2000 rrm Modified for SQL 7.0. Needed to add Database Name in SQL prior to the select statement.
*
**/
-- Working variable for size calc.
DECLARE @dbsize dec(15,0)
DECLARE @cmd varchar(255)
DECLARE @low int
SELECT @low = low
FROM master..spt_values
WHERE number = 1 and type = 'E'
/*
** We need to create a temp table to do the calculation.
** reserved: sum(reserved) where indid in (0, 1, 255)
** data: sum(dpages) where indid < 2 + sum(used) where indid = 255 (text)
** indexp: sum(used) where indid in (0, 1, 255) - data
** unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
*/
CREATE TABLE #sptspace
(
dbnamevarchar(30) null,
asofdatetime null,
dbsizenumeric(12,2) null,
dbunallocnumeric(12,2) null,
pagesnumeric(12,2) null,
reservednumeric(12,2) null,
datanumeric(12,2) null,
indexpnumeric(12,2) null,
unusednumeric(12,2) null
)
SET NOCOUNT ON
select @cmd = 'use '+@dbname+char(10)+ 'INSERT INTO #sptspace (dbname, asof, dbsize)
SELECT db_name(), getdate(), dbsize = sum(convert(dec(15),size))/128 FROM ' +@dbname+ '..sysfiles s'
--print @cmd
exec (@cmd)
/*
** Now calculate the summary data.
** reserved: sum(reserved) where indid in (0, 1, 255)
*/
SELECT @cmd = 'UPDATE #sptspace SET reserved = (SELECT sum(convert(numeric(12,2),i.reserved))) FROM ' +@dbname +
'..sysindexes i WHERE indid in (0, 1, 255)'
EXEC (@cmd)
/*
** data: sum(dpages) where indid < 2
**+ sum(used) where indid = 255 (text)
*/
SELECT @cmd = 'UPDATE #sptspace SET pages = (SELECT sum(dpages) FROM '+@dbname + '..sysindexes WHERE
indid < 2 )'
EXEC (@cmd)
WAITFOR DELAY '00:00:01'
SELECT @cmd = 'UPDATE #sptspace SET pages = pages + (SELECT isnull( sum(used), 0) FROM ' +
@dbname + '..sysindexes WHERE indid = 255) FROM #sptspace, sysindexes'
EXEC (@cmd)
UPDATE #sptspace SET data = pages
/* index: sum(used) where indid in (0, 1, 255) - data */
SELECT @cmd = 'UPDATE #sptspace SET indexp = (SELECT sum(used) FROM ' + @dbname +
'..sysindexes WHERE indid IN (0, 1, 255)) - #sptspace.data'
EXEC (@cmd)
/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255)... Note 1048576 = 1 MB*/
SELECT @cmd = 'UPDATE #sptspace SET unused = reserved - (SELECT sum(used) FROM ' + @dbname +
'..sysindexes WHERE indid in (0, 1, 255))'
EXEC (@cmd)
UPDATE #sptspace SET data = data * @low/1048576, indexp = indexp * @low/1048576,
unused = unused * @low/1048576, reserved = reserved * @low/1048576
UPDATE #sptspace SET dbunalloc = dbsize - reserved
INSERT INTO dbsizelog
(
dbname,
sampledate,
totalsize,
unallocatedsize,
reserved,
data,
dbindex,
unused
)
SELECT
dbname,
asof,
dbsize,
dbunalloc,
reserved,
data,
indexp,
unused
FROM #sptspace
RETURN (0)
October 22, 2007 at 10:35 am
try this
SELECT @cmd = 'DECLARE @Sum int; SELECT @SUM = SUM(used) FROM ' + @dbname +
'..sysindexes WHERE indid in (0, 1, 255));
UPDATE #sptspace SET unused = reserved - @Sum FROM ' + @dbname +
'..sysindexes WHERE indid in (0, 1, 255))'
instead of
SELECT @cmd = 'UPDATE #sptspace SET unused = reserved - @Sum FROM ' + @dbname +
'..sysindexes WHERE indid in (0, 1, 255))'
October 22, 2007 at 12:28 pm
Thanks for the suggestion. You had the right idea, just referenced the wrong piece of code. I updated the 1st update statement with the suggested change and it works perfectly. Thanks again.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply