What Are Maintenance Records?

Database maintenance means that when a database is created, the work is called database maintenance. Including backing up system data, restoring the database system, generating user information tables, authorizing the information tables, monitoring the system operating status, handling system errors in a timely manner, ensuring system data security, periodically changing user passwords, database maintenance is more difficult than database creation and use .

database maintenance

The routine maintenance of the database is an important responsibility of the system administrator. Its content mainly includes the following parts:

Database maintenance backup system data

The backup and recovery mechanism of the SYBASE system guarantees the possibility of reacquiring data in the event of a system failure. SQL Server provides two different types of recovery mechanisms: one is the system's auto-completed recovery. This measure is performed automatically each time the system is started, ensuring that transactions completed before the system crashes are written to the database device. Unfinished transactions are rolled back; the other is manual recovery, which is performed by the DUMP and LOAD commands to perform manual backup and recovery work. Therefore, regularly backing up transaction logs and databases is a very important daily maintenance task.

1.Back up the database

Each database should be unloaded after creation to provide a load base. After that, it unloads according to a scheduled time table. For example, unload the database every Friday. For general database systems, it is recommended to unload the database once a week.

In addition to unloading the database on a scheduled cycle, you also need to unload the database after each operation without logs. E.g:

· DUMP TRAN WITH NO_LOG was forced to run every time (because the database's disk empty overflowed);

Use sp_dboption to allow select into / bulkcopy to do a quick copy each time, or use the SELECT INTO command to create a permanent table, or use the WRITETEXT command.

The command to unload the database is:

DUMP DATABASE database_name

TO dump_device

database_name is the name of the database to be unloaded, and dump_device is the name of the unloaded device. Use the system procedure 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 the transaction log

If the transaction log is on the same device as the database, the transaction log should not be backed up separately from the database. This is the case for master databases and user databases less than 4M. The database and logs of general database systems are placed on different devices, so you can use the DUMP TRAN command to back up the logs separately.

The period of backing up the transaction log directly affects the degree of data recovery, so it is recommended to make a daily backup.

The command format for backing 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 the database name of the transaction to be backed up, and dump_device is the name of the backup device, which can be backed up to the device only if a WITH TRUNCATE_ONLY or WITH NO_LOG clause is included.

Note: If you always use DUMP DATEBASE (back up the database and its logs) instead of DUMP TRAN, the transaction log will not be flushed and will become very large.

For master and small databases, you should run the DUMP TRANsaction command every time you run DUMP DATEBASE to flush the logs.

The following command backs up the transaction log of the database db160 to the backup device:

DUMP TRANsaction db160

TO db_log_bk_dev

WITH TRUNCATE_ONLY

3. The interaction between the backup database and its logs

It is pointless to unload the transaction log until the database has been unmounted at least once. The following figure shows the relationship between the backup database and its logs

If there is a non-hardware failure at 5:01 pm on Tuesday, all that needs to be done is to load tape 5 (see next section: Data Recovery). Since tape 5 was just backed up at 5:00 pm, only backup and load Data loss within one minute.

But what if it expires at 4:49 pm on Tuesday? In this case, tape 1 is loaded (unloaded at 5:00 PM on Friday). Then, load the tapes 2, 3, and 4 in that order. In this way, the system will return to 10:00 am on Tuesday, and most of the work on Tuesday is lost. This example shows the importance of frequently unloading transactions.

Database maintenance recovery database system

If the user database storage device fails and the database is damaged or inaccessible, the database can be restored by loading the latest database backup and subsequent transaction log backups. Assuming the current transaction log exists on a device that has not been destroyed, the DUMP TRANsaction command with the WITH NO_TRUNCATE option unloads it.

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 corrupted or inaccessible user database transaction log.

2. Use the following query to check the device usage of the device allocation destroyed database. The same space blocks must be assigned for the same purpose.

The following query shows the usage and size of the mydb device assigned to the database:

SELECT segmap, size FROM sysusages

WHERE dbid =

(SELECT dbid FROM sysdatabases WHERE name = "mydb")

3. Check the output of the query. '3' in the segmap column represents data allocation, and '4' represents log allocation. The size column represents the number of 2K data blocks. Note the order, use, and size sections of this information. For example, the output is:

segmapSize

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

310240 // The actual size is: 20M

35120 // The actual size is: 10M

45120 // The actual size is: 10M

31024 // The actual size is: 2M

42048 // The actual size is: 4M

4. Use the DROP DATABASE command to delete the database on the damaged 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 damaged device.

6. Use DISK INIT to initialize the 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. Rebuild the remaining entries with the ALTER DATABASE command. In this example, to allocate more space on datadev1, the command is:

ALTER DATABASE mydb ON datadev1 = 2 [page]

9. Reload the database with LOAD DATABASE, and then load the previously unloaded log with LOAD TRAN.

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 belong to the database owner and can be passed to other users; permissions to load databases and transactions also belong to the database owner, but cannot be passed.

Database maintenance generates user information tables

Another routine task for system maintainers is to create new information tables for users and authorize them. The method of creating a table and authorizing a table has already been described, only the relevant command syntax is written here.

The command to create the table is:

CREATE TABLE table_name

(Column_1 datatype [NULL | NOT NULL | IDENTITY],

column_2 ......

)

ALTER TABLE table_name

ADD PRIMARY KEY (column_list)

The delete command format is:

DROP TABLE table_name

The command format for table authorization is:

GRANT {ALL | permission_list}

ON table_name TO user_name

The command format for revoking permissions is

REVOKE {ALL | permission_list}

ON table_name FROM user_name

Database maintenance monitoring system health

Another day-to-day job of the system administrator is to monitor the system operation and handle system errors in a timely manner. There are the following aspects:

1.Monitor the current user and process information

Use system procedure: sp_who

Explanation: This command displays the information of all registered users and processes of the current system. The following table is the information of a certain system.

SpidStatusLoginamehostnameblkdbnamecmd

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

1RunningSascosysv0MasterSELECT

2SleepingNULL0MasterNETWORK HANDLE

3SleepingNULL0MasterDEADLOCK TUNE

4SleepingNULL0MasterMIRROR HANDLER

5SleepingNULL0MasterHOUSEKEEPER

6SleepingNULL0MasterCHECKPOINT SLEEP

From left to right: process number, current status, registered user name, host name, number of occupied blocks, database name, and current command.

If you find that the total number of processes is close to the maximum number of connections during the monitoring (using the system process: sp_configure user conn), you should drop inactive or irrelevant processes to ensure the normal operation of the system; In addition, you can monitor illegal users or users Databases that belong to their own use.

2. Monitor the space occupied by the target

Use system procedure: sp_spaceused

Explanation: This procedure displays the number of rows, the number of data pages, and the space occupied by a target or all targets in the current database. The following table is the information of a database log table:

NameRow_totalreserveddataIndex_sizeunused

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

SyslogsNot avail32KB32KB0KBNot avail

The main goals to be monitored on a daily basis are: user databases, database log tables (syslogs), and billing raw data tables. If you find that the space is too large, you need to dump the log table; for other targets, you should expand the space or clear the garbage data.

3. Monitor SQL Server statistics

Use system procedure: sp_monitor

Note: sp_monitor displays the historical statistics of SQL Server. The following table shows the statistics of a certain system:

Last_runCurrent_runSeconds

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

May 13 2000 1:27 PM May 13 2000 3:01 PM5678

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 above table gives the last time, current time, interval seconds, CPU usage, IO usage, packet sending and receiving, and system read-in and write-out information of this system in turn.

Database maintenance to ensure system data security

To ensure the security of system data, the system administrator must implement a series of security measures based on the actual situation of the system. Among them, changing the user password periodically is a relatively common and very effective measure.

Changing the user password is accomplished by calling the system procedure sp_password. The syntax of Sp_password is:

sp_password caller_password, new_password [, loginame]

Where caller_password is the login password (old password), new_password is the new password, and loginname is the login name.