What is 3926 error?
Pavel
I don't do any criminal. I don't use bind sessions also.
But with growing my SQL2000 server’s workload I've started
to receive this errors. What does it mean? How should I
process it?
Thanks
EB
3926 10 The transaction active in this session has been committed or
aborted by another session.
I've no actual clue as to what to look for next. My guess would be some
process is being killed, either explicitly (kill) or by deadlock, if indeed
your transaction has been aborted. If another session commits your
transaction, I would like to hear an explanation of how this can be
possible.
Good luck,
EB
"Pavel" <p...@sbrf.ru> schreef in bericht
news:0bc001c3a214$7570fad0$a501...@phx.gbl...
Billy Yao [MSFT]
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with your issue.
From your description, I understand that you wonder what the error 3926 message actually
means. I think EB has pointed out this and I'm not focusing on how to solve the problem.
However, I'm unsure of what changes have you made after your grew your SQL Server's
workload. Could you specify it with detailed information?
As well as EB's suspicion, I also take into account that the symptom may be related to
server's machine account issue. But I'm not sure about this only based on the 3926 error.
Therefore, please also post the accurate error message you received for our further
troubleshooting.
All our efforts will make things clear and move closer to the causes and resolutions. Thanks in
advance! If there is anything more I can also assist you with, please feel free to post it in the
group.
Best regards,
Billy Yao
Microsoft Online Partner Support
----------------------------------------------------
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.
Pavel
I'm still interested in the problem because it relates to
my production SQL server.
I noticed, that error usually occurs in moments of high
workload. My software has a lot of threads and each of it
has it’s own database connect. Thread with error request
has following request logic:
1. The thread execute the request.
2. The request time out (10sec).
3. The thread detect SQL error, close session and
disconnect from database at all.
4. The thread reestablish connection to the SQL
server and try to execute request again.
5. At this moment the thread can receive error 3629,
then it go to 3.
Sometime there are some (up to 5-7) cycles occur until
request is executed successfully. My request is a stored
procedure call. Procedure has a transaction inside.
Error description: Level 10, “The transaction active in
this session has been
committed or aborted by another session”
I really don’t know what it mean and what is solution of
it?
Good luck!
>.
>
Billy Yao [MSFT]
Thank you for your update and detailed information!
Things are now clearer, and I think the issue is somewhat related to a DTC transaction
problem because DTC transaction has maybe timed out and this causes the thread to close
the session, disconnect from the database, and reestablish connection later.
To troubleshoot this problem in case the error occurs next time, you can perform the following
actions:
1. Check the Transaction Timeout Setting:
In Component Services, navigate to My Computer -> Properties (Right click) -> Options tab,
then check the transaction timeout setting (60 seconds by default). If there will be long-live
transactions (or high workload of transactions), we'd better enlarge this number.
2. After changing the Transaction timeout setting, Check the code of the following
component:
a) Check the ConnectionTimeout property of the connection object and CommandTimeout
property of the command object.
The Default value of CommandTimeout is 30, however if this value is set to 0 (infinite) or a
number larger than the Transaction timeout setting, the transaction may expire before the
command timeout, when executing time-consuming statements. Thus, this may be the cause
of the error.
b) Check to see if there are any time-consuming statements, such as sleep(), etc.
3. Detect possible deadlocks:
Although it seems that there are no deadlocks between your transactions, it's better to run the
following statements in Query Analyzer:
DBCC traceon
For more information, please refer to Books Online on the topic"DBCC traceon"
Pavel, please apply my suggestion above and let me know if it helps you resolve your
problem.
If the symptom still exists, please send the error message to v-bi...@microsoft.com for
further troubleshooting and researching. If there is anything more I can assist you with, please
feel free to post it in the newsgroup.
Billy Yao [MSFT]
Thank you for your detailed and logical information. It clarifies a lot, especially the specific
error message returned from ADO and I'm clear of the environments on your side. Thank you
so much!
I've received your error log and reviewed it carefully, but I also find nothing strange inside.
After discussing with some of our team members and consulting our technical leader, I'm
informed that such an error message is really infrequent and hard to reproduce.
Considering your issue may be urgent or the transaction performance is crucial on your
business, I recommend you contact Microsoft Product Support Services (PSS) to open a
graceful case via http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS.
However, I'm still try my best to assist you by providing further information and some possible
workarounds. They might address the cause and help solve the problem.
Based on my further researching, I notice that SQLOLEDB allows only ONE connection in
scope of transaction. As your SQL Server's version is "2000 - 8.00.194", I suspect that multiple
commands running in the same session within the scope of your transaction causes this
problem. What's more, the pending resultset associated with forward-only and read-only
cursor (also known as firehose or default resultset) can also be a plus.
In this case, the error message will pop up because the Microsoft OLE DB provider for SQL
Server does not allow more than one active connection within the scope of a transaction even
you applied the MDAC 2.7 and apply the latest SQL Server Service Pack.
To work around this symptom, I recommend you use a server-side or client-side cursor other
than the forward-only and read-only (often called a firehose) cursor.
For detailed information and how you could suppress the "Transaction active in this
session..." error, please review the following article carefully in case they address the issue on
your side:
272358 PRB: SQLOLEDB Allows Only One Connection in Scope of Transaction
http://support.microsoft.com/?id=272358
180843 PRB: Error Creating More Than One Recordset in a Transaction
http://support.microsoft.com/?id=180843
271128 PRB: Implicit Connections Created by the SQL Server OLE DB Provider
http://support.microsoft.com/?id=271128
Other actions I also strongly recommend you to take are:
1. Check your MDAC version using the following MDAC Check Tool:
http://www.microsoft.com/downloads/details.aspx?FamilyId=8F0A8DF6-4A21-4B43-BF53-
14332EF092C9&displaylang=en
If the MDAC version is not 2.7 SP1 Refresh or MDAC 2.8, please download this MDAC 2.7 SP1
Refresh on the following link and apply it on your operating system:
http://www.microsoft.com/downloads/details.aspx?FamilyID=9ad000f2-cae7-493d-b0f3-
ae36c570ade8&DisplayLang=en
2. Disable the Connection Pooling for SQL Server in the ODBC Data Source
a) Start -> Administrative Tools -> Data Sources (ODBC)
b) Move to the "Connection Pooling"
c) Select "SQL Server" in the left box of "ODBC Drivers" and select "Disable" in the right
PerfMon
d) Double click "SQL Server" in the left box of "ODBC Drivers", in the "Set Connection Pooling
Attributes" dialog, select "Do not pool connections to this driver".
Pavel, please read the KB I provided you and apply my suggestions and workarounds to see
if it helps you resolve your problem. After that, if the error message still occurs, you may
consider contacting the Microsoft Product Support Services (PSS) directly for further insightful
troubleshooting and support.
If there is anything more I can still help, please feel free to let me know or post it in the group.
Best regards,
Billy Yao
Microsoft Online Partner Support
----------------------------------------------------
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.
Your email below:
===================
-----Original Message-----
From: UBC POS [mailto:p...@sbrf.ru]
Sent: 2003?那11??5?? 14:34
To: Bin Yao (Intl Vendor)
Subject: What is 3926 error?
Hi, Billy!
There are some additional information that relate to your latest mail.
1. DTC doesn't used in the DB request in which error occurs.
2. A thread logic (close on SQL error -> close the session -> disconnect
from the database -> reestablish connection later -> try again) is
handmade. This logic is used in my code for any SQL DB error (not exists in ADO or OLEDB).
3. The request executed in 0.01sec usually. It's light and easy, but involve some tables which
can be locked by other "long" request.
4. ConnectionTimeout property of the connection object (ADO in my case) set to 10secs in my
code for the request so a don't see any reason to change timeout there: My Computer ->
Properties (Right click) -> Options tab
5. I think 10secs is enough. And it's important to understand what is
problem with 3926. I can set timeout to 20 or 30 secs, but in case of heavy load error can
occurs again.
6. Not any time-consuming statements, such as sleep() in the DB section of my code.
7. No deadlocks have been detected.
8. I'm MSDN Universal subscriber but not any info about the error there.
9. I'm MSDBA and MCSD so I think the error is or stupid or very hard.
10. It's exact eddor description (from ADO):
ADO/SQL Server Error[0]: NativeError[00000f56=3926],
Number[80004005=-2147467259], Source[Microsoft OLE DB Provider for SQL
Server], Description[The transaction active in this session has been
committed or aborted by another session.], SQLError[01000]
COM Error: Code[80004005], Meaning[Unspecified error],
Source[Microsoft OLE DB Provider for SQL Server], Description[The
transaction active in this session has been committed or aborted by another
session.]
Thank you for your suggestions and help.
Good luck,
Pavel.
Steve Kass
No one should be running SQL Server version 2000 - 8.00.194 in production.
That version is the initial release, and many security and performance
problems
have been fixed in the three service packs that have been issued. If
this is the
version you are running, the very first thing I recommend you do is
upgrade to
the most recent service pack, sp3a, which can be found here.
http://www.microsoft.com/sql/downloads/2000/sp3.asp
-- Steve Kass
-- Drew University
-- Ref: E68EE6D3-80F2-4BD2-94E5-E4C49B2CB1B3
Billy Yao [MSFT]
You are right! SQL Server version 2000 - 8.00.194 is the initial release and many security and
performance problems. I focused more on the MDAC and forgot this initial release version.
Thank you for your reminder!
To Pavel:
Please also apply the latest SQL Service Pack 3a:
http://www.microsoft.com/sql/downloads/2000/sp3.asp
After that, the version should be 2000 - 8.00.760. (Select @@version in Query Analyzer).
Furthermore, I also recommend you apply MS03-031 security patch to update the version to
2000 - 8.00.818.
Good Luck!
Billy Yao,
Microsoft Online Partner Support
Pavel
Thank you for your advices and help!
There are some new info about the error.
1. I’ve turned on the Profiler to cutch all type of errors
from my server. Some time later I’ve got 3926 in my
program’s log but not in Profiler trace log! It means that
error occurs in connection drivers!
2. I read your notice about simultaneous active
transactions in one connection. It’s not mÕ case. I use
one connection per thread. I don’t use command which
returns recordsets – only to run stored procedures (in the
thread with the error). All commands are executed
sequentially.
3.I noticed that error occurs in another thread too.
Recordsets are used there, but Recordser objects are
destroyed right after command execution until next command
starts.
4. I doesn’t support servers (I’m developer and manager),
but I promise to make people who support servers to
upgrade my one to SP3a.
Thanks!
Good luck!
Billy Yao [MSFT]
Thank you for your further information.
The issue seems to be much clearer and we narrow down it on the connection drivers.
Congratulations and thank you for all your efforts!
I suspect that the latest Service Pack 3a and MDAC 2.7 SP1 Refresh (or MDAC 2.8) may be
the accurate solution to your issue. So let's wait and see if the problem will be solved after
upgrading the problematic server to the current version of Service Pack and MDAC.
Thank you for your patience!
Billy Yao [MSFT]
Thank you for your prompt feedback! Good news that there wasn't the 3926 error after the
upgrade. I agree with you that we can monitor the issue for a while and please let me know if
no 3926 error occurs for some time. :-)
For error 208, this may occur when an object that does not exist is referenced. If the object
does exist, I recommend you include the owner's name in the four-part object name, and if you
are performing a distributed query, I strongly suggest you using
OPENQUERY/OPENROWSET instead of four-part naming queries.
As to error 8114, it may caused by inaccurately converting data type and you should check it.
The following articles describe the details on the error message and the workarounds within
them may address your new problems.
Error 208:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_1_
1qyg.asp
Error 8114:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_2_
5ppw.asp
If the suggestions don't work on your side, could you please create another post in our
newsgroup titled "error 208 and 8114" so that we can focus on this error in a separate post?
Thanks in advance!
Pavel, next time you can post the message directly in the newsgroup for the community
members' benefits, and what's more, there may be anyone else who can also add values on
your issue.
Best regards,
Billy Yao
Microsoft Online Support
----------------------------------------------------
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.
=========================================
From: UBC POS [mailto:p...@sbrf.ru]
Sent: 2003年11月11日 14:54
To: Bin Yao (Intl Vendor)
Subject: RE: Microsoft MSDN Issue Confirmation: What is 3926 error?
Hi, Billy!
I upgraded my server to the SQL Server Service Pack 3a and MDAC 2.8 just yesterday
(monday). So I'm waiting to detect the error now. There wasn't the error after upgrade yet.
I've detected a lots of 208 and 8144 errors inside SQL server by Profiler. Do you know what it
means?
This errors don't appear in C++ program code (ADO errors collection) or any logs. Errors'
description is very weak.
Thanks.
Good luck.
Pavel.