blocks|key|2796939|text|Tempdb是这样成长的，因为有东西在使用它。只要这种情况持续下去，任何修复、清理或操作tempdb的尝试都不会起作用。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2796940|要解决这个问题，你需要找出是什么导致它长成这样，然后首先修复这个问题。仅凭这一点就可以解决整个问题，并消除您对tempdb做任何其他操作的需要。根据我的经验，这种情况最有可能的原因是无法控制的交叉连接查询(尽管还有很多其他可能性)。|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|E|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|F|8|@]|9|@]|A|$]]]|D|$]]

Tempdb is growing like this because something is using it. No attempt to fix, clean or manipulate tempdb is going to work as long as this is going on.

To address this problem, you need to find out what is causing it to grow like this and then fix that problem first. This alone may resolve the whole issue and remove your need to do anything else with tempdb. In my experience, the most likely cause for something like this is an out-of-control Cross Join query (though there are lots of other possibilities).

blocks|key|2796921|text|我们最近也面临着同样的问题。我找到了一个很好的解决方案：清洗诱饵|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|2796922|2796923|这肯定对你有帮助。万事如意!！|entityMap|0|LINK|mutability|MUTABLE|url|http://potomac9499.wordpress.com/2007/12/31/cleaning-up-tempdb/^0|S|4|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]]|C|$]]|$1|D|3|-4|5|6|7|R|8|@]|9|@]|C|$]]|$1|E|3|F|5|6|7|S|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]]]

We recently faced the same issue. I got a marvellous solution over here : <a href="http://potomac9499.wordpress.com/2007/12/31/cleaning-up-tempdb/" rel="nofollow">Cleaning tempdb</a> 
This will definitely help you. All the best !!

blocks|key|1656584|text|共享查询以获取使用量大小。比MS+kb查询简单多了。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1656585|use+tempdb
go;

SELECT+sum(unallocated_extent_page_count)+[Free_Pages],
+(sum(unallocated_extent_page_count)*1.0/128)+[Free_Space_MB]
FROM+sys.dm_db_file_space_usage;|code-block|syntax|javascript|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|I|8|@]|9|@]|A|$E|F]]]|G|$]]

Sharing a query to get the usage sizing. Little easier than MS kb query. 

<pre><code>use tempdb
go;

SELECT sum(unallocated_extent_page_count) [Free_Pages],
 (sum(unallocated_extent_page_count)*1.0/128) [Free_Space_MB]
FROM sys.dm_db_file_space_usage;
</code></pre>

blocks|key|2797179|text|开发一个日志分析器工具，并在查询它和生成报告之后，Tempdb很快就会满员。|type|blockquote|depth|inlineStyleRanges|entityRanges|data|2797180|根据您的工具在生成报表时所做的事情--创建大量的tempdb表，在使用排序操作符时将数据泄漏到tempdb，等等，我建议您预先调整tempdb的大小。|unstyled|2797181|最好优化生成报表的SQL。可能会有适当的指数/S肯定会有帮助。|2797182|只查询所需的数据始终是最佳做法。|2797183|从分析等待状态开始。|offset|length|2797184|如果你看到大量的PAGELATCH等待，然后添加更多的数据文件。确保文件大小相同，因为Server使用比例填充算法填充数据文件。|unordered-list-item|2797185|启用跟踪标志1117+(在FILEGROUP中平等地增长所有文件)和1118+(仅为完全区段)+)。|2797186|您可以使用服务提供商来分析服务器实例上发生的事情。|2797187|下面的查询将帮助您识别大量使用tempdb的会话：|2797188|/*
Lists+the+TempDB+usage+per+each+active+session.
It+helps+identifying+the+sessions+that+use+the+tempdb+heavily+with+internal+objects.

When+the+internal+objects+usage+is+high,+the+session+is+probably+using+big+hash+tables+or+spooling+in+worktables.+It+could+be+a+symptom+of+an+inefficient+plan+or+a+missing+index.

Shrinking+a+TempDB+full+of+internal+objects+will+probably+have+no+effect,+because+the+engine+will+not+release+the+deallocated+space.+

Ref:+https://dba.stackexchange.com/a/19871/8783
*/


