HOWTO: Fix Sybase ASE: 17283 error when trying to extend a database segment

Did you ever receive the error 17283 “Procedure ‘sp_extendsegment’, Line 182 ‘tempdev1′ is reserved exclusively as a log device.” error? You can get the 17283 error when there is a device fragment that contains only the log segment. Part of the problem is when you extend the log segment onto a device, it will usually drop all other segments so it becomes ‘log only’.

 device_fragments               size          usage                created             free kbytes
 ------------------------------ ------------- -------------------- ------------------- ----------------
 master                                8.0 MB data and log         Dec 16 2004  4:09AM             3376
 tempdev1                           1024.0 MB data and log         Dec 16 2004  7:02AM          1044480
 tempdev1                            256.0 MB data and log         Mar  9 2005 10:49AM           261120
 tempdev1                            256.0 MB log only             Mar  9 2005 10:54AM not applicable
 tempdev1                            488.0 MB data and log         Mar  9 2005 12:10PM           497760
 tempdev1                            256.0 MB data and log         Mar 11 2005  2:03PM           261120
 tempdev1                            244.0 MB data and log         Mar 11 2005  3:09PM           248880
 tempdev1                            200.0 MB data and log         Jun  1 2005 12:33PM           204000
 tempdev1                            200.0 MB data and log         Jul  8 2005  2:55PM           203808
 tempdev1                            200.0 MB data and log         Jul 21 2005  2:54PM           204000
 tempdev1                           1000.0 MB data and log         Feb  6 2006  1:11PM          1020000

So, how to fix this? Well, Sybase doesn’t provide any way to do so without modifying the system tables.

use master
exec sp_configure "allow updates", 1
go
begin tran
go

since we are dealing with tempdb and we want data and log on all the device fragments, we can make a blanket update. We would make the update more selective if we had other requirements (update only one row or something)

update sysusages set segmap = 7 where dbid = db_id("tempdb")

if the number of rows updated exceed the number of rows for the database, issue a ‘rollback’

select segmap from sysusages where dbid = db_id("tempdb")

all good? if so, issue a ‘commit’

commit tran
go
exec sp_configure "allow updates", 0
go

Now that the system table is updated, we need to refresh the dbtable memory structure so that the changes we made become ‘live’

dbcc dbrepair(tempdb, remap)
go

That’s it! It’s fixed and running with the segmaps without having to restart ASE. Wasn’t that easy?

Share Button

One thought on “HOWTO: Fix Sybase ASE: 17283 error when trying to extend a database segment

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>