Database daily maintenance
Author:
Moon Western Lake Published: 2002/09/20 04:01 PM
Database Daily Maintenance (Reference) Database Daily Maintenance is an important responsibility of system administrators. Its content mainly includes the following parts: 1. Backup system data Sybase system's backup and recovery mechanism guarantee the possibility of re-acquiring data when the system fails. SQL Server provides two different types of recovery mechanisms: a class is the recovery of the system automatically, which is automatically performed at each system startup, ensuring that transactions completed before the system are written on the database device. The unfinished transactions are rolled back; the other is the recovery of manual completion, which is to perform artificial backup and recovery through Dump and Load commands. Therefore, regular backup transaction logs and databases are a very important daily maintenance. 1. Each database of backup databases should be removed after creating, thereby providing an loading base point. After this, press the scheduled time periodic table. For example, the database is unloaded every Friday. Recommendation of the general database system to unload the database cycle is once a week. In addition to unloading the database as scheduled, you need to remove the database after running without a log. For example: • Each time the Dump TRAN WITH NO_LOG is run (because the database's disk space overflows); • Each time you use sp_dboption to allow SELECT INTO / BULKCOPY to make a quick copy, or create a permanent table with the Select Into command, or use The WriteText command. The command to remove the database is: DUMP DATABASE DATABASE_NAMETO DUMP_DEVICE DATABASE_NAME is the name of the database to be removed, and dump_device is the name of the unloading device. Use system procedure sp_helpdevice to get information about the device. The following command is used to remove the database my_db: dump database my_dbto db_bk_dev 2, backup transaction logs If the transaction log is placed on the same device in the same device, the transaction log shall be backed up with the database. The Master database and a user database less than 4m are this. The database and logs of the general database system are placed on different devices, so they can be backed up separately with the Dump TRAN command. The cycle of the backup transaction log directly affects the degree of recovery of the data, so it is recommended to back up every day. Command Format Back up the transaction log is: DUMP TRANsaction database_name [TO dump_device] [WITH TRUNCATE_ONLY | WITH NO_LOG | WITH NO_TRUNCATE] where database_name is to back up the database name of the transaction, dump_device is the backup device name, only if included WITH TRUNCATE_ONLY or WITH NO_LOG You can back up your device when you can back. Note: If you always use Dump DateBase, you don't have to refresh, but it becomes very large if you always use Dump Datebase. You should run the Dump Transaction command to refresh the log after each run Dump DateBase each run Dump Datebase. The following command backup database DB160 transaction log to backup devices: Dump Transaction DB160to DB_LOG_BK_DEVWITH TRUNCATE_ONLOG_BK_DEVWITH TRUNCATE_ONLY 3, the interaction between the backup database and its logs is unimplete. The figure below shows the relationship between the backup database and its logs. If you have a non-hardware failure at 5:01 pm, Tuesday, all the work that needs to be done is to load tape 5 (see the next section: Data recovery), due to tape 5 is 5 : 00 is just backup, so only data loss within one minute between backup and load.
However, what if it is fade at 4:49 pm on Tuesday? In this case, the tape 1 is loaded (11 pm on Friday afternoon). Then, the tapes 2, 3 and 4 are sequentially loaded. In this way, the system will recover to the state of 10:00 am on Tuesday, most of the work on Tuesday is lost. This example shows the importance of frequent unloading transactions. Second, if the system fails, the database system is restored if the user database stores fails, so that the database is destroyed or inexhaustible, and the database can be restored by loading the latest database backup and later transaction log backups. Assuming that the current transaction log exists on a device that is not destroyed, the dump transaction command with the with no_truncate option removes it. To restore the database, you can do it as follows: 1. If the log is on a separate device, use the Dump Transaction command with the NO_TRUNCATE Option to remove the destroyed or unveracuible user database transaction log. 2. Check the device to assign the device usage of the destroyed database with the following query. The same spatial block must be assigned to the same purpose. The following query shows the use and size assigned to the database MYDB device: select segmap, size from systemageswhere dbid = (Select DBID from sysdatabase where name = "mydb") 3, check the output of the query. The '3' represents data allocation in the segmap column, '4' represents log assignment. The size column represents the number of 2K data blocks. Note the order, use, and size of this information. For example, the output is: segmapsize ------------------------------------------------------ The actual size is: 2M42048 // The actual size is: 4m 4, deletes the database on the destroying device with the drop database command. If the system is wrong, use the DBCC DBREPAIR command for the DROPDB option. 5. After deleting the database, remove the destroyed device with sp_dropdevice. 6, initialize the new database device with Disk init. 7, rebuild the database. Copy all the rows from the old Sysusages table with the CREATE DATABASE command and contain the first logical device. For the above example, the command is: create database mydb on datav1 = 20, DATADEV2 = 10Log ON logDev1 = 10 8, with the alter database command to rebuild the remaining entrance. In this example, more space is allocated on the DATADEV1, the command is: ALTER DATABASE MYDB ON DATADEV1 = 2 9, reload the database with Load Database, and then load the previously detached log. The Load Database Command Syntax is: Load Database Database_nameFrom Dump_Device Load Transaction Command Syntax is: Load Transaction Database_nameFrom Dump_Device Default Permissions to Diffuses and Transaction Logs belong to the database owner, and can be passed to other users; loaded databases and transactions Returning to the database owner, but cannot be passed. Second, generate user information tables and authorize the information sheet; another daily transaction of the system maintenance personnel is to create a new information table for the user, and authorize.
The method of creating a table and authorization for the table is already taking, here only writing the command syntax. · Command to create a table to: CREATE TABLE table_name (column_1 datatype [NULL | NOT NULL | IDENTITY], column_2 ......) command to go ALTER TABLE table_nameADD PRIMARY KEY (column_list) go · Delete table format: DROP TABLE table_namego · Table The authorized command format is: grant {all | permission_list} on table_name to user_namego • Recital permission command format is revoke {all | permission_list} on table_name from user_namego III, monitor system health, timely handling system error; system administrator A daily work is to monitor system operations. There are mainly the following aspects: 1. Monitor the current user and the information using the system process: sp_who Description: This command displays all registered users and process information of the current system, as the following table is a system information. SpidStatusLoginamehostNameBlkdbnamecmd ----------------------------------- -------------- 1RunningSascosysv0MasterSELECT2SleepingNULL0MasterNETWORK HANDLE3SleepingNULL0MasterDEADLOCK TUNE4SleepingNULL0MasterMIRROR HANDLER5SleepingNULL0MasterHOUSEKEEPER6SleepingNULL0MasterCHECKPOINT SLEEP display from left to right: process ID, current state, registered user name, host name, the number of occupied blocks, the database name and the current command. If the total number of processes is close to the maximum connection when monitoring (using system procedure: sp_configure "user conn), it should be inactive or unrelated to ensure the normal operation of the system; it can also monitor illegal users or users. A database that belongs to the scope of yourself. 2. Monitor the target occupancy Space Space Use System Process: sp_spaceused Description: This process displays the number of lines, data pages, and the space occupied by a target or all targets in the current database. As with the following table, information on a database log table: Namerow_totalReServedDataIndex_sizeunused ----------------------------------------- --------------------- SyslogsNot Avail32kb32kb0kbnot Avail 32kb32kb0kbnot avil32kb32kb0kbnot Avail's main goals to be monitored include: user database, database log table (syslogs), and billing original data tables. If the occupancy is too large, the log table should be dumped; the space or clear spam should be expanded to other targets.
