Enabling SQL Tracing on a TFS 2010 database

If you’ve ever had trouble setting up TFS 2010 (errors, baby that’s what I’m talking about) you might like to have SQL Tracing on TFS database for more info.  By default you don’t have it (and you need a regedit to get it).
(This also refers to the program from SysInternals that is a free download “ DeBug View”)

How about an example!

Let’s say in the Advanced Configuration Wizard you type in the wrong name of the SQL Server Instance you want to use. The error message you will get is:

clip_image002

But look at some of the trace output:

[6468] [Verbose@18:45:56.241] Checking if database master exists on SQL connection Data Source=HORVICKVM-DEV2;Integrated Security=True
[6468] [Error @18:46:21.381] Microsoft.TeamFoundation.Admin.TfsAdminException: TF255049: A connection cannot be made to the server that is running SQL Server. Verify that you have entered the correct name for the server, including the instance name, that the server you are attempting to connect to is online, and that you have the required permissions to connect. —> Microsoft.TeamFoundation.Framework.Server.DatabaseConnectionException: TF246017: Team Foundation Server could not connect to the database. Verify that the server that is hosting the database is operational, and that network problems are not blocking communication with the server. —> System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)

What’s so great?

Well first the error message is highlighted red (in DbgView it will be white text on a red background given my example above – but here I made it red text on white to be more readable in the blog post).

Also the message contains the true .NET Exception that occurred and the connection string we used. Those are two really useful pieces of information when you are trying to figure out what is happening. More specifically it even told you how the check was being performed (“Checking if database master exists…”).

Oh No! I have Warnings and Errors!

Well – did anything actually go wrong with your configuration? If not – then it’s probably nothing to worry about. We have some messages that come out as warnings or errors but are in fact benign.

Why Don’t I See Server Messages?

This blog post only explains how to enable tracing for the TFS 2010 admin tools. It does not explain how to enable tracing for the server components, SQL, the job service (though we do trace the job service output when a servicing operation is initiated by an admin tool such as tfsmgmt.exe or tfsconfig.exe). This trace data will provide you zero insight into your server’s realtime activities. Only configuration actions.

Here are the steps you need to follow.

Enabling Tracing

  1. Close the TFS 2010 configuration wizard or admin console (if running).
    1. We only check the trace level when the program starts up. So if it’s already running and you haven’t done this – you need to close it!
    2. Oh – if you are running the TFS 2010 Setup and are at the window where you are asked if you want to launch the configuration tool – you can update the registry key now and not need to close the configuration wizard since the MSI and configuration phases are separate executables).
  2. Enable verbose tracing by doing the following:
    1. Open Regedit (must be an Administrator)
    2. Find the key HKLM\SOFTWARE\Microsoft\TeamFoundationServer\10.0\Admin
    3. Edit the value “TraceLevel” (DWORD) – set to “4” (it is probably “0”)
    4. You can do this from an admin command prompt by running:
      1. reg.exe add HKLM\SOFTWARE\Microsoft\TeamFoundationServer\10.0\Admin /t REG_DWORD /v TraceLevel /d 4 /f
  3. Run DbgView
    1. You can download DbgView from http://technet.microsoft.com/en-us/sysinternals/bb896647.aspx
      1. You can also run it right from the website at this link: http://live.sysinternals.com/Dbgview.exe
  4. By default DbgView is ready to go – Under the “Capture” menu make sure that “Capture Win32” (CTRL+W), “Pass-Through” and “Capture Events” (CTRL+E) are enabled. Nothing else is needed.
    1. clip_image002[4]
  5. Start up the TFS Administration Console (or configuration tool, whatever).

When the config tool starts you should see some data streaming in to DbgView – it should look something like this:

[6468] Admin TraceLevel = Verbose

