Skip to Content

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

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.

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • Posted on Mar 24, 2015 at 12:37 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Mar 11, 2015 at 10:08 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Mar 24, 2015 at 03:07 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.