Sybase ASE: Adding log to a completely log full database – errors 1105 and 3475 “There is no space available in SYSLOGS” – SOLVED

When SAP Sybasea sybase database’s log is completely full, you won’t be able to add any log space to it. Attempting to add to the log produces a 3475 error:
 

00:0006:00000:00001:2014/01/08 09:03:09.09 server  ERROR: Can't get a new log page in db 4. num_left=17 num_in_plc=17.
00:0006:00000:00001:2014/01/08 09:03:09.09 server  Error: 1105, Severity: 17, State: 7
00:0006:00000:00001:2014/01/08 09:03:09.09 server  Can't allocate space for object 'syslogs' in database 'mydb' because 'logsegment' segment is full/has no free extents. If you ran out of space in syslo
gs, dump the transaction log. Otherwise, use ALTER DATABASE to increase the size of the segment.
00:0006:00000:00001:2014/01/08 09:03:09.09 server  Error: 3475, Severity: 21, State: 7
00:0006:00000:00001:2014/01/08 09:03:09.09 server  There is no space available in SYSLOGS to log a record for which space has been reserved in database 'mydb' (id 4). This process will retry at interval
s of one minute.

 
So what to do? If you separate your data and log segments, you will need to temporarily add the log segment to a data device so the database can recover. Once it recovers, we can add space to the log and remove the log segment from the data device. For good measure, we run dbccs to correct any allocation issues that may be contributing to the out of log space.
 
Add the log segment to a data device (use sp_helpdb dbname to determine which data device has space):

exec sp_configure "allow updates", 1
go
update sysusages set segmap = 7 where dbid = 4 and lstart = 1492992
go
checkpoint
go
shutdown with nowait
go

Add space to the log:

alter database mydb log on mydevicel001 = 500
go

 
Before we do anything else, let’s run dbccs. Of course, you will want to run the dbccs without the fix option to identify if there are other issues prior to running with the fix:

exec kill_user_connections mydb
exec kill_user_connections mydb
exec kill_user_connections mydb
exec kill_user_connections mydb
exec kill_user_connections mydb
exec sp_dboption mydb, 'dbo use', true
exec sp_dboption mydb, 'single user', true
dbcc traceon(3604)
dbcc checkdb(mydb, fix_spacebits)
dbcc checkalloc(mydb, fix)
exec sp_dboption mydb, 'dbo use', false
exec sp_dboption mydb, 'single user', false
go

If no lingering issues, we can remove the log segment from the data device:

exec sp_dboption mydb, 'single user', true
go
use mydb
go
exec sp_dropsegment logsegment, mydb, mydeviced005
go
use master
go
exec sp_dboption mydb, 'single user', false
go

SAP is fixing Bug CR 756957 in ASE 15.7 SP110 that may be the root cause of the 3475 error:

In certain circumstances, databases, including system databases, can incorrectly get into LOG SUSPEND mode, issuing message: “Space available in the log segment has fallen critically low in database ‘ < dbname > ‘. All future modifications to this database will be suspended until the log is successfully dumped and space becomes available.” This may happen even though there is much unreserved space in the database. The problem may also manifest in 3475 errors: “There is no space available in SYSLOGS to log a record for which space has been reserved in database < dbname > .”

Share Button

5 Replies to “Sybase ASE: Adding log to a completely log full database – errors 1105 and 3475 “There is no space available in SYSLOGS” – SOLVED”

  1. Jason,
    Another solution would be reboot the database with no recovery option, extent the logsegment and reboot the dB with recovery with dbcc dbreboot. I can dig up the article about it, I think it was on one of Mark Kusma’s presentations.

    Regards,

    max

    1. Hi Max,

      I’ve ran into three occasions so far where rebooting with no recovery for a database still resulted in the error. No recovery is not a sure thing but it is definitely worth a try prior to extending the log on to a data device

      jason

  2. Jason,

    I’ve seen this error twice now in separate ASE 15.7 SP101 servers. Both times I tried the
    dbcc gam command – failed both times. Tried the dbcc dbreboot once, failed too. Never occurred to me to sidestep the issue by allocating log in a separate device, good idea.

    The solution in my case was to update master..sysdatabases and set status to -32768 for the affected db to bypass recovery on the next startup. Loaded dumps after the restart and was back in business. I wish there was a way to correct this without restarts, but that’s probably asking too much.

    I’ll be testing SP110 shortly. Both times I had plenty of log space when the error popped up and can’t risk it happening again in production – restarts here are not easy to get authorized.

    HTH,

    Hector

  3. Issue exists in 15.7 SP135 🙁

    Adaptive Server Enterprise/15.7/EBF 24747 SMP SP135 /P/Sun_svr4/OS 5.10/ase157sp133x/3927/64-bit/FBO/Fri Jun 5 01:27:51 2015

    00:0008:00000:00059:2016/01/31 12:24:58.63 server ERROR: Can’t get a new log page in dbid 11 (num_left=1, num_in_plc=1, num_in_plc_at_start=1, num_newlogpgs_allocated=0).
    00:0009:00000:00060:2016/01/31 12:25:02.93 server ERROR: Can’t get a new log page in dbid 11 (num_left=1, num_in_plc=1, num_in_plc_at_start=1, num_newlogpgs_allocated=0).
    00:0008:00000:00082:2016/01/31 12:25:17.14 server ERROR: Can’t get a new log page in dbid 11 (num_left=1, num_in_plc=1, num_in_plc_at_start=1, num_newlogpgs_allocated=0).
    00:0007:00000:00096:2016/01/31 12:25:17.53 server ERROR: Can’t get a new log page in dbid 11 (num_left=15, num_in_plc=15, num_in_plc_at_start=15, num_newlogpgs_allocated=0).

    Regards,

  4. Jason,

    I was able to recover using your method when bypassing the database upon recovery and restart had not helped. Thanks very much for your posting: clear, short and to the point.

Leave a Reply

Your email address will not be published. Required fields are marked *