Tracing Driver Operation

The Microsoft SQL Server JDBC Driver supports the use of tracing (or logging) to help resolve issues and problems with the JDBC driver when it is used in your application. To enable the use of tracing, the JDBC driver uses the logging APIs in java.util.logging, which provides a set of classes for creating Logger and LogRecord objects.

Note

For the native component (sqljdbc_xa.dll) that is included with the JDBC driver, tracing is enabled by the Built-In Diagnostics (BID) framework. For information about BID, see Data Access Tracing in SQL Server.

When you develop your application, you can make calls to Logger objects, which in turn create LogRecord objects, which are then passed to Handler objects for processing. Logger and Handler objects both use logging levels, and optionally logging filters, to regulate which LogRecords are processed. When the logging operations are complete, the Handler objects can optionally use Formatter objects to publish the log information.

By default, the java.util.logging framework writes its output to a file. This output log file must have write permissions for the context under which the JDBC driver is running.

Note

For more information about using the various logging objects for program tracing, see the Java Logging APIs documentation on the Sun Microsystems Web site.

The following sections describe the logging levels and the categories that can be logged, and provide information about how to enable tracing in your application.

Logging Levels

Every log message that is created has an associated logging level. The logging level determines the importance of the log message, which is defined by the Level class in java.util.logging. Enabling logging at one level also enables logging at all higher levels. This section describes the logging levels for both public logging categories and internal logging categories. For more information about the logging categories, see the Logging Categories section in this topic.

The following table describes each of the available logging levels for public logging categories.

Name Description

SEVERE

Indicates a serious failure and is the highest level of logging. In the JDBC driver, this level is used for reporting errors and exceptions.

WARNING

Indicates a potential problem.

INFO

Provides informational messages.

CONFIG

Provides configuration messages. Note that the JDBC driver does not currently provide any configuration messages.

FINE

Provides basic tracing information including all exceptions thrown by the public methods.

FINER

Provides detailed tracing information including all public method entry and exit points with the associated parameter data types, and all public properties for public classes. In addition, input parameters, output parameters, and method return values except CLOB, BLOB, NCLOB, Reader, <stream> return value types.

FINEST

Provides highly detailed tracing information. This is the lowest level of logging.

OFF

Turns off logging.

ALL

Enables logging of all messages.

The following table describes each of the available logging levels for the internal logging categories.

Name Description

SEVERE

Indicates a serious failure and is the highest level of logging. In the JDBC driver, this level is used for reporting errors and exceptions.

WARNING

Indicates a potential problem.

INFO

Provides informational messages.

FINE

Provides tracing information including basic object creation and destruction. In addition, all exceptions thrown by the public methods.

FINER

Provides detailed tracing information including all public method entry and exit points with the associated parameter data types, and all public properties for public classes. In addition, input parameters, output parameters, and method return values except CLOB, BLOB, NCLOB, Reader, <stream> return value types.

The following logging categories existed in version 1.2 of the JDBC driver and had the FINE logging level: SQLServerConnection, SQLServerStatement, XA, and SQLServerDataSource. In the version 2.0 release, these are upgraded to the FINER level.

FINEST

Provides highly detailed tracing information. This is the lowest level of logging.

The following logging categories existed in version 1.2 of the JDBC driver and had the FINEST logging level: TDS.DATA and TDS.TOKEN. In the version 2.0 release, they retain the FINEST logging level.

OFF

Turns off logging.

ALL

Enables logging of all messages.

Logging Categories

When you create a Logger object, you must tell the object which named entity or category that you are interested in getting log information from. The JDBC driver supports the following public logging categories, which are all defined in the com.microsoft.sqlserver.jdbc driver package.

Name Description

Connection

Logs messages in the SQLServerConnection class. The applications can set the logging level as FINER.

Statement

Logs messages in the SQLServerStatement class. The applications can set the logging level as FINER.

DataSource

Logs messages in the SQLServerDataSource class. The applications can set the logging level as FINE.

ResultSet

Logs messages in the SQLServerResultSet class. The applications can set the logging level as FINER.

Driver

Logs messages in the SQLServerDriver class. The applications can set the logging level as FINER.

Starting with the Microsoft JDBC Driver version 2.0, the driver also provides the com.microsoft.sqlserver.jdbc.internals package, which includes the logging support for the following internal logging categories.

Name Description

AuthenticationJNI

Logs messages regarding the Windows integrated authentication issues.

The applications can set the logging level as FINEST and FINE.

SQLServerConnection

Logs messages in the SQLServerConnection class. The applications can set the logging level as FINE and FINER.

SQLServerDataSource

Logs messages in the SQLServerDataSource, SQLServerConnectionPoolDataSource, and SQLServerPooledConnection classes.

The applications can set the logging level as FINER.

InputStream

Logs messages regarding the following data types: java.io.InputStream, java.io.Reader and the data types, which have a max specifier such as varchar, nvarchar, and varbinary data types.

The applications can set the logging level as FINER.

SQLServerException

Logs messages in the SQLServerException class. The applications can set the logging level as FINE.

SQLServerResultSet

Logs messages in the SQLServerResultSet class. The applications can set the logging level as FINE, FINER, and FINEST.

SQLServerStatement

Logs messages in the SQLServerStatement class. The applications can set the logging level as FINE, FINER, and FINEST.

XA

Logs messages for all XA transactions in the SQLServerXADataSource class. The applications can set the logging level as FINE and FINER.

TDS.DATA

Logs messages containing the TDS protocol-level conversation between the driver and SQL Server. The detailed contents of each TDS packet sent and received are logged in ASCII and hexadecimal. The login credentials (user names and passwords) are not logged. All other data is logged.

This category creates very verbose and detailed messages, and can only be enabled by setting the logging level to FINEST.

TDS.Channel

This category traces actions of the TCP communications channel with SQL Server. The logged messages include socket opening and closing as well as reads and writes. It also traces messages related to establishing a Secure Sockets Layer (SSL) connection with SQL Server.

This category can only be enabled by setting the logging level to FINE, FINER, or FINEST.

TDS.Writer

This category traces writes to the TDS channel. Note that only the length of the writes are traced, not the contents. This category also traces issues when an attention signal is sent to the server to cancel a statement's execution.

This category can only be enabled by setting the logging level to FINEST.

TDS.Reader

This category traces certain read operations from the TDS channel at the FINEST level. At the FINEST level, tracing can be quite verbose. At WARNING and SEVERE levels, this category traces when the driver receives an invalid TDS protocol from SQL Server before the driver closes the connection.

This category can only be enabled by setting the logging level to FINER and FINEST.

TDS.Command

This category traces low-level state transitions and other information associated with executing TDS commands, such as T-SQL statement executions, ResultSet cursor fetches, commits, and so on.

This category can only be enabled by setting the logging level to FINEST.

TDS.TOKEN

This category logs only the tokens within the TDS packets, and is less verbose than the TDS.DATA category. It can only be enabled by setting the logging level to FINEST.

At the FINEST level, this category traces TDS tokens as they are processed in the response. At the SEVERE level, this category traces when an invalid TDS token is encountered.

SQLServerDatabaseMetaData

Logs messages in the SQLServerDatabaseMetaData class. The applications can set the logging level as FINE.

SQLServerResultSetMetaData

Logs messages in the SQLServerResultSetMetaData class. The applications can set the logging level as FINE.

SQLServerParameterMetaData

Logs messages in the SQLServerParameterMetaData class. The applications can set the logging level as FINE.

SQLServerBlob

Logs messages in the SQLServerBlob class. The applications can set the logging level as FINE.

SQLServerClob

Logs messages in the SQLServerClob class. The applications can set the logging level as FINE.

SQLServerSQLXML

Logs messages in the internal SQLServerSQLXML class. The applications can set the logging level as FINE.

SQLServerDriver

Logs messages in the SQLServerDriver class. The applications can set the logging level as FINE.

SQLServerNClob

Logs messages in the SQLServerNClob class. The applications can set the logging level as FINE.

Enabling Tracing Programmatically

Tracing can be enabled programmatically by creating a Logger object and indicating the category to be logged. For example, the following code shows how to enable logging for SQL statements:

Logger logger = Logger.getLogger("com.microsoft.sqlserver.jdbc.Statement");
logger.setLevel(Level.FINER);

To turn off logging in your code, use the following:

logger.setLevel(Level.OFF);

To log all available categories, use the following:

Logger logger = Logger.getLogger("com.microsoft.sqlserver.jdbc");
logger.setLevel(Level.FINE);

To disable a specific category from being logged, use the following:

Logger logger = Logger.getLogger("com.microsoft.sqlserver.jdbc.Statement");
logger.setLevel(Level.OFF);

Enabling Tracing by Using the Logging.Properties File

You can also enable tracing by using thelogging.propertiesfile, which can be found in thelibdirectory of your Java Runtime Environment (JRE) installation. This file can be used to set the default values for the loggers and handlers that will be used when tracing is enabled.

The following is an example of the settings that you can make in the logging.properties files:

   # Specify the handlers to create in the root logger
   # (all loggers are children of the root logger).
   # The following creates two handlers.
   handlers = java.util.logging.ConsoleHandler, java.util.logging.FileHandler

   # Set the default logging level for the root logger.
   .level = OFF

   # Set the default logging level for new ConsoleHandler instances.
   java.util.logging.ConsoleHandler.level = FINE

   # Set the default logging level for new FileHandler instances.
   java.util.logging.FileHandler.level = OFF

   # Set the default formatter for new ConsoleHandler instances.
   java.util.logging.ConsoleHandler.formatter = java.util.logging.SimpleFormatter

   # Set the default logging level for the logger named ConnectionPool.
   ConnectionPool.level = OFF

Note

You can set the properties in the logging.properties file by using the LogManager object that is part of java.util.logging.

See Also

Other Resources

Diagnosing Problems with the JDBC Driver