由于文件组 ‘DEFAULT’ 中的磁盘空间不足,无法为数据库 ‘TEMPDB’ 分配新页

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:由于文件组 ‘DEFAULT’ 中的磁盘空间不足,无法为数据库 ‘TEMPDB’ 分配新页

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

今天接到一个朋友的问题,sql server报“由于文件组 ‘DEFAULT’ 中的磁盘空间不足,无法为数据库 ‘TEMPDB’ 分配新页”,按照错误提示应该是tempdb空间不足,让其查看tempdb对应数据库文件大小为不到4g,查看其对应文件为自增长,硬盘空间还有40g多点,那应该没有问题。后来她说该文件所处位置的盘是fat32的,那问题确定是fat32最大支持文件大小是4g。解决办法:
1、让tempdb中的文件恢复默认值,设置其最大值
1)重启sql server服务,tempdb一般会自动变为初始化大小
2)如果1)失败,使用下面语句
dbcc shrinkfile (tempdev, 10)
dbcc shrinkfile (templog, 10)

2)然后设置tempdev和templog的最大大小为4000m

2、添加数据文件和日志文件,并设置其最大值为4000m

3、修改程序,从根源上解决问题
分析产生该问题原因
询问朋友后,原来是她对一个6kw的表和1kw的表进行关联,然后取得需要的数据插入到另一张表中,因为涉及的数据量非常大,所有导致tempdb数据库飞速增大,超过系统文件大小限制。导致文章开始的错误,光添加文件,对于这么大数据量的程序来说还是不能解决问题。应该在程序上去优化,而不是靠添加tempdb中文件的大小和数量来解决问题。
添加程序的提交次数,而不是所有数据一次提交,放在一个事物中。
我根据程序需求模拟的写了两种处理过程的方法
模拟环境:

--创建模拟表
SELECT id,name INTO t_1 FROM sys.sysobjects;
SELECT object_id id,name INTO t_2 FROM sys.columns;
--创建跟踪表
CREATE TABLE t_s (id int DEFAULT 0,date datetime DEFAULT getdate());
INSERT INTO t_s  VALUES(0,getdate());
--创建需要插入数据表
    SELECT t1.name n1,
           t2.name n2,
           t1.id
           INTO test_1
    FROM   t_1 t1,
           t_2 t2
    WHERE  t1.id = t2.id 
    AND 1=2;

方法一:
特点:1、优点执行效率比较高,无需排序和插入跟踪表
2、缺点:不能查看程序执行进度,如果异常终止,必须重新执行

create PROCEDURE [dbo].[INSERT_d_1]
AS
DECLARE @name1 varchar(4000)
DECLARE @name2 varchar(4000)
DECLARE @id int
DECLARE c1 CURSOR  
FOR
    SELECT t1.name,
           t2.name,
           t1.id
    FROM   t_1 t1,
           t_2 t2
    WHERE  t1.id = t2.id
    ORDER BY
           t1.id
open c1                       
fetch next from c1 into @name1,@name2,@id 
WHILE @@fetch_status=0
BEGIN
	INSERT INTO test_1 VALUES(@name1,@name2,@id)
    FETCH next FROM c1 INTO @name1,@name2,@id
END
CLOSE c1                  
DEALLOCATE c1

方法二:
特点:1、可以跟踪程序的执行进度查询t_s表,如果异常终止可以写其他程序继续
2、程序使用二重游标,执行效率比较低

create PROCEDURE [dbo].[INSERT_d_2]
AS
DECLARE @name1 varchar(4000)
DECLARE @name2 varchar(4000)
DECLARE @id int
DECLARE c1 CURSOR  
FOR
    SELECT t1.name,          
           t1.id
    FROM   t_1 t1
    ORDER BY t1.id

open c1                       
fetch next from c1 into @name1,@id 
WHILE @@fetch_status=0
BEGIN
	--二重循环
	DECLARE c2 CURSOR FOR 
	SELECT t2.name FROM t_2 t2 WHERE t2.id=@id
	OPEN c2
	FETCH next FROM c2 INTO @name2
	WHILE @@FETCH_STATUS=0
	BEGIN
		INSERT INTO test_1 VALUES(@name1,@name2,@id)
		UPDATE t_s SET id=@id,date=getdate()
		FETCH next FROM c2 INTO @name2
	END
	CLOSE c2
	DEALLOCATE c2
    FETCH next FROM c1 INTO @name1,@id
END
CLOSE c1                  
DEALLOCATE c1
此条目发表在 SQL Server 分类目录。将固定链接加入收藏夹。

评论功能已关闭。