Sybase Error Message: Msg 1514, Level 21, State 1

[pmcmicha]

I am using the following:

SCO UNIXWARE 7.1.1
Sybase Adaptive Server: Enterprise 11.9.2

When attempting to update one of the databases, I am getting the following error message from time to time causing the script to prematurely exit. I can run the script later that day and it works without any problems.

Msg 1514, Level 21, State 1:
Server 'SYBASE', Line 1:
Sort failed because it attempted to allocate page 200712 and found that page was busy. Buffer BUF pointer = '0xbfbc2a3c', MASS pointer = '0xbfbc2a3c', (Buf#:, page ptr = '0xbdb6c800', dbid = '7', Mass virtpage = '117615624', Buffer page = '200712', Mass status = '0x44001008', Buffer status = '0x1', size = '2048', cache (id: 0) - 'default data cache' contains a cached and referenced page with objid 1646628909 indid 0 and status 0x8000.

I would appreciate any assistance with this since I do not even know where to begin.

Thanks in advance.

 

[qyllr]

hello,

check out the info from sybase just click on the links below:

http://manuals.sybase.com/onlineboo...rtsg/@Generic__BookView;pt=552?DwebQuery=1514

http://manuals.sybase.com/onlineboo...rtsg/@Generic__BookView;pt=552?DwebQuery=1514

it sounds like your are contending with another process on the same page when you are doing your update...

here are some system procedures you can run to troubleshoot when you get the error again...

sp_who - reports information about user and processes
sp_lock - will return information about processes that are locked

SQL - system table syslogshold

select * from master..syslogshold
go

you can checkout the reference manual for more info - go to chapter 7 for system procedures and chapter 11 for system tables:

http://manuals.sybase.com/onlinebooks/group-as/asg1200e/aserefmn/@Generic__BookView

hth,
q.

 

[pmcmicha]

I added some of the commands you suggested and this is what I got back since it failed again last night:

>> sp_who
fid spid status loginame origname hostname blk
dbname cmd
------ ------ ------------ ------------ ------------ ---------- -----
---------- ----------------
0 1 running ${NAME} ${NAME} ${HOST} 0
master SELECT
0 2 sleeping NULL NULL 0
master NETWORK HANDLER
0 3 sleeping NULL NULL 0
master NETWORK HANDLER
0 4 sleeping NULL NULL 0
master DEADLOCK TUNE
0 5 sleeping NULL NULL 0
master MIRROR HANDLER
0 6 sleeping NULL NULL 0
master CHECKPOINT SLEEP
0 7 sleeping NULL NULL 0
master HOUSEKEEPER


>> sp_helpdb ${DB}
name db_size owner dbid
created
status

------------------------ ------------- ------------------------ ------
--------------
------------------------------------------------------------------------
------------------------------
${DB} 850.0 MB sa 7
Aug 17, 2000
select into/bulkcopy/pllsort, trunc log on chkpt, abort tran on log full

(1 row affected)
device_fragments size usage free kbytes
------------------------------ ------------- -------------------- -----------
DATADEV_07 100.0 MB data only 3488
DATADEV_07 150.0 MB data only 32272
DATADEV_07 150.0 MB data only 4080
DATADEV_07 400.0 MB data only 237248
LOGDEV_06 50.0 MB log only 51072


>> sp_lock
The class column will display the cursor name for locks associated with a cursor
for the current user and the cursor id for other users.
fid spid locktype table_id page row
dbname class
context
------ ------ ---------------------------- ----------- ----------- ------
--------------- ------------------------------
----------------------------
0 1 Sh_intent 400004456 0 0
master Non Cursor Lock
Fam dur


>> syslogshold
dbid reserved spid page xactid masterxactid
starttime
name
------ ----------- ------ ----------- -------------- --------------
--------------------------
-------------------------------------------------------------------

(0 rows affected)


In addition to that, I also ran /usr/bin/ps -ef for all other unix processes running and I got a total of 10 processes running that involved sybase. Four of those processes were running the dataserver and backup server. Four other processes were monitoring sybase for errors on the dataserver and backup server. The other two were updating sybase. One process from other two, is a general script designed to call all the other scripts for updating sybase. And the other script is doing the actual updating.

I do not see where the running dataserver and running backup server would effect this update process and where the running monitors for the dataserver and backup server would effect this update process.

 

[qyllr]

hello,

try the following:

if you can, bounce the database server and see if the problem is fixed by refreshing the cache... sometimes the page is pointed to the wrong object_id...

if the problem persists, i would bcp out the data of the table and recreate the table... if there is corruption in the page, this is the only solution i can think of that will fix your problem...

hth,
q.

 

[JMCraig]

Definitely try running DBCC checks on the table in question. That will at least tell you if the table's corrupted.

HTH,

John J M Craig
Alpha-G Consulting, LLC

http://alphagconsulting.bizhosting.com

nsjmcraig@netscape.net