Affects of Snapshot Isolation Level and Tempdb datafile full

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.

1. Sorting.  
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

      SELECT

      ses.original_login_name,

      trn.session_id,trn.transaction_id,

      rtrim(ltrim(str(elapsed_time_seconds/3600)))+' Hours '

      +rtrim(ltrim(str((elapsed_time_seconds/60)%60)))+' Minutes'

      as 'row versioning since',

      [host_name]--,last_request_start_time,last_request_end_time

      ,st.[text]

FROM

      sys.dm_tran_active_snapshot_database_transactions trn

      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

Use tempdb
Go
select
      Operation ,
      AllocUnitName ,
      [Begin Time],
      [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/