;WITH+task_space_usage+AS+(
++++--+SUM+alloc/delloc+pages
++++SELECT+session_id,
+++++++++++request_id,
+++++++++++SUM(internal_objects_alloc_page_count)+AS+alloc_pages,
+++++++++++SUM(internal_objects_dealloc_page_count)+AS+dealloc_pages
++++FROM+sys.dm_db_task_space_usage+WITH+(NOLOCK)
++++WHERE+session_id+<>+@@SPID
++++GROUP+BY+session_id,+request_id
)
SELECT+TSU.session_id,
+++++++TSU.alloc_pages+*+1.0+/+128+AS+[internal+object+MB+space],
+++++++TSU.dealloc_pages+*+1.0+/+128+AS+[internal+object+dealloc+MB+space],
+++++++EST.text,
+++++++--+Extract+statement+from+sql+text
+++++++ISNULL(
+++++++++++NULLIF(
+++++++++++++++SUBSTRING(
+++++++++++++++++++EST.text,+
+++++++++++++++++++ERQ.statement_start_offset+/+2,+
+++++++++++++++++++CASE+WHEN+ERQ.statement_end_offset+<+ERQ.statement_start_offset+THEN+0+ELSE(+ERQ.statement_end_offset+-+ERQ.statement_start_offset+)+/+2+END
+++++++++++++++),+''
+++++++++++),+EST.text
+++++++)+AS+[statement+text],
+++++++EQP.query_plan
FROM+task_space_usage+AS+TSU
---+Changed+from+inner+join+to+left+outer+join+to+return+rows+for+sessions+that+aren't+currently+actively+running+queries.
left+outer+join+sys.dm_exec_requests+ERQ+WITH+(NOLOCK)
++++ON++TSU.session_id+=+ERQ.session_id
++++AND+TSU.request_id+=+ERQ.request_id
OUTER+APPLY+sys.dm_exec_sql_text(ERQ.sql_handle)+AS+EST
OUTER+APPLY+sys.dm_exec_query_plan(ERQ.plan_handle)+AS+EQP
WHERE+EST.text+IS+NOT+NULL+OR+EQP.query_plan+IS+NOT+NULL
ORDER+BY+3+DESC,+5+DESC|code-block|syntax|javascript|2797189|除了重新启动服务器之外，是否有任何解决方案可以及时清除microsoft+2008中的tempdb文件？|2797190|在生产服务器上小心使用它!!|header-two|2797191|重新启动服务的唯一可能选择是运行|2797192|DBCC+FREESYSTEMCACHE('ALL')--这将清除所有缓存的对象，不仅包括内部对象，还包括缓存的查询计划。DBCC+FREESYSTEMCACHE+('tempdb')+--清除DBCC+FREESYSTEMCACHE+('Temporary+Tables+&+Table+Variables')的缓存--清除所有临时表和变量。|style|CODE|2797193|另外，请参阅FreeProcCache与FreeSystemCache的区别|entityMap|0|LINK|mutability|MUTABLE|url|http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/|1|http://www.sqlskills.com/blogs/paul/the-accidental-dba-day-27-of-30-troubleshooting-tempdb-contention/|2|http://www.brentozar.com/archive/2014/06/trace-flags-1117-1118-tempdb-configuration/|3|http://whoisactive.com/|4|https://dba.stackexchange.com/a/56855/8783^0|0|0|0|0|1|6|0|0|5|Q|1|0|6|17|2|0|5|5|3|0|0|0|0|0|0|0|R|1P|V|2P|1N|0|6|W|4^^$0|@$1|2|3|4|5|6|7|1S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|1T|8|@]|9|@]|A|$]]|$1|E|3|F|5|D|7|1U|8|@]|9|@]|A|$]]|$1|G|3|H|5|D|7|1V|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|1W|8|@]|9|@$K|1X|L|1Y|1|1Z]]|A|$]]|$1|M|3|N|5|O|7|20|8|@]|9|@$K|21|L|22|1|23]]|A|$]]|$1|P|3|Q|5|O|7|24|8|@]|9|@$K|25|L|26|1|27]]|A|$]]|$1|R|3|S|5|D|7|28|8|@]|9|@$K|29|L|2A|1|2B]]|A|$]]|$1|T|3|U|5|D|7|2C|8|@]|9|@]|A|$]]|$1|V|3|W|5|X|7|2D|8|@]|9|@]|A|$Y|Z]]|$1|10|3|11|5|6|7|2E|8|@]|9|@]|A|$]]|$1|12|3|13|5|14|7|2F|8|@]|9|@]|A|$]]|$1|15|3|16|5|D|7|2G|8|@]|9|@]|A|$]]|$1|17|3|18|5|6|7|2H|8|@$K|2I|L|2J|19|1A]|$K|2K|L|2L|19|1A]|$K|2M|L|2N|19|1A]]|9|@]|A|$]]|$1|1B|3|1C|5|D|7|2O|8|@]|9|@$K|2P|L|2Q|1|2R]]|A|$]]]|1D|$1E|$5|1F|1G|1H|A|$1I|1J]]|1K|$5|1F|1G|1H|A|$1I|1L]]|1M|$5|1F|1G|1H|A|$1I|1N]]|1O|$5|1F|1G|1H|A|$1I|1P]]|1Q|$5|1F|1G|1H|A|$1I|1R]]]]

