09 October 2008

Orphaned Distributed Transactions

----- We occasionally get orphaned transactions when unit tests fail.

----- This will kill the orphaned transactions for a specific database,

-----      or all on the server without having to restart the Distributed

-----      Transaction Coordinator (which could cause successfully executing

-----      unit tests within a different database to fail).

DECLARE @DBName sysname

--SET @DBName = 'Bob'


DECLARE @KILL varchar(1024)

DECLARE @currentUOW uniqueidentifier

SET @currentUOW = (SELECT TOP 1 req_transactionUOW

                   FROM sys.syslockinfo

                   WHERE ( rsc_dbid = db_id(@DBName) OR @DBName IS NULL)

                          AND req_spid = -2

                          AND rsc_objid <> 0 

                   ORDER BY req_transactionUOW)



   SET @KILL = 'KILL ''' + cast(@currentUOW AS varchar(40)) + ''';'


   EXEC ( @KILL )

   SET @currentUOW = (SELECT TOP 1 req_transactionUOW

                      FROM sys.syslockinfo

                      WHERE (    rsc_dbid = db_id(@DBName) OR @DBName IS NULL)

                             AND req_spid = -2

                             AND rsc_objid <> 0

                             AND req_transactionUOW > @currentUOW

                      ORDER BY req_transactionUOW)



02 October 2008

SQL Server Per-Processor Licensing

Setting processor affinity does not allow you to reduce the number of processors for which you are licensed. Any processor to which the OS has access must be licensed [1].

Creating a virtual machine with fewer virtual processors than the physical host contains does allow you to reduce the number of processors for which you are licensed. You only have to have licenses for the number of virtual processors [2].

[1] If you run the software in a physical OS environment, you need a license for each physical processor used by the physical OS environment.
[2] If you run the software in virtual OS environments, you need a license for each virtual processor used by those virtual OS environments on a particular server—whether the total number of virtual processors is lesser or greater than the number of physical number of processors in that server.

These two references are both from page 6 from the following document:

MS Forum: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1884303&SiteID=1
MS Licensing FAQ: http://www.microsoft.com/sql/howtobuy/faq.mspx#EZB

DBA Role

I recently read an article by Scott Ambler called “Where did all the positions go?” It was about how traditional software roles fit into an agile development paradigm. I thought I'd make some observations as to how I think this relates to our organization's current state of database development.

One thing I've noticed since we have adopted the agile development model is that I've been doing a lot less DBA work. Sure, I still do some modeling, write some SQL, and put the occasional database together but that is what I would consider database development; definitely not administration. I have often felt that, as a DBA, I'm not able to make a full contribution to my agile team.

Another thing I've heard from some of my database peers is that the iterations tend to be front loaded with database work because much of the development is dependent upon the completion of the database portion. Once the database work is done, development of the application can continue and the database developer tends to settle down to an application support role with minor fixes. Unless your project is a database only project, there is not typically enough database work to fill multiple complete iterations.

Scott's original positions question, as it pertains to our group, might be asked as “How does a traditional DBA fit on an agile development team?” The short answer is “s/he doesn't.” Does this sound harsh? Let's take a look at the longer answer.

Although the traditional DBA doesn't have a fully contributing role on an agile team (neither does the traditional programmer, business analyst, or project leader for that matter), Scott refers to something he calls a “generalizing specialist.” As traditional database developers, we obviously cover the “specialist” portion of this role but, with just the specialist portion covered, we are still only partial contributors to the project team.

To become fully contributing members of our agile teams, we are learning to expand our contributions to some non-database areas as project needs dictate. We are developing the “generalizing” to go with our “specialist.” I've seen some of our formerly traditional DBAs doing things such as writing VB.NET code, running regression tests, working with VB and C# CodeSmith templates, and creating and executing QTP scripts. These are definitely skills you don't expect to develop in a traditional DBA role!

So... about my previous lack of contribution as a DBA; job title aside, I'm now fully contributing to my team as a general agile developer with a database specialty.