Recently, we observed the tempdb has grown out of proportion and filled up all the disk space ( 1 TB tempdb data drive).
At that moment we arent able to get shrink the tempdb as well and it was keep growing and growing.
Upon troubleshooting a little further came to know for some db's we see "is_read_committed_snapshot"
and for some db's "snapshot_isolation_state" was turned on. I used below query to pull that info.
There are around 9 databases for which these properties have been set to true.
upper(sysDB.Name) as 'Database Name',
from sys.databases sysDB
INNER JOIN sys.syslogins syslogin ON sysDB.owner_sid = syslogin.sid
where database_id > 4
and snapshot_isolation_state =1 or is_read_committed_snapshot_on = 1
Based on the scenario, I have few questions around them. If anyone has used these, kindly share your thoughts.
1. What is the difference between "read_committed_snapshot" and "snapshot_isolation" when it comes to version store behavior or do I have to turn on both for enabling row versions in tempdb to reduce blocking?
2. How can we track down the spids, queries , sizeofversion store for that particular spid which using up the version store?