Popularity of log problems
Author:
ALADDIN Published: 2002/12/06 03:57 PM
The following information is derived from each database of Sybase.com, whether it is a system database (Master, Model, SybsystemProcs, Tempdb), or the user database, has its own transaction log, and there is a syslogs table for each library. Log records the user's operation to the database, so if you do not need to clear it, the log will grow until the space is covered. Clear the log to use the dump transaction command; or open the database option Trunc log on chkpt, the database will automatically clear the log every other interval. Managing Database Log is the side of the user to operate the database. Let's talk about log and its management: 1. How to record and read log information, SQL Server is a mechanism for the first righter log. Server Cache Memory Middle Log Page is always written in the data page: log page is written in commit, checkpoint, space needed, is written to the hard drive. Data Pages are written to the hard drive when Checkpoint, Space Needed. The system reads the information of each Database's syslogs table in Recovery, retreating the unfinished transaction (data changes to the pre-transaction status); complete the submitted transaction (data change is a state of transaction) . Make a checkpoint point in the log. This ensures consistency and integrity of the entire database system. Second, the Transaction logs and the checkpoint process checkpoint command function is to force all "dirt" pages (the updated page after the last written database device) is written to the database device. The automatic CheckPoint interval is calculated from SQL Server calculated from the recovery interval value in system activity and system table sysconfigures. The recovery interval determines the frequency of checkpoint by specifying the total amount of time required for system recovery. If the database opens the Trunc Log on Chkpt option, SQL Server automatically clears the log when performing CheckPoint in the database system. However, the user writes the checkpoint command does not clear the log, even if the Trunc Log On Chkpt option is open. SQL Server automatically performs CheckPoint action when the Trunc Log On Chkpt option is open, can automatically clear the log. This automatic CHECKPOINT action is called the checkpoint process in SQL Server. When the Trunc log on chkpt option is open, the checkpoint process clears the log every 0 seconds, regardless of the interval of the Recovery Interval setting time. Third, the size of the Transaction log does not have a very strict and exact way to determine how big space should be given. For a newly built database, the LOG size is about 20% of the entire database size. Because log records for the changes to the database, if the modified action is frequent, the growth of logs is very rapid. Therefore, the log space is dependent on how the user uses the database. For example: Update, INSERT, and DELETE frequency Each transaction's modified SQL Server system parameter recovery interval value LOG does not save database recovery and other factors affect the log size, we should estimate the log size according to operation, The log is backed up and cleared at a period of one cycle.
Fourth, detecting the size of logs If log is on his own device, DBCC CheckTable (syslogs) has the following information: Example: *** NOTICE: SPACE USED On The log segment is 12.87MBYTES, 64.35% *** NOTICE: Space Free on The Log Segment IS 7.13MBYTES, 35.65% Decide whether to use the dump transaction command to back up and clear the log according to the LOG remaining spatial ratio. Use a quick way to judge the extent of Transaction LOG. 1> Use Database_name 2> Go 1> Select Data_PGS (8, DOA MPG) 2> from sysindexes where id = 8 3> Go Note: This Query May BE OFF BY AS MANY AS 16 PAGES. In the Syslogs mesh SP_SPACEUSED command. 5. Log devices Generally, you should store DATA and LOG of a database on different database devices. The benefits of this: You can back up (Back Up) Transaction Log Prevent the database from being able to see the usage of the log space. [DBCC CHECKTABLE (SYSLOGS)] can be mirrored LOG device six, Log's LOG's LOG is growing, and must be cleared before it is full space. As mentioned earlier, clearing the log can open the database option Trunc log on chkpt, so that the database system automatically clears the log every time interval, you can also perform the command dump transaction to clear the log.trunc log on chkpt option as the dump transaction with truncate_only command. , Just clearing the log without retaining the log to the backup device. So if you only want to clear the log without making a backup, you can use the Trunc Log On Chkpt option and Dump Transaction with TRUNCATE_ONLY, DUMP Transaction With no_log command. If you want to back up, you should do Dump Transaction Database_name to DumpDevice. Seven, managed Transactions Some operations are large quantities modified data, and log growth is very fast, such as: a lot of data modifications to delete all records of a table, sub-query-based data insertion bulk data copy, how to use these transactions make logs By full of data modifications: 1> Update Large_tab set col_1 = 0 2> Go If this table is large, this UPDATE action may make LOG, causing a 1105 error (LOG FULL) before completion, and execute this EXCLUSIVE TABLE LOC generated by large Transaction prevents other users from modifying this table during Update, which may cause a deadlock. To avoid these situations, we can divide this large transaction into several small Transactions and perform Dump Transaction action.
The above examples can be divided into two or more small transactions. For example: 1> Update large_tab set col1 = 0 2> Where col2 3> Go 1> DUMP Transaction Database_name with truncate_only 2> Go 1> Update Large_Tab Set Col1 = 0 2> Where Col2> = x 3> Go 1> Dump Transaction Database_name with truncate_only 2> Go If this Transaction requires backup to the media, no with the with truncate_only option. If Dump Transaction With Truncate_only, you should first do the dump database command. Remove all records: Example: 1> Delete Table Large_tab 2> Go, delete the entire Table record, to remember a lot of logs, we can use the TRUNCATE TABLE command to do the same function in place of the above statement. 1> Truncate Table Large_tab 2> Go This, the record is deleted, and the TRUNCATE TABLE command is used, and the log only records the space recovery, not the operation of the delete the table. Sub-based data insertion example: 1> INSERT New_TAB SELECT COL1, COL2 from Large_Tab 2> Go The same method, for this big transaction, we should process as a few small Transactions. 1> Insert new_tab 2> select col1, col2 from large_tab where col1 <= y 3> go 1> dump transaction database_name with truncate_only 2> go 1> insert new_tab 2> select col1, col2 from large_tab where col1> y 3> go 1 > DUMP DATABASE DATABASE_NAME WITH TRUNCATE_ONLY 2> Go, if you want to save log to the media, Dump Transaction does not add the with truncate_only option. If you execute dump transaction with truncate_only, you should do Dump Database action first. Batch data copies When using BCP to copy data into the database, we can turn this large transaction into several small Transactions to avoid LOG drama. Open Trunc Log On Chkpt Option 1> Use master 2> Go 1> sp_dboption database_name, trunc, true 2> Go 1> Use database_name 2> Go 1> CheckPoint 2> Go BCP ... -B 100 (on) BCP. .. / batch_size = 100 (ON VMS) Turns off the Trunc Log on Chkpt option and Dump Database. In this example, a batch executes 100 row copies.
You can also divide the BCP input file into two or more separate files, do Dump Transaction after each file is executed to avoid LOG full. If BCP uses a quick way (no index, there is no triggers), this does not remember the log, in other words, log only the spatial allocation. In this case, you must do DUMP DATABASE (for recovery database). If log is too small, you can set the trunc log on chkpt option so that the log is cleared after each checkpoint. 8. Threshold and Transaction Log Manage SQL Server provides a threshold management function that helps users automatically monitor the free space of the database log device segment. When using the Sybase Database Management System (SQL Server) development enterprise application, or developing a good database application system is actually running, the free storage space of the user database is increasingly reduced due to the continuous increase or modifying data in the database. In particular, the database log is very fast. Once the free space is exhausted, SQL Server hangs all data manipulation transactions by default, the client application stops execution. This is likely to affect the daily business processing process. Sybase SQL Server System 10 provides mechanism-threshold management automatically monitoring database free storage. When the database uses the remaining space below a certain value, the free space is controlled by performing a custom stored procedure. Take the corresponding measures before the space is used, so it is conducive to the smooth operation. If you can make full use of SQL Server's threshold management capabilities, users can automate some database management and procedure. So, here we introduce this important function of SQL Server to the reader. The threshold management of SQL Server allows the user to set the threshold and define the corresponding stored procedure for free space on a certain segment of the database. When the free space on this segment is lower than the set threshold, the SQL Server automatically runs the corresponding stored procedure. The stored procedure corresponding to the threshold is defined by the user, and SQL Server is not available. In general, database administrators can complete some daily management transactions through these stored procedures, such as backup databases, cleanup logs, and delete old data backup database logs extended database space copying tables in the table, clean up logs, and so on. (1) SEGMENT SQL Server threshold management is based on Segment, so let's take a look at the concept of a lower section. When each database is created, it has three missing segments: (1) SYSTEM segment; (2) Default segment; (3) LogSegment segment. Failure, you can add user custom segments for the database. All database objects will be built on these segments, or the system-defined segment or is the user-defined segment. The system table of the database is placed on the System segment. The built object is placed on the Default segment when there is no referring to a specified segment. The database's transaction log is placed on the LogSegment segment, which is defined by using the log on option using the Create Database command.
(2) Last_Chance Threshold By default, the SQL Server is running the sp_ thresholdAction process when the SQL Server monitors the free space of the log segment. This free space is referred to as the last chance threshold (Last_Chance Threshold), which is calculated by SQL Server and cannot be changed. Sp_threshold must be written by the user, and SQL Server is not available. In addition, if the last chance threshold is over, SQL Server has been hanging all transactions before the log space is released. However, you can use sp_dboption to change this line to a certain database. Setting the Abort TRAN ON LOG FULL Options to True, allows the last chance to override all the unregistered transactions. (3) The threshold management system is default the last chance threshold, and the user is written by the user to process the stored procedure (sp_thresholdAction) to control free space. In addition, the following stored procedure management threshold can also be used: sp_addthreshold creates a threshold sp_dropthresholD to delete a threshold sp_helpthreshold display threshold "hang or cancel" behavior and cancel threshold management sp_helpsegment display a certain segment space Size and free space size information (4) Increase the threshold (sp_addthreshold) it is used to create a threshold to monitor the use of space in the database segment. If the free space is lower than the specified value, the SQL Server runs the relevant stored procedure. Increase the threshold command syntax as: sp_addthreshold database, segment, free_pages, procedure: Database - To add a threshold database name. Must be the current database name. Segment - The free space is monitored. Use quotes when specifying "default" segments. Free_pages - The number of free space pages referred to in the threshold. When the free space in the segment is lower than the standard, SQL Server runs the stored procedure. Procedure - When the free space in segment is lower than free_pages, SQL Server executes the stored procedure. This process can be placed in any database of current SQL Server or Open Server. However, when the threshold is exceeded, the stored procedure on the remote SQL Server cannot be performed. For example: sp_addthreshold pubs2, logsegment, 200, dump_transaction wherein the stored procedure is defined as: create procedure dump_transaction @ dbname varchar (30), @ segmentname varchar (30), @ space_left int, @ status int as dump transaction @dbname to "/ dev / RMTX "So, when the space is available on the day, SQL Server runs the stored procedure dump_transaction, and the Pubs2 database is dump the log of the PUBS2 database to another. SP_ADDTHRESHOLD does not check if the stored procedure has no.
However, when the threshold is over, if the stored procedure does not exist, SQL Server sent the error message to the error log (ERRORLOG). The system allows each database to have 256 thresholds, and the minimum space between the two thresholds on the same paragraph is 128 pages. Its storage procedure can be a system stored procedure, or in other databases, or the Open Server remote process call. (5) Remove the threshold (sp_dropthreshold) it deletes the free space threshold for a certain segment, but cannot delete the last chance threshold of the log segment. The command syntax of the delete threshold is: sp_dropthreshold database_name, segment_name, free_pages, three parameters, respectively: the segment name of the free space monitored by the threshold belonging, and the free space page. For example: sp_dropthreshold pubs2, logsegment, 200 deletes thresholds 200 in the PUBS2 library. (6) Display threshold (sp_helpthreshold) It reports all thresholds related to all thresholds on the current database, free space value, status, and stored procedures or report all thresholds for a particular segment. The syntax of the display threshold is: sp_helpthreshold [segment_name] where segment_name is the name of a segment on the current database. For example, sp_helpthreshold logsegment Displays all thresholds sp_helpthreshold on the log segment SP_HELPTHRESHOLD Displays all the thresholds (7) sp_dboption on all segments on the current database Abort Xact When Log Is Full When the last chance threshold of the day segment is exceeded, try to go to this The user process on the log section will be hanging or withdrawn? By default, the system hangs all processes. But you can use sp_aboption to change it. After executing sp_dboption salesdb, "Abort Xact When Log Is Full, after the True command, once the log is full, the database modifies the transaction will be rolled back. Disable Free Space AcctG This option cancels threshold management in the database, but does not affect the last chance threshold. Execute: sp_dboption sales, "Disable Free Space Acctg", TRUE is to cancel statistics on free space on non-log segments. After cancellation, the system has the following effects: SQL Server only calculates the threshold on the free space log segment on the log segment, continues to be active on the data segment, the system table does not change, and the value obtained by sp_spaceused is the selected time. The threshold failover on the value database segment speeds up (8) The trigger process of the threshold is frequently inserted and deleted. The spatial fluctuations in the database segment may be used to transcend multiple times, SQL Server uses system variables @@ THRESH_HYSTERESIS to avoid continuous triggering Threshold stored procedure. Its value is set by SQL Server. In System 10.0, @@ THRESH_HYSTERESIS is 64 pages. As shown in the figure: Therefore, an increase of a threshold must be at least 2 * @@ thresh_hysteresis pages from the next nearest threshold.
