| Note: In April this year, I had a data migration from sqlserver2000 to sqlserver2005, and sqlserver2005 was also a dual-active failover cluster built on the Windows2003 MSC cluster. It took several days to build an environment, I thought I was very familiar with sqlserver, But I did little research on sqlserver's principles at the time. Later I had a maintenance project called sqlserver2000. The customer said that their transaction logs had exploded the hard disk, after asking me how to handle the problem, I carefully studied the Log Mechanism of sqlserver and the Backup recovery principle. I found that it is similar to Oracle and has different features, the following is a collection and summary of some knowledge, in case of unexpected needs. After all, there are fewer opportunities to engage in sqlserver at work... 1. Transaction Log (transaction logs) Transaction logs are records of database changes. They can record any operations on the database and save the record results in an independent file. Transaction logs are fully recorded for every transaction process. Based on these records, data files can be restored to the pre-transaction status. Starting from the transaction action, the transaction log is recorded, and any operations on the database during the transaction are within the record range until the user clicks submit or back to complete the record. Each database has at least one transaction log and one data file. For performance considerations, SQL Server saves user changes to the cache. These changes are immediately written into the transaction log, but are not immediately written into the data file. The transaction log uses a tag to determine whether a transaction has written cached data to a data file. After the SQL server restarts, it will view the latest mark point in the log and erase the transaction record after the mark point, because these transaction records do not actually write data in the cache into the data file. This prevents the interrupted transactions from modifying data files. (Annotation, similar to Oracle's redo log file, redo log buffer, instance recover mechanism) 2. Log interception and Compression In addition, the old log is marked as inactive, and then the database overwrites the new log to the location of the old log, which can prevent the transaction log from expanding. The interception can occur when a log file is backed up. For example, the backup log databasename with no_log interception can overwrite the old log space, but does not reduce the disk space occupied by the log, therefore, logs are sometimes compressed to free up space, The compression process is to completely delete old logs and release space. 3. Restore Model 3.1) Simple Recovery Model This model can be used to restore the database to the real-time point of the last backup, but cannot be restored to the fault point. (Note: This is similar to the non-archive mode of Oracle) 3.2) full recovery model full The full recovery model provides maximum restoration flexibility. This recovery model is used by default for new databases. Using this model, you can restore part of the database or completely recover it. Assume that the transaction log has not been destroyed, and the last committed transaction can be restored before the failure. Among all the restoration models, this model uses the largest transaction record space and slightly affects the performance of SQL Server.
3.3) bulk_logged The bulk_logged recovery model has fewer recovery options than the full model, but bulk operation does not seriously affect performance. When performing some batch operations, because it only needs to record the results of the operation, it uses less record space. However, with this model, you cannot restore a specific mark in the database or just a part of the database. 4. Back up the database 4.1) Backup command example Backup database pubs to disk = "C: \ pubs. Bak" Backup log pubs with no_log;
Backup log northwind to logbackupdevice with no_truncate4.2) backup techniques: Back up the master database once a week. Back up the MSDB database once a day. Use the SQL Server Agent to schedule your backup work. 5. Processing Method When transaction logs blow up the hard disk 5.1) method 1 (When the business is not busy) : A) use the Enterprise Manager to completely back up the database
B) Use the query analyzer to execute commands
Dump transaction database name with no_log
C) Open the Enterprise Manager and right-click the database you want to compress -- all tasks -- contract the database -- contract the file -- select the log file -- select to contract to xxm in the contraction mode, here we will provide a minimum number of M that can be reduced. Enter this number directly and click OK.
D. Back up the database completely again.5.2) method 2 (When the business is not busy) : Use tool: prosqllogclear.exe clears logs and backs up the database before and after use. 5.3) after clearing the logs, you can tolerate the loss of a database with a small amount of data, you can make the recovery model simple and then perform full backup every day. The procedure is as follows: a) Right-click the database Properties window -- options -- fault recovery model -- set to simple B) Right-click the database Properties window -- options -- settings -- select "auto-contract" C) make a maintenance plan, regularly back up data every day, and customize a backup retained for several days |
|