<blockquote>
developing a log analyzer tool and After queering it and generating reports Tempdb is getting full very quickly .
</blockquote>
Depending on what your tool does when generating reports - creates lot of tempdb tables, spills data to tempdb when using sort operators, etc, I would suggest you to presize your tempdb.
You are better off optimizing the SQL that is generating the report. May be putting proper index/s will definitely help.
Its always a best practice to only query the data that you require.
Start by <a href="http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/" rel="nofollow noreferrer">analyzing wait stats</a>.
<ul>
<li>If you are seeing <a href="http://www.sqlskills.com/blogs/paul/the-accidental-dba-day-27-of-30-troubleshooting-tempdb-contention/" rel="nofollow noreferrer">lots of PAGELATCH waits, then add more tempdb data files</a>. make sure to keep the files equally sized as SQL Server uses proportional fill algorithm to fill up the data files.</li>
<li>Enable Trace Flags <a href="http://www.brentozar.com/archive/2014/06/trace-flags-1117-1118-tempdb-configuration/" rel="nofollow noreferrer">1117 (GROW ALL FILES IN A FILEGROUP EQUALLY) and 1118 (FULL EXTENTS ONLY)</a>.</li>
</ul>
You can use <a href="http://whoisactive.com" rel="nofollow noreferrer">sp_whoisactive</a> to analyze whats going on your server instance.
Below query will help you to identify the sessions that use the tempdb heavily :
<pre><code>/*
Lists the TempDB usage per each active session.
It helps identifying the sessions that use the tempdb heavily with internal objects.

When the internal objects usage is high, the session is probably using big hash tables or spooling in worktables. It could be a symptom of an inefficient plan or a missing index.

Shrinking a TempDB full of internal objects will probably have no effect, because the engine will not release the deallocated space. 

Ref: https://dba.stackexchange.com/a/19871/8783
*/


;WITH task_space_usage AS (
 -- SUM alloc/delloc pages
 SELECT session_id,
 request_id,
 SUM(internal_objects_alloc_page_count) AS alloc_pages,
 SUM(internal_objects_dealloc_page_count) AS dealloc_pages
 FROM sys.dm_db_task_space_usage WITH (NOLOCK)
 WHERE session_id &lt;&gt; @@SPID
 GROUP BY session_id, request_id
)
SELECT TSU.session_id,
 TSU.alloc_pages * 1.0 / 128 AS [internal object MB space],
 TSU.dealloc_pages * 1.0 / 128 AS [internal object dealloc MB space],
 EST.text,
 -- Extract statement from sql text
 ISNULL(
 NULLIF(
 SUBSTRING(
 EST.text, 
 ERQ.statement_start_offset / 2, 
 CASE WHEN ERQ.statement_end_offset &lt; ERQ.statement_start_offset THEN 0 ELSE( ERQ.statement_end_offset - ERQ.statement_start_offset ) / 2 END
 ), ''
 ), EST.text
 ) AS [statement text],
 EQP.query_plan
