views:

42

answers:

1

We have a database (let's call it database A) which becomes unusable every some days and we have to restart it. When I say unusable means all applications using it just block there waiting for the database to respond but it never does.

By luck it was noticed that executing a SELECT statement against a specific table using the SQL Server Management Studio seems to bring some records but at some point it blocks.

The funny thing is that there are no LOCKED or LOCKING processes on the specific database. I found out that the application uses the following transaction isolation:

ALLOW_SNAPSHOT_ISOLATION ON

which explains why we can't see Locked or Locking processes right?

We have another database (let's call it database B) which actually has the same schema and we never had this issue. The only difference between these databases is the isolation I mentioned earlier. This one uses the default transaction isolation and we never had this odd thing of the database blocking. But also database A has a lot more transactions opening per day; much much more. So what I can think of is that the SNAPSHOT ISOLATION should be avoided for a big number of concurrent transactions in this case.

Can someone confirm that most probably it's the SNAPSHOT ISOLATION causing the problems? I mean we have no locks and we just have a database blocking with no actual exceptions or something that will help us detect the root cause of the problem.

Are my assumptions right? I surely hope so.

+2  A: 

Have you tried to monitor your tempdb usage ? (AFAIK, ALLOWSNAPSHOT_ISOLATION ON relies heavily on tempdb, which isn't the case for standard locking strategies)

This MS technet page gives some tips on how to do this (see the section 'Monitoring space')

you can also use this quick query to check your tempdb isn't full :

   use tempdb
   exec sp_spaceused
Brann
Can you give me some tips on what should I monitor on tempdb?
DaUltimateTrooper
just updated my answer...
Brann
How this would help?Here is the doc for this sp:Displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database, or displays the disk space reserved and used by the whole databaseI mean this would show me the space allocated by the tempdb.I mean what else can I do to monitor what makes the database A being very slow?
DaUltimateTrooper
Did you read the article I linked ? It gives detailed information regarding tempdb monitoring.
Brann