April 8, 2016 at 9:28 am
Hello experts,
I started seeing this issue today on one of our SQL Servers and ended up restarting the SQL Server to resolve it.
the transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'.
I now see that there is a way to get information on the "culprit SPID" but before the restart is done -
Is there any way to get that information after the restart? I assume not, but figured I would post this request as a shot in the dark in case it is possible.
Thanks for any help!
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
April 8, 2016 at 11:45 am
It is critically important to gather as much forensics as possible before pulling the plug. Often with good diagnosis the sledge-hammer approach isn't even necessary. But regardless, if you can't find a root cause you can possibly build a box around it to prevent the occurrence in the future.
sp_whoisactive is a great tool. However, I have a suspicion that it will try to use tempdb for it's work so you may not get far with it. For that scenario you need a few scripts that you can fire off that will simply and directly find tempdb usage. Glenn Berry's SQL Server Diagnostics scripts could be of use, or a quick websearch can probably give you a good script.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 8, 2016 at 11:57 am
TheSQLGuru (4/8/2016)
It is critically important to gather as much forensics as possible before pulling the plug. Often with good diagnosis the sledge-hammer approach isn't even necessary. But regardless, if you can't find a root cause you can possibly build a box around it to prevent the occurrence in the future.sp_whoisactive is a great tool. However, I have a suspicion that it will try to use tempdb for it's work so you may not get far with it. For that scenario you need a few scripts that you can fire off that will simply and directly find tempdb usage. Glenn Berry's SQL Server Diagnostics scripts could be of use, or a quick websearch can probably give you a good script.
Thanks, that is what I figured (that is, once the plug is pulled, so to speak, that transaction info is gone). Good to know, though. I will need to work on compiling a checklist of things to try before going for a restart of the SQL service. I realized for starters (20/20 hindsight and all) I could have done sp_who2 (or sp_whoisactive as you mentioned) or even DBCC OPENTRAN to at least record some info on the current transactions on the server.
In this case, I did try to issue a CHECKPOINT against tempdb as one article suggested, but that statement failed. Also I did not know based on the 'ACTIVE_TRANSACTION' error what SQL work in general was being obstructed by the issue - something I will need to read up on.
Thanks again.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
April 8, 2016 at 12:31 pm
I don't think CHECKPOINT will help with a full tempdb. Maybe a transaction log (but likely not then unless there is enough room to write a bit of stuff about the checkpoint itself). But not sure it will help either with ACTIVE_TRANSACTION. Do you have a reference to the link about using that to help a full tempdb?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 8, 2016 at 12:42 pm
TheSQLGuru (4/8/2016)
I don't think CHECKPOINT will help with a full tempdb. Maybe a transaction log (but likely not then unless there is enough room to write a bit of stuff about the checkpoint itself). But not sure it will help either with ACTIVE_TRANSACTION. Do you have a reference to the link about using that to help a full tempdb?
Thanks for your follow-up assistance.
The original error message was about the log file of tempdb, btw. Although honestly, I don't know whether the data file was also full at that point.
Here is the link I found regarding CHECKPOINT.
https://sqltimes.wordpress.com/2014/07/05/sql-server-error-messages-the-transaction-log-for-database-tempdb-is-full-due-to-active_transaction/[/url]
I didn't pursue it further because I got the error below after trying it a couple of times:
"Could not write a checkpoint record in database tempdb because the log is out of space. Contact the database administrator to truncate the log or allocate more space to the database log files."
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
April 8, 2016 at 2:10 pm
Like I said, you have to have enough room to write the checkpoint stuff. 🙂
tlog of tempdb is more like it.
LOTS of potential culprits here. Got to observe during the event, although you may just start logging tempdb usage and look for bad actors BEFORE someone causes it to fill up again. Proactive can be an awesome win! If more of my clients did that I would get a lot less "we need help right now" critical support fees from them! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 8, 2016 at 4:13 pm
You've got a spill to tempdb thats not playing nicely in the park.
Do you know who the user is running the query and the actual query they're running.
If not you'll need to go digging to get more info
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 20, 2016 at 8:23 am
Perry Whittle (4/8/2016)
You've got a spill to tempdb thats not playing nicely in the park.Do you know who the user is running the query and the actual query they're running.
If not you'll need to go digging to get more info
Thanks for your reply, and sorry for my delayed response. This issue reminds me of trying to see the light go off in the refrigerator when I close the door - i.e., frustratingly elusive. I can see that one or two open connections from an app we recently deployed seems to be rapidly causing the tempdb log file space to get consumed. However, when I run sp_who2, the SPIDs are SLEEPING, and when run DBCC INPUTEBUFFER(SPID) I get only this:
EventTypeParametersEventInfo
RPC Event0NULL
By running dbcc sqlperf(logspace) from time to time I can see that in about 2 days the tempdb log file goes from about 1% used to 90+% used. If I kill the offending SPID at that point, the space used drops back to 1%. But I don't know why this is happening.
Can it be the open connection itself that causes the tempdb log file to fill? This is a Java app so that is all I can think of as of yet. But if I can run a server-side trace to catch the relevant queries in those connections that could be the culprits, I would have more evidence to present to my operations manager.
Thanks again,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
April 20, 2016 at 8:54 am
Sounds like the application is beginning a transaction and never committing/rolling back. Tracking down why is the next step.
If the activity from that particular application is low enough or you have enough headroom to tolerate the overhead, I'd use XEvents to monitor what code that application passes through.
Once the log starts getting held open by an active transaction, you can then dig through those results for the offending spid and figure out where it's going wrong.
I'd also check DBCC OPENTRAN for tempdb when the active transaction is holding the log open, so you know whether you're looking for an explicit transaction that isn't committed/rolled back or an implicit transaction.
Cheers!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply