SQL Transaction Logs Test

Every SQL Server database has at least two files associated with it: one data file that houses the actual data and one transaction log file. The transaction log is a fundamental component of a database management system. All changes to application data in the database are recorded serially in the transaction log. The information recorded includes the following:

  • the beginning time of each transaction
  • the actual changes made to the data and enough information to undo the modifications made during each transaction (accomplished using before and after images of the data)
  • the allocation and reallocation of database pages
  • the actual commit or rollback of each transaction

Using this information, the DBMS can track which transaction made which changes to SQL Server data. However, it is only during transaction rollbacks, commits, and database recovery operations that the transaction log serves its true purpose. When a transaction is rolled back, the SQL Server copies before images to the database for every modification made since the BEGIN TRANSACTION. During a recovery scenario you can use the transaction log to restore a database. This causes a roll forward of the transaction log. During a roll forward SQL Server will copy after images of each modification to the database. Using the logged data SQL Server ensures that each modification is applied in the same order that it originally occurred.

Lack of adequate space in the transaction log would hence have serious repercussions on the way the SQL server carries out database updations; sometimes, critical changes to the database could get lost due to a space crunch. The SQL Transaction Logs test enables administrators to constantly track the space consumption by the transaction log, so that administrators are instantly notified of inadequacies, and are prompted to act fast.

Target of the test : A Microsoft SQL server

Agent deploying the test : An internal agent

Outputs of the test : One set of results for every Microsoft SQL server database monitored

Configurable parameters for the test
  1. TEST PERIOD - How often should the test be executed
  2. Host – The IP address of the Microsoft SQL server.
  3. Port - The port number through which the Microsoft SQL server communicates. The default port is 1433.
  4. ssl – If the Microsoft SQL server being monitored is an SSL-enabled server, then set the ssl flag to Yes. If not, then set the ssl flag to No.
  5. instance - In this text box, enter the name of a specific Microsoft SQL instance that is to be monitored. The default value of this parameter is “default”. To monitor a Microsoft SQL instance named “CFS”, enter this as the value of the INSTANCE parameter.

  6. USER – If a Microsoft SQL Server 7.0/2000 is monitored, then provide the name of a SQL user with the Sysadmin role in this text box. While monitoring a Microsoft SQL Server 2005 or above, provide the name of a SQL user with all of the privileges outlined in User Privileges Required for Monitoring Microsoft SQL server.

  7. password - The password of the specified user.
  8. confirm password - Confirm the password by retyping it.
  9. domain - By default, none is displayed in the DOMAIN text box. If the ‘SQL server and Windows’ authentication has been enabled for the server being monitored, then the DOMAIN can continue to be none. On the other hand, if ‘Windows only’ authentication has been enabled, then, in the DOMAIN text box, specify the Windows domain in which the managed Microsoft SQL server exists. Also, in such a case, the USER name and PASSWORD that you provide should be that of a user authorized to access the monitored SQL server.
  10. isntlmv2 - In some Windows networks, NTLM (NT LAN Manager) may be enabled. NTLM is a suite of Microsoft security protocols that provides authentication, integrity, and confidentiality to users. NTLM version 2 (“NTLMv2”) was concocted to address the security issues present in NTLM. By default, the isntlmv2 flag is set to No, indicating that NTLMv2 is not enabled by default on the target Microsoft SQL host. Set this flag to Yes if NTLMv2 is enabled on the target host.
  11. ISPASSIVE – If the value chosen is yes, then the Microsoft SQL server under consideration is a passive server in a SQL cluster. No alerts will be generated if the server is not running. Measures will be reported as “Not applicable" by the agent if the server is not up.
Measurements made by the test
Measurement Description Measurement Unit Interpretation

Log size

Indicates the current size of the transaction log attached to this database.

MB

 

Usage of allocated space

Indicates the percentage of transaction log space that is currently in use.

Percent

Ideally, this value should be low. A high value or a value close to 100% requires immediate attention, as it indicates that the transaction log is suffering from severe space constraints. There is hence the danger of subsequent database modifications going unrecorded. Typically, excessive space usage can be attributed to too many changes been written to the log, but very little/none to the database. Further diagnosis can alone reveal the root-cause of this deviant behavior.

Is auto-growth enabled?

Indicates whether/not auto-growth is enabled for the transaction logs.

 