Show Me The Errors!

  1. Bring up the filters screen in DbgView (CTRL+L or clicking on this button: clip_image004).
  2. Leave the Include and Exclude fields alone.
  3. In the Highlight section leave “Filter 1” selected and type “[Error” into the Red text area (please note that I typed an opened brace but not a closing one and also do not type the double quotes). It should look like this:
    1. clip_image006
  4. Now change “Filter 1” to “Filter 3” (Nothing magically about 3. I use 3 because it’s kind of orange – I don’t like the purple of “Filter 2” and I don’t want to get into color changing in this blog post – you want to change colors? Go nuts.).
  5. Add the string “[Warning” – again, no closing brace and don’t use the double quotes.
    1. clip_image008
  6. Now if you happen to have an error it will be much easier to detect.
[tweetmeme only_single=”false”]
Advertisements

Undocumented stored procedure for looping through all tables in a database

I got tired of writing code to loop through all tables in a database.  I found out about an undocumented stored procedure that does most of that for me.
The stored procedure name is sp_MSforeachtable it takes one input parameter (@command).
This simplifies so much code (no more looping through sysobjects).

Here are some examples of how to use it.

This command will return a row count for each table in your database.

EXEC sp_MSforeachtable ‘SELECT ‘‘?’‘, Count(*) as NumberOfRows FROM ?’

Notice the question mark (?), that gets substituted as the table (in the sp).
So basically any command you can do against a table you can use and put a ? where you put a table name.

Here is how to rebuild all indexes.

EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"

Here is how you update all statistics on your tables.

EXEC sp_MSforeachtable 'UPDATE statistics ? WITH ALL'

You can also add additional conditional logic if you need to restrict your commands to a subset of tables.
This is very powerful. You don’t always need to hit all tables with a command.

This one will loop through all your tables and add the Column CreatedOn on any table that doesn’t already have it.

EXEC sp_MSforeachtable '
    if not exists (select column_name from INFORMATION_SCHEMA.columns 
                   where table_name = ''?'' and column_name = ''CreatedOn'') 
    begin
        ALTER TABLE [?] ADD CreatedOn datetime NOT NULL DEFAULT getdate();
    end
'
[tweetmeme only_single=”false”]

How to view SQL Server property information

Run the query below to find out the property information about SQL Server.
This is helpful when try to determine which features (based on the version) that are available.

 SELECT  'BuildClrVersion' ColumnName, SERVERPROPERTY('BuildClrVersion') ColumnValue
 UNION ALL  SELECT 'Collation', SERVERPROPERTY('Collation')
 UNION ALL  SELECT 'CollationID', SERVERPROPERTY('CollationID')
 UNION ALL  SELECT 'ComparisonStyle', SERVERPROPERTY('ComparisonStyle')
 UNION ALL  SELECT 'ComputerNamePhysicalNetBIOS', SERVERPROPERTY('ComputerNamePhysicalNetBIOS')
 UNION ALL  SELECT 'Edition', SERVERPROPERTY('Edition')
 UNION ALL  SELECT 'EditionID', SERVERPROPERTY('EditionID')
 UNION ALL  SELECT 'EngineEdition', SERVERPROPERTY('EngineEdition')
 UNION ALL  SELECT 'InstanceName', SERVERPROPERTY('InstanceName')
 UNION ALL  SELECT 'IsClustered', SERVERPROPERTY('IsClustered')
 UNION ALL  SELECT 'IsFullTextInstalled', SERVERPROPERTY('IsFullTextInstalled')
 UNION ALL  SELECT 'IsIntegratedSecurityOnly', SERVERPROPERTY('IsIntegratedSecurityOnly')
 UNION ALL  SELECT 'IsSingleUser', SERVERPROPERTY('IsSingleUser')
 UNION ALL  SELECT 'LCID', SERVERPROPERTY('LCID')
 UNION ALL  SELECT 'LicenseType', SERVERPROPERTY('LicenseType')
 UNION ALL  SELECT 'MachineName', SERVERPROPERTY('MachineName')
 UNION ALL  SELECT 'NumLicenses', SERVERPROPERTY('NumLicenses')
 UNION ALL  SELECT 'ProcessID', SERVERPROPERTY('ProcessID')
 UNION ALL  SELECT 'ProductVersion', SERVERPROPERTY('ProductVersion')
 UNION ALL  SELECT 'ProductLevel', SERVERPROPERTY('ProductLevel')
 UNION ALL  SELECT 'ResourceLastUpdateDateTime', SERVERPROPERTY('ResourceLastUpdateDateTime')
 UNION ALL  SELECT 'ResourceVersion', SERVERPROPERTY('ResourceVersion')
 UNION ALL  SELECT 'ServerName', SERVERPROPERTY('ServerName')
 UNION ALL  SELECT 'SqlCharSet', SERVERPROPERTY('SqlCharSet')
 UNION ALL  SELECT 'SqlCharSetName', SERVERPROPERTY('SqlCharSetName')
 UNION ALL  SELECT 'SqlSortOrder', SERVERPROPERTY('SqlSortOrder')
 UNION ALL  SELECT 'SqlSortOrderName', SERVERPROPERTY('SqlSortOrderName')

If you want a very simple view of the version info run this query.
Select @@version as Version

Track Changes to objects in your database

Ever have the need to be able to track changes to the objects in your database? This code sample was provided by a friend, Josh Shilling.  It provides you with a change log on your database.  This will be helpful in multi developer environments when you think someone made a change to your database, but you don’t know what changes were made.

So you can run Select * from dbo.ChangeLog to see what changes have been to your db objects.

I had a hard time getting the image of the results to show.

image

GO
/****** Object: Table [DBVersion].[ChangeLog] ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
SET
ANSI_PADDING ON
GO
CREATE
TABLE [dbo].[ChangeLog](
[LogId] [bigint] IDENTITY(1,1) NOT NULL,
[DatabaseName] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EventType] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ObjectName] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ObjectType] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SqlCommand] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EventDate] [datetime] NOT NULL DEFAULT (getutcdate()),
[LoginName] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
PRIMARY
KEY NONCLUSTERED
(
[LogId] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
ON [PRIMARY]
GO
SET
ANSI_PADDING OFF
--Trigger
GO
/****** Object: DdlTrigger [ddltrg_ObjectRevisionHistory] ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
CREATE
TRIGGER [ddltrg_ObjectRevisionHistory]
ON
database
FOR
CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
CREATE_TABLE
, ALTER_TABLE, DROP_TABLE, CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,
CREATE_INDEX
, ALTER_INDEX, DROP_INDEX, CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER,
CREATE_STATISTICS
, DROP_STATISTICS, UPDATE_STATISTICS, CREATE_SCHEMA, ALTER_SCHEMA,
DROP_SCHEMA
, GRANT_DATABASE, DENY_DATABASE, REVOKE_DATABASE, CREATE_USER, ALTER_USER,
DROP_USER
, CREATE_VIEW, ALTER_VIEW, DROP_VIEW
as
BEGIN
SET NOCOUNT ON
DECLARE @data XML
SET @data = EVENTDATA()
INSERT INTO dbo.ChangeLog
(databasename, eventtype, objectname, objecttype, sqlcommand, loginname)
VALUES
(
@data
.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),
@data
.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'),
@data
.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),
@data
.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'),
@data
.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'),
@data
.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')
)
END
GO
SET
ANSI_NULLS OFF
GO
SET
QUOTED_IDENTIFIER OFF
GO
ENABLE
TRIGGER [ddltrg_ObjectRevisionHistory] ON DATABASE