tempdb无法收缩。使用 DBCC FREESYSTEMCACHE 解决

【1】tempdb无法收缩

tempdb没规划好,设计在C盘,快把C盘撑爆了!

【1.1】无法收缩信息

  疯狂运行下图,都没什么用。发现MDF 12G多,死活无法收缩。

    

【1.2】查看tempdb情况

(1)查看tempdb属性

  我们可以发现,tempdb整个库大小12G多,且可用空间几乎99%,按道理应该可以收缩的

    

   查看文件选项,发现文件居然只显示初始大小.....

     

 

 (2)查看连接

  sysprocesses(如果有很多连接,删连接后再收缩也是可以试试的)

    

   requests

    

发现均没有相关进程使用tempdb,这这这,搞事情嘛。

【2】分析

【2.1】基本分析

tempdb详细基本概念参考:https://www.cnblogs.com/gered/p/10670668.html

tempdb一般存储这些:

  

 但既然已经变成了可用空间,按道理这些相关缓存信息就过期没有使用了啊。真叫人头秃。

 最后只能怀疑是缓存问题了。

【2.2】使用 dbcc freesystemcache('all') 应急解决

(1)使用 FREESYSTEMCACHE 的风险:

官网参考:https://docs.microsoft.com/zh-cn/sql/t-sql/database-console-commands/dbcc-freesystemcache-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15

  从所有缓存中释放所有未使用的缓存条目。 

    SQL Server 数据库引擎会事先在后台清理未使用的缓存条目,以使内存可用于当前条目。

    但是,可以使用此命令从每个缓存中或者从指定的 Resource Governor 池缓存中手动删除未使用的条目。

  运行 DBCC FREESYSTEMCACHE 可清除 SQL Server 实例的计划缓存。 清除计划缓存将导致对所有即将到来的执行计划进行重新编译,并可能导致查询性能暂时性地突然降低。

     对于计划缓存中的每个已清除缓存存储,SQL Server 错误日志都包含以下信息性消息:“SQL Server 刷新了 %d 次(计划缓存中的)'%s' 缓存存储,因为有 'DBCC FREEPROCCACHE' 或 'DBCC FREESYSTEMCACHE' 操作。

    ” 每隔五分钟,只要缓存在这段时间间隔内得到刷新,此消息就记录一次。

  有一个疑问

     比如有个执行计划缓存,使用频率不是特别高,比如半分钟运行一次。。

    执行 DBCC FREESYSTEMCACHE的时候,这个执行计划缓存没在使用,那是不是也清掉了? (暂时还未得可知)
 

(2) dbcc freesystemcache('all') 应急解决tempdb无法收缩

使用如下代码,收缩成功。

USE [tempdb]
GO
DBCC FREESYSTEMCACHE ('ALL')
GO

USE [TEMPDB]
GO
DBCC SHRINKFILE('TEMPDEV',1024)
GO

【2.3】后续优化迁移走TEMPDB到其他盘符

(1)基本参考

use master
go
alter database tempdb modify file (name=tempdev,filename='d:\database\TempDB.mdf',size=1000MB,FILEGROWTH = 100MB)
go
alter database tempdb modify file(name=templog,filename='d:\database\TempLog.ldf',size=1000MB,FileGrowth=100MB)
go

 (2)最佳实践 

use tempdb
go
dbcc shrinkfile('tempdev',1000);
dbcc shrinkfile('templog',1000);
use db_tank;
declare @path Nvarchar(500),@path_mdf  Nvarchar(500),@path_ldf  Nvarchar(500),@sql nvarchar(4000);
select @path=physical_name from sys.database_files where type_desc='rows';
set @path=CAST(left(@path,len(@path)-charindex('\',reverse(@path))+1) AS NVARCHAR);
set @path_mdf=@path+N'tempdb.mdf';
set @path_ldf=@path+N'templog.ldf';
print @path+','+@path_mdf+','+@path_ldf
set @sql='
alter database tempdb modify file( name =''tempdev'',filename='''+@path_mdf+''',filegrowth=128MB);
alter database tempdb modify file( name =''templog'',filename='''+@path_ldf+''',filegrowth=128MB);
'
exec(@sql)

 

参考文档

  联机丛书:DBCC FREESYSTEMCACHE (Transact-SQL)

  感谢:sql server大佬群中的两位大佬的指点:烈火寒冰JT 与  八风不动

posted @ 2020-01-10 11:28  郭大侠1  阅读(2548)  评论(0编辑  收藏  举报