March 14, 2020 at 7:32 am
Hi Experts,
Need your help for handling the unexpected error, Called
"The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'."
and i am not able to work on SQL Server , unless i have to Re-Start the Services or Server.
Once i have restarted the Server/Services My tempDB Log file is 1 MB in Size, what i am missing and what Proper action i have take to handle this issue.
Thank you in advance
Mohamad Feroz Patel
Patel Mohamad
March 15, 2020 at 8:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
March 16, 2020 at 10:07 am
You can use XE to capture filegrowths
https://www.brentozar.com/archive/2015/12/tracking-tempdb-growth-using-extended-events/
March 16, 2020 at 10:22 am
The issue with the log file on tempdb will be either a very big transaction using a temp table, or an uncommitted transaction that is using tempdb, which is causing the log to not be able to truncate.
one of the tricks that i use is
USE TEMPDB
dbcc opentran()
this will give you the spid of the oldest transaction in tempdb
you'll get something like
Transaction information for database 'tempdb'.
Oldest active transaction:
SPID (server process ID): 1249
UID (user ID) : -1
Name : sort_init
LSN : (27574:91949:77)
Start time : Mar 16 2020 10:14:48:203AM
SID : 0x62cca564ddf02749b9c5c7ebff1b9de6
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Completion time: 2020-03-16T10:16:18.4361790+00:00
then you can trace that back using sp_who2 and use the Kill command (rather than reboot the server)
also look at the Name field - in the example above it is a sort in tempdb... but google those and you will understand it quite well.
what i would really strongly recommend is that you monitor the space used in your t-log file and alert you at 80% full and then you can gather the info on the spid before it become critical.
Hope this helps
MVDBA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply