Tracing Driver Operation
Collapse the table of content
Expand the table of content

Tracing Driver Operation

 

DownloadDownload 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 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.

System_CAPS_ICON_note.jpg 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.

System_CAPS_ICON_note.jpg 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.

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.

NameDescription
SEVEREIndicates a serious failure and is the highest level of logging. In the JDBC driver, this level is used for reporting errors and exceptions.
WARNINGIndicates a potential problem.
INFOProvides informational messages.
CONFIGProvides configuration messages. Note that the JDBC driver does not currently provide any configuration messages.
FINEProvides basic tracing information including all exceptions thrown by the public methods.
FINERProvides 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.
FINESTProvides highly detailed tracing information. This is the lowest level of logging.
OFFTurns off logging.
ALLEnables logging of all messages.

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

NameDescription
SEVEREIndicates a serious failure and is the highest level of logging. In the JDBC driver, this level is used for reporting errors and exceptions.
WARNINGIndicates a potential problem.
INFOProvides informational messages.
FINEProvides tracing information including basic object creation and destruction. In addition, all exceptions thrown by the public methods.
FINERProvides 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. Beginning in the version 2.0 release, these are upgraded to the FINER level.
FINESTProvides 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. Beginning in the version 2.0 release, they retain the FINEST logging level.
OFFTurns off logging.
ALLEnables logging of all messages.

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.

NameDescription
ConnectionLogs messages in the SQLServerConnection class. The applications can set the logging level as FINER.
StatementLogs messages in the SQLServerStatement class. The applications can set the logging level as FINER.
DataSourceLogs messages in the SQLServerDataSource class. The applications can set the logging level as FINE.
ResultSetLogs messages in the SQLServerResultSet class. The applications can set the logging level as FINER.
DriverLogs 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.

NameDescription
AuthenticationJNILogs messages regarding 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.
SQLServerConnectionLogs messages in the SQLServerConnection class. The applications can set the logging level as FINE and FINER.
SQLServerDataSourceLogs messages in the SQLServerDataSource, SQLServerConnectionPoolDataSource, and SQLServerPooledConnection classes.

The applications can set the logging level as FINER.
InputStreamLogs 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.
SQLServerExceptionLogs messages in the SQLServerException class. The applications can set the logging level as FINE.
SQLServerResultSetLogs messages in the SQLServerResultSet class. The applications can set the logging level as FINE, FINER, and FINEST.
SQLServerStatementLogs messages in the SQLServerStatement class. The applications can set the logging level as FINE, FINER, and FINEST.
XALogs messages for all XA transactions in the SQLServerXADataSource class. The applications can set the logging level as FINE and FINER.
KerbAuthenticationLogs messages regarding 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.DATALogs 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.ChannelThis 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.WriterThis 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.ReaderThis 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.CommandThis 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.TOKENThis 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.
SQLServerDatabaseMetaDataLogs messages in the SQLServerDatabaseMetaData class. The applications can set the logging level as FINE.
SQLServerResultSetMetaDataLogs messages in the SQLServerResultSetMetaData class. The applications can set the logging level as FINE.
SQLServerParameterMetaDataLogs messages in the SQLServerParameterMetaData class. The applications can set the logging level as FINE.
SQLServerBlobLogs messages in the SQLServerBlob class. The applications can set the logging level as FINE.
SQLServerClobLogs messages in the SQLServerClob class. The applications can set the logging level as FINE.
SQLServerSQLXMLLogs messages in the internal SQLServerSQLXML class. The applications can set the logging level as FINE.
SQLServerDriverLogs messages in the SQLServerDriver class. The applications can set the logging level as FINE.
SQLServerNClobLogs messages in the SQLServerNClob class. The applications can set the logging level as FINE.

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);  

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) 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 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  
  

System_CAPS_ICON_note.jpg Note


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

Diagnosing Problems with the JDBC Driver

Community Additions

ADD
Show:
© 2016 Microsoft