Sybase Database routine maintenance

Routine database maintenance is an important responsibility of the system administrator. Its content mainly includes the following parts:
I. Back up System Data
The backup and recovery mechanism of SYBASE ensures the possibility of re-retrieving data when the system fails. SQL Server provides two different types of recovery mechanisms: one is the automatic recovery of the system, which is automatically performed each time the system is started, this ensures that all transactions completed before the system crash are written to the database device, and all unfinished transactions are rolled back. The other is manual recovery, this is done through the DUMP and LOAD commands for manual backup and recovery. Therefore, regular backup of transaction logs and databases is a very important task of daily maintenance.
1. Back up the database
Each database should be detached after creation to provide a base point for loading. After that, it is detached according to the scheduled periodic table. For example, the database is detached every Friday. It is recommended that the general database system unload the database once a week.
In addition to unloading the database on a scheduled basis, you also need to unload the database after each operation without logs. For example:
· Dump tran with NO_LOG is forcibly run every time because the disk of the database overflows );
· Each time sp_dboption is used, select into/bulkcopy is allowed for fast copy, or a permanent table is created using the select into command, or the WRITETEXT command is used.
Command:
Dump database database_name
TO dump_device
Database_name is the name of the database to be detached, and dump_device is the name of the detached device. Use the system process sp_helpdevice to obtain device information.
The following command is used to unload the database my_db:
Dump database my_db
TO db_bk_dev
2. Back up transaction logs
If the transaction log and the database are on the same device, the transaction log should not be backed up separately from the database. This is the case for master databases and user databases smaller than 4 MB. Generally, the databases and logs of the database system are stored on different devices. Therefore, you can use the dump tran command to back up logs separately.
The cycle of the backup transaction log directly affects the data recovery degree. Therefore, we recommend that you back up the data every day.
The command format for backing up transaction logs is:
DUMP TRANsaction database_name
[TO dump_device]
[WITH TRUNCATE_ONLY | WITH NO_LOG | WITH NO_TRUNCATE]
Specifically, database_name is the name of the database for which the transaction is to be backed up, And dump_device is the name of the backup device. It can be backed up to the device only when it contains the WITH TRUNCATE_ONLY or WITH NO_LOG clause.
Note: If you always use dump datebase to back up the database and its logs) without using dump tran, the transaction logs will not be refreshed and become very large.
For master databases and small databases, run the DUMP TRANsaction command to refresh the log after each dump datebase operation.
The following command backs up the transaction log of database db160 to the backup device:
DUMP TRANsaction db160
TO db_log_bk_dev
WITH TRUNCATE_ONLY
3. Interaction between backup databases and logs
Before at least one database is detached, it is meaningless to unload transaction logs. Displays the relationship between the backup database and its logs.
If a non-hardware fault occurs at pm on Tuesday, all the work to be done is to mount the tape. 5 see the next section: data recovery.) Because tape 5 was backed up at pm, therefore, there is only one minute of data loss between backup and loading.
But what if it becomes invalid at on Tuesday? In this case, the tape will be loaded and unloaded at on Friday ). Then, attach the tapes 2, 3, and 4 in sequence. In this way, the system will return to the status at on Tuesday morning, and most work on Tuesday will be lost. This example shows the importance of frequent dismounting transactions.
2. Restore the database system in case of system failure
If the device stored in the user database fails, and the database is damaged or inaccessible, the database can be restored by loading the latest database backup and subsequent transaction log backup. Assume that the current TRANsaction log exists on a device that has not been destroyed, and the DUMP TRANsaction command WITH the WITH NO_TRUNCATE option is taken out.
To restore the database, follow these steps:
1. If the log exists on a separate device, use the DUMP TRANsaction command with the NO_TRUNCATE option to unload the destroyed or inaccessible user database TRANsaction logs.
2. Use the following query to check the usage of devices that have already destroyed the database. The same space block must be assigned for the same purpose.
The following query shows the usage and size of the mydb device allocated to the database:
SELECT segmap, size FROM sysusages
WHERE dbid =
SELECT dbid FROM sysdatabases WHERE name = "mydb ")
3. Check the query output. '3' in the segmap column indicates data allocation, and '4' indicates log allocation. The size column represents the number of 2 K data blocks. Note the order, usage, and size of this information. For example, the output is:
SegmapSize
--------------------
310240 // actual size: 20 M
35120 // the actual size is 10 M
45120 // the actual size is 10 M
31024 // actual size: 2 M
42048 // actual size: 4 M
4. Run the drop database Command to delete the DATABASE that destroys the device. If the system reports an error, use the DROPDB option of the dbcc dbrepair command.
5. After deleting the database, use sp_dropdevice to delete the destroyed device.
6. Use disk init to initialize a new database device.
7. Rebuild the database. Use the create database Command to copy all rows from the old sysusages table and include the first logical device.
For the above example, the command is:
Create database mydb
ON datadev1 = 20, datadev2 = 10
Log on logdev1 = 10
8. Run the alter database command to recreate other entries. In this example, allocate more space on datadev1 with the command:
Alter database mydb ON datadev1 = 2 [page]
9. Use the load database to reload the DATABASE and load tran to LOAD the logs that are previously detached.
The load database command syntax is:
Load database database_name
FROM dump_device
The syntax of the LOAD TRANsaction command is:
LOAD TRANsaction database_name
FROM dump_device
The default permissions for unloading databases and transaction logs are granted to the database owner and can be passed to other users. The permissions for loading databases and transactions are also granted to the database owner, but cannot be passed.
2. Generate a user information table and authorize it;
Another daily task of the system maintenance personnel is to create and authorize a new information table for the user. The method for creating a table and granting permissions to the table has been discussed. Here, only the relevant command syntax is written.
· The command for creating a table is:
Create table table_name
Column_1 datatype [NULL | not null | IDENTITY],
Column_2 ......
)
Go
Alter table table_name
Add primary key column_list)
Go
· The command format for deleting a table is:
Drop table table_name
Go
· The command format for table authorization is:
GRANT {ALL | permission_list}
ON table_name TO user_name
Go
· The command format for revoking permissions is
REVOKE {ALL | permission_list}
ON table_name FROM user_name
Go
3. Monitor the system running status and handle system errors in a timely manner;
Another routine task of the system administrator is to monitor the system operation. It mainly includes the following aspects:
1. Monitor the information of the current user and Process
System procedure: sp_who
Note: This command displays information about all registered users and processes in the current system. The following table lists information about a system.
SpidStatusLoginamehostnameblkdbnamecmd
---------------------------------------------------------------
1RunningSascosysv0MasterSELECT
2SleepingNULL0MasterNETWORK HANDLE
3SleepingNULL0MasterDEADLOCK TUNE
4SleepingNULL0MasterMIRROR HANDLER
5SleepingNULL0MasterHOUSEKEEPER
6SleepingNULL0MasterCHECKPOINT SLEEP
The process number, current status, registered user name, Host Name, number of occupied parts, database name, and current command are displayed from left to right.
If you find that the total number of processes is close to the maximum number of connections during monitoring, use the system process: sp_configure "user conn" to view) to check the number of idle or unrelated processes to ensure normal operation of the system; in addition, you can also monitor illegal users or users using databases that are not within the scope of your use.
2. Monitor the space occupied by the target
System Process: sp_spaceused
Note: This process displays the number of rows, number of data pages, and the space occupied by a certain target or all targets in the current database. The following table lists the information of a database log table:
NameRow_totalreserveddataIndex_sizeunused
------------------------------------------------------------
SyslogsNot avail32KB32KB0KBNot avail
The main goals of daily monitoring are user database, database log table syslogs, and original billing data table. If the occupied space is found to be too large, the log table should be dumped; otherwise, the space should be expanded or the garbage data should be clear.
3. Monitoring SQL Server statistics
System Process: sp_monitor
Note: sp_monitor displays the historical statistics of SQL Server. The following table lists the statistics of a system:
Last_runCurrent_runSeconds
---------------------------------------------------------------
May 13 2000 1: 27 PMMay 13 2000 3: 01PM5678
CPU_busyIO_busyIdle
---------------------------------------------------------------
16 (6)-0% 0 (0)-0% 5727 (5672)-99%
Packets_receivedPackets_sentPacket_errors
---------------------------------------------------------------
21 (17) 100 (97) 0 (0)
Total_readTotal_writeTotal_errorsConnections
--------------------------------------------------------
785 (366) 311 (113) 0 (0) 3 (2)
The table above provides information such as the last running time, current time, interval seconds, CPU usage, IO usage, packet sending and receiving status, and system read/write status of the system in sequence.
4. Ensure system data security and change the user password periodically;
To ensure the security of system data, the system administrator must perform a series of security measures based on the actual situation of the system. Periodically changing user passwords is a common and effective measure.
The user password is changed by calling the system process sp_password. The syntax of Sp_password is:
Sp_password caller_password, new_password [, loginame]
Here, caller_password is the old logon password), new_password is the new password, and loginame is the logon name.