cancel
Showing results for 
Search instead for 
Did you mean: 

Error 12205, Severity: 17, State: 2

DilipVoora
Participant
0 Kudos

Hi All,

One of our scheduled 'REORG DEFRAG' job is getting failed everyday with the error 12205 showed below highlighted in Red.

00:0007:00000:00099:2016/09/13 00:05:14.26 server  Beginning REORG REBUILD of index 'DBH_SNAP_INDEXES~1' on table 'DBH_SNAP_INDEXES'.

00:0006:00000:00099:2016/09/13 00:05:16.69 server  REORG REBUILD of index 'DBH_SNAP_INDEXES~1' on table 'DBH_SNAP_INDEXES' completed.

00:0006:00000:00099:2016/09/13 00:05:16.70 server  Beginning REORG DEFRAG of 'DBH_SNAP_INDEXES' partition 'DBH_SNAP_INDEXES_60524218'.

00:0007:00000:00099:2016/09/13 00:38:48.63 server  Error: 12205, Severity: 17, State: 2

00:0007:00000:00099:2016/09/13 00:38:48.63 server  Could not acquire a lock within the specified wait period. SERVER level wait period=2000 seconds, spid=99, lock type=shared table, dbid=5, objid=60524218, pageno=0, rowno=0. Aborting the transaction.

We have checked the lock wait period at server level ( using sp_configure) as well as session level(from dbacockpit-databasename-Diagnostics) and found the period is of 2000 mS and now to fix this in response to the Sybase documentation we want to increase the value of the parameter 'lock wait period' at session level(using set lock wait <time in mS(say 5000)>. At this situation few questions are running in my mind and are specified below.

1.Can we inquire the configured value of lock wait period of a session level from isql before using  - set lock wait 5000 ?

2.As the error the clearly telling the 'SERVER level wait period' should we touch the same by increasing it to 5000 from 2000 using - sp_configure by leaving away the session level (as it will be lesser than the new value at server level and the server level will take the precedence) ?

3.Is there any relation between this error 12205 and the subsequent Signal 11 errors occurred after this job failure?

Attached the error log details for your reference. Please help me with a solution to fix this at the earliest.

System Details:

Adaptive Server Enterprise/16.0 SP02 PL01/EBF 25184 SMP/P/x86_64/Enterprise Linux/ase160sp02pl00/2424/64-bit/FBO/Thu Sep 17 22:46:42 2015

Regards,

Dilip Voora

DilipVoora
Participant
0 Kudos

Thanks Mark for your response.

I have triggered reorg defrag manually on the table specified earlier after changing the session level lock wait period using 'set lock wait 5000' it got completed successfully.

Now when I trying to inquire the current setting withg @@lock_timeout the output is below. Is it correct Mark?

1> select @@lock_timeout

2> go

-----------

           0

(1 row affected)

1>

Mark_A_Parsons
Contributor
0 Kudos

Did you run set lock wait and select @@lock_timeout in the *same* isql session?

I get the following in a couple different ASE versions (ASE 15.7 SP136 ; ASE 16.0 SP02 PL03):

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

-- set lock wait not issued, yet:

select @@lock_timeout

go

-----------

         -1

set lock wait 5000

go


-- after issuing set lock wait:


select @@lock_timeout

go

-----------

       5000

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

DilipVoora
Participant
0 Kudos

I have fired the command in different isql session as you predicted it correctly. Now I am able to see the output.

1> select @@lock_timeout

2> go

-----------

     5000

(1 row affected)

Regards,

Dilip Voora

Accepted Solutions (0)

Answers (1)

Answers (1)

amitkumar01
Explorer
0 Kudos

Dear Dilip,

You can check the lock holding transaction through the below commnad:

sp_lock

go

and then you can kill that transaction to release the lock.

Also before killing it you can run the dbcc checktable and tablealloc on those table

and see if the issue disappears.

Thanks & Regards,

Amit Kumar Singh