Clearing out a Mysterious Table Lock (LCK_M_S) in Sql Server 2008

A user reported today that they were trying to query a table with 181 records and the output stopped at 178 and just hung there. I tried to replicate this and the same thing happened – also couldn’t run a Select (*) on the table. So I pulled up the Activity Monitor for the server and observed that there was only one query in process – the hung query. It listed the process as being suspended with a Wait Type of LCK_M_S. Some quick searching told me that the task was waiting to acquire a shared lock. However, it was not obvious what was locking the table. What to do?

After some more searching, I found two more posts that gave advice on what to do in the given scenario:

Based on these, I did the following:

    1. Ran Exec sp_who2 through Sql Management Studio. This gave me a list of all current processes. There was only one open process on this database at the time, so it was the likely culprit.
    2. Next, ran the code in the Sql Server Locks article above to show the process that was locking my table. Bingo – process 52.
USE DBNAME; GO;

DECLARE @dbid INT ; --what's the dbid for DBNAME?
SET @dbid = DB_ID() ;

DECLARE @objectid INT ; --what's objectid for our demo table?
SET @objectid = OBJECT_ID(N'TABLENAME') ;

--look at locking in the DB
SELECT  resource_type
       ,resource_database_id
       ,resource_associated_entity_id
       ,request_mode
       ,request_type
       ,request_session_id
FROM    sys.dm_tran_locks
WHERE   resource_database_id = @dbid ;

--limit the results to only the offending table
SELECT  *
FROM    sys.dm_tran_locks
WHERE   resource_database_id = @dbid AND
        resource_associated_entity_id = @objectid ;

After I had confirmed the identity of the process, I ran Kill 52 to kill the offending process. Everything immediately started working again with the table.

(After the fact, went back through the code with my team and found the spot where someone was running db.Connection.BeginTransation() on a data context that already had an open transaction, causing an error: “SqlConnection does not support parallel transactions“. And to make things worse, this was done outside of the try/catch/finally structure that ensured that any open transactions would be committed. Oy.)

As Pinal Dave writes:

As mentioned earlier, if you kill something important on your production server, there’s a great possibility that you’ll face some serious integrity issues, so I there’s no way I advise use this method. As the title goes, this is a dirty solution so you must utilize this only if you are confident.

It is important to keep that in mind. But that said, this technique for identifying and killing a hung transaction can definitely come in handy. (And be sure to backup your DB and transaction logs regularly!)

In Search of an Embedded DB for a Windows Forms Application

So I am in the middle of building a pretty substantial Windows Forms application in .Net 2.0/C#, which will rely very heavily on database integration. Currently development is proceeding using Sql Server Express 2005 for the database server. It is a very robust product that supports anything that you can do on the full edition of SQL Server, and is freely distributable. Stored procedures, triggers, the works.

However, when making the first internal release intended to be installed by other people, I ran into some problems, specifically related to the way that SQL Express works. As it is really the same thing as the full version of SQL Server (with limits put on DB size and other licensing issues), it requires a big download (around 35mb), large db file footprint (around 20mb) separate installation, and runs as a service on the client machine. Though the install can be packaged as part of your overall distribution and set to install “silently”, it is still an issue that requires ongoing support and trouble-shooting. Additionally, you can sometimes run into problems with the SQL Express service, permissions, problems attaching the database to the server, etc. It is a robust solution, but the potential problems involved in installing SQL Express on thousands of client machines and getting it to run flawlessly all the time is enough to give me pause.
Continue reading

Where Sql Server Management Studio Falls Short

When I was installing programs on my new laptop (Dell Latitude D820), some of my coworkers recommended that instead of installing Enterprise Manager (the client tools for SQL Server 2000), I should use the Microsoft SQL Server Management Studio that is included with SQL Server 2005 instead. It can perform all of the functions that Enterprise Manager/Query Analyzer can perform, except better. It is just constructed better, and lacks many of the annoying features of the previousl client tools (like how the modify Stored Procedure dialog box was modal, preventing you from doing anything else – like opening up another sproc and copying some text – while it was open, as well as close integration between the query windows and the manager interface). Plus, it could manage SQL Server 2000 databases perfectly well (which is important, as my company still uses this for their primary DB server).

So I installed the Management Studio a few weeks ago. Yet today I find myself reinstalling Enterprise Manager/Query Analyzer, because it has some features I need that Management Studio does not provide. Here they are:

  • Cannot handle diagrams from Sql Server 2000 – This one was the deal-breaker for me. The database diagramming component of Enterprise Manager was always my primary way to define relationships and foreign keys between tables in the database. The graphical representation made the whole process much easier and helped me make sure that I wasn’t missing anything. When I tried to make a DB diagram for my 2000 database using Management Studio, I was directed to a MSDN help page that informed me that this was not supported by Management Studio (since MSSQL2000 uses a different diagram definition than 2005). Argh. I know that the format is different, but knowing that lots of people still use 2000, would it have been so difficult to add in support for the old formatting model? (This was the most annoying. The rest of the list are other pet peaces I have with Management Studio that I will be happy to see again when using Enterprise Manager)
  • Setting the Identity Column – In Enterprise Manager, when defining the Identity column in a table, I can simply double click on the No in the Columns property display and it is set. In Management Studio, I have to First expand the Identity Specification section in the Column Properties, and then double-click on the (Is Identity) value. This extra click is annoying, especially considering how the Column Properties always needs to be made taller (it opens up too short) and the checkbox to expand the section is very small. Why can’t I just double-click on the value of Identity Specification like I do in Enterprise Manager and be done with it?
  • Declaring Column Sizes – In Enterprise Manager, when defining a table, the column type and the size of the column were in two different rows, so when I want to enter a Varchar(100) row, I type “varc” (at which point the drop-down box identifies my selection as a varchar) “[tab]” (to get to the size column) “100″ to set the size. In Management Studio however, there is not separate column for column size. So when I am setting a Varchar(100) column, I have to type it all out “varchar(100)”. You might say “It is only five more characters, quit crying about it”, but it actually makes a difference in terms of work-flow (especially considering how if you want to change the size of a column that was previously set, you have to either retype the entire type+size, or use your mouse to highlight the numbers and change them. It is impossible to do the editing with the keyboard alone. Plus, if you make a typo when putting in the parentheses, you have to start over)

It is the small things that make the difference. So at least for now, I will be using Enterprise Manager again for initial database setups. Too bad, since other than these things, Management Studio really does excel. (Subtle hint from anyone in the Microsoft SQL Server 2005 Management Studio team who is reading this).