{"id":474,"date":"2018-06-25T20:56:23","date_gmt":"2018-06-25T20:56:23","guid":{"rendered":"http:\/\/dbtut.com\/?p=474"},"modified":"2018-11-08T11:21:11","modified_gmt":"2018-11-08T11:21:11","slug":"which-queries-fill-the-tempdb","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2018\/06\/25\/which-queries-fill-the-tempdb\/","title":{"rendered":"Which Queries Fill The Tempdb"},"content":{"rendered":"<p>&nbsp;<\/p>\n<p>Tempdb is the most important of the system databases in terms of performance.<\/p>\n<p>Therefore, it is necessary to correctly configure tempdb in instances that use tempdb frequently.<\/p>\n<p>You can find details on how to configure tempdb in the article &#8220;<a href=\"https:\/\/dbtut.com\/index.php\/2018\/08\/08\/sql-server-system-databases\/\" target=\"_blank\" rel=\"noopener\">SQL Server System Databases<\/a>&#8220;.<\/p>\n<p>One night I got an error mail from the alerts which I have defined on the instance. The error is as follows.<\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"font-size: 10pt;\"><em>Insufficient space in tempdb to hold row versions.\u00a0 Need to shrink the version store to free up some space in tempdb. <\/em><\/span><\/p>\n<p><span style=\"font-size: 10pt;\"><em>Transaction (id=xsn=3669532288 spid=elapsed_time=) has been marked as victim and it will be rolled back if it accesses the version store. <\/em><\/span><\/p>\n<p><span style=\"font-size: 10pt;\"><em>If the problem persists, the likely cause is improperly sized tempdb or long running transactions. <\/em><\/span><\/p>\n<p><span style=\"font-size: 10pt;\"><em>Please refer to BOL on how to configure tempdb for versioning.<\/em><\/span><\/p>\n<p>&nbsp;<\/p>\n<p>This error can be caused by the filling of the tempdb. First, I right-clicked tempdb and looked at the size on the General tab.<\/p>\n<p>I saw that the size of Tempdb was 3.2 TB. Normally tempdb was not used that much.<\/p>\n<p>The first thing to come to mind in such a situation is how much of this 3.2 TB is being used right now.<\/p>\n<p>I ran the following script to determine how much of tempdb was used, and I saw that 16 GB of 3.2 TB was empty.<\/p>\n<pre class=\"lang:default decode:true\">SELECT SUM(unallocated_extent_page_count) AS [free pages], \r\n(SUM(unallocated_extent_page_count)*1.0\/128) AS [free space in MB]\r\nFROM sys.dm_db_file_space_usage;<\/pre>\n<p>&nbsp;<\/p>\n<p>This means that queries currently running are filling the TEMPDB with approximately 3.2 TB of data.<\/p>\n<p>Because, the data in tempdb is erased when the session ends, it is not kept in tempdb later.<\/p>\n<p>&nbsp;<\/p>\n<h3>What causes tempdb to fill?<\/h3>\n<ol>\n<li>Created as temp;\n<ul>\n<li>Global or local temp tables(I would recommend reading the article titled &#8220;<a href=\"http:\/\/dbtut.com\/index.php\/2018\/07\/05\/how-to-create-temp-table-on-sql-server\/\" target=\"_blank\" rel=\"noopener\">How To Create Temp Table On SQL Server<\/a>&#8220;)<\/li>\n<li>Temp stored procedures<\/li>\n<li>Table variables(I would recommend reading the &#8220;<a href=\"http:\/\/dbtut.com\/index.php\/2018\/06\/29\/what-is-table-variables\/\" target=\"_blank\" rel=\"noopener\">What is Table Variables<\/a>&#8220;)<\/li>\n<li>Cursor(I would recommend reading the\u00a0 article named &#8220;<a href=\"http:\/\/dbtut.com\/index.php\/2018\/07\/01\/cursor-usage-in-sql-server\/\" target=\"_blank\" rel=\"noopener\">Cursor Usage On SQL Server<\/a>&#8220;)<\/li>\n<\/ul>\n<\/li>\n<li>Objects created internally;\n<ul>\n<li>Spools( I would recommend reading the article named &#8220;<a href=\"http:\/\/dbtut.com\/index.php\/2018\/06\/24\/spool-concept-in-execution-planeager-spool-lazy-spool\/\" target=\"_blank\" rel=\"noopener\">Spool Concept in Execution Plan(Eager Spool, Lazy Spool)<\/a>&#8220;<\/li>\n<li>\u00a0Sorting(Sort operations in query)<\/li>\n<\/ul>\n<\/li>\n<li>Because of Row Versioning,\n<ul>\n<li>Some isolation levels use row versioning and row versioning is done in tempdb. This means that if you take the Isolation Level to Snapshot Isolation or Read Committed Snapshot Isolation Level, your tempdb database must be configured correctly and there must be enough disk space. You can read &#8220;<a href=\"http:\/\/dbtut.com\/index.php\/2018\/06\/27\/isolation-levels-1\/\" target=\"_blank\" rel=\"noopener\">Isolation Level 1<\/a>&#8220;, &#8220;<a href=\"http:\/\/dbtut.com\/index.php\/2018\/06\/28\/isolation-levels-2\/\" target=\"_blank\" rel=\"noopener\">Isolation Level 2<\/a>&#8221; and &#8220;<a href=\"http:\/\/dbtut.com\/index.php\/2018\/06\/28\/isolation-levels-3\/\" target=\"_blank\" rel=\"noopener\">Isolation Level 3<\/a>&#8221; for more detailed information about isolation levels.<\/li>\n<li>Online Index Operations<\/li>\n<li>(MARS)Multiple Active Result Sets(It is a feature that comes with SQL Server 2005. Ability to run multiple batches concurrently over a connection)<\/li>\n<li>AFTER Triggers(You can find the details in the article titled &#8220;<a href=\"http:\/\/dbtut.com\/index.php\/2018\/07\/02\/sql-server-trigger-types\/\" target=\"_blank\" rel=\"noopener\">SQL Server Trigger Types<\/a>&#8220;)<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n<p>&nbsp;<\/p>\n<h3>Is the tempdb database filled up by Row Versioning?<\/h3>\n<p>If you are using SNAPSHOT or Read Committed Snapshot Isolation Level, you should find the number of pages used by the version store with the following script.<\/p>\n<p>As you can see in the screenshot below, the tempdb usage for the version store is very low.<\/p>\n<p>We see that the problem is not this.<\/p>\n<pre class=\"lang:default decode:true\">USE tempdb\r\nSELECT SUM(version_store_reserved_page_count) AS [version store pages used],\r\n(SUM(version_store_reserved_page_count)*1.0\/128) AS [version store space in MB]\r\nFROM sys.dm_db_file_space_usage;<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"\" src=\"http:\/\/dbtut.com\/wp-content\/uploads\/2018\/06\/297-1.png\" width=\"528\" height=\"149\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>If the size of the tempdb database is full because of the version store, we need to find long running queries using the script below.<\/p>\n<pre class=\"lang:default decode:true\">SELECT * FROM sys.dm_tran_active_snapshot_database_transactions\r\nORDER BY elapsed_time_seconds DESC;<\/pre>\n<p>&nbsp;<\/p>\n<h3>Is the tempdb database filled up by internally created objects?<\/h3>\n<p>The following query shows the number and size of pages created internally in tempdb.<\/p>\n<p>The answer to my problem was this script.<\/p>\n<p>A single query filled out tempdb due to spools.<\/p>\n<p>So I wrote the article &#8220;<a href=\"http:\/\/dbtut.com\/index.php\/2018\/06\/24\/spool-concept-in-execution-planeager-spool-lazy-spool\/\" target=\"_blank\" rel=\"noopener\">Spool Concept in Execution Plan(Eager Spool, Lazy Spool)<\/a>&#8220;.<\/p>\n<pre class=\"lang:default decode:true\">Use tempdb\r\nSELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],\r\n(SUM(internal_object_reserved_page_count)*1.0\/128) AS [internal object space in MB]\r\nFROM sys.dm_db_file_space_usage;<\/pre>\n<h3><\/h3>\n<h3>Is the tempdb database filled up by temporarily created objects?<\/h3>\n<p>With the help of the following query, you can also see the size of temp objects in tempdb by created users.<\/p>\n<pre class=\"lang:default decode:true\">Use tempdb\r\nSELECT SUM(user_object_reserved_page_count) AS [user object pages used],\r\n(SUM(user_object_reserved_page_count)*1.0\/128) AS [user object space in MB]\r\nFROM sys.dm_db_file_space_usage;<\/pre>\n<p>&nbsp;<\/p>\n<p>You can also find out the currently running queries in tempdb with the help of the following script.<\/p>\n<p>First of all, you should follow the steps I mentioned above to find out why tempdb is filled.<\/p>\n<p>You can then fix the problem by finding the query you are looking for with the following script.<\/p>\n<p>In my case, a query was filling almost all of the 3.2 TB.<\/p>\n<p>When I communicated with the developer, he said that he made joins from 3 databases that are over 5 TB and he took the data for the last 3 years as a report.<\/p>\n<p>I told the developer to take the data with 3 monthly periods.<\/p>\n<p>So when each part of the query is finished running, the space created internally in tempdb will be emptied and the next piece will use that empty space.<\/p>\n<p>The problem has improved in this way.<\/p>\n<pre class=\"lang:default decode:true\">;WITH task_space_usage AS (\r\n\u00a0\u00a0\u00a0 -- SUM alloc\/delloc pages\r\n\u00a0\u00a0\u00a0 SELECT session_id,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 request_id,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SUM(internal_objects_alloc_page_count) AS alloc_pages,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SUM(internal_objects_dealloc_page_count) AS dealloc_pages\r\n\u00a0\u00a0\u00a0 FROM sys.dm_db_task_space_usage WITH (NOLOCK)\r\n\u00a0\u00a0\u00a0 WHERE session_id &lt;&gt; @@SPID\r\n\u00a0\u00a0\u00a0 GROUP BY session_id, request_id\r\n)\r\nSELECT TSU.session_id,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 TSU.alloc_pages * 1.0 \/ 128 AS [internal object MB space],\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 TSU.dealloc_pages * 1.0 \/ 128 AS [internal object dealloc MB space],\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 EST.text,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 -- Extract statement from sql text\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ISNULL(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NULLIF(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SUBSTRING(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 EST.text,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ERQ.statement_start_offset \/ 2,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CASE WHEN ERQ.statement_end_offset &lt; ERQ.statement_start_offset\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN 0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ELSE( ERQ.statement_end_offset - ERQ.statement_start_offset ) \/ 2 END\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ), ''\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ), EST.text\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ) AS [statement text],\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 EQP.query_plan\r\nFROM task_space_usage AS TSU\r\nINNER JOIN sys.dm_exec_requests ERQ WITH (NOLOCK)\r\n\u00a0\u00a0\u00a0 ON\u00a0 TSU.session_id = ERQ.session_id\r\n\u00a0\u00a0\u00a0 AND TSU.request_id = ERQ.request_id\r\nOUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST\r\nOUTER APPLY sys.dm_exec_query_plan(ERQ.plan_handle) AS EQP\r\nWHERE EST.text IS NOT NULL OR EQP.query_plan IS NOT NULL\r\nORDER BY 3 DESC;<\/pre>\n\n<div class=\"pvc_clear\"><\/div>\n<p id=\"pvc_stats_474\" class=\"pvc_stats all  \" data-element-id=\"474\" style=\"\"><i class=\"pvc-stats-icon medium\" aria-hidden=\"true\"><svg aria-hidden=\"true\" focusable=\"false\" data-prefix=\"far\" data-icon=\"chart-bar\" role=\"img\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" viewBox=\"0 0 512 512\" class=\"svg-inline--fa fa-chart-bar fa-w-16 fa-2x\"><path fill=\"currentColor\" d=\"M396.8 352h22.4c6.4 0 12.8-6.4 12.8-12.8V108.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v230.4c0 6.4 6.4 12.8 12.8 12.8zm-192 0h22.4c6.4 0 12.8-6.4 12.8-12.8V140.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v198.4c0 6.4 6.4 12.8 12.8 12.8zm96 0h22.4c6.4 0 12.8-6.4 12.8-12.8V204.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v134.4c0 6.4 6.4 12.8 12.8 12.8zM496 400H48V80c0-8.84-7.16-16-16-16H16C7.16 64 0 71.16 0 80v336c0 17.67 14.33 32 32 32h464c8.84 0 16-7.16 16-16v-16c0-8.84-7.16-16-16-16zm-387.2-48h22.4c6.4 0 12.8-6.4 12.8-12.8v-70.4c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v70.4c0 6.4 6.4 12.8 12.8 12.8z\" class=\"\"><\/path><\/svg><\/i> <img loading=\"lazy\" decoding=\"async\" width=\"16\" height=\"16\" alt=\"Loading\" src=\"https:\/\/dbtut.com\/wp-content\/plugins\/page-views-count\/ajax-loader-2x.gif\" border=0 \/><\/p>\n<div class=\"pvc_clear\"><\/div>\n","protected":false},"excerpt":{"rendered":"<p>&nbsp; Tempdb is the most important of the system databases in terms of performance. Therefore, it is necessary to correctly configure tempdb in instances that use tempdb frequently. You can find details on how to configure tempdb in the article &#8220;SQL Server System Databases&#8220;. One night I got an error mail from the alerts which &hellip;<\/p>\n<div class=\"pvc_clear\"><\/div>\n<p id=\"pvc_stats_474\" class=\"pvc_stats all  \" data-element-id=\"474\" style=\"\"><i class=\"pvc-stats-icon medium\" aria-hidden=\"true\"><svg aria-hidden=\"true\" focusable=\"false\" data-prefix=\"far\" data-icon=\"chart-bar\" role=\"img\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" viewBox=\"0 0 512 512\" class=\"svg-inline--fa fa-chart-bar fa-w-16 fa-2x\"><path fill=\"currentColor\" d=\"M396.8 352h22.4c6.4 0 12.8-6.4 12.8-12.8V108.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v230.4c0 6.4 6.4 12.8 12.8 12.8zm-192 0h22.4c6.4 0 12.8-6.4 12.8-12.8V140.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v198.4c0 6.4 6.4 12.8 12.8 12.8zm96 0h22.4c6.4 0 12.8-6.4 12.8-12.8V204.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v134.4c0 6.4 6.4 12.8 12.8 12.8zM496 400H48V80c0-8.84-7.16-16-16-16H16C7.16 64 0 71.16 0 80v336c0 17.67 14.33 32 32 32h464c8.84 0 16-7.16 16-16v-16c0-8.84-7.16-16-16-16zm-387.2-48h22.4c6.4 0 12.8-6.4 12.8-12.8v-70.4c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v70.4c0 6.4 6.4 12.8 12.8 12.8z\" class=\"\"><\/path><\/svg><\/i> <img loading=\"lazy\" decoding=\"async\" width=\"16\" height=\"16\" alt=\"Loading\" src=\"https:\/\/dbtut.com\/wp-content\/plugins\/page-views-count\/ajax-loader-2x.gif\" border=0 \/><\/p>\n<div class=\"pvc_clear\"><\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[3],"tags":[513,516,511,507,501,502,499,519,510,506,514,515,500,504,518,155,512,498,505,509,508,497,520,503,517],"class_list":["post-474","post","type-post","status-publish","format-standard","","category-mssql","tag-marsmultiple-active-result-sets","tag-after-trigger","tag-cursor","tag-global-temp-tables","tag-has-been-marked-as-victim-and-it-will-be-rolled-back-if-it-accesses-the-version-storehas-been-marked-as-victim-and-it-will-be-rolled-back-if-it-accesses-the-version-store","tag-if-the-problem-persists","tag-insufficient-space-in-tempdb-to-hold-row-versions","tag-internally-created-objects","tag-isolation-level","tag-local-temp-tables","tag-mars","tag-multiple-active-result-sets","tag-need-to-shrink-the-version-store-to-free-up-some-space-in-tempdb","tag-please-refer-to-bol-on-how-to-configure-tempdb-for-versioning","tag-read-committed-snapshot","tag-snapshot","tag-sql-server-cursor","tag-sql-server-system-databases","tag-sys-dm_db_file_space_usage","tag-table-variables","tag-temp-tables","tag-tempdb","tag-temporarily-created-objects","tag-the-likely-cause-is-improperly-sized-tempdb-or-long-running-transactions","tag-version-store"],"aioseo_notices":[],"a3_pvc":{"activated":true,"total_views":10213,"today_views":0},"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v24.9 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Which Queries Fill The Tempdb - Database Tutorials<\/title>\n<meta name=\"description\" content=\"Which Queries Fill The Tempdb\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbtut.com\/index.php\/2018\/06\/25\/which-queries-fill-the-tempdb\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Which Queries Fill The Tempdb - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"Which Queries Fill The Tempdb\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2018\/06\/25\/which-queries-fill-the-tempdb\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2018-06-25T20:56:23+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2018-11-08T11:21:11+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/dbtut.com\/wp-content\/uploads\/2018\/06\/297-1.png\" \/>\n<meta name=\"author\" content=\"dbtut\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"dbtut\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/06\/25\/which-queries-fill-the-tempdb\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/06\/25\/which-queries-fill-the-tempdb\/\"},\"author\":{\"name\":\"dbtut\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\"},\"headline\":\"Which Queries Fill The Tempdb\",\"datePublished\":\"2018-06-25T20:56:23+00:00\",\"dateModified\":\"2018-11-08T11:21:11+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/06\/25\/which-queries-fill-the-tempdb\/\"},\"wordCount\":771,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/06\/25\/which-queries-fill-the-tempdb\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/dbtut.com\/wp-content\/uploads\/2018\/06\/297-1.png\",\"keywords\":[\"(MARS)Multiple Active Result Sets\",\"AFTER Trigger\",\"Cursor\",\"global temp tables\",\"has been marked as victim and it will be rolled back if it accesses the version storehas been marked as victim and it will be rolled back if it accesses the version store\",\"If the problem persists\",\"Insufficient space in tempdb to hold row versions\",\"internally created objects\",\"Isolation Level\",\"local temp tables\",\"MARS\",\"Multiple Active Result Sets\",\"Need to shrink the version store to free up some space in tempdb\",\"Please refer to BOL on how to configure tempdb for versioning\",\"Read Committed Snapshot\",\"snapshot\",\"SQL Server Cursor\",\"sql server system databases\",\"sys.dm_db_file_space_usage\",\"Table variables\",\"temp tables\",\"Tempdb\",\"temporarily created objects\",\"the likely cause is improperly sized tempdb or long running transactions\",\"version store\"],\"articleSection\":[\"MSSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2018\/06\/25\/which-queries-fill-the-tempdb\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/06\/25\/which-queries-fill-the-tempdb\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2018\/06\/25\/which-queries-fill-the-tempdb\/\",\"name\":\"Which Queries Fill The Tempdb - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/06\/25\/which-queries-fill-the-tempdb\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/06\/25\/which-queries-fill-the-tempdb\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/dbtut.com\/wp-content\/uploads\/2018\/06\/297-1.png\",\"datePublished\":\"2018-06-25T20:56:23+00:00\",\"dateModified\":\"2018-11-08T11:21:11+00:00\",\"description\":\"Which Queries Fill The Tempdb\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/06\/25\/which-queries-fill-the-tempdb\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2018\/06\/25\/which-queries-fill-the-tempdb\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/06\/25\/which-queries-fill-the-tempdb\/#primaryimage\",\"url\":\"http:\/\/dbtut.com\/wp-content\/uploads\/2018\/06\/297-1.png\",\"contentUrl\":\"http:\/\/dbtut.com\/wp-content\/uploads\/2018\/06\/297-1.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/06\/25\/which-queries-fill-the-tempdb\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Which Queries Fill The Tempdb\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbtut.com\/#website\",\"url\":\"https:\/\/dbtut.com\/\",\"name\":\"Database Tutorials\",\"description\":\"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux\",\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbtut.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/dbtut.com\/#organization\",\"name\":\"dbtut\",\"url\":\"https:\/\/dbtut.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"width\":223,\"height\":36,\"caption\":\"dbtut\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\",\"name\":\"dbtut\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"caption\":\"dbtut\"},\"description\":\"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.\",\"sameAs\":[\"http:\/\/NurullahCAKIR\"],\"url\":\"https:\/\/dbtut.com\/index.php\/author\/dbtut\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Which Queries Fill The Tempdb - Database Tutorials","description":"Which Queries Fill The Tempdb","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/dbtut.com\/index.php\/2018\/06\/25\/which-queries-fill-the-tempdb\/","og_locale":"en_US","og_type":"article","og_title":"Which Queries Fill The Tempdb - Database Tutorials","og_description":"Which Queries Fill The Tempdb","og_url":"https:\/\/dbtut.com\/index.php\/2018\/06\/25\/which-queries-fill-the-tempdb\/","og_site_name":"Database Tutorials","article_published_time":"2018-06-25T20:56:23+00:00","article_modified_time":"2018-11-08T11:21:11+00:00","og_image":[{"url":"http:\/\/dbtut.com\/wp-content\/uploads\/2018\/06\/297-1.png","type":"","width":"","height":""}],"author":"dbtut","twitter_card":"summary_large_image","twitter_misc":{"Written by":"dbtut","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2018\/06\/25\/which-queries-fill-the-tempdb\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/06\/25\/which-queries-fill-the-tempdb\/"},"author":{"name":"dbtut","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408"},"headline":"Which Queries Fill The Tempdb","datePublished":"2018-06-25T20:56:23+00:00","dateModified":"2018-11-08T11:21:11+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/06\/25\/which-queries-fill-the-tempdb\/"},"wordCount":771,"commentCount":0,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/06\/25\/which-queries-fill-the-tempdb\/#primaryimage"},"thumbnailUrl":"http:\/\/dbtut.com\/wp-content\/uploads\/2018\/06\/297-1.png","keywords":["(MARS)Multiple Active Result Sets","AFTER Trigger","Cursor","global temp tables","has been marked as victim and it will be rolled back if it accesses the version storehas been marked as victim and it will be rolled back if it accesses the version store","If the problem persists","Insufficient space in tempdb to hold row versions","internally created objects","Isolation Level","local temp tables","MARS","Multiple Active Result Sets","Need to shrink the version store to free up some space in tempdb","Please refer to BOL on how to configure tempdb for versioning","Read Committed Snapshot","snapshot","SQL Server Cursor","sql server system databases","sys.dm_db_file_space_usage","Table variables","temp tables","Tempdb","temporarily created objects","the likely cause is improperly sized tempdb or long running transactions","version store"],"articleSection":["MSSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2018\/06\/25\/which-queries-fill-the-tempdb\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2018\/06\/25\/which-queries-fill-the-tempdb\/","url":"https:\/\/dbtut.com\/index.php\/2018\/06\/25\/which-queries-fill-the-tempdb\/","name":"Which Queries Fill The Tempdb - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/06\/25\/which-queries-fill-the-tempdb\/#primaryimage"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/06\/25\/which-queries-fill-the-tempdb\/#primaryimage"},"thumbnailUrl":"http:\/\/dbtut.com\/wp-content\/uploads\/2018\/06\/297-1.png","datePublished":"2018-06-25T20:56:23+00:00","dateModified":"2018-11-08T11:21:11+00:00","description":"Which Queries Fill The Tempdb","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/06\/25\/which-queries-fill-the-tempdb\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2018\/06\/25\/which-queries-fill-the-tempdb\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/index.php\/2018\/06\/25\/which-queries-fill-the-tempdb\/#primaryimage","url":"http:\/\/dbtut.com\/wp-content\/uploads\/2018\/06\/297-1.png","contentUrl":"http:\/\/dbtut.com\/wp-content\/uploads\/2018\/06\/297-1.png"},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2018\/06\/25\/which-queries-fill-the-tempdb\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"Which Queries Fill The Tempdb"}]},{"@type":"WebSite","@id":"https:\/\/dbtut.com\/#website","url":"https:\/\/dbtut.com\/","name":"Database Tutorials","description":"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux","publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbtut.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/dbtut.com\/#organization","name":"dbtut","url":"https:\/\/dbtut.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","width":223,"height":36,"caption":"dbtut"},"image":{"@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408","name":"dbtut","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","caption":"dbtut"},"description":"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.","sameAs":["http:\/\/NurullahCAKIR"],"url":"https:\/\/dbtut.com\/index.php\/author\/dbtut\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/474","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/comments?post=474"}],"version-history":[{"count":0,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/474\/revisions"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=474"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=474"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=474"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}