SQL Server Versions

A rather useful site with all the SQL Server versions and hotfixes. For a more detailed (though less readable) list look here.

Dynamic PIVOT table

SQL 2005 Pivot command is great if you have a fixed column result set. However, if the columns are data derived then it is unfortunately not possible to use a SELECT in the PIVOT IN clause. The following overcomes this shortfall :

--==============================
-- Create pivot "columns"
--==============================

SELECT @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
'],[' + t2.Name
FROM ColumnNamesTable AS t2
ORDER BY '],[' + t2.Name
FOR XML PATH('')
), 1, 2, '') + ']'

--==============================
-- Create SQL for Pivot table
--==============================
SET @sqlStmt = N'SELECT MainColumn, ' + @Cols +
' FROM ResultsTable' +
' PIVOT'+
' (SUM(Value) FOR ColumnName IN (' + @cols + ')) AS PivotedResults' +
' ORDER BY MainColumn'

EXEC sp_executeSql @sqlStmt

Clustering SQL Server

Some useful sites on how to create W2K03 cluster servers with SQL 2005:

MSDN How To: Create a new SQL 2005 Failover Cluster(setup)

MSDN Before installing failover clustering

MS How to create Microsoft Distributed Transaction Coordinator on Windows 2003 cluster

MS How to enable network DTC access in Windows Server 2003

MS How to manually re-create Cluster service account

Memory check which tables are loaded into memory

Great tip here for analysing whether you are over stretching your memory in SQL. The root of the issue is that clustered indexes are loaded into memory (the whole table is loaded) in order to query. Should your table be of a significant size, then all of your memory (or more) may be required to load a primary index in for querying. The solution to this is to remove the clustered index - this may equate to higher disk i/o however since the size of the index is smaller it may be able to load into memory anyhow.

A check for table memory usage is as follows:

SELECT sys.tables.name TableName,
sum(a.page_id)*8 AS MemorySpaceKB,
SUM(sys.allocation_units.data_pages)*8 AS StorageSpaceKB,
CASE WHEN SUM(sys.allocation_units.data_pages) <> 0 THEN
SUM(a.page_id)/CAST(SUM(sys.allocation_units.data_pages) AS NUMERIC(18,2))
END AS 'Percentage Of Object In Memory'
FROM (SELECT database_id, allocation_unit_id, COUNT(page_id) page_id FROM
sys.dm_os_buffer_descriptors GROUP BY database_id, allocation_unit_id) a
JOIN sys.allocation_units ON a.allocation_unit_id =
sys.allocation_units.allocation_unit_id
JOIN sys.partitions ON (sys.allocation_units.type IN (1,3)
AND sys.allocation_units.container_id = sys.partitions.hobt_id)
OR (sys.allocation_units.type = 2 AND sys.allocation_units.container_id
= sys.partitions.partition_id)
JOIN sys.tables ON sys.partitions.object_id = sys.tables.object_id
AND sys.tables.is_ms_shipped = 0
WHERE a.database_id = DB_ID()
GROUP BY sys.tables.name

For further information on this and perfmon settings, look here

TFS Event Subscription

Finally got round to looking into this. Firstly, the next iteration of the 2008 Power Tools will introduce a nicer interface for the maintenance of event subscriptions. However, that is not due quite yet so in the meantime there are two tools to fill the gap.

The TfsAlert tool provides a system tray app that balloon tips whenever events change. It monitors 8 different events, and allows filtering. The interface for filtering is to edit the config file, so knowledge of syntax is required. However, that syntax can be built by the next tool.

The second tool is TFS Event Subscription and is a GUI builder for event subscription strings. This allows you to subscribe to a particular event and get a message sent by SOAP, PlainText or EmailHTML. This is a standard feature of TFS, however this interface can be run on a client and is a great deal easier to use compared to command line.

Lastly, this blog provides details on how to amend the emails sent by TFS to point to the TFS Web Access for the work item, rather than a summary report.

Common SQL Server errors and failures

Blatant link, but very useful : Common SQL Server failures and errors

Clustered SQL Server error 28000 - cluster service login error

Whilst trial testing a failover for a clustered (W2K3) SQL Server 2005, we experienced an error 28000, failing on authenticating the cluster service account. This meant that the server service did not restart under cluster manager (though it is possible to start it from command prompt or via services).

This was due to removing the BUILTIN\Administrators group from security for SQL Server. Although this is standard practice, it appears that SQL Server is unable to verify the cluster serice account via an AD group. The solution to this is to either add back the BUILTIN\Administrators account (not desired) or create a security entry for the cluster service account.