Useful SQL queries for the TFS Administrator

Right, right – I know:

I would actually replace can with will, but people might think I am too harsh. Remember – the TFS databases cannot be modified by nobody but Microsoft, otherwise you won’t get support, you might experience unknown (and untested!) issues and mostly important – you would lose any upgrade path as the schemas are checked by the TFS installer.

Anyway I won’t be talking about modifying the databases, while instead I wanted to share some useful queries I found during my almost ten years experience with Team Foundation Server.

SQL Server is a deterministic system, but sometimes it doesn’t seem to be. For instance, AlwaysOn is synchronising some data after you messed up with a Team Project Collection used for testing purposes, and despite it might look stuck it is actually doing something.

The AlwaysOn Dashboard doesn’t show anything but a Synchronizing status, so how can I say so?

Run this query:

SELECT dmv_2.login_name AS Invoker,
dmv_1.session_id AS SPID,
command as 'Instruction Type',
a.text AS Query,
start_time AS 'Initiated at',
percent_complete AS Percentage,
dateadd(second,estimated_completion_time/1000, getdate()) AS ETA
FROM sys.dm_exec_requests dmv_1
CROSS APPLY sys.dm_exec_sql_text(dmv_1.sql_handle) a
INNER JOIN sys.dm_exec_sessions dmv_2
ON dmv_1.session_id = dmv_2.session_id

This is what you’ll get:

Excluding the line where I am the Invoker – of course – I can see all the activity at 11:08am:

• NT AUTHORITY\SYSTEM running sp_server_diagnostics
• The TFS and SQL service account in this testing environment running an INSERT query – that is AlwaysOn!

That query leverages SQL Server’s DMVs, and it is very handy for checking all the things happening in the Database Engine.

Another one is about Transaction Log files – what about their physical status?

SELECT Name,
database_id,
log_reuse_wait,
log_reuse_wait_desc
FROM sys.databases

Querying sys.databases can give you the status of your Transaction Log files. I needed that because I was running some extreme tests in borderline conditions, hence I had to monitor their status. Sys.databases is very handy for other information as well.

Eventually, as Grant suggests (and he is always right!), DBCC CHECKDB regularly is a must. And please, have a test server around so you can compare all the behaviours, if any.