Spaceused on 6.5 version
-
Hi all!
I´m working with this old version on a (also old) production box. When i try to get the info about the space used by the prod db using sp_spaceused, i receive this
database_name database_size unallocated_space proddb 3013.00 MB -1079.49 MB
reserved data index_size unused 4190706 KB 3744268 KB 443260 KB 3178 KB
Even using the command sp_spaceused @updateusage = 'true' or dbcc updateusage (proddb) the info keeps innacurate...
Any ideas?
PD: dont try to use the 'preview mode', it erases all the post
-
upa!
-
nobody?
-
I'm not sure this will help, but you might try
DBCC CHECKTABLE(syslogs)
This is supposed to update the space used by the transaction log.
I remember being faced with the situation you are dealing with, but I don't remember the solution, if there was one.
Steve
-
I spoke with my associate and she remembers the issue as well.
She recommends running all of the following:
DBCC UPDATEUSAGE(dbname)
DBCC CHECKDB(dbname)
DBCC CHECKALLOC(dbname)
I would add the DBCC CHECKTABLE(syslogs) as well, for good measure.
hope this helps
Steve
-
thanks a lot, Steve. I will try it
-
Wrote this long time ago, could probably do better job now but we still use it (still managing over 100 6.5 servers). Has a hard-coded hackish calculation of log-space used for our primary DB, worked better than anything else we could find--probably need to modify this for your use, search for word 'Transcription'.
if object_id('sp_spaceused2') is not null
begin
drop proc sp_spaceused2
print 'dropped proc sp_spaceused2'
end
go
create proc sp_spaceused2
@object_pattern varchar(30) = null,
@sort_by varchar(30) = 'Table' --or 'total', 'rows', 'data', 'index', 'unused'
as
---------------------------------------------------------------------------------------------
/*
Procedure : sp_spaceused2
Purpose : enhanced version of sp_spaceused2
Returns : nothing
Notes : Added hard-coded hack for EDIX Transcription DB log space calculation. Since we
don't updateusage on syslogs very often, all normal attempts to report log space
usage are wildly inaccurate. The formula used here was determined experimentally,
by incrementally filling up the log and running dbcc updateusage(db, syslogs) and
calculating the ration between #rows in syslogs and the size of the log. This is
only an approximation, but it's the best I can find for now.
UNITS: Results of early versions of this proc differed from those reported by
MS GUI tools. Determined that was due to fact that MS tools report usage as follows:
at the lowest level, usage is reported in KBytes, where a "K" is 1024 bytes. Next
comes MBytes, where "M" is 1024*1024 (1,048,576) bytes. Beyond that, though, the
multiplier used by Microsoft is not 1024, but 1000. Thus, when MS reports GB it
is really reporting (1,024,000) bytes. To eliminate conflict w/MS tools, I've
adopted the MS units here. NOTE:
Checking the syslogs table locks up, even using (nolock), when a log dump is
going on, so we check for that condition.
Created : 04/30/99 - MSG
*/
---------------------------------------------------------------------------------------------
set nocount on
declare @free float, @logfree float, --units are 2k pages, until formatted
@dbsize float, @logdbsize float,
@allocated float, @logallocated float,
@percentfree float, @logpercentfree float
select @logdbsize = NULL
--if @object_pattern is 'syslogs' and there's no separate log device, then treat this as a full DB space request
if (@object_pattern = 'syslogs') and not exists (select size from master..sysusages(nolock) where dbid = db_id() and segmap = 4)
select @object_pattern = null
--handle case where we're reporting on space used by the entire DB
if (@object_pattern is null)
begin
--calculate summary data for the entire DB's data segment
select @dbsize = sum(size)
from master..sysusages(nolock)
where dbid = db_id()
and segmap <> 4
select @allocated = sum(reserved)
from sysindexes(nolock)
where indid in (0, 1, 255)
and segment <> 2
select @free = @dbsize - @allocated
select @percentfree = case @dbsize
when 0 then 0
else (@free * 100) / @dbsize
end
--calculate summary data for entire DB's log segment (if there is one)
if exists (select size from master..sysusages(nolock) where dbid = db_id() and segmap = 4)
begin
select @logdbsize = sum(size)
from master..sysusages(nolock)
where dbid = db_id()
and segmap = 4
--trouble conflicting w/log dump, so we avoid it
if exists(select * from master..sysprocesses(nolock) where cmd = 'DUMP TRANSACTION' and dbid = db_id())
select @logallocated = -1
--this code is supposed to work, but fails miserably for EDIX's Transcription DB
else if (db_name() <> 'Transcription')
select @logallocated = sum(reserved)
from sysindexes(nolock)
where indid in (0, 1, 255)
and segment = 2
--so for Transcription, we use empirically-derived hack
else
select @logallocated = 0.128 * count(*)
from syslogs(nolock)
select @logfree = @logdbsize - @logallocated
select @logpercentfree = case @logdbsize
when 0 then 0
else (@logfree * 100) / @logdbsize
end
end
--display results
select "Database" = substring(db_name(), 1, 15),
"Category" = case
when (@logdbsize is NULL) then 'Data+Log'
else 'Data'
end,
"Size" = case
when (@dbsize >= 512000000) then str(@dbsize / 512000000.0, 5, 1) + ' TB'
when (@dbsize between 512000 and 519999999) then str(@dbsize / 512000.0, 5, 1) + ' GB'
when (@dbsize between 512 and 519999) then str(@dbsize / 512.0, 5, 1) + ' MB'
when (@dbsize < 512) then str(@dbsize * 2.0, 5, 1) + ' KB'
end,
"Used" = case
when (@allocated >= 512000000) then str(@allocated / 512000000.0, 5, 1) + ' TB'
when (@allocated between 512000 and 519999999) then str(@allocated / 512000.0, 5, 1) + ' GB'
when (@allocated between 512 and 519999) then str(@allocated / 512.0, 5, 1) + ' MB'
when (@allocated < 512) then str(@allocated * 2.0, 5, 1) + ' KB'
end,
"Free" = case
when (@free >= 512000000) then str(@free / 512000000.0, 5, 1) + ' TB'
when (@free between 512000 and 519999999) then str(@free / 512000.0, 5, 1) + ' GB'
when (@free between 512 and 519999) then str(@free / 512.0, 5, 1) + ' MB'
when (@free < 512) then str(@free * 2.0, 5, 1) + ' KB'
end,
"Free%" = str(@percentfree, 3) + '%'
union all
select "Database" = substring(db_name(), 1, 15),
"Category" = 'Log',
"Size" = case
when (@logdbsize >= 512000000) then str(@logdbsize / 512000000.0, 5, 1) + ' TB'
when (@logdbsize between 512000 and 519999999) then str(@logdbsize / 512000.0, 5, 1) + ' GB'
when (@logdbsize between 512 and 519999) then str(@logdbsize / 512.0, 5, 1) + ' MB'
when (@logdbsize < 512) then str(@logdbsize * 2.0, 5, 1) + ' KB'
end,
"Used" = case
when (@logallocated = -1) then '*DUMPING*'
when (@logallocated >= 512000000) then str(@logallocated / 512000000.0, 5, 1) + ' TB'
when (@logallocated between 512000 and 519999999) then str(@logallocated / 512000.0, 5, 1) + ' GB'
when (@logallocated between 512 and 519999) then str(@logallocated / 512.0, 5, 1) + ' MB'
when (@logallocated < 512) then str(@logallocated * 2.0, 5, 1) + ' KB'
end,
"Free" = case
when (@logfree >= 512000000) then str(@logfree / 512000000.0, 5, 1) + ' TB'
when (@logfree between 512000 and 519999999) then str(@logfree / 512000.0, 5, 1) + ' GB'
when (@logfree between 512 and 519999) then str(@logfree / 512.0, 5, 1) + ' MB'
when (@logfree < 512) then str(@logfree * 2.0, 5, 1) + ' KB'
end,
"Free%" = case
when (@logallocated = -1) then '****'
else str(@logpercentfree, 3) + '%'
end
where (@logdbsize is not null) --if no log segment, this portion of union left empty
end
--only reporting on a single object (not Transcription..syslogs)
else if ((@object_pattern <> 'syslogs') or (db_name() <> 'Transcription'))
begin
select "Table" = convert(char(45), user_name(uid) + '.' + so.name),
"Total" = case
when ((si.reserved + isnull(sitext.reserved, 0)) >= 512000000)
then str((si.reserved + isnull(sitext.reserved, 0)) / 512000000.0, 5, 1) + ' TB'
when ((si.reserved + isnull(sitext.reserved, 0)) between 512000 and 519999999)
then str((si.reserved + isnull(sitext.reserved, 0)) / 512000.0, 5, 1) + ' GB'
when ((si.reserved + isnull(sitext.reserved, 0)) between 512 and 519999)
then str((si.reserved + isnull(sitext.reserved, 0)) / 512.0, 5, 1) + ' MB'
when ((si.reserved + isnull(sitext.reserved, 0)) < 512)
then str((si.reserved + isnull(sitext.reserved, 0)) * 2.0, 5, 1) + ' KB'
end,
"Rows" = substring(str(si.rows, 10), 1, 4) + case when si.rows > 999999 then ',' else '' end +
substring(str(si.rows, 10), 5, 3) + case when si.rows > 999 then ',' else '' end +
substring(str(si.rows, 10), 8, 3),
"Data" = case
when ((si.dpages + isnull(sitext.used, 0)) >= 512000000)
then str((si.dpages + isnull(sitext.used, 0)) / 512000000.0, 5, 1) + ' TB'
when ((si.dpages + isnull(sitext.used, 0)) between 512000 and 519999999)
then str((si.dpages + isnull(sitext.used, 0)) / 512000.0, 5, 1) + ' GB'
when ((si.dpages + isnull(sitext.used, 0)) between 512 and 519999)
then str((si.dpages + isnull(sitext.used, 0)) / 512.0, 5, 1) + ' MB'
when ((si.dpages + isnull(sitext.used, 0)) < 512)
then str((si.dpages + isnull(sitext.used, 0)) * 2.0, 5, 1) + ' KB'
end,
"Index" = case
when ((si.used - si.dpages) >= 512000000)
then str((si.used - si.dpages) / 512000000.0, 3, 0) + ' TB'
when ((si.used - si.dpages) between 512000 and 519999999)
then str((si.used - si.dpages) / 512000.0, 3, 0) + ' GB'
when ((si.used - si.dpages) between 512 and 519999)
then str((si.used - si.dpages) / 512.0, 3, 0) + ' MB'
when ((si.used - si.dpages) < 512)
then str((si.used - si.dpages) * 2.0, 3, 0) + ' KB'
end,
"Unused" = case
when ((si.reserved - si.used + isnull(sitext.reserved - sitext.used, 0)) >= 512000000)
then str((si.reserved - si.used + isnull(sitext.reserved - sitext.used, 0)) / 512000000.0, 3, 0) + ' TB'
when ((si.reserved - si.used + isnull(sitext.reserved - sitext.used, 0)) between 512000 and 519999999)
then str((si.reserved - si.used + isnull(sitext.reserved - sitext.used, 0)) / 512000.0, 3, 0) + ' GB'
when ((si.reserved - si.used + isnull(sitext.reserved - sitext.used, 0)) between 512 and 519999)
then str((si.reserved - si.used + isnull(sitext.reserved - sitext.used, 0)) / 512.0, 3, 0) + ' MB'
when ((si.reserved - si.used + isnull(sitext.reserved - sitext.used, 0)) < 512)
then str((si.reserved - si.used + isnull(sitext.reserved - sitext.used, 0)) * 2.0, 3, 0) + ' KB'
end
from sysobjects so(nolock)
join sysindexes si(nolock)
on si.id = so.id
and si.indid < 2
left join sysindexes sitext(nolock)
on sitext.id = so.id
and sitext.indid = 255
where so.name like @object_pattern
and so.type = 'U'
order by case when @sort_by = "Total" then si.reserved + isnull(sitext.reserved, 0) else 0 end desc,
case when @sort_by = "Rows" then si.rows else 0 end desc,
case when @sort_by = "Data" then si.dpages + isnull(sitext.used, 0) else 0 end desc,
case when @sort_by = "Index" then si.used - si.dpages else 0 end desc,
case when @sort_by = "Unused" then si.reserved - si.used + isnull(sitext.reserved - sitext.used, 0) else 0 end desc,
so.name asc
if (@@rowcount = 0)
raiserror('No tables were found that match @object_pattern = "%s"!', 0, 1, @object_pattern)
end
--only reporting on Transcription..syslogs, which we have to hack
else
begin
if exists(select * from master..sysprocesses(nolock) where cmd = 'DUMP TRANSACTION' and dbid = db_id())
select "Table" = 'syslogs',
"Total" = '*DUMPING*',
"Rows" = '*DUMPING*',
"Data" = '*DUMPING*',
"Index" = str(0, 3, 0) + ' KB',
"Unused" = str(0, 3, 0) + ' KB'
else
select "Table" = 'syslogs',
"Total" = case
when ((0.128 * count(*)) >= 512000000)
then str((0.128 * count(*)) / 512000000.0, 5, 1) + ' TB'
when ((0.128 * count(*)) between 512000 and 519999999)
then str((0.128 * count(*)) / 512000.0, 5, 1) + ' GB'
when ((0.128 * count(*)) between 512 and 519999)
then str((0.128 * count(*)) / 512.0, 5, 1) + ' MB'
when ((0.128 * count(*)) < 512)
then str((0.128 * count(*)) * 2.0, 5, 1) + ' KB'
end,
"Rows" = substring(str(count(*), 10), 1, 4) + case when count(*) > 999999 then ',' else '' end +
substring(str(count(*), 10), 5, 3) + case when count(*) between 1000 and 999999 then ',' else '' end +
substring(str(count(*), 10), 8, 3),
"Data" = case
when ((0.128 * count(*)) >= 512000000)
then str((0.128 * count(*)) / 512000000.0, 5, 1) + ' TB'
when ((0.128 * count(*)) between 512000 and 519999999)
then str((0.128 * count(*)) / 512000.0, 5, 1) + ' GB'
when ((0.128 * count(*)) between 512 and 519999)
then str((0.128 * count(*)) / 512.0, 5, 1) + ' MB'
when ((0.128 * count(*)) < 512)
then str((0.128 * count(*)) * 2.0, 5, 1) + ' KB'
end,
"Index" = str(0, 3, 0) + ' KB',
"Unused" = str(0, 3, 0) + ' KB'
from syslogs(nolock)
end
go
if object_id('sp_spaceused2') is not null
begin
print 'created proc sp_spaceused2'
grant execute on sp_spaceused2 to public
end
else
print 'FAILED to create proc sp_spaceused2'
go
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply