tempdb log file growing abnormally and unable to shrink log file.
In my environment i came across these scenarios where tempdb log file is growing abnormally and unable to shrink, and we can run below queries to find that out. Sometimes we can find out active transaction value under log_reusage_wait description under sys.databases view.
use
tempdb
go
select
log_reuse_wait,log_reuse_wait_desc,
* from sys.databases
/*
To find out is there any open transactions are
existing
*/
DBCC
OPENTRAN('tempdb')
/*
To find that out through 3rd party tool
*/
sp_whoisactive
And sometimes we can find out negative spid', with sort operation like info, that might be due to ORDER BY clause in a SELECT query,some times even this is causing to tempdb file growth and we are unable to shrink the log file of growth of tempdb.
https://blogs.msdn.microsoft.com/psssql/2012/09/08/revisiting-inside-tempdb/
Comments