Error: 1105, Severity: 17, State: 4?

[adarsh_jainer]

Hi mates,

I am getting this error today in solman.I had got a suggestion to increase the mis log space which i did yesterday and it was fine.But today again the same error occured and i again increased the log space but still the same error.Please check the details provided below and help me in solving the problem.

Error:

Error: 1105, Severity: 17, State: 4

00:0003:00000:00054:2015/03/10 12:20:29.43 server  Can't allocate space for object 'syslogs' in database 'MIS' because 'logsegment' segment is full/has no free extents. If you ran out of space in syslogs, dump the transaction log. Otherwise, use ALTER DATABASE to increase the size of the segment.

Db details:

Microsoft Windows [Version 6.2.9200]

(c) 2012 Microsoft Corporation. All rights reserved.

C:\Users\misadm>d:

D:\>cd sybase

D:\sybase>cd mis

D:\sybase\MIS>cd ocs-15_0

D:\sybase\MIS\OCS-15_0>isql -Usapsa -SMIS -X

Password:

1> use master

2> go

1> sp_helpdevice

2> go

device_name

         physical_name

         description

         status                   cntrltype

         vdevno                   vpn_low

         vpn_high

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

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

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

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

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

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

MIS_data_001

         D:\sybase\MIS\sapdata_1\MIS_data_001.dat

         unknown device type, special, dsync off, directio on, physical disk, 14

         4584.00 MB, Free: 0.00 MB

              2                           0

              5                         0

         74027007

MIS_log_001

         D:\sybase\MIS\saplog_1\MIS_log_001.dat

         unknown device type, special, dsync off, directio on, physical disk, 14

         386.00 MB, Free: 2.00 MB

              2                           0

              6                         0

          7365631

master

         D:\sybase\MIS\sybsystem\master.dat

         file system device, special, dsync on, directio off, default disk, phys

         ical disk, 400.00 MB, Free: 80.00 MB

              3                           0

              0                         0

           204799

saptempdb_data_001

         D:\sybase\MIS\saptemp\saptempdb_data_001.dat

         file system device, special, dsync off, directio on, physical disk, 204

         8.00 MB, Free: 0.00 MB

              2                           0

              9                         0

          1048575

saptools_data_001

         D:\sybase\MIS\sapdiag\saptools_data_001.dat

         file system device, special, dsync off, directio on, physical disk, 404

         8.00 MB, Free: 500.00 MB

              2                           0

              7                         0

          2072575

saptools_log_001

         D:\sybase\MIS\sapdiag\saptools_log_001.dat

         file system device, special, dsync off, directio on, physical disk, 204

         .80 MB, Free: 0.80 MB

              2                           0

              8                         0

           104856

sybmgmtdev

         D:\sybase\MIS\sybsystem\sybmgmtdb.dat

         file system device, special, dsync off, directio on, physical disk, 350

         .00 MB, Free: 54.00 MB

              2                           0

              4                         0

           179199

sysprocsdev

         D:\sybase\MIS\sybsystem\sysprocs.dat

         file system device, special, dsync off, directio on, physical disk, 200

         .00 MB, Free: 0.00 MB

              2                           0

              1                         0

           102399

systemdbdev

         D:\sybase\MIS\sybsystem\sybsysdb.dat

         file system device, special, dsync off, directio on, physical disk, 24.

         00 MB, Free: 0.00 MB

              2                           0

              3                         0

            12287

tapedump1

         \\.\TAPE0

         unknown device type, disk, dump device

             16                           2

              0                         0

            20000

tapedump2

         \\.\TAPE1

         unknown device type, tape,        625 MB, dump device

             16                           3

              0                         0

            20000

tempdbdev

         D:\sybase\MIS\sybtemp\tempdbdev.dat

         file system device, special, dsync off, directio off, physical disk, 10

         24.00 MB, Free: 0.00 MB

              2                           0

              2                         0

           524287

(12 rows affected)

(return status = 0)

1>

and our router is located in solman and we are also getting OSS Connection error.Please help.

[former_member187136]

Adarsh,

I think you already fixed this issue as I explained the other day. Kindly close this thread to avoid the redundant data flow 🙂

Also for your understanding I posted the causes for the space issues and log file full.

Let me know if you have any queries on my writeup so that I can explain you accordingly.

Regards

Kiran K Adharapuram

[amitkumar01]

Hi Adarsh,

As I can see from the output:

MIS_data_001

         D:\sybase\MIS\sapdata_1\MIS_data_001.dat

         unknown device type, special, dsync off, directio on, physical disk, 14

         4584.00 MB, Free: 0.00 MB

Your  data device of the database  is full, 

You can try creating a new data device and add it to your database MIS by using Alter Database command and check if it helps to solve you issue.

You can do this by 2 ways:

1) Through DBA Cockpit

2)ISQL session

if you have any query do let me know.

Thanks & Regards,

Amit Kumar Singh

[former_member187136]

Adarsh,

As already said, try increasing the "MIS_log_001" log device, however you can restart the database server once to release the un-commited transactions and can monitor closely.

Also please refer to blog which my colleague posted to check the transactions which fills the log space:

===========

My transaction log filled, which process is responsible for filling up the log?

A: There are several different possible interpretations of "process which filled up the log" - you can have the oldest open transaction preventing the log from being truncated, even if that transaction itself has generated few log records or is just the replication LTM marker, or you can have a process that has generating a lot of activity in one big transaction, or you can have a process that has generated a lot of small transactions.  There is also the spid that used up the very last of the available space and hit the first 1105 error.

