Skip to Content
0

Interpreting result of 'sp_spaceused syslogs' in master

Jun 12, 2017 at 08:47 AM

140

avatar image

Folks,

Could you tell me understanding each value of sp_spaceused syslogs in master database in ASE 12.5.4?

Example

1> use master
2> go
1> sp_spaceused syslogs
2> go
 name         total_pages  free_pages   used_pages    reserved_pages
 ------------ ------------ ------------ ------------- --------------
 syslogs      15360        9915         1645          0
1> sp_spaceused
2> go
 database_name     database_size
 ----------------- -------------
 master            30.0 MB

 reserved    data        index_size  unused
 ----------- ----------- ----------- -----------
 10754 KB    7946 KB     744 KB      2064 KB
1> dbcc checktable(syslogs)
2> go
Checking syslogs: Logical pagesize is 2048 bytes
The total number of data pages in this table is 1585.

I know total_pages is master database size.
But how are the values of free_pages and used_pages of syslogs come from?

The number of used_pages doen't match dbcc checktable output.
dbcc checktable(syslogs) shows the number of data page is 1585.

As for free pages, sp_spaceused syslogs shows 9915 pages.
Calculating from the result of sp_spaceused without argument,
Free pages = DB size - reserved = 30*1024 - 10754 KB = 19966 KB = 9983 Pages
They don't match.

I ran dbcc checkalloc, so I believe the stats are updated.

One of my customer would like to know the free pages of syslogs in master by calculation.

Regards,
Kazuo Otani

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Avinash Kothare Jun 12, 2017 at 06:12 PM
0

master database typically has mixed data and log.

The dbcc checktable(syslogs) output has a clear line saying the following

*** NOTICE: Notification of log space used/free cannot be reported because the log segment is not on its own device.

So for master and any other database with mixed log and data segments, the equation will not work.

What you get is some indication of log usage.

And with "dump transaction master with truncate_only" the used pages count can go down a bit up to a point.

For a user database with separate data and log segments total pages for log segment = total used + total free.

HTH

Avinash

Share
10 |10000 characters needed characters left characters exceeded
Kazuo Otani Jun 13, 2017 at 08:44 AM
0

I answer my own question.

When running "sp_spaceused syslogs", the equation

total_pages = free_pages + used_pages + reserved_pages

is not applied to a database with the log segment is not on its own device.

On a "mixed log and data" database, the values of "sp_spaceused syslogs" result are calculated as follows:

total_pages = 'database size in # of pages'

free_pages = lct_admin("logsegment_freepages",dbid) - lct_admin("reserved_for_rollbacks",dbid)

used_pages = lct_admin("num_logpages",dbid) + "total_pages"/256

Number of data pages shown in "dbcc checktable(syslogs)" is identical to the value of lct_admin("num_logpages",dbid)

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Cool.

Looking inside SAP/Sybase provided SQL for sp_spaceused is useful.

Thanks for bringing the nugget out.

Avinash

0