同一段sql,查询分析器直接执行没有问题,放在存储过程执行会因tempdb过度增长失败

geodetic 2009-12-08 03:37:31
sql形式是:
update TableA set xxx=xxx,……
from (select field1,count(*) as xxx,……
from TableB
where ……
group by field1)B
where TableA.field1=B.field1

这样形式的sql语句共有十余条,要更新3个表,表结构类似。
TableB有600多万行数据,每个where 筛选出的结果约有30万条数据。
把sql直接放查询分析器执行,没有问题,tempdb最大增长为3G多;放在存储过程里,执行存储过程,跟踪发现,更新完第一张表之后,tempdb开始不断增长,直到增长到约15G之后,因为磁盘空间不足无法继续增长,存储过程执行失败。
开发环境是sql server 2008 sp1。

麻烦大家帮忙看看啊,帮顶有分,分不够另开新帖加,谢谢呵呵。

...全文
160 8 打赏 收藏 转发到动态 举报
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
geodetic 2009-12-09
  • 打赏
  • 举报
回复
[Quote=引用 7 楼 geodetic 的回复:]
引用 2 楼 xuam 的回复:
每更新完一个表,做一下事务提交试试!

更新语句里面没有声明任何事务,IMPLICIT_TRANSACTIONS也是off状态,这样每一个update都是自动提交的才对。
现在没有环境,明天我试试显式声明每一个update为事务试试
[/Quote]
这个方法不行
geodetic 2009-12-08
  • 打赏
  • 举报
回复
[Quote=引用 2 楼 xuam 的回复:]
每更新完一个表,做一下事务提交试试!
[/Quote]
更新语句里面没有声明任何事务,IMPLICIT_TRANSACTIONS也是off状态,这样每一个update都是自动提交的才对。
现在没有环境,明天我试试显式声明每一个update为事务试试
ChinaJiaBing 2009-12-08
  • 打赏
  • 举报
回复

用Transact-SQL 命令压缩数据库
可以使用DBCC SHRINKDATABASE 和DBCC SHRINKFILE 命令来压缩数据库。其中DBCC SHRINKDATABASE 命令对数据库进行压缩,DBCC SHRINKFILE 命令对数据库中指定的文件进行压缩。
(1) DBCC SHRINKDATABASE
DBCC SHRINKDATABASE 命令语法如下:
DBCC SHRINKDATABASE (database_name [, target_percent]
[, {NOTRUNCATE | TRUNCATEONLY}] )
各参数说明如下:
·target_percent 指定将数据库压缩后,未使用的空间占数据库大小的百分之几。如果指定的百分比过大,超过了压缩前未使用空间所占的比例,则数据库不会被压缩。并且压缩后的数据库不能比数据库初始设定的容量小。
·NOTRUECATE
将数据库缩减后剩余的空间保留在数据库,中不返还给操作系统
。如果不选择此选项,则剩余的空间返还给操作系统。
·TRUNCATEONLY
将数据库缩减后剩余的空间返还给操作系统。使用此命令时SQL Server 将文件缩减到最后一个文件分配,区域但不移动任何数据文件。选择此项后,target_percent 选项就无效了。例6-14: 压缩数据库mytest 的未使用空间为数据库大小的20%

dbcc shrinkdatabase (mytest, 20)
运行结果如下:
DBCC execution completed. If DBCC printed error
messages, contact your system administrator.
(2) DBCC SHRINKFILE
DBCC SHRINKFILE 命令压缩当前数据库中的文件。其语法如下:
DBCC SHRINKFILE ( {file_name | file_id }
{ [, target_size] |
[, {EMPTYFILE | NOTRUNCATE | TRUNCATEONLY}] } )
各参数说明如下:
·file_id
指定要压缩的文件的鉴别号(Identification number, 即ID)
。文件的ID 号可以通过 FILE_ID()函数或如本章前面所讲述
的Sp_helpdb 系统存储过程来得到。
·target_size
指定文件压缩后的大小。以MB 为单位。如果不指定此选项,SQL Server 就会尽最大可能地缩减文件。
·EMPTYFILE
指明此文件不再使用,将移动所有在此文件中的数据到同一文件组中的其它文件中去。执行带此参数的命令后,此文件就可以用ALTER DATABASE 命令来删除了。 其余参数NOTRUNCATE 和TRUNCATEONLY 与DBCC SHRINKDATABASE
命令中的含义相同。 例6-15: 压缩数据库mydb 中的数据库文件mydb_data2 的大小到1MB。 use mydb dbcc shrinkfile (mydb_data2, 1)


企业管理器里面的方法:
1、打开企业管理器
2、打开要处理的数据库
3、点击最上面菜单>工具>SQL查询分析器,打开SQL查询分析器
4、在输入窗口里面输入:
Code:
DUMP TRANSACTION [数据库名] WITH NO_LOG
BACKUP LOG [数据库名] WITH NO_LOG
DBCC SHRINKDATABASE([数据库名])

点击绿色的小三角(或按F5)执行查询,等状态栏提示处理完成
即可!

程序里面的方法:
压缩数据库日志
--1.清空日志
exec('DUMP TRANSACTION ['+@dbname+'] WITH NO_LOG')
--2.截断事务日志:
exec('BACKUP LOG ['+@dbname+'] WITH NO_LOG')
--3.收缩数据库文件(如果不压缩,数据库的文件不会减小
exec('DBCC SHRINKDATABASE(['+@dbname+'])')

  4、减小日志的方法:
一、用如下步做了:
1、DUMP TRANSACTION 庫名 WITH no_log
2、dbcc shrinkfile(logfilename)
3、收縮數據庫
4、設定自動收縮。
  二、分离数据库,删除日志文件,再附加,OK!右击数据库--所有任务--分离or 附加
  三、1、backup log 庫名 WITH no_log,2、dbcc shrinkfile(logfilename),3、收縮數據庫
4、設定自動收縮。


pbsh 2009-12-08
  • 打赏
  • 举报
回复
或者你可以不限制TempDB数据库增长。
pbsh 2009-12-08
  • 打赏
  • 举报
回复
分开提交看看吧
快乐_石头 2009-12-08
  • 打赏
  • 举报
回复
放到一個事務裡看看~~
xuam 2009-12-08
  • 打赏
  • 举报
回复
每更新完一个表,做一下事务提交试试!
--小F-- 2009-12-08
  • 打赏
  • 举报
回复
SQL Server:收缩 SQL Server Tempdb 大小 
系统管理员报告说,SQL Server Tempdb 数据库文件占用了大量的磁盘空间。让解决下。

查看 MS-SQL Tempdb 所占空间大小。
exec sp_helpdb tempdb
发现 Tempdb 数据文件大小有 10G,Tempdb 日志文件大小有 2G。

收缩 MS-SQL Tempdb 大小。
收缩 MS-SQL Tempdb 大小,我一般采用 dbcc shrinkfile() 命令。它作用在数据库文件上,可以最大限度的收缩 Tempdb 数据库。

use tempdb
go

dbcc shrinkfile(tempdev, 1024)
想把 Tempdb 数据文件(逻辑文件名为 tempdev)收缩到 1024MB。结果只能缩小到 6556 MB。

use tempdb
go

dbcc shrinkfile(templog, 512)
很顺利就把 Tempdb 日志文件大小(逻辑文件名为 tempdev)缩小到 512M 左右。

如果条件允许的话,你也可以重启 SQL Server,这样 Tempdb 就恢复到创建数据库时的初始大小了。

22,199

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