0

Since we migrated our database from SQL Server 2005 to SQL 2014, we're experiencing repetitive locks in the database. We used to have very few locks in SQL 2005. The READ_COMMITTED_SNAPSHOT is ON in the database and still ON in SQL 2014.

We migrated the database by Backup and Restore.

SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name= 'YourDatabase'

The result of the above code returns 1, meaning that is ON.

The new Database server is a Windows Server 2012 R2 with 128 GB or RAM, on RAID 1 for the OD and RAID 10 for the Database, Logs and TempDB. The edition of SQL Server 2014 is Standard.

Why are we getting more locks with READ_COMMITTED_SNAPSHOT ON?

Thanks,

4
  • When you say "more locks", what types of locks are you getting on what objects? And how does that compare versus your old system? – Ben Thul Apr 4 '16 at 20:27
  • If you run sp_whoisactive by Adam Machanic, can you see what is blocking and what query is blocked? Post the queries here. – Tom V Apr 5 '16 at 7:07
  • So I ran sp_whoisactive and I guess the blocking_session_is column shows me the locked sessions but it returned NULL in all the rows. – Blake Zero Apr 6 '16 at 18:42
  • I remember seen AUTO SHRINK when I was using sp_who2 and when AUTO SHRINK was present, everything started to lock. I have disabled the AUTO SHRINK on the database and I have not seen any locks so far. I hope that is a permanent solution. I have to monitor for at least 2 weeks. – Blake Zero Apr 6 '16 at 18:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.