失败SQL查询磁盘空间不足 [英] Failure SQL query insufficient disk space

问题描述

信息1101，级别17，状态10，第12行 由于文件组"DEFAULT"中的磁盘空间不足，无法为数据库"TEMPDB"分配新页面.通过在文件组中放置对象，向文件组添加其他文件或为文件组中的现有文件设置自动增长来创建必要的空间.

Msg 1101, Level 17, State 10, Line 12 Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

这用简单的英语是什么意思.

What does this mean in plain English.

推荐答案

我发现，TempDB爆炸式增长的正常原因是查询，无论是临时查询还是存储过程查询，都存在意想不到的问题-多对联接，其中一些被称为意外交叉联接".在幕后，它可以创建数十亿个内部行，最终存储在TempDB中的工作"表中.

I've found that the normal cause of such explosive growth of TempDB is a query, either ad hoc or in a stored procedure, that has an unexpected many-to-many join in it that some refer to as an "Accidental Cross Join". Behind the scenes, it can create litterally billions of internal rows that end up living in "work" tables that live in TempDB.

解决方法不是简单地分配更多的磁盘空间.解决方法是查找导致问题的原因并进行修复.否则，您将陷入无休止的循环，必须重新启动SQL Server等.

The fix isn't to simply allocate more disk space. The fix is to find which query is the cause of the problem and fix it. Otherwise, you'll be stuck in a never ending cycle of having to restart SQL Server, etc, etc.

而且，不...您不必检查TempDB是否处于简单"恢复模式，因为您无法将其设置为其他任何模式.试试看.

And, no... you don't have to check to see if TempDB is in the "SIMPLE" recovery mode because you can't set it to anything else. Try it and see.

这篇关于失败SQL查询磁盘空间不足的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！