Tracing driver operation

Download JDBC driver

The Microsoft JDBC Driver for SQL Server supports the use of tracing (or logging) to help resolve issues and problems with the JDBC driver when it's 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 article.

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. The JDBC driver doesn't 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. Also, input parameters, output parameters, and method return values except CLOB, BLOB, NCLOB, Reader, <stream> return value types.
FINEST Provides highly detailed tracing information. This setting 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. Also, 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. Also, 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. Beginning in the version 2.0 release, these categories are upgraded to the FINER level.
FINEST Provides highly detailed tracing information. This setting 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. Beginning in the version 2.0 release, they keep 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're 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.
Resiliency Logs messages in the SQLServerConnection class relating only to idle connection resiliency reconnects. The applications can set the logging level as FINE and FINER.
Redirection Logs messages in the SQLServerConnection class relating only to connection open retries. The applications can set the logging level as FINE.

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 about the Windows-integrated authentication issues (when the authenticationScheme connection property is implicitly or explicitly set to NativeAuthentication).

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 about 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.
KerbAuthentication Logs messages about type 4 Kerberos authentication (when the authenticationScheme connection property is set to JavaKerberos). The application can set the logging level as FINE or 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) aren't 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 Transport Layer Security (TLS), previously known as 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. Only the length of the writes is 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 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 Transact-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're 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 code:

logger.setLevel(Level.OFF);

To log all available categories, use the following code:

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

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

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 the logging.properties file, which can be found in the lib directory of your Java Runtime Environment (JRE) 8 installation or the conf folder for Java 9 and higher. 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 configuration is an example of the settings that you can make in the logging.properties files:

# Specify the handler, the handlers will be installed during VM startup.
handlers= java.util.logging.FileHandler

# Default global logging level.
.level= OFF

# default file output is in user's home directory.
java.util.logging.FileHandler.pattern = %h/java%u.log
java.util.logging.FileHandler.limit = 5000000
java.util.logging.FileHandler.count = 20
java.util.logging.FileHandler.formatter = java.util.logging.SimpleFormatter
java.util.logging.FileHandler.level = FINEST

# Facility specific properties.
com.microsoft.sqlserver.jdbc.level=FINEST

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

Diagnosing problems with the JDBC driver