Deadlock occurring on database, SQL Server 2008R2

Question

Last few days I had major problems with my production site, turns out deadlock occurred, every half an hour maybe 50-60 of them. To do a quick fix in order to keep the site running I set : ALLOW_SNAPSHOT_ISOLATION ON

I believe my log file will grow faster than before now?

Are there different types of isolation and is it possible to set the isolation to just 2 tables which causes the deadlock?

::Update::

::2013-03-21 15:09::

Can I query the process ID's involved in the deadlock? select object_name() returns null always

Answer 1

Enabling snapshot isolation on a production server without testing the application first is rather a high risk strategy, but if you seem to have got away with it, congratulations.

The side effects of snapshot isolation tend to be to do with tempdb rather than the transaction log, see for instance http://msdn.microsoft.com/en-us/library/tcbchxcb%28v=vs.110%29.aspx.

The best thing to do is to look at the code that is accessing those two tables, and see if you can correct the problem there.

Answer 2

I think your best friend in this situation is sp_whoisactive developed by Adam Machanic; you may get the same from Brent Ozar's website. First Responder Kit

I was having similar condition wherein I used to get frequent blocking and my application used to go in non-responding state. I didn't change isolation level directly rather I enabled only Read Committed Snapshot. Below is command to do that:

   --Query to check current state of database
SELECT name, is_read_committed_snapshot_on FROM sys.databases where name = ‘DB_NAME’

--Query to modify the database:

ALTER DATABASE DB_NAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

ALTER DATABASE DB_NAME SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;
GO

Please keep in mind that this change requires downtime at production server. Read committed snapshot Isolation enabling requires code change at times and could put you in trouble. If its working in your case then, its perfectly fine. you may read more about the same on Little Kendra's Website Isloation level by Kendra

I hope above info would be of help or you. In case you are using any database monitoring tool like Solarwinds DPA or Whitesands Proactive DBA; this could be of great help in terms of alerting.

As far as your second question goes - yes, you can change isolation level at session level however I am not very sure about table level.

You may refer my question on this forum at Database Blocking