fix for long running transaction problem

[pb648174]

I just wanted to post a follow up to a message I posted some months ago
about a long running transaction that was blocking all other users...
The link is below

http://groups.google.com/group/comp.databases.ms-sqlserver/browse_thread/thread/1063b65df1f97492/8649bee2002646a2

By using the new "Row versioning" functionality of SQL 2005, it
completely solved this problem. By reading the books online, it says
there is a performance impact, but that the better performance of SQL
2005 in general might offset it. So far this seems to be the case. just
posting it here in case anyone else has the problem. The SQL command Ii
had to execute to get everything working properly was:

ALTER DATABASE DBname
SET READ_COMMITTED_ SNAPSHOT ON;

[Erland Sommarskog]

Re: fix for long running transaction problem

pb648174 (google@webpaul .net) writes:[color=blue]
> I just wanted to post a follow up to a message I posted some months ago
> about a long running transaction that was blocking all other users...
> The link is below
>
> http://groups.google.com/group/comp.databases.ms-
>sqlserver/browse_thread/thread/1063b65df1f9749 2/8649bee2002646a 2
>
> By using the new "Row versioning" functionality of SQL 2005, it
> completely solved this problem. By reading the books online, it says
> there is a performance impact, but that the better performance of SQL
> 2005 in general might offset it. So far this seems to be the case. just
> posting it here in case anyone else has the problem. The SQL command Ii
> had to execute to get everything working properly was:
>
> ALTER DATABASE DBname
> SET READ_COMMITTED_ SNAPSHOT ON;[/color]

Snapshot isolation is indeed a cool new feature in SQL 2005!


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx