How to SET and check READ_COMMITTED_SNAPSHOT

17 April,2014 by Jack Vamvas

Question: How can I set and check the isolation level  READ_COMMITTED_SNAPSHOT ? I executed the command and it’s still running after 60 minutes, I’m worried I may have corrupted the database

Answer: You probably haven’t corrupted the database, but the command you’re running is blocked by another active connection.   SQL Server Books Online states that no other connections to the database are allowed while the command is executing.  Although single-user mode is not required.

Use sp_who2 to see who else is connected to the database. Read How to detect and troubleshoot blocking and deadlocks

 

--to set read_commited_snapshot ON
ALTER DATABASE [MY_DATABASE] SET READ_COMMITTED_SNAPSHOT ON
--to check if read_commited_snapshot is on
select name,is_read_committed_snapshot_on from sys.databases where name = 'MY_DATABASE'


Read More

SQL Server - How to find Open Transactions

SQL Server - How to improve Execution Plan Reuse

SQL Server – How to troubleshoot a slow running query


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to SET and check READ_COMMITTED_SNAPSHOT


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer