Journal Issues Popular article

Issue Log Problem Universal article
Author: Aladdin Published time: 2002/12/06 03:57pm
The following information originates from sybase.com.cn
Ssybase every database in SQL Server, whether it is a system database (Master,model, Sybsystemprocs, tempdb), or a user database, has its own transaction log, each with a syslogs table. Log records the user's modifications to the database, so if the command is not cleared, the log will grow up to fill up the space. Clear Log Available DUMP TRANSACTION command, or Open database option trunc Log on chkpt, the database automatically clears log at intervals. Managing database log is one aspect that users must consider when manipulating a database.
Here are a few ways to talk about log and its management:
How SQL Server logs and reads log information
We know that SQL Server is the first log mechanism. Log pages in the Server Cache memory are always written first on the data page:
　

Log pages are written to the hard drive when a commit, checkpoint,space needed.
Data pages are written to the hard drive when Checkpoint,space needed.
The system reads information from each database's syslogs table at recovery, returns the unfinished Transaction (transaction) (data changes to the transaction state), completes the Committed transaction (transaction) (the data changes to the state after the transaction is committed). Write down the checkpoint point in the log. This ensures the consistency and integrity of the entire database system.
Ii. Transaction logs and checkpoint process
The function of the checkpoint command is to force all dirty pages (pages that have been updated since the last time they were written to the database device) to write to the database device. The automatic checkpoint interval is computed by SQL Server based on the system activity and the recovery interval (recovery interval) value in the system table sysconfigures. By specifying the total amount of time required for system recovery, the recovery interval determines the frequency of checkpoint.
If the database opens the Trunc log on chkpt option, SQL Server automatically clears the log when the database system executes checkpoint. However, the user writes the executed checkpoint command without clearing the log, even if the trunc log on chkpt option is open. SQL Server automatically clears the log only if the trunc log on chkpt option is open, and the checkpoint action is performed by the same time. The process of this automatic checkpoint action in SQL Server is called the checkpoint process. When the trunc log on chkpt option is open, the checkpoint process clears the log every 0 seconds, regardless of the recovery interval set time interval.
Three, Transaction log size
There is no very strict and precise way to determine how much space a database log should give. For a newly created database, the log size is about 20% of the size of the entire database. Log is growing very quickly because log records modify the database and if the changes are frequent. So the log space size depends on how the database is used by the user.
For example:
Frequency of Update,insert and deletes
The amount of data modified in each transaction
SQL Server system parameter recovery interval value
Log is stored on media for database recovery
There are other factors that affect the log size, and we should estimate the log size based on the operation, and then back up and clear the log at intervals of one cycle.
Four, the detection log size
If log is on its 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%
Determine whether to use the DUMP TRANSACTION command to back up and clear log based on the log remaining space ratio.
Use a quick method to determine the degree of transaction log full.
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 is off by as many pages.
Use the sp_spaceused command on the syslogs table.
Five, log equipment
In general, you should store the data and log of a database on different database devices. The benefits of doing this:
Can be backed up separately (back up) transaction log
Prevent database Overflow
You can see the log space usage. [DBCC CHECKTABLE (syslogs)]
can mirror log device
Vi. Removal of Log
The log of the database is growing and must be cleared before it fills up space. As discussed earlier, clearing log can open the database option Trunc Log on chkpt, so that the database system automatically clears log at intervals, and can also execute command DUMP TRANSACTION to clear Log.trunc log on chkpt option, like the DUMP TRANSACTION with truncate_only command, just clears the log and does not keep the log on the backup device. So if you just want to clear the log without making a backup, you can use the Trunc log on chkpt option and the DUMP TRANSACTION with TRUNCATE_ONLY,DUMP transaction and NO_LOG commands. If you want to back up, you should do DUMP TRANSACTION database_name to Dumpdevice.
Vii. Management of large transactions
Some operations are to modify data in large quantities, log speed is very fast, such as:
Large amount of data modification
Delete all records for a table
Data insertion based on subqueries
Bulk Data copy
Here's how to use these transaction to make the log not overflow:
Large amount of data modification
Cases:
1>update Large_tab Set Col_1=0
2>go
If the table is large, the update action may be full before it is completed, causing a 1105 error (log) and executing the exclusive table LOC generated by this large transaction, preventing other users from modifying the table during update , which can cause deadlocks. To avoid these situations, we can divide this large transaction into small transactions and perform the DUMP transaction action.
The above example 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 needs to be backed up to media, it is not available with the TRUNCATE_ONLY option. If you perform a dump transaction with TRUNCATE_ONLY, you should first do the DUMP DATABASE command.
Delete all records for a table:
Cases:
1>delete Table Large_tab
2>go
Similarly, delete the entire table record, to remember a lot of log, we can use the TRUNCATE TABLE command in lieu of the above statement to complete the same function.
1>truncate Table Large_tab
2>go
In this way, the records in the table are deleted, and with the TRUNCATE TABLE command, log logs only the space reclamation, not the actions that delete each row in the table.
Data insertion based on subqueries
Cases:
1>insert New_tab Select Col1,col2 from Large_tab
2>go
The same way, for this big transaction, we should deal with 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
Similarly, if you want to save log to media, the dump transaction is not followed by the WITH TRUNCATE_ONLY option. If you perform a dump transaction with TRUNCATE_ONLY, you should do the DUMP database action first.
Bulk Data copy
When using bcp to copy data into a database, we can turn this large transaction into several small transactions handles to avoid log surges.
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 (on Unix)
bcp .../batch_size=100 (on VMS)
Turn off the trunc log on chkpt option and dump the database.
In this example, a batch performs a 100-line copy. You can also split a bcp input file into two or more separate files and DUMP transaction to avoid log-full after each file is executed.
If BCP uses a Quick Method (no index, no triggers), this operation does not log, in other words, log records only space allocations. In this case, you do the DUMP database first (for recovery databases). If the log is too small, you can set the trunc log on chkpt option so that the log is cleared after each checkpoint.
Viii. Threshold and transaction log management
SQL Server provides a threshold management feature that helps users automatically monitor the free space of database log device segments.
When using Sybase database management system (SQL Server) to develop enterprise application systems, or when a well developed database application system is put into operation, the free storage space of the user database will be reduced as the user continuously increases or modifies the data in the database. Especially the database log, the growth rate is very fast. Once free space is exhausted, SQL Server suspends all data manipulation transactions by default, and the client application stops executing. This may affect the day-to-day business processes of the enterprise. Sybase SQL Server SYSTEM10 provides a mechanism for automatically monitoring database free storage space--threshold management, which controls free space by executing a custom stored procedure when the database uses the remaining space below a certain value. Before the space is used up, the corresponding measures should be taken, which will facilitate the business processing smoothly. If you can take full advantage of SQL Server's threshold management capabilities, users can automate and discipline some database management tasks. So here we introduce the important function of SQL Server to the reader.
The threshold management of SQL Server allows users to set thresholds and define corresponding stored procedures for free space on a segment of a database. SQL Server automatically runs the appropriate stored procedures when the free space on the segment is below the threshold value. Stored procedures corresponding to thresholds are defined by the user and are not provided by SQL Server. In general, database administrators can use these stored procedures to complete a number of day-to-day management transactions, such as:
Back up databases, clean logs, and delete old data
Backing Up Database logs
Extending Database Space
Copy the contents of the table, clean the log, and so on.
(i) paragraph (Segment)
The threshold management for SQL Server is based on segment (Segment), so let's review the concept of paragraph first. When each database is created, it has three missing segments: (1) system segment, (2) Default paragraph, (3) logsegment segment. Later, you can also add user-defined segments to the database. All future database objects are built on these segments, either system-defined segments or user-defined segments. The system tables for the database are stored on the systems section. In the absence of a specified paragraph, the object is set up on the default section. The transaction log for the database is placed on the logsegment segment, which is defined by using the Log on option to create the database command.
(ii) Final opportunity threshold (Last_chance Threshold)
By default, SQL Server monitors the free space of the log segment and runs the sp_ thresholdaction process when the amount of free space is less than the need for the transaction log to successfully dump. This amount of free space is called the Last chance Threshold (Last_chance threshold), which is computed by SQL Server and cannot be changed by the user.

Sp_thresholdaction must be written by the user and not provided by SQL Server. Additionally, if the last chance threshold is exceeded, SQL Server suspends all transactions until the log space is released. However, you can use sp_dboption to change this behavior for a database. Set abort tran on log the full option is true to allow SQL Server to recall all transactions that have not yet been registered when the last opportunity comes out.
(iii) Threshold management
The system defaults to setting the last chance threshold, and the user writes the default threshold processing stored procedure (sp_thresholdaction) to control the free space. In addition, you can use the following stored procedures to manage thresholds:
Sp_addthreshold Establish a threshold value
Sp_dropthreshold Deletes a threshold value
Sp_helpthreshold information about the threshold value displayed
sp_dboption "suspend or Cancel" behavior and cancel threshold management for changing threshold values
Sp_helpsegment displays information about the size of space and the size of free space on a segment
(iv) increased thresholds (Sp_addthreshold)
It is used to create thresholds (threshold) to monitor the use of space in database segments. If the free space in the segment falls below the specified value, SQL Server runs the stored procedure. The command syntax for increasing the threshold value is:
Sp_addthreshold database, segment, free_pages, procedure
which
database--the name of the database to which to add the threshold value. Must be the current database name.
segment--the segment in which its free space is monitored. Quotation marks are used when the "default" paragraph is specified.
free_pages--the number of free space pages that the threshold value refers to. When free space in a segment falls below this standard, SQL Server runs the stored procedure.
procedure--SQL Server executes the stored procedure when the free space in the segment is lower than the free_pages. This procedure can be placed in any database in the current SQL Server or open server. However, a stored procedure on a remote SQL Server cannot be executed when the threshold value is exceeded.
For example: Sp_addthreshold pubs2, Logsegment, dump_transaction
Where 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"
Then, when the free space on the log segment is less than 200 pages, SQL Server runs the stored procedure dump_transaction and dumps the log of the PUBS2 database to another setting.
Sp_addthreshold does not check whether the stored procedure exists. However, when the threshold is exceeded, if the stored procedure does not exist, SQL Server sends the error message to the error log (errorlog). The system allows 256 thresholds per database, while the minimum space between two thresholds on the same paragraph is 128 pages. Its stored procedures can be system stored procedures, but also stored procedures in other databases, or open server remote procedure calls.
(v) Deletion threshold (Sp_dropthreshold)
It deletes the free space threshold for a segment, but it cannot delete the last opportunity threshold for the log segment. The command syntax for deleting thresholds is:
Sp_dropthreshold database_name,segment_name,free_pages
Among them, three parameters are: the threshold belongs to the database name, the threshold value of the free space monitored by the segment name, and free space page. For example:
Sp_dropthreshold PUBS2, logsegment, 200
Deletes the threshold value of the logsegment segment in the PUBS2 Library 200.
(vi) Display thresholds (Sp_helpthreshold)
It reports on the current database of all thresholds associated with all threshold values, free space values, states, and stored procedures or reports of all thresholds for a particular segment. The syntax for displaying thresholds is:
Sp_helpthreshold [Segment_name]
Where Segment_name is the name of the previous segment of the current database.
For example:
Sp_helpthreshold logsegment All thresholds displayed on the log segment
Sp_helpthreshold displays all thresholds on all segments on the current database
(vii) New options for sp_dboption
Abort XACT when log are full
When the last opportunity threshold for the log segment is exceeded, will the user process that is attempting to log on to that journal segment be suspended or withdrawn? By default, all processes are suspended by the system. But you can use sp_aboption to change it. Execute sp_dboption salesdb, "abort xact when log are full", after true, the database modification transaction will be rolled back once the log is filled.
Disable free space ACCTG
This option cancels the threshold management in the database, but does not affect the last opportunity threshold. Execution: sp_dboption saledb, "Disable free spaces ACCTG", true it cancels the statistics on non log segments. After cancellation, the following effects are on the system:
SQL Server only calculates free space on the log segment
The threshold on the log segment continues to be active
On the data segment, the system table does not change, and the value that the sp_spaceused gets is the value of the time that the selection was canceled
Invalid threshold on database segment
Recovery accelerated
(eight) trigger process of threshold value
Frequent insertions and deletions may cause space fluctuations in the database segment, and thresholds may be exceeded multiple times, and SQL Server uses the system variable @ @thresh_hysteresis to avoid successive triggering of threshold stored procedures. Its value is set by SQL Server. In System 10.0, the @ @thresh_hysteresis is 64 pages.
As shown in the figure:


Therefore, add a threshold value that must be at least 2*@ @thresh_hysteresis page from the next nearest threshold.
A threshold value is triggered and requires the following conditions:
Threshold value must be reached
Threshold is active (that is, after it is established or the free space reaches the threshold minus @ @thresh_hysteresis)
Only the free space reduces the threshold to trigger, and if free space increases, it never triggers