FROM task_space_usage AS TSU
--- Changed from inner join to left outer join to return rows for sessions that aren't currently actively running queries.
left outer join sys.dm_exec_requests ERQ WITH (NOLOCK)
 ON TSU.session_id = ERQ.session_id
 AND TSU.request_id = ERQ.request_id
OUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST
OUTER APPLY sys.dm_exec_query_plan(ERQ.plan_handle) AS EQP
WHERE EST.text IS NOT NULL OR EQP.query_plan IS NOT NULL
ORDER BY 3 DESC, 5 DESC
</code></pre>
<blockquote>
Is there any solution to clean the tempdb file in microsoft sql server 2008 on a timely basis other than restarting the server ?
</blockquote>
Use it carefully on a production server !!
The only possible alternative to restarting the service, is running
<blockquote>
<code>DBCC FREESYSTEMCACHE('ALL')</code>-- that will clear all cached objects, including not only internal objects, but also cached query plans.
<code>DBCC FREESYSTEMCACHE ('tempdb')</code> -- clears cache for tempdb
<code>DBCC FREESYSTEMCACHE ('Temporary Tables &amp; Table Variables')</code> -- clears all the temp table and variables
</blockquote>
Also, refer to <a href="https://dba.stackexchange.com/a/56855/8783">Difference between FreeProcCache and FreeSystemCache</a>

blocks|key|2796881|text|如果您有一个针对DB的夜间维护窗口，最简单的方法是将数据库从完整日志更改为简单，然后再更改为FULL。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2796882|所有其他方法(例如http://support.microsoft.com/kb/307487+)都过于复杂，但您可能还是不得不考虑它们。|offset|length|entityMap|0|LINK|mutability|MUTABLE|url|http://support.microsoft.com/kb/307487^0|0|9|12|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@$D|O|E|P|1|Q]]|A|$]]]|F|$G|$5|H|I|J|A|$K|L]]]]

If you have a nightly maintenance window for the DB the simplest method is to change your database from FULL logging to SIMPLE and then back to FULL again.

All other methods, e.g. <a href="http://support.microsoft.com/kb/307487" rel="nofollow">http://support.microsoft.com/kb/307487</a>
are overly complicated but you may be forced to consider them nonetheless.

blocks|key|2797284|text|您看到的是temp的默认大小，而不是使用的大小。您可以重新启动SQL服务器服务以清理临时数据库。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|entityMap^0^^$0|@$1|2|3|4|5|6|7|C|8|@]|9|@]|A|$]]]|B|$]]

you are seeing the default size of temp DB but not the consumed size . you may restart the SQL server services to clean the Temp DB.

<img src="https://i.stack.imgur.com/vsEPD.jpg" alt="snapshot of window and space">I am developing a log analyzer tool and After queering it and
generating reports <code>Tempdb</code> is getting full very quickly . 

Is there any solution to clean the <code>tempdb</code> file in microsoft sql server
2008 on a timely basis other than restarting the server ? 

Thank you so much in Advance

Tempdb is getting full very quickly in microsoft sql server 2008

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

📷我正在开发一个日志分析器工具，并在它和生成报告后，Tempdb变得非常迅速。除了重新启动服务器之外，是否有任何解决方案可以及时清除microsoft 2008中的tempdb文件？提前谢谢你

问在microsoft sql server 2008中，Tempdb正在迅速满员。
EN

回答 6

Database Administration用户

Database Administration用户

Database Administration用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在microsoft sql server 2008中，Tempdb正在迅速满员。EN

回答 6

Database Administration用户

Database Administration用户

Database Administration用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在microsoft sql server 2008中，Tempdb正在迅速满员。
EN