Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

A little background

I am working with an ASP.NET MVC ERP-like Intranet application. It uses the Entity Framework technology for data access for a MS SQL server. We have 3 versions of the application deployed. One for development (dev), one for testing (staging) and one live (production). The dev version runs on IIS 8 Express on my machine while both staging and production runs on IIS 8 (or 8.5; I am not certain) on a Windows 2012 server. The dev version uses a MS SQL 2012 Express instance running on my machine while both staging and production has their own database on the same MS SQL 2005 server/instance running on Windows 2003.

The problem

We started having a problem with the intranet. The update queries on a certain table (I'm not sure if it's only THAT table but it certainly wasn't every tables) of the Production environement would just hang. Entity Framework (or SQL?) has a 30 second timeout by default, so the query would just hand 30 seconds, then throw an exception in my application's error logs. For anyone interested, this was the exception (in french, sorry):

System.Data.Entity.Infrastructure.DbUpdateException: Une erreur s'est produite lors de la mise à jour des entrées. Pour plus d'informations, consultez l'exception interne. --->
System.Data.UpdateException: Une erreur s'est produite lors de la mise à jour des entrées. Pour plus d'informations, consultez l'exception interne. --->
System.Data.SqlClient.SqlException: Expiration du Timeout. Le délai d'attente s'est écoulé avant la fin de l'opération ou le serveur ne répond pas.
L'instruction a été arrêtée. --->
System.ComponentModel.Win32Exception: Dépassement du délai d’attente

I decided to open up the SQL profiler and see what was the query that would hang. This is what I found:

exec sp_executesql N'update [dbo].[Employe]
set [initiales] = @0
where ([id] = @1)
',N'@0 nvarchar(max) ,@1 int',@0=N'SSS',@1=5950

First thing I told myself is: Wow. This is such a simple query... So I copied that query in the SQL Server Management Studio, ran it, and it just hanged. Waited about a minute then I canceled it. I simplified the query to not use stored procedures and variables:

update [dbo].[Employe]
set [initiales] = 'SSS'
where ([id] = 5950)

Still hangs. I went on the Staging database and ran the exact same query and it passed instantly... So I figured maybe I screwed up with a database migration.

I opened up "Open DBDiff" which is a Diff tool for SQL databases. The only thing that came up was different views, different users, different store procedures. The tables were identical.

We finally solved the problem by rebooting the SQL server machine (maybe just a SQL service would have worked but we didn't feel like troubleshooting while the production server was faulty so we just went Microsoft style and rebooted the machine.

So now everything works fine, but this problem made me really worried. This intranet system deals with money, pays, time sheets, etc. I really need to understand what happened.

The question

So my question is, does anyone have any ideas or places I could look at to put some light on what actually happened since databases aren't my expertise.

share|improve this question
    
I would suspect that you had some kind of blocking occurring. Either it was a long running process or someone had left an open transaction that was blocking you. You could have validated this by running "SELECT * FROM Master.sys.sysprocesses" or running SP_WHO2. If it was blocking it would tell you what SPID was causing the blocking. –  Ian Chamberland Sep 20 '13 at 17:12
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.