System.Data.Entity.Infrastructure.CommitFailedException: C# Multithreading & SQL Server 2012

We have a C# multi threaded (100 threads) program which reads the records from the DB and each thread picks up one record (one Entity Framework connection per thread) and update the a single DB table.

For first few minutes (5 minutes) the program works fine without exception then all of a sudden all threads starts throwing the below error messages.. After ~1 min everything will come back to normal stage.. I think the SQL Server is getting too many locks for a single DB table (might be trying to acquire table lock on that table) or too many connections to a single DB and closing all the connections..

I am unable to debug this, can some one help me in getting the following information,

-
Where does SQL Server 2012 store its logs?

-
Can we increase the log level to see why it throws an exception while saving the DB entity

-
How to get the number of locks per a table, different kind of locks (table lock, page lock, num row locks, etc) acquired by DB

Any other pointers to debug this issue.

FYI, I didn't find anything useful in sqlerror log got from this cmd:

SELECT SERVERPROPERTY('ErrorLogFileName')

Here is the stack trace of the exception:

```
System.Data.Entity.Infrastructure.CommitFailedException: An error was reported while committing a database transaction but it could not be determined whether the transaction succeeded or failed on the database server. See the inner exception and http://go.microsoft.com/fwlink/?LinkId=313468 for more information.

System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
System.ComponentModel.Win32Exception: The wait operation timed out
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.S

Looks like blocking in action.

You should try to monitor blocking with tools such as sp_WhoIsActive or an Extended Events session.

If you want to use Extended Events, there's a tool called ExtendedTSQLCollector that can help you automate monitoring and collection of the events you need. I wrote a blog post on how to use it to monitor blocking and deadlocking. You can find it here: Monitoring blocking and deadlocking with Extended T-SQL Collector

StackExchange Database Administrators Q#93211, answer score: 2