使用全局临时表的TempDB日志文件增长 [英] TempDB Log File Growth Using Global Temp Tables
问题描述
这确实是两个问题.
一个，我遇到一种现象，即使用全局临时表时SQL Server占用大量tempDB log 文件空间，而使用本地临时表时会消耗数据文件空间?
One, I'm experiencing a phenomenon where SQL server consumes a lot of tempDB log file space when using a global temp table while using a local temp table will consume data file space?
这正常吗?我在网络上找不到任何地方谈论使用全局临时表与本地临时表时以这种方式消耗日志文件空间.
Is this normal? I can't find anywhere on the web where it talks about consuming log file space in such a way when using global temp tables vs. local temp tables.
两个，如果这是预期的行为，有什么方法可以告诉它不要这样做:).我有足够的数据空间(6 GB)，但是我的日志空间受到限制(750 MB，增长有限).像往常一样，tempDB是通过Simple Recovery设置的，因此运行日志文件空间限制以前从来都不是问题...但是我从未像以前一样使用过全局临时表.
Two, if this is expected behavior, is there any way to tell it not to do this :). I have plenty of data space (6 GB), but my log space is restricted (750 MB with limited growth). As usual, the tempDB is setup with Simple Recovery so running into the log file space limit has never been a problem before ... but I've never used global temp tables like I'm using them before either.
谢谢！乔尔
推荐答案
创建任意形式的临时表(本地或全局)时，将物理创建该表并将其存储在tempdb数据库中.因此，这些表上的任何事务活动都记录在tempdb事务日志文件中.
When either form of temporary table is created (local or global) the table is physically created and stored in the tempdb database. Any transactional activity on these tables is therefore logged in the tempdb transaction log file.
每句话都没有设置，您可以实现一个物理表而不是一个临时表，以便将数据存储在用户数据库中，从而为该数据库使用关联的数据和事务日志文件.
There is no setting per say however, you could implement a physical table as opposed to a temporary table in order to store your data within a user database, thereby using the associated data and transaction log file for this database.
如果您真的想深入了解tempdb数据库，请查看以下资源.
If you really want to get stuck in and learn about the tempdb database take a look at the following resources.
这篇关于使用全局临时表的TempDB日志文件增长的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
