home *** CD-ROM | disk | FTP | other *** search
- Path: senator-bedfellow.mit.edu!dreaderd!not-for-mail
- Message-ID: <databases/sybase-faq/part7_1082468590@rtfm.mit.edu>
- Supersedes: <databases/sybase-faq/part7_1074677126@rtfm.mit.edu>
- Expires: 2 Aug 2004 13:43:10 GMT
- References: <databases/sybase-faq/part1_1082468590@rtfm.mit.edu>
- X-Last-Updated: 2003/03/02
- From: dowen@midsomer.org (David Owen)
- Newsgroups: comp.databases.sybase,comp.answers,news.answers
- Subject: Sybase FAQ: 7/19 - ASE Admin (4 of 7)
- Reply-To: dowen@midsomer.org (David Owen)
- Followup-To: comp.databases.sybase
- Distribution: world
- Organization: Midsomer Consultants Inc.
- Approved: news-answers-request@MIT.EDU
- Keywords: FAQ, DATABASES, SYBASE, ASA, ASE, REP
- Originator: faqserv@penguin-lust.MIT.EDU
- 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
- (sean.kiely@sybase.com) 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
-
-