Path: senator-bedfellow.mit.edu!dreaderd!not-for-mail
Message-ID: <databases/sybase-faq/[email protected]>
Supersedes: <databases/sybase-faq/[email protected]>
Expires: 2 Aug 2004 13:43:10 GMT
References: <databases/sybase-faq/[email protected]>
X-Last-Updated: 2003/03/02
From: [email protected] (David Owen)
Newsgroups: comp.databases.sybase,comp.answers,news.answers
Subject: Sybase FAQ: 7/19 - ASE Admin (4 of 7)
Reply-To: [email protected] (David Owen)
Followup-To: comp.databases.sybase
Distribution: world
Organization: Midsomer Consultants Inc.
Approved: [email protected]
Keywords: FAQ, DATABASES, SYBASE, ASA, ASE, REP
Originator: [email protected]
Date: 20 Apr 2004 13:45:05 GMT
Lines: 183
NNTP-Posting-Host: penguin-lust.mit.edu
X-Trace: 1082468705 senator-bedfellow.mit.edu 565 18.181.0.29
Xref: senator-bedfellow.mit.edu comp.databases.sybase:106205 comp.answers:56951 news.answers:270291

Archive-name: databases/sybase-faq/part7
URL: http://www.isug.com/Sybase_FAQ
Version: 1.7
Maintainer: David Owen
Last-modified: 2003/03/02
Posting-Frequency: posted every 3rd month
  A how-to-find-the-FAQ article is posted on the intervening months.

                           General Troubleshooting



1. How do I turn off marked suspect on my database?
2. On startup, the transaction log of a database has filled and recovery has
   suspended, what can I do?
3. Why do my page locks not get escalated to a table lock after 200 locks?

Performance and Tuning Advanced Administration ASE FAQ

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

1.4.1 How do I turn off marked suspect on my database?

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

Say one of your database is marked suspect as the SQL Server is coming up. Here
are the steps to take to unset the flag.

   Remember to fix the problem that caused the database to be marked suspect
   after switching the flag.

System 11

1. sp_configure "allow updates", 1
2. select status - 320 from sysdatabases where dbid = db_id("my_hosed_db") --
   save this value.
3. begin transaction
4. update sysdatabases set status = -32768 where dbid = db_id("my_hosed_db")
5. commit transaction
6. shutdown
7. startserver -f RUN_*
8. fix the problem that caused the database to be marked suspect
9. begin transaction
10. update sysdatabases set status = saved_value where dbid = db_id
   ("my_hosed_db")
11. commit transaction
12. sp_configure "allow updates", 0
13. reconfigure
14. shutdown
15. startserver -f RUN_*

System 10

1. sp_configure "allow updates", 1
2. reconfigure with override
3. select status - 320 from sysdatabases where dbid = db_id("my_hosed_db") -
   save this value.
4. begin transaction
5. update sysdatabases set status = -32768 where dbid = db_id("my_hosed_db")
6. commit transaction
7. shutdown
8. startserver -f RUN_*
9. fix the problem that caused the database to be marked suspect
10. begin transaction
11. update sysdatabases set status = saved_value where dbid = db_id
   ("my_hosed_db")
12. commit transaction
13. sp_configure "allow updates", 0
14. reconfigure
15. shutdown
16. startserver -f RUN_*

Pre System 10

1. sp_configure "allow updates", 1
2. reconfigure with override
3. select status - 320 from sysdatabases where dbid = db_id("my_hosed_db") -
   save this value.
4. begin transaction
5. update sysdatabases set status = -32767 where dbid = db_id("my_hosed_db")
6. commit transaction
7. you should be able to access the database for it to be cleared out. If not:
    1. shutdown
    2. startserver -f RUN_*
8. fix the problem that caused the database to be marked suspect
9. begin transaction
10. update sysdatabases set status = saved_value where dbid = db_id
   ("my_hosed_db")
11. commit transaction
12. sp_configure "allow updates", 0
13. reconfigure

Return to top

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

1.4.2 On startup, the transaction log of a database has filled and recovery has
suspended, what can I do?

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

You might find the following in the error log:

00:00000:00001:2000/01/04 07:43:42.68 server Can't allocate space for object
'syslogs' in database 'DBbad' 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 or sp_extendsegment to increase size of the
segment.
00:00000:00001:2000/01/04 07:43:42.68 server Error: 3475, Severity: 21, State:
7
00:00000:00001:2000/01/04 07:43:42.68 server There is no space available in
SYSLOGS for process 1 to log a record for which space has been reserved. This
process will retry at intervals of one minute. The internal error number is -4.

which can prevent ASE from starting properly.  A neat solution from Sean Kiely
([email protected]) of Sybase Technical Support, that works if the database
has any "data only" segments.  Obviously this method does not apply to the
master database.   The Sybase Trouble Shooting Guide has very good coverage of
recovering the master database.

1. You will have to bring the server up with trace flag 3608 to prevent the
   recovery of the user databases.
2. sp_configure "allow updates",1
   go
3. Write down the segmap entries from the sysusages table for the toasted
   database.
4. update sysusages
      set segmap = 7
   where dbid   = db_id("my_toasted_db")
      and segmap = 3
5. select status - 320
     from sysdatabases
   where dbid = db_id("my_toasted_db") -- save this value.
   go
   begin transaction
   update sysdatabases set status = -32768 where dbid = db_id("my_toasted_db")
   go -- if all is OK, then...
   commit transaction
   go
   shutdown
   go
6. Restart the server without the trace flag.  With luck it should now have
   enough space to recover.  If it doesn't, you are in deeper trouble than
   before, you do have a good, recent backup don't you?
7. dump database my_toasted_db with truncate_only
   go
8. Reset the segmap entries in sysusages to be those as saved in 3. above.
9. Shutdown ASE and restart.  (The traceflag should have gone at step 6., but
   ensure that it is not there!)

Return to top

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

1.4.3: Why do my page locks not get escalated to a table lock after 200 locks?

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

Several reasons why this may be happening.

 * Are you doing the updates from within a cursor?

   The lock promotion only happens if you are attempting to take out 200 locks
   in a single operation ie a single insert, update or delete.  If you
   continually loop over a table using a cursor, locking one row at time, the
   lock promotion never fires.   Either use an explicit mechanism to lock the
   whole table, if that is required, or remove the cursor replacing it with an
   appropriate join.

 * A single operation is failing to escalate?

   Even if you are performing a single insert, update or delete, Sybase only
   attempts to lock the whole table when the lock escalation point is
   reached.  If this attempt fails because there is another lock which
   prevents the escalation, the attempt is aborted and individual page locking
   continues.

Return to top

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

Performance and Tuning Advanced Administration ASE FAQ

You are viewing proxied material from ftp.icm.edu.pl. The copyright of proxied material belongs to its original authors. Any comments or complaints in relation to proxied material should be directed to the original authors of the content concerned. Please see the disclaimer for more details.