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.
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.
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.