Note: This applies to SQL Server 2005
Note: This applies to DocuShare 6.x and 6.5.3
Issue
Deadlock errors displayed in the \logs\dsserver.log file.
Solution
Note: You must be an Administrator to perform this solution.
1. Set database to SNAPSHOT isolation.
This is a different way of handling locks that allows SQL Server to better support concurrency by providing what is called non-locking, or non-blocking read committed isolation using row versioning. This is key to avoiding deadlocks. If a site experiences SQL Server 2005 deadlocks, it is highly recommended to put the database in isolation mode using the commands below .
To set database to SNAPSHOT isolation:
Note: If you download an SQL Express manager for Vista or SQL 2008 and you administer the SQL 2005 database with the new SQL Manager the commands listed below will not work.
a. Stop DocuShare.
Note: The Stop DocuShare step has been included to free up connections to the SQL server to execute he commands listed below.
b. Open Microsoft SQL Server Management Studio.
c. Right-click the Database used for DocuShare and select New Query.
Note: The default database name during installation is Docushare.
d. In the Query window on the right hand side of the screen type the following query
ALTER DATABASE MyDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON;
GO
ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON
WITH ROLLBACK IMMEDIATE;
GO
Where MyDatabase is replaced with the Database name used for DocuShare in your installation environment.
Note: The with rollback statement will rollback any active transactions to make sure that DocuShare is not running and that no long running background job is running. The ALTER DATABASE command requires that there are no other open connections in the database until the ALTER DATABASE command is complete.
e. Click the ! execute button on the toolbar at the top of the window.
f. When query has completed the Messages window displays the following message. Command(s) completed successfully.
2. Verify the database is set to SNAPSHOT isolation.
It is important to verify that these two settings are set correctly using the below queries:
a. How to check whether snapshot transaction isolation is allowed
1) In the Query window on the right hand side of the screen type the following query
select sys.databases.snapshot_isolation_state,sys.databases.snapshot_isolation_state_desc
from sys.databases
where (sys.databases.[name]='MyDatabase')
Where MyDatabase is replaced with the Database name used for DocuShare in your installation environment.
2) Click the ! execute button on the toolbar at the top of the window.
3) The results tab should be list snapshot_isolation_state with a value of 1 and snapshot_isolation_state_desc with a value of ON.
b. How to check whether read committed transactions use snapshots.
1) In the Query window on the right hand side of the screen type the following query
select sys.databases.is_read_committed_snapshot_on
from sys.databases
where (