on 06-12-2017 9:47 AM
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
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)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.