sum(reserved) at Tbl not tally with reserved at DB level

Last Post 22 Apr 2008 12:04 AM by SQLUSA. 11 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
ykcpf
New Member
New Member

--
14 Apr 2008 08:47 PM
hi,

I did sp_spaceused at DB and Tbl level:

1)
sp_spaceused null, true -- DB level

2)
sp_spaceused Tbl_1, true -- TBL level
....
....
sp_spaceused Tbl_N, true -- TBL leve

The reserved, data, index_size, return by 2) is the allocated space for each tables and the sum of all these columns in the same database should tally with the reserved, data, index_size return by 1). Is it correct? I can't seems to get the 2 figures tally even though I have set @updateusage = 'true'.

If my understanding is correct, then what is taking up the size of the difference of 2 figures? overhead, etc?




SQLUSA
New Member
New Member

--
15 Apr 2008 02:22 AM
Can you post the queries with the results?

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/
ykcpf
New Member
New Member

--
15 Apr 2008 03:34 AM
1) sp_spaceused null, 'true'

database_name database_size unallocated space
pubs 200.00 MB 98.18 MB

reserved data index_size unused
1864 KB 1064 KB 624 KB 176 KB


2) do a sp_spaceused with @updateusage = 'true for all tables in pubs and get a total sum of all reserved,data,index_size,unused columns

name rows reserved data index_size unused
authors 23 40 8 32 0
discounts 3 16 8 8 0
employee 43 40 8 32 0
extentinfo 17017 712 656 8 48
jobs 14 24 8 16 0
pub_info 8 160 120 16 24
publishers 8 24 8 16 0
roysched 86 32 8 24 0
sales 21 40 8 32 0
stores 6 24 8 16 0
titleauthor 25 56 8 48 0
titles 18 40 8 32 0
zzz *** total *** zzz 17272 1208 856 280 72

------------------------------------------------------------------------------
so compare this

reserved data index_size unused
1864 KB 1064 KB 624 KB 176 KB

to this

zzz *** total *** zzz 17272 1208 856 280 72


this is what i meant. any clues?
ykcpf
New Member
New Member

--
15 Apr 2008 11:37 PM
In case some of you wonder what is SQL version and SP level.

SQL Server 2000 Ent Edn, 8.00.2187.
ykcpf
New Member
New Member

--
16 Apr 2008 11:57 PM
Is anyone out there please point me to the right path?
Thanks.
SQLUSA
New Member
New Member

--
18 Apr 2008 01:50 AM
Can you run this query and post results:

USE master;

GO


SELECT

'Microsoft SQL Server ' +

convert(varchar, SERVERPROPERTY('ProductVersion') ) + ' -@- ' +

convert(varchar, SERVERPROPERTY('ProductLevel') ) + ' -@- ' +

convert(varchar, SERVERPROPERTY('Edition') ) + ' -2- ' +

convert(varchar, SERVERPROPERTY('EngineEdition') ), @@VERSION ;

GO


EXEC sp_dbcmptlevel AdventureWorks



GO


Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/
SQLUSA
New Member
New Member

--
18 Apr 2008 01:53 AM
Why are you worried about this?

I never found sp_spaceused numbers to be scientific, in fact on some occasion they were down right off the mark.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/

SQLUSA
New Member
New Member

--
18 Apr 2008 01:55 AM
One more thing....to shock you.....

sp_spaceused is coming from Sybase circa 1990....

Microsoft is introducing new metadata, so sooner or later it will be replaced, or at least made accurate.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/
ykcpf
New Member
New Member

--
21 Apr 2008 11:08 PM
pete,

bingo, you are right! query 2) doesn't includes system tables. With system tables, the figures does tally with @updateusage = 'false' set.

however, when i tried to include sp_spaceused <SYSOBJECTS>, @updateusage = 'true' for system tables, encountered

Server: Msg 2591, Level 16, State 19, Procedure sp_spaceused, Line 115
Could not find row in sysindexes with index ID -1 for table 'sysfiles'.

not all system tables throw this error. I believe some are system catalog views not really a table.

Question: how to i differential whether a system object is a table or view in using TSQL programming? Is there a way out? I tried looking up the type,xtype column in sysobjects, both are 'S' and also checked whether objectproperty(id,'IsSystemTable')=1, doesn't seem to distinct which is system tables or views.
SQLUSA
New Member
New Member

--
22 Apr 2008 12:04 AM
These are user tables:

select TableName=Name from sysobjects where type='U' order by name


Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
The Best SQL Server Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/
ykcpf
New Member
New Member

--
22 Apr 2008 01:13 AM
what I wanted to do now is

1)
sp_spaceused null, true -- DB level

2)
sp_spaceused <usertable>, 'true'
go
....
sp_spaceused <systemtable>, 'true'
go
....
sp_spaceused <systemview>, 'false' -- if set to true it will give me "Could not find row in sysindexes with index ID -1 for table...." error
go

I want to set @updateusage = 'false' in my TSQL code dynamically if the object is a sys view.



SQLUSA
New Member
New Member

--
22 Apr 2008 11:27 PM
A system view is dynamically generated.

It does not take up space.


Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/
You are not authorized to post a reply.

Acceptable Use Policy