MSSQL tempdb日志空间不足

• 报错日志

"The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.

• 处理步骤

1. 设置temp file的initial size
   查看initial size

select size/128 as initialsize,* from sys.master_files 

where  database_id=2

order by name

查看实际分配空间

select size/128 as initialsize,

       CONVERT(decimal(10,2),A.SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS int)/128.0)) as usedspace,*

From   tempdb.sys.database_files as a

order by name

修改initial size

alter database tempdb modify file(name='tempdev_05n_ndf2',size=512mb)

2. 重启sql 服务

net stop mssqlserver
net start mssqlserver

3. 打开SQL Agent

有时候Agent会无法启用

这时候执行   EXEC SP_CONFIGURE'Agent XPs'
显示运行值被设置为0

更改运行值为1
EXEC SP_CONFIGURE'Agent XPs',1
GO
RECONFIGURE

再尝试start Agent

  Agent参见：https://www.cnblogs.com/gered/p/9131961.html