Tuesday, May 18, 2010

TDS traceflags in SQL Server

by SQL Protocols via SQL Protocols on 5/18/2010 6:40:00 AM

Microsoft SQL Server has a traceflag framework through which users can set specific server characteristics or to switch on/off a particular behavior. For details about how to use traceflags, please refer to http://msdn.microsoft.com/en-us/library/ms188396.aspx

In this blog, I will introduce two useful traceflags related to TDS, -T4052/-T4055.-T4052 can be used to print the TDS packet sent to client (i.e. output) to console.-T4055 can be used to print the TDS packet received (i.e. input) to console.Both traceflag are startup only, meaning it can only be turned on during server startup. With these two traceflags, you can see everything that flows in and out of SQL Server. The TDS protocol is published at http://msdn.microsoft.com/en-us/library/dd304523(PROT.13).aspx

In order to see things printed to console, users would need to start up SQL Server from a command window with a -c argument. However, this is not what customer usually does. SQL Server provides another  traceflag -T3605 which can be used to print console data to SQL Server errorlog file.

You can search master.mdf HKLM\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server (remove Wow6432Node if 64-bit instance) to find the location for configuring startup parameters in SQL Server. The location is under:

HKLM\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\<InstanceID>\MSSQLServer\Parameters

Add three string values under the key:

SQLArg3  --> -T4052
SQLArg4  --> -T4055
SQLArg5  --> -T3605

The number after SQLArg does not have to be 3/4/5. As long as it's unique, it's OK. Remember to update <InstanceID> with the instance ID of your own instance.

Now, restart your SQL Server, you will see all TDS data in SQL Server errorlog.

There is another way to observe data input/output of SQL Server. Customer can use Netmon parser(http://www.bing.com/search?q=microsoft+netmon+parser&form=QBRE&qs=n&sk=) to see TDS data. The benefit of TDS netmon parser is it can break down the structure of TDS packets. But, if the data channel is encrypted, Netmon parser won't help. At this time, the approach introduced here can be used. Another advantage of this approach is that it can be used to monitor local connection data easily, irrelevent to underlying protocols(TCP/NP/LPC,etc).

Xinwei Hong, SQL Server Protocols
Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights