Skip to Content
0

Log space in sp_helpdb and dbcc checktable(syslogs) reports different informations

Feb 02, 2017 at 10:22 AM

161

avatar image
Former Member

Experts,

I have situation.

The dbcc checktables for syslogs shows 25 gigs , free for a databases, while when I do a sp_helpdb to the same database, i see only 5 gigs free.

Ironically, 5 gigs was resized later ( using disk resize), couple of month ago, when the logdevice was 20 gigs then.

The sysusages shos the segmaps all correct.

Though the applications is all down and there are no user connections exists. How is the 20 gigs missing? Each time I bring the SAP application UP, it get hit with 3275 error and log space issues, for which while reboot often the db goes offline. Which then I will perform a bypass recovery, and dbcc rebuild_log to set this normal. SAP Note 2048776 has been followed and executed.

Please Advise.

===============================================================

use SMD

1> dbcc checktable (syslogs)

2> go

Checking table 'syslogs' (object ID 8): Logical page size is 16384 bytes. Checking partition 'syslogs_8' (partition ID 8) of table 'syslogs'. The logical page size of this table is 16384 bytes. The total number of data pages in partition 'syslogs_8' (partition ID 8) is 1. Partition 'syslogs_8' (partition ID 8) has 97 data rows. The total number of data pages in this table is 1. *** NOTICE: Space used on the log segment is 6401 pages (100.02 MB), 0.39%. *** NOTICE: Space reserved on the log segment is 0 pages (0.00 MB), 0.00%. *** NOTICE: Space free on the log segment is 1631999 pages (25499.98 MB), 99.61%. Table has 97 data rows. DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.

1> sp_helpdb SMD

2> go

name db_size owner dbid created durability lobcomplvl inrowlen status

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SMD 189440.0 MB sapsa 4 Jan 13, 2013 full 100 2000 ddl in tran, single user, allow nulls by default, abort tran on log full, allow wide dol rows, page compression (1 row affected) device_fragments size usage created free kbytes

------------------------------ ------------- -------------------- ------------------------- ----------------

dataSMD_1 92160.0 MB data only Jan 13 2013 12:25PM 9193600

dataSMD_2 40960.0 MB data only Jan 13 2013 12:25PM 41317392

logSMD_1 20480.0 MB log only Jan 13 2013 12:25PM not applicable

dataSMD_1 10240.0 MB data only Apr 18 2016 4:13PM 10444800

dataSMD_2 10240.0 MB data only Apr 18 2016 4:17PM 10444800

dataSMD_1 10240.0 MB data only Apr 18 2016 4:42PM 10444800

logSMD_1 5120.0 MB log only Oct 3 2016 12:47PM not applicable

----------------------------------------------------------------------------------------------------------------------

log only free kbytes = 5222624

device segment ------------------------------------ ----------------------------------------

dataSMD_1 default

dataSMD_1 system

dataSMD_2 default

dataSMD_2 system

logSMD_1 logsegment

(return status = 0)

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

2 Answers

avatar image
Former Member Feb 06, 2017 at 05:20 AM
0

Dear Experts,

How do I find the orphaned space allocations for my logsegment?

Following is the version , i am running:

Adaptive Server Enterprise/15.7.0/EBF 20073 SMP ESD#01 Refresh#1/P/RS6000/AIX 6.1/aseasap/2926/64-bit/FBO/Thu May 24 03:06:43 2012

Regards,

Ajith Prabhakaran

Share
10 |10000 characters needed characters left characters exceeded
Mark A Parsons Feb 06, 2017 at 01:05 PM
0

What is the output from running: exec SMD..sp_helpsegment logsegment

I'm wondering if you could have a problem with free space accounting in the log. The output from sp_helpsegment may help, but I'd also suggest you review these threads where the topic's been discussed before, along with some suggested solutions for fixing log free space accounting issues:

https://archive.sap.com/discussions/thread/3425014

https://answers.sap.com/questions/63920/sp-spaceused-negative-count-display.html

Share
10 |10000 characters needed characters left characters exceeded