SYBASE TECHNICAL NEWS

     sp_listsuspect_db
     sp_listsuspect_page [dbname]

     sp_forceonline_db dbname
       {"sa_on" | "sa_off" | "all_users"}

     sp_forceonline_page dbname, pagenumber
       {"sa_on" | "sa_off" | "all_users"}

1. No backups are available or the backups are too old.
2. Insufficient time to restore.

Useful dbcc Commands

dbcc traceflags

dbcc traceon (trace_flag)

dbcc traceoff (trace_flag)

dbcc memusage

1. Keep Up-to-Date Backups Maintaining current backups of your data is vital for any recovery plan. Keep multiple generations of backups, and keep some offsite as an extra precaution. Make regular database dumps of:
   • the master database. To insure that your backup of master is always current, back up master after each maintenance command that affects disks, storage, databases, or segments - for example, after creating or deleting databases, initializing new devices, and creating or modifying segments.
   • the model database
   • the sybsystemprocs database
   • user databases.
2. Maintain copies of System Tables and DDL Keep the latest offline copies of the following tables:
   • sysusages
   • syslogins
   • sysloginroles
   • sysdatabases
   • sysdevices
   • syscharsets
   • sysconfigures
   • sysservers
   • sysremotelogins
   • sysresourcelimits (11.5 and later)
   • systimeranges (11.5 and later)
   Use the bcp utility to copy out these tables. In addition, maintain a hardcopy by printing the output of the following queries:

        select * from sysusages order by vstart
        select * from sysusages order by dbid, lstart
        select * from syslogins
        select * from sysloginroles
        select * from sysdatabases
        select * from sysdevices
        select * from syscharsets
        select * from sysconfigures
        select * from sysservers
        select * from sysremotelogins
        select * from sysresourcelimits (11.5 and later)
        select * from systimeranges (11.5 and later)
   

   Also maintain:
   • copies of your configuration file.
   • the first two blocks (2 pages) of the master device.
   • a copy of the config block. You can generate this using Sybase Central or Power Designer. On Unix platforms, you can obtain a copy of the config block with this command: dd if=master_device of=$SYBASE/config_block.bak bs=1024 count=8
   • all Data Definition Language (DDL) scripts you use to create user objects, specially stored procedures if you elect to use sp_hidetext (11.5 and later).
   Note: Implement all changes to schema in the same way that the installmaster script is implemented.
3. Verify Database Consistency Run dbcc checks on a regular basis to monitor the health of your databases. Database-wide checks are available with dbcc checkdb, dbcc checkalloc, and dbcc checkstorage (11.5 and higher). dbcc checkcatalog is also a useful tool. For a brief overview of dbcc commands, see "Useful dbcc Commands". Detailed information appears in the System Administration Guide.
   
   Since dbcc checks can be resource intensive, consider adopting a strategy to take advantage of object level dbcc's. On a given day run a certain number of checktable and tablealloc commands for a portion of the database. On subsequent days, run different tables. Over a period of days you can accomplish a complete check of your databases for integrity. For example if your database has 200 tables in addition to the system tables, run dbcc's on the system tables on night one, run dbcc's on each of the first 50 of the user tables on night two, the next 50 the next night and so on, until at the end of five nights you have checked every table in the database. On the sixth night you can begin the cycle again.
   
   Note: Running table-level dbcc's misses the GAM page checks.
   
   Alternative strategies include:
   
   • loading the database to another server, and running the dbcc's on that server;
     
   • dbcc checkstorage (11.5 and higher).
     
     
   Building dbcc checks into your regular backup/maintenance schedule can ensure that you have consistent, accurate backups available at all times.
   
4. Implement Mirroring Mirroring, either at the Adaptive Server level or at the operating system level, can provide nonstop recovery in the event of media failure.
   
   The factors you need to consider, and instructions on implementing Adaptive Server mirroring, are detailed in the section titled "Mirroring Database Devices" in the System Administration Guide.
   
5. Perform Ongoing Maintenance
   
   As part of a routine program of server maintenance, you should:
   • Monitor the Adaptive Server error log for errors. Note that users may not report errors of severity 17 or 18 if their work is not interrupted. Set up a routine that browses the error log, searching for errors. See "How to Monitor the Adaptive Server Error Log" for an example. For information on the error log format and severity levels, see the System Administration Guide. Note: NT users can also monitor server messages by means of the Windows NT Event Log. For details, refer to What's New in Sybase Adaptive Server Enterprise Release 11.5. Prune the error log regularly as it grows constantly since Adaptive Server appends informational messages to the log during startup. A full error log with no space to write to may cause the server to freeze. Remember to shut down the server first, and make a copy of the log before pruning. An example of log pruning on Unix follows:

               % cp errorlog errorlog.date
               % cp /dev/null errorlog
     	

     where date is the current date.
   • Monitor the operating system log to keep an eye on the health of the hardware and the server environment. Many Adaptive Server errors can be due to underlying hardware problems, and can therefore indicate hardware problems. Refer to "Checking the Operating System Error Log" in "Encyclopedia of Tasks" for information on how to locate your log and how to check it.
   • Monitor space usage with system procedures such as sp_helpsegment, sp_spaceused, and sp_helpdb. By running sp_spaceused regularly, for example, you can determine if a database is running out of space for new objects. Alternatively, you can set up thresholds to monitor free space on database segments. See "Getting Information About Database Storage" and "Creating Threshold Procedures" in the System Administration Guide for details.
   • On Versions prior to 11.9.2, update index statistics. Distribution pages hold statistics on the distribution of index key values. As a table grows and changes, these statistics become old, and the server may start to choose the wrong index strategy for queries. You can address this condition by running update statistics periodically. Version 11.9.2 and later do not utilize distribution pages; instead, they use a different mechanism for maintaining statistics. Refer to New Functionality in Adaptive Server Enterprise 11.9.2 for details.
6. Avoid Risky Practices
   • Avoid moving tempdb off the master device. When Adaptive Server is installed, tempdb resides on the master device. Although it is possible to move tempdb off the master device later for space considerations, this is not advisable. Once tempdb is moved off the master device, it is difficult and time-consuming to recover if a problem occurs on the master device or the device to which tempdb is moved.
   • Never put anything other than master, model and tempdb on the master device. Storing user databases on the master device may make it difficult to recover the system databases or user databases if either become damaged.
7. Recovery Tips, Or What to do When Things Go Wrong
   • Choose the correct recovery method. Your choice of methods will be dictated by the type of failure you encounter. For example, loss of a device will require restoring from backups.
     
     Network/machine failure usually has little impact on the server but could corrupt data in some situations, and recovery may fail.
   • If mirroring is enabled at your site, disable the mirror before loading a dump, thus preserving a copy of what you had before in case dumps are bad.
   • Never run buildmaster on the original master device. It may contain information you need later. Instead, do a buildmaster on a different device, and when your environment is completely restored, you can move back to your original master device.
8. Additional Tips After an operating system upgrade, check permissions on your sybase devices.