星空sql临时表清理_星空数据库过大临时表清理

问题描述

星空临时表过多，占用大量存储空间，执行计划中临时表清理服务正常也无法减少临时表数量。

可以通过以下sql语句查看临时表占用情况

–SQL server数据库里查看临时表，执行下SQL
select * from sys.sysobjects where xtype=’u’ and name like ‘tmp%’order by crdate

–查看系统所有临时表占用的总空间
select cast(sum(a.total_pages)*8/1024 as varchar)+’ MB’ total
from sys.partitions p join sys.allocation_units a on p.partition_id = a. container_id
join sys. tables it on p.object_id = it.object_id
where it.name like ‘TMP%’

方案

星空产品有执行计划定时清理临时表，但执行计划常因IIS重启等原因处理假执行中，如果查询多，临时表也会大量生成，容易占用数据库空间，也容易导致系统慢。

所以最好的方式是建立SQL作业（开启SQL代理服务）

作业创建可以百度。如：https://blog.csdn.net/lwandlw/article/details/121496772

–SQLserver手工删临时表的方法（可在业务期间运行，推荐使用该方法删除临时表）：

 

–第一步：删除登记表中的可删除的临时表登记记录

delete from T_BAS_TEMPORARYTABLENAME where FPROCESSTYPE=1 or FCREATEDATE<DATEADD(hh,-24, GETDATE())

–第二步：删除临时表，每次删除50个、每批500个 临时表释放一次资源，可在业务运行期间执行语句

if object_id(‘temptb’,’table’)>0 drop table temptb;

declare @sql varchar(max)

declare @icount int

declare @I int

set @sql=’drop table ‘

set @i=1

select top 500 name,IDENTITY(int,1,1) id into temptb from sys.tables t where name like ‘tmp%’ and len(name)=30 and name not like ‘tmp[_]%’

and not exists(select 1 from T_BAS_TEMPORARYTABLENAME where FTABLENAME=t.name) and create_date<=DATEADD(hh,-24, GETDATE())

select @icount=@@ROWCOUNT

while @i<@icount

begin

select @sql=@sql+name+’,’ from temptb where id between @i and @i+49

if @@ROWCOUNT>0

set @sql=substring(@sql,1,len(@sql)-1)+’;’

set @i=@i+50

exec(@sql)

set @sql=’drop table ‘

end

if object_id(‘temptb’,’table’)>0 drop table temptb;