Affects of Snapshot Isolation Level on Tempdb
Last weekend we face an issue where we got alert due tempdb datafile is 95.05% full, which is as usual for weekend but there is one thing unusual we found that log file was only 4 % full.
We thought there could be 2 possible reasons.
2. Row versioning
Issue : The data (tempdb) is 95.05% full; >TH = 95
Free Space Available in Drive : 2 Gb free space
Tempdb file stats :
Option 1: Sorting
So we used our usual query to find transactions.
And we found some query running in batches. But when we cummulate size of tables used its not more than few GBs where as tembdb size had grown much more. Which states sorting (sorting uses tempdb)is not the culprit
Option 2: Row versioning, this could only possible with snapshot isolation level, and we found we are using Read committed. But that’s only for tempdb. And for user database its read committed snapshot
Syntax: dbcc useroptions
Output : isolation level = read committed (for tempdb)
Output : isolation level = read committed Snapshot (for user database)
We found one transaction is running from such a long time causing tempdb is getting filled using below query
rtrim(ltrim(str(elapsed_time_seconds/3600)))+' Hours '
as 'row versioning since',
inner join sys.dm_exec_sessions ses on ses.session_id=trn.session_id
JOIN sys.dm_exec_connections C
ON ses.[session_id] = c.[session_id]
LEFT OUTER JOIN sys.dm_exec_requests R
ON ses.[session_id] = R.[session_id]
CROSS APPLY sys.dm_exec_sql_text (c.[most_recent_sql_handle]) AS st
OUTER APPLY sys.dm_exec_query_plan (R.[plan_handle]) AS qp
ORDER BY elapsed_time_seconds DESC;
And we find lot of rows in”select db_name(database_id),* from sys.dm_tran_version_store" due to row versioning.
We also check transaction log and found 4 extents allocated in a batch which says whole story thats why datafile is growing much as compared to logfile.
I lost the session where i saved the allocation details of log file , but you can querry same using
[End Time] ,
Description from fn_dblog(null,null) where Description <> ''
There is one more reason why tempdb log file grows less as compare to log file of user database. In user databases ,log files will have to gothru Undo and redo activity where as in tempdb there is no redo activity required as tempdb never faces Recovery phase. When ever we restart sql server we get fresh new tempdb.
Refrence : http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/d901e9de-9fa3-44b6-a54c-23699e3c8969/