Sometimes we in support need you to take a BID (Built In Diagnostics) or ETW (Event Tracing for Windows) trace in order to troubleshoot a problem.

 

There are a few documents out there on how to create these, but they are somewhat verbose for the purpose of just creating the .etl file.

So this is a short version on how to create a BID/ETW trace that you can send to support.

 

.1 Create a directory called C:\BidTrace.

.2 Start a command prompt (as administrator) and navigate to C:\BidTrace.

.3 Hook up the tracing dll via the registry. This is done by running the following from the console:

 

reg add HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\BidInterface\Loader /v ":Path" /t REG_SZ /d msdadiag.dll

 

.4 Start the trace for the provider that we are interested in, for example, this is for SQL Server Native Client 10.0

 

logman start ErrorTrace -p {AB6D5EEB-0132-74AB-C5F5-B23E1644DADA} 0xFFFFFFFF -ct perf -o Out.etl -ets

 

. 5 Reproduce the error that we are interested of capturing a trace for.

 

. 6 Stop the trace:

 

logman stop ErrorTrace –ets

 

.7 Remove the tracing .dll from the registry:

 

reg delete HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\BidInterface\Loader /v ":Path" /f

 

That is it. You should now have a file called Out.etl in the C:\BidTrace directory. This is the one we want.

 

Some notes.

In the example above, we will only trace for sqlncli10.dll (SQL Server Native Client 10.0), if we want to trace on something else, then

replace the GUID ({A9377239-477A-DD22-6E21-75912A95FD08}) in the example above with the GUID for the provider we are interested in. Some other GUIDs are:

 

{914ABDE2-171E-C600-3348-C514171DE148}  0x00000000  0   System.Data.1

{A68D8BB7-4F92-9A7A-D50B-CEC0F44C4808}  0xFFFFFFFF  0   System.Data.Entity.1

{C9996FA5-C06F-F20C-8A20-69B3BA392315}  0xFFFFFFFF  0   System.Data.SNI.1

{DCD90923-4953-20C2-8708-01976FB15287}  0x00000000  0   System.Data.OracleClient.1

{BD568F20-FCCD-B948-054E-DB3421115D61}  0x00000007  0   DBNETLIB.1

{F34765F6-A1BE-4B9D-1400-B8A12921F704}  0x00000007  0   ODBC.1

{0DD082C4-66F2-271F-74BA-2BF1F9F65C66}  0x00000007  0   OLEDB.1

{C5BFFE2E-9D87-D568-A09E-08FC83D0C7C2}  0x00000007  0   SQLOLEDB.1

{4B647745-F438-0A42-F870-5DBD29949C99}  0x00000007  0   SQLSRV32.1

{FC9F92E6-D521-9C9A-1D8C-D8980B9978A9}  0xFFFFFFFF  0   SQLBROWSER.1

{BA798F36-2325-EC5B-ECF8-76958A2AF9B5}  0xFFFFFFFF  0   SQLNCLI.1

{AB6D5EEB-0132-74AB-C5F5-B23E1644DADA}  0xFFFFFFFF  0   SQLSERVER.SNI.1

{A9377239-477A-DD22-6E21-75912A95FD08}  0xFFFFFFFF  0   SQLNCLI10.1

{48D59D84-105B-00FA-6B49-03462F696737}  0xFFFFFFFF  0   SQLSERVER.SNI10.1

 

You could also add the GUIDs into a file and call it, for example, ctrl.guid.all, save it into the C:\BidTrace directory.

You then have to replace the command line in step 4 with the following:

 

logman start ErrorTrace -pf ctrl.guid.all -ct perf -o Out.etl –ets

 

This is because the switch –p is one provider, -pf indicates that provider GUIDs should be read from file.

More information on BID and ETW tracing is found here:

 

"Data Access Tracing in SQL Server 2008"

http://msdn.microsoft.com/en-us/library/cc765421.aspx

 

At this link, you will also find a download that contains files with different GUID lists, ctrl.guid.all, ctrl.guid.mdac, ctrl.guid.sqlbrowser etc.