InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 12:25:20 GMT20http://www.sqlservercentral.com/Forums/Topic629370-360-1.aspxUsing the method in my previous posting, I found the culprit: it is an UPDATE trigger on a large table.Tue, 06 Jan 2009 08:02:24 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic629370-360-1.aspxThank you all for your input.According to this link - [url]http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx#E4CAC[/url] - the features generating version-store records in tempdb are:• Snapshot isolation • Read committed snapshot isolation (RCSI) • Online index build • Triggers • MARS Of all these the only one we use in our SQL instance is triggers to store information on DML operations in history tables for auditing purposes.So I think my plan will be to poll [i]sys.dm_exec_requests[/i] at the time during which this happens (seems to be a regularly occurring process) and use "OUTER APPLY sys.dm_exec_sql_text..." to get to the actual SQL running at the time. I will store this info in a table and look for the trigger operation and the table(s) involved.Ultimately, my goal is to get at the actual SQL that is causing this and the login/session_ID under which it is running.What do you guys think?Sun, 04 Jan 2009 08:04:21 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic629370-360-1.aspxDo you have a snapshot of any database as this will do the version control in tempdb.Sun, 04 Jan 2009 07:33:14 GMTTRACEY-320982http://www.sqlservercentral.com/Forums/Topic629370-360-1.aspxI'd have a look what transactions and which databases the version store is generated for. It might give me clue regarding what process may be responsible for the issue.But I never came across this issue myself, so it's pure speculation at this point.It looks like sys.dm_tran_active_snapshot_database_transactions might contain the information you are looking for.RegardsPiotrSun, 04 Jan 2009 06:59:13 GMTPiotr.Rodakhttp://www.sqlservercentral.com/Forums/Topic629370-360-1.aspx[quote][b]Piotr Rodak (1/3/2009)[/b][hr]Have you tried to select from sys.dm_tran_version_store?RegardsPiotr[/quote]I took a look at [url]http://msdn.microsoft.com/en-us/library/ms186328(SQL.90).aspx[/url]. I need to get session and task-level info on the process that gives rise to the high number of version-store pages: login name, query-text, reads etc. Not sure how to use [i]sys.dm_tran_version_store[/i] to get this info.Sat, 03 Jan 2009 21:13:23 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic629370-360-1.aspxThank you both, I'll have a look at your suggestions.Sat, 03 Jan 2009 21:03:42 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic629370-360-1.aspxHave you tried to select from sys.dm_tran_version_store?RegardsPiotrSat, 03 Jan 2009 17:05:41 GMTPiotr.Rodakhttp://www.sqlservercentral.com/Forums/Topic629370-360-1.aspxI know this doesn't describe exactly the problem that you are facing, however the concepts within this doc might help point you in the right direction[url=http://technet.microsoft.com/en-us/library/ms176029(SQL.90).aspx]http://technet.microsoft.com/en-us/library/ms176029(SQL.90).aspx[/url]Sat, 03 Jan 2009 17:00:11 GMTNicholas Cainhttp://www.sqlservercentral.com/Forums/Topic629370-360-1.aspxI have scheduled the following query to run once a minute on my instance and store the results in a table for analysis:[quote]SELECT SUM(version_store_reserved_page_count) FROM sys.dm_db_file_space_usage;[/quote]Neither one of the row-versioning isolation levels is enabled on any of the databases.At some point I get a utilization count of over 300,000 version-store pages recorded (close to 3 GB of data), but I don't know what query is causing this.How can I find out which query is the culprit next time this happens?The [i]sys.dm_db_task_space_usage[/i] and [i]sys.dm_db_session_space_usage[/i] DMVs do not provide any information on processes that trigger utilization of the version store.Sat, 03 Jan 2009 16:08:17 GMTMarios Philippopoulos