Turning on the autogrowth setting of the transaction logs enables the transaction log files to automatically grow by a configured percentage of the current log file size when more data space is required.

If this setting is enabled for the transaction logs, this measure will report the value Yes. If this setting is not enabled, then this measure will report the value No.

The numeric values that correspond to the above-mentioned measure values are listed in the table below:  

Measure Value Numeric Value

Yes

 100

No

 0

Note:

Typically, this measure reports the Measure Values listed in the table above as the status of the Autogrowth setting. In the graph of this measure however, the status will be represented using the numeric values – i.e., 100 and 0.

This measure is available for Microsoft SQL Server 2008 R2 and above only.

Auto-growth percent

Indicates the percentage by which the transaction log files have been configured to automatically grow when more data space is required.

Percent

The growth increment of your transaction log must be large enough to stay ahead of the needs of your transaction units. Also, the growth increment must be large enough to avoid the following performance penalities:

  1. If you run a transaction that requires more log space than is available, and you have turned on the autogrow option for the transaction log of that database, then the time it takes the transaction to complete will include the time it takes the transaction log to grow by the configured amount. If the growth increment is large or there is some other factor that causes it to take a long time, the query in which you open the transaction might fail because of a timeout error.

  2. If you run a large transaction that requires the log to grow, other transactions that require a write to the transaction log will also have to wait until the grow operation completes. 

  3. If you have many file growths in your log files, you may have an excessively large number of virtual log files (VLF). This can lead to performance problems with database startup/online operations, replication, mirroring, and change data capture (CDC). Additionally, this can sometimes cause performance problems with data modifications.

Besides considering the above factors when deciding on an autogrowth value to set, you also need to decide whether to set this value as a percentage or in MB. Typically, when you use percentage as an auto growth factor and the transaction log is smaller in size, you will probably encounter many repeated growth instances. At large file size or percentages you may encounter a timeout or long period of blocking while the file is grown.   Typically, it is recommended to set the AUTOGROW value to an optimum value in MB instead of percentages. A general rule of thumb to you can use for testing is to set your autogrow setting to about one-eight the size of the file.

These measures are available for Microsoft SQL Server 2008 R2 and above only.

Auto growth size

 

Indicates the size (in MB) by which the transaction log files have been configured to automatically grow when more data space is required.

 

MB

 

Max size of log

Indicates the maximum size to which the transaction log can grow.

MB

You need to turn on the MAXSIZE setting for each file to prevent any one file from growing to a point where it uses up all available disk space.

This measure is available for Microsoft SQL Server 2008 R2 and above only.

Usage percentage of max size

 

Indicates the percentage of maximum log file size that is currently being used.

 

Percent

 

If the value of this measure keeps growing close to 100%, it  indicates that the log is growing rapidly and may soon run out of space! In this case, do the following to make sure that the transaction log file does not become full:

  • In case of a database that has been configured with the Simple Recovery model, you need to ensure that the transaction log is automatically cleared on all checkpoints to ensure that there is adequate space to log subsequent transactions. You should also check for long-running transactions and search for possible flaws in the applications using the database.
  • In case of a database that has been configured with the Full Recovery model, check whether the transaction log backups have been defined and have succeeded. Also, verify whether the log backups occur often enough.  Here again, keep your eyes open for long-running transactions and the reasons for their longevity.

On the other hand, if the value of this measure touches 100%, it implies that the log file cannot grow more. An error 9002 is then raised. This means, that operations that change the state of the database cannot be successfully executed until sufficient free space is available in the transaction log. But, this also means that there’s no guarantee that all the necessary information was recorded into the transaction log.

The previous check list applies also this time but since the log is really full, you should also check the auto growth setting of the log file(s). Is it properly defined or should the log be let to grow more?

Regardless of the decision to let the log grow more or not, the log can be truncated in a situation like this.

This measure is available for Microsoft SQL Server 2008 R2 and above only.

Free space of max size

Indicates the amount of maximum log file size that is currently available for use.

MB

A high value is desired for this measure.

This measure is available for Microsoft SQL Server 2008 R2 and above only.

Free space percentage of max size

Indicates the percentage of maximum log file size that is currently available for use.

Percent

A sudden/gradual decrease in the value of this measure indicates that the log is growing rapidly and may soon run out of space!

This measure is available for Microsoft SQL Server 2008 R2 and above only.