Generally, after the log is truncated a time or two, the first two cases merge - the open big transaction will also be the oldest open transaction. By itself, a session generating a large number of small transactions shouldn't cause the log to completely fill as long as some process is being used to truncate the log, though it would lead to large transaction log dump files.

Determining log usage by spid depends on exactly what you want to measure (number of records, sum of the size of all records, etc.) Note that space can be used both directly (actual size of the log record) and indirectly.  For example, on a 2k page size server, a transaction inserting a batch of 1100 byte rows will use a 2K log page for each inserted row as only one insert record that size will fit on each row.

Some possible approaches to answering the question:

1) Oldest open transaction

This is the transaction that is preventing the log from being truncated.  It may not have used much log space directly, but as everything behind it in the log cannot be deallocated, it can be said to be responsible for using up all that space (or at least the space up to the next oldest open transaction).

SELECT * FROM master..syslogshold

2) monProcessActivity summary

From within ASE, you can get a rough feel for how many bytes a SPID has written to the log from monProcessActivity.  (ULC = User Log Cache, the spid accumulates log records in the ULC and flushes them to syslogs in batches).

SELECT TOP 5
     SPID,
     ULCBytesWritten,
     Transactions,
     Commits,
     Rollbacks
FROM
     master..monProcessActivity
ORDER BY ULCBytesWritten DESC

However, not all logged activity registers in ULCBytesWritten, and the number of transactions, commits, and rollbacks tells you little about the size of the individual transactions or which database they occurred in.  The values are also for the life of the spid and so include activities that may have been truncated from the log a long time ago.  So this is most useful if you only have one main database in use on the server, and clients that don't stay connected for long.

3) Transaction with the most records

You may actually be more interested in simply identifying who is running the largest transactions in syslogs and what those transactions are. This is a considerably easier problem.

The syslogs table only exposes two columns to the user, even though each log record actually contains much more. xactid is the session id with the two fields (page (int),row (smallint)), in hex, concatenated. The session id identifies the syslogs page and row containing the BEGINXACT log record for the transaction and appears in all the log records for that transaction.  The following query identifies the transactions with the largest number of log records:

SELECT TOP 5 -- '5' is arbitrary, just used to limit output
     xactid,
     convert(int,substring(xactid,1,4)) as "logpage",
     convert(smallint,substring(xactid,5,2)) as "logrow",
     count(*) as "records_in_xact"
FROM
     syslogs
GROUP BY
     xactid
ORDER BY
     count(*) desc

  xactid         logpage     logrow records_in_xact
-------------- ----------- ------ ---------------
0x00001962000b        6498     11            9951
0x000019410010        6465     16              38
0x000019440009        6468      9              37
0x0000195d0002        6493      2              34
0x000019610003        6497      3              28

(5 rows affected)

A slight variation on the query gives you just the transactions that are currently still open:

SELECT TOP 5
      xactid,
      count(*) as "records_in_xact"
FROM      syslogs
GROUP BY  xactid
HAVING
xactid NOT IN ( SELECT xactid FROM  syslogs
    WHERE op = 17 /*checkpoint is atomic, no commit*/
    OR op = 30 /*commit tran */ 
)
ORDER BY count(*)

You can now plug the session id (page,row) values into dbcc log to get the BEGINXACT log record for the transaction which will give you the uid of the user, the spid, the transaction name and when it started.

dbcc log(dbid, 1, <session page>, <session row>, 1,0)

LOG SCAN DEFINITION:
Database id : 2
Forward scan: starting at beginning of log
Log records for session id 935,21,0
Log operation type BEGINXACT (0)
maximum of 1 log records.

LOG RECORDS:
BEGINXACT (935,21) sessionid=935,21,0
attcnt=1 rno=21 op=0 padlen=1 sessionid=935,21,0 len=76
odc_stat=0x0000 (0x0000)
loh_status: 0x0 (0x00000000)
masterxsid=(invalid sessionid)
xstat=XBEG_ENDXACT,
spid=19 suid=1 uid=1 masterdbid=0 dtmcord=0
name=$user_transaction time=Nov 16 2010 10:42:19:910AM

Total number of log records 1
DBCC execution completed. If DBCC printed error messages, contact a user
with
System Administrator (SA) role.

Note:  You can easily generate the DBCC command by adding the following as a column in the select list:

"dbcc log( "
  + db_name()
  + ",1, "
  + str(convert(int,xactid),10,0)
  + ", "
  + str(convert(smallint, substring(xactid,5,2)),10,0)
  + ", 1, 0)" as "dbcc command"

4) DBCC LOG

DBCC LOG can provide far more information, but is a less convenient brute force method.

Given a spid <spid>, you can get the beginxact log records (and thus the session ids) for every transaction that spid has started.

set switch on 3604  -- sends dbcc output to client
go
dbcc log(<dbid>, -<spid>, -3,0)
go

You can get the BEGINXACT log records for all spids with

dbcc log(<dbid>, 0, 0,0, <records>,0)

Passing a positive number for <records> will give you the that many of the oldest BEGINXACTS; passing a negative number will give you the most recent records.

For each of those session ids, you can dump all the log records for that session with

dbcc log(<dbid>, 1, <session page>, <session row>)

Save that output to a file and use grep/awk/perl to extract the value for the "len" field of each record and sum those up. The result will be the total amount of log space on disk directly used by the log records for that spid.  It does not account for any share of the unused space on log pages holding these records.

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

Regards

Kiran K Adharapuram