I found this error on my sql server:
The log file for database dbname is full. Back up the transaction log for
the database to free up some log space.
Then I run the backup program for transaction log, now my sql can work fine.
But the .LDF file still very big. Can I decrease this file's size?
Such as defrag or compress'Dear all:
I've check the file again and find out that the .LDF file was smaller then
before!
From 3G to 1M !!! I think its because the backup.
But why not change size immedialy?
Thank you very much!
"LetsCount" <come2@.ms1.pchome.com.tw> ¦b¶l¥ó
news:u82uPwQxDHA.2356@.TK2MSFTNGP12.phx.gbl ¤¤¼¶¼g...
> I found this error on my sql server:
> The log file for database dbname is full. Back up the transaction log for
> the database to free up some log space.
> Then I run the backup program for transaction log, now my sql can work
fine.
> But the .LDF file still very big. Can I decrease this file's size?
> Such as defrag or compress'
>|||Check out below KB articles:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default.aspx?scid=kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
Log File Grows too big
http://www.support.microsoft.com/?id=317375
Log file filling up
http://www.support.microsoft.com/?id=110139
Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=315512
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"LetsCount" <come2@.ms1.pchome.com.tw> wrote in message news:u82uPwQxDHA.2356@.TK2MSFTNGP12.phx.gbl...
> I found this error on my sql server:
> The log file for database dbname is full. Back up the transaction log for
> the database to free up some log space.
> Then I run the backup program for transaction log, now my sql can work fine.
> But the .LDF file still very big. Can I decrease this file's size?
> Such as defrag or compress'
>
Showing posts with label transaction. Show all posts
Showing posts with label transaction. Show all posts
Tuesday, March 27, 2012
Error 8525: Distributed transaction completed. Either enlist this session ... PROBLEM!
Hi there,
Fun problem for a Monday morning (NOT!). Running a stored procedure on
one server (SQL 2000, with latest patches), which is similar to the
example below, in connecting to a linked server to archive data out
from 'SERVER2' to a database on 'SERVER1' (the code below is obviously
a simplified part of the actual code to illustrate the functionality).
The stored procedure is run via a job on 'SERVER1'
This was OK, but recently we upgraded to a new clustered server and
are getting these problems now and again. The DBA informs me all looks
OK for the MSDTC and the Component Services is looking OK. Are there
any tips out there'
Thanks for any ideas.
Rob
--Running on SERVER1
CREATE TABLE #RecordsToDelete
(RefId varchar(100) NOT NULL)
INSERT INTO #RecordsToDelete
SELECT RefID FROM SERVER2.MyDB.dbo.MyTable
WHERE [name] = 'Jo Bloggs'
BEGIN DISTRIBUTED TRAN
INSERT INTO HISTORY.dbo.MyTable mt
SELECT * FROM SERVER2.MyDB.dbo.MyTable mt
JOIN #RecordsToDelete rtd ON rtd.RefID = mt.RefID
DELETE FROM SERVER2.MyDB.dbo.MyTable
WHERE RefID IN (SELECT RefId FROM #RecordsToDelete)
COMMIT> Fun problem for a Monday morning (NOT!). Running a stored procedure on
> one server (SQL 2000, with latest patches), which is similar to the
> example below, in connecting to a linked server to archive data out
> from 'SERVER2' to a database on 'SERVER1' (the code below is obviously
> a simplified part of the actual code to illustrate the functionality).
> The stored procedure is run via a job on 'SERVER1'
If the other server is 7.0, then this article might help:
http://support.microsoft.com/defaul...b;en-us;834849.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com|||"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in m
essage news:<e90GmTCsEHA.1164@.TK2MSFTNGP10.phx.gbl>...
> If the other server is 7.0, then this article might help:
> http://support.microsoft.com/defaul...b;en-us;834849.
No, they are both SQL Server 2000, which makes it confusing as the
only explanation/docmentation on this problem is to do with one server
being SQL Server 7.0!
Regards
Rob
Fun problem for a Monday morning (NOT!). Running a stored procedure on
one server (SQL 2000, with latest patches), which is similar to the
example below, in connecting to a linked server to archive data out
from 'SERVER2' to a database on 'SERVER1' (the code below is obviously
a simplified part of the actual code to illustrate the functionality).
The stored procedure is run via a job on 'SERVER1'
This was OK, but recently we upgraded to a new clustered server and
are getting these problems now and again. The DBA informs me all looks
OK for the MSDTC and the Component Services is looking OK. Are there
any tips out there'
Thanks for any ideas.
Rob
--Running on SERVER1
CREATE TABLE #RecordsToDelete
(RefId varchar(100) NOT NULL)
INSERT INTO #RecordsToDelete
SELECT RefID FROM SERVER2.MyDB.dbo.MyTable
WHERE [name] = 'Jo Bloggs'
BEGIN DISTRIBUTED TRAN
INSERT INTO HISTORY.dbo.MyTable mt
SELECT * FROM SERVER2.MyDB.dbo.MyTable mt
JOIN #RecordsToDelete rtd ON rtd.RefID = mt.RefID
DELETE FROM SERVER2.MyDB.dbo.MyTable
WHERE RefID IN (SELECT RefId FROM #RecordsToDelete)
COMMIT> Fun problem for a Monday morning (NOT!). Running a stored procedure on
> one server (SQL 2000, with latest patches), which is similar to the
> example below, in connecting to a linked server to archive data out
> from 'SERVER2' to a database on 'SERVER1' (the code below is obviously
> a simplified part of the actual code to illustrate the functionality).
> The stored procedure is run via a job on 'SERVER1'
If the other server is 7.0, then this article might help:
http://support.microsoft.com/defaul...b;en-us;834849.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com|||"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in m
essage news:<e90GmTCsEHA.1164@.TK2MSFTNGP10.phx.gbl>...
> If the other server is 7.0, then this article might help:
> http://support.microsoft.com/defaul...b;en-us;834849.
No, they are both SQL Server 2000, which makes it confusing as the
only explanation/docmentation on this problem is to do with one server
being SQL Server 7.0!
Regards
Rob
Error 8525: Distributed transaction completed. Either enlist this session ... PROBLEM!
Hi there,
Fun problem for a Monday morning (NOT!). Running a stored procedure on
one server (SQL 2000, with latest patches), which is similar to the
example below, in connecting to a linked server to archive data out
from 'SERVER2' to a database on 'SERVER1' (the code below is obviously
a simplified part of the actual code to illustrate the functionality).
The stored procedure is run via a job on 'SERVER1'
This was OK, but recently we upgraded to a new clustered server and
are getting these problems now and again. The DBA informs me all looks
OK for the MSDTC and the Component Services is looking OK. Are there
any tips out there?
Thanks for any ideas.
Rob
--Running on SERVER1
CREATE TABLE #RecordsToDelete
(RefIdvarchar(100) NOT NULL)
INSERT INTO #RecordsToDelete
SELECT RefID FROM SERVER2.MyDB.dbo.MyTable
WHERE [name] = 'Jo Bloggs'
BEGIN DISTRIBUTED TRAN
INSERT INTO HISTORY.dbo.MyTable mt
SELECT * FROM SERVER2.MyDB.dbo.MyTable mt
JOIN #RecordsToDelete rtd ON rtd.RefID = mt.RefID
DELETE FROM SERVER2.MyDB.dbo.MyTable
WHERE RefID IN (SELECT RefId FROM #RecordsToDelete)
COMMIT
> Fun problem for a Monday morning (NOT!). Running a stored procedure on
> one server (SQL 2000, with latest patches), which is similar to the
> example below, in connecting to a linked server to archive data out
> from 'SERVER2' to a database on 'SERVER1' (the code below is obviously
> a simplified part of the actual code to illustrate the functionality).
> The stored procedure is run via a job on 'SERVER1'
If the other server is 7.0, then this article might help:
http://support.microsoft.com/default...;en-us;834849.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
|||"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in message news:<e90GmTCsEHA.1164@.TK2MSFTNGP10.phx.gbl>...
> If the other server is 7.0, then this article might help:
> http://support.microsoft.com/default...;en-us;834849.
No, they are both SQL Server 2000, which makes it confusing as the
only explanation/docmentation on this problem is to do with one server
being SQL Server 7.0!
Regards
Rob
Fun problem for a Monday morning (NOT!). Running a stored procedure on
one server (SQL 2000, with latest patches), which is similar to the
example below, in connecting to a linked server to archive data out
from 'SERVER2' to a database on 'SERVER1' (the code below is obviously
a simplified part of the actual code to illustrate the functionality).
The stored procedure is run via a job on 'SERVER1'
This was OK, but recently we upgraded to a new clustered server and
are getting these problems now and again. The DBA informs me all looks
OK for the MSDTC and the Component Services is looking OK. Are there
any tips out there?
Thanks for any ideas.
Rob
--Running on SERVER1
CREATE TABLE #RecordsToDelete
(RefIdvarchar(100) NOT NULL)
INSERT INTO #RecordsToDelete
SELECT RefID FROM SERVER2.MyDB.dbo.MyTable
WHERE [name] = 'Jo Bloggs'
BEGIN DISTRIBUTED TRAN
INSERT INTO HISTORY.dbo.MyTable mt
SELECT * FROM SERVER2.MyDB.dbo.MyTable mt
JOIN #RecordsToDelete rtd ON rtd.RefID = mt.RefID
DELETE FROM SERVER2.MyDB.dbo.MyTable
WHERE RefID IN (SELECT RefId FROM #RecordsToDelete)
COMMIT
> Fun problem for a Monday morning (NOT!). Running a stored procedure on
> one server (SQL 2000, with latest patches), which is similar to the
> example below, in connecting to a linked server to archive data out
> from 'SERVER2' to a database on 'SERVER1' (the code below is obviously
> a simplified part of the actual code to illustrate the functionality).
> The stored procedure is run via a job on 'SERVER1'
If the other server is 7.0, then this article might help:
http://support.microsoft.com/default...;en-us;834849.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
|||"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in message news:<e90GmTCsEHA.1164@.TK2MSFTNGP10.phx.gbl>...
> If the other server is 7.0, then this article might help:
> http://support.microsoft.com/default...;en-us;834849.
No, they are both SQL Server 2000, which makes it confusing as the
only explanation/docmentation on this problem is to do with one server
being SQL Server 7.0!
Regards
Rob
Error 8525: Distributed transaction completed. Either enlist this session ... PROBLEM!
Hi there,
Fun problem for a Monday morning (NOT!). Running a stored procedure on
one server (SQL 2000, with latest patches), which is similar to the
example below, in connecting to a linked server to archive data out
from 'SERVER2' to a database on 'SERVER1' (the code below is obviously
a simplified part of the actual code to illustrate the functionality).
The stored procedure is run via a job on 'SERVER1'
This was OK, but recently we upgraded to a new clustered server and
are getting these problems now and again. The DBA informs me all looks
OK for the MSDTC and the Component Services is looking OK. Are there
any tips out there'
Thanks for any ideas.
Rob
--Running on SERVER1
CREATE TABLE #RecordsToDelete
(RefId varchar(100) NOT NULL)
INSERT INTO #RecordsToDelete
SELECT RefID FROM SERVER2.MyDB.dbo.MyTable
WHERE [name] = 'Jo Bloggs'
BEGIN DISTRIBUTED TRAN
INSERT INTO HISTORY.dbo.MyTable mt
SELECT * FROM SERVER2.MyDB.dbo.MyTable mt
JOIN #RecordsToDelete rtd ON rtd.RefID = mt.RefID
DELETE FROM SERVER2.MyDB.dbo.MyTable
WHERE RefID IN (SELECT RefId FROM #RecordsToDelete)
COMMIT> Fun problem for a Monday morning (NOT!). Running a stored procedure on
> one server (SQL 2000, with latest patches), which is similar to the
> example below, in connecting to a linked server to archive data out
> from 'SERVER2' to a database on 'SERVER1' (the code below is obviously
> a simplified part of the actual code to illustrate the functionality).
> The stored procedure is run via a job on 'SERVER1'
If the other server is 7.0, then this article might help:
http://support.microsoft.com/default.aspx?scid=kb;en-us;834849.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com|||"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in message news:<e90GmTCsEHA.1164@.TK2MSFTNGP10.phx.gbl>...
> If the other server is 7.0, then this article might help:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;834849.
No, they are both SQL Server 2000, which makes it confusing as the
only explanation/docmentation on this problem is to do with one server
being SQL Server 7.0!
Regards
Rob
Fun problem for a Monday morning (NOT!). Running a stored procedure on
one server (SQL 2000, with latest patches), which is similar to the
example below, in connecting to a linked server to archive data out
from 'SERVER2' to a database on 'SERVER1' (the code below is obviously
a simplified part of the actual code to illustrate the functionality).
The stored procedure is run via a job on 'SERVER1'
This was OK, but recently we upgraded to a new clustered server and
are getting these problems now and again. The DBA informs me all looks
OK for the MSDTC and the Component Services is looking OK. Are there
any tips out there'
Thanks for any ideas.
Rob
--Running on SERVER1
CREATE TABLE #RecordsToDelete
(RefId varchar(100) NOT NULL)
INSERT INTO #RecordsToDelete
SELECT RefID FROM SERVER2.MyDB.dbo.MyTable
WHERE [name] = 'Jo Bloggs'
BEGIN DISTRIBUTED TRAN
INSERT INTO HISTORY.dbo.MyTable mt
SELECT * FROM SERVER2.MyDB.dbo.MyTable mt
JOIN #RecordsToDelete rtd ON rtd.RefID = mt.RefID
DELETE FROM SERVER2.MyDB.dbo.MyTable
WHERE RefID IN (SELECT RefId FROM #RecordsToDelete)
COMMIT> Fun problem for a Monday morning (NOT!). Running a stored procedure on
> one server (SQL 2000, with latest patches), which is similar to the
> example below, in connecting to a linked server to archive data out
> from 'SERVER2' to a database on 'SERVER1' (the code below is obviously
> a simplified part of the actual code to illustrate the functionality).
> The stored procedure is run via a job on 'SERVER1'
If the other server is 7.0, then this article might help:
http://support.microsoft.com/default.aspx?scid=kb;en-us;834849.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com|||"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in message news:<e90GmTCsEHA.1164@.TK2MSFTNGP10.phx.gbl>...
> If the other server is 7.0, then this article might help:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;834849.
No, they are both SQL Server 2000, which makes it confusing as the
only explanation/docmentation on this problem is to do with one server
being SQL Server 7.0!
Regards
Rob
Thursday, March 22, 2012
Error 8102, Cannot update identity
Hi,
I have setup unidirectional transaction replication between two SQL2k
servers. On the subscription database I have set the PK as (Indentity not
for Replication).
I can insert and delete records on the publisher and they get replicated
without any errors, but on the updates I get an error.
The issue I belieive is with the generated sp_MSupd stored procedure. For
some reason it is tring to execute the first block even if the logic is
false.
Can someone please advise how to get around this issue.
Thanks
This problem is caused by updating the PK. You have to look at exactly what
the application which is modifying the data on the publisher is doing.
For some applications (including ones using the .net DataGrid component),
any update of any column in a table results in the update of all columns.
This is translated as a delete followed by an insert.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"mm" <postto@.news.com> wrote in message
news:%23lUTk14mEHA.1296@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have setup unidirectional transaction replication between two SQL2k
> servers. On the subscription database I have set the PK as (Indentity not
> for Replication).
> I can insert and delete records on the publisher and they get replicated
> without any errors, but on the updates I get an error.
> The issue I belieive is with the generated sp_MSupd stored procedure. For
> some reason it is tring to execute the first block even if the logic is
> false.
> Can someone please advise how to get around this issue.
> Thanks
>
>
I have setup unidirectional transaction replication between two SQL2k
servers. On the subscription database I have set the PK as (Indentity not
for Replication).
I can insert and delete records on the publisher and they get replicated
without any errors, but on the updates I get an error.
The issue I belieive is with the generated sp_MSupd stored procedure. For
some reason it is tring to execute the first block even if the logic is
false.
Can someone please advise how to get around this issue.
Thanks
This problem is caused by updating the PK. You have to look at exactly what
the application which is modifying the data on the publisher is doing.
For some applications (including ones using the .net DataGrid component),
any update of any column in a table results in the update of all columns.
This is translated as a delete followed by an insert.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"mm" <postto@.news.com> wrote in message
news:%23lUTk14mEHA.1296@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have setup unidirectional transaction replication between two SQL2k
> servers. On the subscription database I have set the PK as (Indentity not
> for Replication).
> I can insert and delete records on the publisher and they get replicated
> without any errors, but on the updates I get an error.
> The issue I belieive is with the generated sp_MSupd stored procedure. For
> some reason it is tring to execute the first block even if the logic is
> false.
> Can someone please advise how to get around this issue.
> Thanks
>
>
Wednesday, March 21, 2012
Error 7391 when distributed transaction started
Node 1:
Windows 2003 Server Standar Edition SP1
SQL Server 2005 Standar Edition
Node 2:
Windows 2000 Server SP4
SQL Server 2000 Standar Edition SP3a
Node 1 have node 2 like "linked server", node 2 have node 1 like "linked
server" too.
Both nodes use Microsoft OLE DB Provider for SQL Server.
Node 1 begins a distributed transaction vs Node 2.
Transaction KO with
"Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a]."
MS DTS is configured in Node 1 like microsoft said in
http://suppport.microsoftl.com/defa...kb;en-us;329332
If I change Node1 with Windows 2000 Server Standard Edition and SQL Server
2000 SP3a, the distribution transaction run OK.
Thanks a lot.Hi
Windows 2003 has more secure defaults than Windows 2000 therefore it is
probably something that has not been configured that is causing the problem
Check out http://support.microsoft.com/kb/839279/
http://support.microsoft.com/kb/191168/
http://support.microsoft.com/kb/306843/
http://support.microsoft.com/kb/306843/
http://support.microsoft.com/kb/329332
http://support.microsoft.com/kb/306212
You can use DTCPing to check it can communicate with the server
http://download.microsoft.com/downl.../>
dtcping.exe
John
"FRF" wrote:
> Node 1:
> Windows 2003 Server Standar Edition SP1
> SQL Server 2005 Standar Edition
> Node 2:
> Windows 2000 Server SP4
> SQL Server 2000 Standar Edition SP3a
> Node 1 have node 2 like "linked server", node 2 have node 1 like "linked
> server" too.
> Both nodes use Microsoft OLE DB Provider for SQL Server.
> Node 1 begins a distributed transaction vs Node 2.
> Transaction KO with
> "Server: Msg 7391, Level 16, State 1, Line 1
> The operation could not be performed because the OLE DB provider 'SQLOLEDB
'
> was unable to begin a distributed transaction.
> OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a]."
> MS DTS is configured in Node 1 like microsoft said in
> http://suppport.microsoftl.com/defa...kb;en-us;329332
> If I change Node1 with Windows 2000 Server Standard Edition and SQL Server
> 2000 SP3a, the distribution transaction run OK.
> Thanks a lot.
>
Windows 2003 Server Standar Edition SP1
SQL Server 2005 Standar Edition
Node 2:
Windows 2000 Server SP4
SQL Server 2000 Standar Edition SP3a
Node 1 have node 2 like "linked server", node 2 have node 1 like "linked
server" too.
Both nodes use Microsoft OLE DB Provider for SQL Server.
Node 1 begins a distributed transaction vs Node 2.
Transaction KO with
"Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a]."
MS DTS is configured in Node 1 like microsoft said in
http://suppport.microsoftl.com/defa...kb;en-us;329332
If I change Node1 with Windows 2000 Server Standard Edition and SQL Server
2000 SP3a, the distribution transaction run OK.
Thanks a lot.Hi
Windows 2003 has more secure defaults than Windows 2000 therefore it is
probably something that has not been configured that is causing the problem
Check out http://support.microsoft.com/kb/839279/
http://support.microsoft.com/kb/191168/
http://support.microsoft.com/kb/306843/
http://support.microsoft.com/kb/306843/
http://support.microsoft.com/kb/329332
http://support.microsoft.com/kb/306212
You can use DTCPing to check it can communicate with the server
http://download.microsoft.com/downl.../>
dtcping.exe
John
"FRF" wrote:
> Node 1:
> Windows 2003 Server Standar Edition SP1
> SQL Server 2005 Standar Edition
> Node 2:
> Windows 2000 Server SP4
> SQL Server 2000 Standar Edition SP3a
> Node 1 have node 2 like "linked server", node 2 have node 1 like "linked
> server" too.
> Both nodes use Microsoft OLE DB Provider for SQL Server.
> Node 1 begins a distributed transaction vs Node 2.
> Transaction KO with
> "Server: Msg 7391, Level 16, State 1, Line 1
> The operation could not be performed because the OLE DB provider 'SQLOLEDB
'
> was unable to begin a distributed transaction.
> OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a]."
> MS DTS is configured in Node 1 like microsoft said in
> http://suppport.microsoftl.com/defa...kb;en-us;329332
> If I change Node1 with Windows 2000 Server Standard Edition and SQL Server
> 2000 SP3a, the distribution transaction run OK.
> Thanks a lot.
>
Error 7391 when distributed transaction started
Node 1:
Windows 2003 Server Standar Edition SP1
SQL Server 2005 Standar Edition
Node 2:
Windows 2000 Server SP4
SQL Server 2000 Standar Edition SP3a
Node 1 have node 2 like "linked server", node 2 have node 1 like "linked
server" too.
Both nodes use Microsoft OLE DB Provider for SQL Server.
Node 1 begins a distributed transaction vs Node 2.
Transaction KO with
"Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a]."
MS DTS is configured in Node 1 like microsoft said in
http://suppport.microsoftl.com/defau...b;en-us;329332
If I change Node1 with Windows 2000 Server Standard Edition and SQL Server
2000 SP3a, the distribution transaction run OK.
Thanks a lot.
Hi
Windows 2003 has more secure defaults than Windows 2000 therefore it is
probably something that has not been configured that is causing the problem
Check out http://support.microsoft.com/kb/839279/
http://support.microsoft.com/kb/191168/
http://support.microsoft.com/kb/306843/
http://support.microsoft.com/kb/306843/
http://support.microsoft.com/kb/329332
http://support.microsoft.com/kb/306212
You can use DTCPing to check it can communicate with the server
http://download.microsoft.com/downlo...c1/dtcping.exe
John
"FRF" wrote:
> Node 1:
> Windows 2003 Server Standar Edition SP1
> SQL Server 2005 Standar Edition
> Node 2:
> Windows 2000 Server SP4
> SQL Server 2000 Standar Edition SP3a
> Node 1 have node 2 like "linked server", node 2 have node 1 like "linked
> server" too.
> Both nodes use Microsoft OLE DB Provider for SQL Server.
> Node 1 begins a distributed transaction vs Node 2.
> Transaction KO with
> "Server: Msg 7391, Level 16, State 1, Line 1
> The operation could not be performed because the OLE DB provider 'SQLOLEDB'
> was unable to begin a distributed transaction.
> OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a]."
> MS DTS is configured in Node 1 like microsoft said in
> http://suppport.microsoftl.com/defau...b;en-us;329332
> If I change Node1 with Windows 2000 Server Standard Edition and SQL Server
> 2000 SP3a, the distribution transaction run OK.
> Thanks a lot.
>
sql
Windows 2003 Server Standar Edition SP1
SQL Server 2005 Standar Edition
Node 2:
Windows 2000 Server SP4
SQL Server 2000 Standar Edition SP3a
Node 1 have node 2 like "linked server", node 2 have node 1 like "linked
server" too.
Both nodes use Microsoft OLE DB Provider for SQL Server.
Node 1 begins a distributed transaction vs Node 2.
Transaction KO with
"Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a]."
MS DTS is configured in Node 1 like microsoft said in
http://suppport.microsoftl.com/defau...b;en-us;329332
If I change Node1 with Windows 2000 Server Standard Edition and SQL Server
2000 SP3a, the distribution transaction run OK.
Thanks a lot.
Hi
Windows 2003 has more secure defaults than Windows 2000 therefore it is
probably something that has not been configured that is causing the problem
Check out http://support.microsoft.com/kb/839279/
http://support.microsoft.com/kb/191168/
http://support.microsoft.com/kb/306843/
http://support.microsoft.com/kb/306843/
http://support.microsoft.com/kb/329332
http://support.microsoft.com/kb/306212
You can use DTCPing to check it can communicate with the server
http://download.microsoft.com/downlo...c1/dtcping.exe
John
"FRF" wrote:
> Node 1:
> Windows 2003 Server Standar Edition SP1
> SQL Server 2005 Standar Edition
> Node 2:
> Windows 2000 Server SP4
> SQL Server 2000 Standar Edition SP3a
> Node 1 have node 2 like "linked server", node 2 have node 1 like "linked
> server" too.
> Both nodes use Microsoft OLE DB Provider for SQL Server.
> Node 1 begins a distributed transaction vs Node 2.
> Transaction KO with
> "Server: Msg 7391, Level 16, State 1, Line 1
> The operation could not be performed because the OLE DB provider 'SQLOLEDB'
> was unable to begin a distributed transaction.
> OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a]."
> MS DTS is configured in Node 1 like microsoft said in
> http://suppport.microsoftl.com/defau...b;en-us;329332
> If I change Node1 with Windows 2000 Server Standard Edition and SQL Server
> 2000 SP3a, the distribution transaction run OK.
> Thanks a lot.
>
sql
Error 7391 when distributed transaction started
Node 1:
Windows 2003 Server Standar Edition SP1
SQL Server 2005 Standar Edition
Node 2:
Windows 2000 Server SP4
SQL Server 2000 Standar Edition SP3a
Node 1 have node 2 like "linked server", node 2 have node 1 like "linked
server" too.
Both nodes use Microsoft OLE DB Provider for SQL Server.
Node 1 begins a distributed transaction vs Node 2.
Transaction KO with
"Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a]."
MS DTS is configured in Node 1 like microsoft said in
http://suppport.microsoftl.com/default.aspx?scid=kb;en-us;329332
If I change Node1 with Windows 2000 Server Standard Edition and SQL Server
2000 SP3a, the distribution transaction run OK.
Thanks a lot.Hi
Windows 2003 has more secure defaults than Windows 2000 therefore it is
probably something that has not been configured that is causing the problem
Check out http://support.microsoft.com/kb/839279/
http://support.microsoft.com/kb/191168/
http://support.microsoft.com/kb/306843/
http://support.microsoft.com/kb/306843/
http://support.microsoft.com/kb/329332
http://support.microsoft.com/kb/306212
You can use DTCPing to check it can communicate with the server
http://download.microsoft.com/download/d/0/0/d00c8f6b-135d-4441-a97b-9de16a1935c1/dtcping.exe
John
"FRF" wrote:
> Node 1:
> Windows 2003 Server Standar Edition SP1
> SQL Server 2005 Standar Edition
> Node 2:
> Windows 2000 Server SP4
> SQL Server 2000 Standar Edition SP3a
> Node 1 have node 2 like "linked server", node 2 have node 1 like "linked
> server" too.
> Both nodes use Microsoft OLE DB Provider for SQL Server.
> Node 1 begins a distributed transaction vs Node 2.
> Transaction KO with
> "Server: Msg 7391, Level 16, State 1, Line 1
> The operation could not be performed because the OLE DB provider 'SQLOLEDB'
> was unable to begin a distributed transaction.
> OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a]."
> MS DTS is configured in Node 1 like microsoft said in
> http://suppport.microsoftl.com/default.aspx?scid=kb;en-us;329332
> If I change Node1 with Windows 2000 Server Standard Edition and SQL Server
> 2000 SP3a, the distribution transaction run OK.
> Thanks a lot.
>
Windows 2003 Server Standar Edition SP1
SQL Server 2005 Standar Edition
Node 2:
Windows 2000 Server SP4
SQL Server 2000 Standar Edition SP3a
Node 1 have node 2 like "linked server", node 2 have node 1 like "linked
server" too.
Both nodes use Microsoft OLE DB Provider for SQL Server.
Node 1 begins a distributed transaction vs Node 2.
Transaction KO with
"Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a]."
MS DTS is configured in Node 1 like microsoft said in
http://suppport.microsoftl.com/default.aspx?scid=kb;en-us;329332
If I change Node1 with Windows 2000 Server Standard Edition and SQL Server
2000 SP3a, the distribution transaction run OK.
Thanks a lot.Hi
Windows 2003 has more secure defaults than Windows 2000 therefore it is
probably something that has not been configured that is causing the problem
Check out http://support.microsoft.com/kb/839279/
http://support.microsoft.com/kb/191168/
http://support.microsoft.com/kb/306843/
http://support.microsoft.com/kb/306843/
http://support.microsoft.com/kb/329332
http://support.microsoft.com/kb/306212
You can use DTCPing to check it can communicate with the server
http://download.microsoft.com/download/d/0/0/d00c8f6b-135d-4441-a97b-9de16a1935c1/dtcping.exe
John
"FRF" wrote:
> Node 1:
> Windows 2003 Server Standar Edition SP1
> SQL Server 2005 Standar Edition
> Node 2:
> Windows 2000 Server SP4
> SQL Server 2000 Standar Edition SP3a
> Node 1 have node 2 like "linked server", node 2 have node 1 like "linked
> server" too.
> Both nodes use Microsoft OLE DB Provider for SQL Server.
> Node 1 begins a distributed transaction vs Node 2.
> Transaction KO with
> "Server: Msg 7391, Level 16, State 1, Line 1
> The operation could not be performed because the OLE DB provider 'SQLOLEDB'
> was unable to begin a distributed transaction.
> OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a]."
> MS DTS is configured in Node 1 like microsoft said in
> http://suppport.microsoftl.com/default.aspx?scid=kb;en-us;329332
> If I change Node1 with Windows 2000 Server Standard Edition and SQL Server
> 2000 SP3a, the distribution transaction run OK.
> Thanks a lot.
>
Error 7391 running distributed transaction
When attempting to run a distributed transaction I get the following
error:
Server: Msg 7391, Level 16, State 1, Line 4
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in
the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
I have two servers.
ServerA
Windows 2003
SQL Server 2000 SP3a
MDAC 2.8 RTM
ServerB
Windows 2000
SQL Server 2000 SP3a
MDAC 2.8 RTM
When I open Query Analyzer on ServerA and execute the following
statement, I get the error.
begin distributed transaction
exec ServerB.master.dbo.sp_helpdb
I've already enabled network access for Component Services:
http://support.microsoft.com/default.aspx?kbid=329332
Didn't fix the issue.
Any ideas?
Thanks,
MarkMake sure that the DTC Service is running on both machines as well..
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mark Newton" <mark.newton@.bdsmarketing.com> wrote in message
news:0ia5sv0ifue6po44fbc1j3aipvpas38put@.4ax.com...
> When attempting to run a distributed transaction I get the following
> error:
> Server: Msg 7391, Level 16, State 1, Line 4
> The operation could not be performed because the OLE DB provider
> 'SQLOLEDB' was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in
> the specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
>
> I have two servers.
> ServerA
> Windows 2003
> SQL Server 2000 SP3a
> MDAC 2.8 RTM
> ServerB
> Windows 2000
> SQL Server 2000 SP3a
> MDAC 2.8 RTM
> When I open Query Analyzer on ServerA and execute the following
> statement, I get the error.
> begin distributed transaction
> exec ServerB.master.dbo.sp_helpdb
>
> I've already enabled network access for Component Services:
> http://support.microsoft.com/default.aspx?kbid=329332
> Didn't fix the issue.
> Any ideas?
> Thanks,
> Mark|||MSDTC is running on both machines.
On Tue, 25 Nov 2003 05:45:42 -0500, "Wayne Snyder"
<wsnyder@.computeredservices.com> wrote:
>Make sure that the DTC Service is running on both machines as well..
error:
Server: Msg 7391, Level 16, State 1, Line 4
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in
the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
I have two servers.
ServerA
Windows 2003
SQL Server 2000 SP3a
MDAC 2.8 RTM
ServerB
Windows 2000
SQL Server 2000 SP3a
MDAC 2.8 RTM
When I open Query Analyzer on ServerA and execute the following
statement, I get the error.
begin distributed transaction
exec ServerB.master.dbo.sp_helpdb
I've already enabled network access for Component Services:
http://support.microsoft.com/default.aspx?kbid=329332
Didn't fix the issue.
Any ideas?
Thanks,
MarkMake sure that the DTC Service is running on both machines as well..
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mark Newton" <mark.newton@.bdsmarketing.com> wrote in message
news:0ia5sv0ifue6po44fbc1j3aipvpas38put@.4ax.com...
> When attempting to run a distributed transaction I get the following
> error:
> Server: Msg 7391, Level 16, State 1, Line 4
> The operation could not be performed because the OLE DB provider
> 'SQLOLEDB' was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in
> the specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
>
> I have two servers.
> ServerA
> Windows 2003
> SQL Server 2000 SP3a
> MDAC 2.8 RTM
> ServerB
> Windows 2000
> SQL Server 2000 SP3a
> MDAC 2.8 RTM
> When I open Query Analyzer on ServerA and execute the following
> statement, I get the error.
> begin distributed transaction
> exec ServerB.master.dbo.sp_helpdb
>
> I've already enabled network access for Component Services:
> http://support.microsoft.com/default.aspx?kbid=329332
> Didn't fix the issue.
> Any ideas?
> Thanks,
> Mark|||MSDTC is running on both machines.
On Tue, 25 Nov 2003 05:45:42 -0500, "Wayne Snyder"
<wsnyder@.computeredservices.com> wrote:
>Make sure that the DTC Service is running on both machines as well..
error 7391 in distributed transaction
I have a procedure running locally on my server A. This procedure does an
insert into a table after getting a return value from a select done on
another server say B . Server B is set up as linked server on server A. th
error i get shows as
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
Has anyone seen this before? Is there any solution to this or any setting
that needs to be changed to get this working? any help will be greatly
appreciated.
TIA
Message posted via http://www.webservertalk.comFor distributed transaction you need to have a Primary key on updated table.
So pLease add primary key to resolve your issue. Please let me know if
doesn't work.
Regards,
Jagan Mohan
MCDBA
"ishaan99 via webservertalk.com" wrote:
> I have a procedure running locally on my server A. This procedure does an
> insert into a table after getting a return value from a select done on
> another server say B . Server B is set up as linked server on server A. th
> error i get shows as
> The operation could not be performed because the OLE DB provider 'SQLOLEDB
'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> Has anyone seen this before? Is there any solution to this or any setting
> that needs to be changed to get this working? any help will be greatly
> appreciated.
> TIA
> --
> Message posted via http://www.webservertalk.com
>|||I've had this same error, mine only happened during times when transactions
were high on the system, I think there is a setting to increase the number o
f
concurrent distributed transactions, don't know what else you could do, I
know it has to do with MSDTC.
"ishaan99 via webservertalk.com" wrote:
> I have a procedure running locally on my server A. This procedure does an
> insert into a table after getting a return value from a select done on
> another server say B . Server B is set up as linked server on server A. th
> error i get shows as
> The operation could not be performed because the OLE DB provider 'SQLOLEDB
'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> Has anyone seen this before? Is there any solution to this or any setting
> that needs to be changed to get this working? any help will be greatly
> appreciated.
> TIA
> --
> Message posted via http://www.webservertalk.com
>sql
insert into a table after getting a return value from a select done on
another server say B . Server B is set up as linked server on server A. th
error i get shows as
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
Has anyone seen this before? Is there any solution to this or any setting
that needs to be changed to get this working? any help will be greatly
appreciated.
TIA
Message posted via http://www.webservertalk.comFor distributed transaction you need to have a Primary key on updated table.
So pLease add primary key to resolve your issue. Please let me know if
doesn't work.
Regards,
Jagan Mohan
MCDBA
"ishaan99 via webservertalk.com" wrote:
> I have a procedure running locally on my server A. This procedure does an
> insert into a table after getting a return value from a select done on
> another server say B . Server B is set up as linked server on server A. th
> error i get shows as
> The operation could not be performed because the OLE DB provider 'SQLOLEDB
'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> Has anyone seen this before? Is there any solution to this or any setting
> that needs to be changed to get this working? any help will be greatly
> appreciated.
> TIA
> --
> Message posted via http://www.webservertalk.com
>|||I've had this same error, mine only happened during times when transactions
were high on the system, I think there is a setting to increase the number o
f
concurrent distributed transactions, don't know what else you could do, I
know it has to do with MSDTC.
"ishaan99 via webservertalk.com" wrote:
> I have a procedure running locally on my server A. This procedure does an
> insert into a table after getting a return value from a select done on
> another server say B . Server B is set up as linked server on server A. th
> error i get shows as
> The operation could not be performed because the OLE DB provider 'SQLOLEDB
'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> Has anyone seen this before? Is there any solution to this or any setting
> that needs to be changed to get this working? any help will be greatly
> appreciated.
> TIA
> --
> Message posted via http://www.webservertalk.com
>sql
Error 7391 for distributed transaction between SQL Server and Oracle
Hi,
I am using SQL server 2000 on Windows 2003 SP1.I am trying to insert a
row into SQL server and Oracle through a Stored Proc.
If i run the sql statement using Query Analyzer with openrowset,i am
able to insert the row fine.
But when it is done through SP it throws this error.
Server: Msg 7391, Level 16, State 1, Procedure
SP_AutoInsertIncentive_oracle, Line 19
The operation could not be performed because the OLE DB provider
'MSDAORA' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDAORA'
ITransactionJoin::JoinTransaction returned 0x8004d01b].
Thanks a lot in advance for your help.
AJHi,
You can try this
[url]http://support.microsoft.com/default.aspx?scid=kb;en-us;306212&Product=sql2k.[/url
]
Hope it helps.
Danijel Novak
<aj70000@.hotmail.com> wrote in message
news:1137444337.934408.326330@.g47g2000cwa.googlegroups.com...
> Hi,
> I am using SQL server 2000 on Windows 2003 SP1.I am trying to insert a
> row into SQL server and Oracle through a Stored Proc.
> If i run the sql statement using Query Analyzer with openrowset,i am
> able to insert the row fine.
> But when it is done through SP it throws this error.
>
> Server: Msg 7391, Level 16, State 1, Procedure
> SP_AutoInsertIncentive_oracle, Line 19
> The operation could not be performed because the OLE DB provider
> 'MSDAORA' was unable to begin a distributed transaction.
> OLE DB error trace [OLE/DB Provider 'MSDAORA'
> ITransactionJoin::JoinTransaction returned 0x8004d01b].
> Thanks a lot in advance for your help.
> AJ
>
I am using SQL server 2000 on Windows 2003 SP1.I am trying to insert a
row into SQL server and Oracle through a Stored Proc.
If i run the sql statement using Query Analyzer with openrowset,i am
able to insert the row fine.
But when it is done through SP it throws this error.
Server: Msg 7391, Level 16, State 1, Procedure
SP_AutoInsertIncentive_oracle, Line 19
The operation could not be performed because the OLE DB provider
'MSDAORA' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDAORA'
ITransactionJoin::JoinTransaction returned 0x8004d01b].
Thanks a lot in advance for your help.
AJHi,
You can try this
[url]http://support.microsoft.com/default.aspx?scid=kb;en-us;306212&Product=sql2k.[/url
]
Hope it helps.
Danijel Novak
<aj70000@.hotmail.com> wrote in message
news:1137444337.934408.326330@.g47g2000cwa.googlegroups.com...
> Hi,
> I am using SQL server 2000 on Windows 2003 SP1.I am trying to insert a
> row into SQL server and Oracle through a Stored Proc.
> If i run the sql statement using Query Analyzer with openrowset,i am
> able to insert the row fine.
> But when it is done through SP it throws this error.
>
> Server: Msg 7391, Level 16, State 1, Procedure
> SP_AutoInsertIncentive_oracle, Line 19
> The operation could not be performed because the OLE DB provider
> 'MSDAORA' was unable to begin a distributed transaction.
> OLE DB error trace [OLE/DB Provider 'MSDAORA'
> ITransactionJoin::JoinTransaction returned 0x8004d01b].
> Thanks a lot in advance for your help.
> AJ
>
Error 7391 for distributed transaction between SQL Server and Oracle
Hi,
I am using SQL server 2000 on Windows 2003 SP1.I am trying to insert a
row into SQL server and Oracle through a Stored Proc.
If i run the sql statement using Query Analyzer with openrowset,i am
able to insert the row fine.
But when it is done through SP it throws this error.
Server: Msg 7391, Level 16, State 1, Procedure
SP_AutoInsertIncentive_oracle, Line 19
The operation could not be performed because the OLE DB provider
'MSDAORA' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDAORA'
ITransactionJoin::JoinTransaction returned 0x8004d01b].
Thanks a lot in advance for your help.
AJHi,
You can try this
http://support.microsoft.com/default.aspx?scid=kb;en-us;306212&Product=sql2k.
Hope it helps.
--
Danijel Novak
<aj70000@.hotmail.com> wrote in message
news:1137444337.934408.326330@.g47g2000cwa.googlegroups.com...
> Hi,
> I am using SQL server 2000 on Windows 2003 SP1.I am trying to insert a
> row into SQL server and Oracle through a Stored Proc.
> If i run the sql statement using Query Analyzer with openrowset,i am
> able to insert the row fine.
> But when it is done through SP it throws this error.
>
> Server: Msg 7391, Level 16, State 1, Procedure
> SP_AutoInsertIncentive_oracle, Line 19
> The operation could not be performed because the OLE DB provider
> 'MSDAORA' was unable to begin a distributed transaction.
> OLE DB error trace [OLE/DB Provider 'MSDAORA'
> ITransactionJoin::JoinTransaction returned 0x8004d01b].
> Thanks a lot in advance for your help.
> AJ
>
I am using SQL server 2000 on Windows 2003 SP1.I am trying to insert a
row into SQL server and Oracle through a Stored Proc.
If i run the sql statement using Query Analyzer with openrowset,i am
able to insert the row fine.
But when it is done through SP it throws this error.
Server: Msg 7391, Level 16, State 1, Procedure
SP_AutoInsertIncentive_oracle, Line 19
The operation could not be performed because the OLE DB provider
'MSDAORA' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDAORA'
ITransactionJoin::JoinTransaction returned 0x8004d01b].
Thanks a lot in advance for your help.
AJHi,
You can try this
http://support.microsoft.com/default.aspx?scid=kb;en-us;306212&Product=sql2k.
Hope it helps.
--
Danijel Novak
<aj70000@.hotmail.com> wrote in message
news:1137444337.934408.326330@.g47g2000cwa.googlegroups.com...
> Hi,
> I am using SQL server 2000 on Windows 2003 SP1.I am trying to insert a
> row into SQL server and Oracle through a Stored Proc.
> If i run the sql statement using Query Analyzer with openrowset,i am
> able to insert the row fine.
> But when it is done through SP it throws this error.
>
> Server: Msg 7391, Level 16, State 1, Procedure
> SP_AutoInsertIncentive_oracle, Line 19
> The operation could not be performed because the OLE DB provider
> 'MSDAORA' was unable to begin a distributed transaction.
> OLE DB error trace [OLE/DB Provider 'MSDAORA'
> ITransactionJoin::JoinTransaction returned 0x8004d01b].
> Thanks a lot in advance for your help.
> AJ
>
Error 7391 for distributed transaction between SQL Server and Oracle
Hi,
I am using SQL server 2000 on Windows 2003 SP1.I am trying to insert a
row into SQL server and Oracle through a Stored Proc.
If i run the sql statement using Query Analyzer with openrowset,i am
able to insert the row fine.
But when it is done through SP it throws this error.
Server: Msg 7391, Level 16, State 1, Procedure
SP_AutoInsertIncentive_oracle, Line 19
The operation could not be performed because the OLE DB provider
'MSDAORA' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDAORA'
ITransactionJoin::JoinTransaction returned 0x8004d01b].
Thanks a lot in advance for your help.
AJ
Hi,
You can try this
http://support.microsoft.com/default...Product=sql2k.
Hope it helps.
Danijel Novak
<aj70000@.hotmail.com> wrote in message
news:1137444337.934408.326330@.g47g2000cwa.googlegr oups.com...
> Hi,
> I am using SQL server 2000 on Windows 2003 SP1.I am trying to insert a
> row into SQL server and Oracle through a Stored Proc.
> If i run the sql statement using Query Analyzer with openrowset,i am
> able to insert the row fine.
> But when it is done through SP it throws this error.
>
> Server: Msg 7391, Level 16, State 1, Procedure
> SP_AutoInsertIncentive_oracle, Line 19
> The operation could not be performed because the OLE DB provider
> 'MSDAORA' was unable to begin a distributed transaction.
> OLE DB error trace [OLE/DB Provider 'MSDAORA'
> ITransactionJoin::JoinTransaction returned 0x8004d01b].
> Thanks a lot in advance for your help.
> AJ
>
I am using SQL server 2000 on Windows 2003 SP1.I am trying to insert a
row into SQL server and Oracle through a Stored Proc.
If i run the sql statement using Query Analyzer with openrowset,i am
able to insert the row fine.
But when it is done through SP it throws this error.
Server: Msg 7391, Level 16, State 1, Procedure
SP_AutoInsertIncentive_oracle, Line 19
The operation could not be performed because the OLE DB provider
'MSDAORA' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDAORA'
ITransactionJoin::JoinTransaction returned 0x8004d01b].
Thanks a lot in advance for your help.
AJ
Hi,
You can try this
http://support.microsoft.com/default...Product=sql2k.
Hope it helps.
Danijel Novak
<aj70000@.hotmail.com> wrote in message
news:1137444337.934408.326330@.g47g2000cwa.googlegr oups.com...
> Hi,
> I am using SQL server 2000 on Windows 2003 SP1.I am trying to insert a
> row into SQL server and Oracle through a Stored Proc.
> If i run the sql statement using Query Analyzer with openrowset,i am
> able to insert the row fine.
> But when it is done through SP it throws this error.
>
> Server: Msg 7391, Level 16, State 1, Procedure
> SP_AutoInsertIncentive_oracle, Line 19
> The operation could not be performed because the OLE DB provider
> 'MSDAORA' was unable to begin a distributed transaction.
> OLE DB error trace [OLE/DB Provider 'MSDAORA'
> ITransactionJoin::JoinTransaction returned 0x8004d01b].
> Thanks a lot in advance for your help.
> AJ
>
Monday, March 19, 2012
Error 705 while restoring transaction logs
While in the process of restoring a large number of
transaction logs, I recieved the following error
"There not enough room for process 11 to store PROC_HDR
0x5975000 in Pss.
What doesn't it have room for? The system databases have
plenty of room.
The error occurred on the 14th of about 30 transaction
logs being loaded. I am using SQL 6.5, I am unsure of the
service pack level, it migt be 5a (the build number is
6.50.479).
Any help would be greatly appreciatedDaniel,
PSS here stands for "process status structure" and refers to something SQL
Server keeps in memory. In effect, the message is saying SQL Server ran out
of memory in some buffer area. Were you able to finish the restores?
Ron
--
Ron Talmage
SQL Server MVP
"Daniel Inman" <dwinman.ns@.iqe.com> wrote in message
news:00f401c356cb$73274890$a601280a@.phx.gbl...
> While in the process of restoring a large number of
> transaction logs, I recieved the following error
> "There not enough room for process 11 to store PROC_HDR
> 0x5975000 in Pss.
> What doesn't it have room for? The system databases have
> plenty of room.
> The error occurred on the 14th of about 30 transaction
> logs being loaded. I am using SQL 6.5, I am unsure of the
> service pack level, it migt be 5a (the build number is
> 6.50.479).
> Any help would be greatly appreciated
>|||Thank you for your response, but unfortunately, we
couldn't continue restoring logs. The database was
marked suspect as a result of the error, and in 6.5 it
seems that you can't restore a corrupt database without
dropping it first. My client was getting nervous over
the downtime so we gave upon restoring the transaction
logs and went back to the last full backup. I don't know
what would have happened if I had tried the entire
restore process again - just restoring the full backup
took over five hours - the DB is over 5gb.
I am just concerned that I may encounter the same error
the next time I have to restore transaction logs for this
client. For future reference, do you think this was
related to some attribute of the transaction log it faile
on, such as size or contents? Does it matter that I ran
the load database in the same batch as the load
transaction?
Daniel Inman
>--Original Message--
>Daniel,
>PSS here stands for "process status structure" and
refers to something SQL
>Server keeps in memory. In effect, the message is saying
SQL Server ran out
>of memory in some buffer area. Were you able to finish
the restores?
>Ron
>--
>Ron Talmage
>SQL Server MVP
>
>"Daniel Inman" <dwinman.ns@.iqe.com> wrote in message
>news:00f401c356cb$73274890$a601280a@.phx.gbl...
>> While in the process of restoring a large number of
>> transaction logs, I recieved the following error
>> "There not enough room for process 11 to store PROC_HDR
>> 0x5975000 in Pss.
>> What doesn't it have room for? The system databases
have
>> plenty of room.
>> The error occurred on the 14th of about 30 transaction
>> logs being loaded. I am using SQL 6.5, I am unsure of
the
>> service pack level, it migt be 5a (the build number is
>> 6.50.479).
>> Any help would be greatly appreciated
>
>.
>
transaction logs, I recieved the following error
"There not enough room for process 11 to store PROC_HDR
0x5975000 in Pss.
What doesn't it have room for? The system databases have
plenty of room.
The error occurred on the 14th of about 30 transaction
logs being loaded. I am using SQL 6.5, I am unsure of the
service pack level, it migt be 5a (the build number is
6.50.479).
Any help would be greatly appreciatedDaniel,
PSS here stands for "process status structure" and refers to something SQL
Server keeps in memory. In effect, the message is saying SQL Server ran out
of memory in some buffer area. Were you able to finish the restores?
Ron
--
Ron Talmage
SQL Server MVP
"Daniel Inman" <dwinman.ns@.iqe.com> wrote in message
news:00f401c356cb$73274890$a601280a@.phx.gbl...
> While in the process of restoring a large number of
> transaction logs, I recieved the following error
> "There not enough room for process 11 to store PROC_HDR
> 0x5975000 in Pss.
> What doesn't it have room for? The system databases have
> plenty of room.
> The error occurred on the 14th of about 30 transaction
> logs being loaded. I am using SQL 6.5, I am unsure of the
> service pack level, it migt be 5a (the build number is
> 6.50.479).
> Any help would be greatly appreciated
>|||Thank you for your response, but unfortunately, we
couldn't continue restoring logs. The database was
marked suspect as a result of the error, and in 6.5 it
seems that you can't restore a corrupt database without
dropping it first. My client was getting nervous over
the downtime so we gave upon restoring the transaction
logs and went back to the last full backup. I don't know
what would have happened if I had tried the entire
restore process again - just restoring the full backup
took over five hours - the DB is over 5gb.
I am just concerned that I may encounter the same error
the next time I have to restore transaction logs for this
client. For future reference, do you think this was
related to some attribute of the transaction log it faile
on, such as size or contents? Does it matter that I ran
the load database in the same batch as the load
transaction?
Daniel Inman
>--Original Message--
>Daniel,
>PSS here stands for "process status structure" and
refers to something SQL
>Server keeps in memory. In effect, the message is saying
SQL Server ran out
>of memory in some buffer area. Were you able to finish
the restores?
>Ron
>--
>Ron Talmage
>SQL Server MVP
>
>"Daniel Inman" <dwinman.ns@.iqe.com> wrote in message
>news:00f401c356cb$73274890$a601280a@.phx.gbl...
>> While in the process of restoring a large number of
>> transaction logs, I recieved the following error
>> "There not enough room for process 11 to store PROC_HDR
>> 0x5975000 in Pss.
>> What doesn't it have room for? The system databases
have
>> plenty of room.
>> The error occurred on the 14th of about 30 transaction
>> logs being loaded. I am using SQL 6.5, I am unsure of
the
>> service pack level, it migt be 5a (the build number is
>> 6.50.479).
>> Any help would be greatly appreciated
>
>.
>
Error 64 while backup TL onto NAS
Dear All,
I have a problem that I cannot seem to find much info on. I am backing up
SQL 2000 Transaction logs onto a network share (NAS). This share is
connected to the SQL server by a gigabit switch. When backing up the
transactions logs overnight - I find that the SQL server reports errors
"Operating System error 64 (The specified network name is no longer
available). Error 15457 Severity:0, State:1 on large logs (over 1Gb) - it
seems to backup smaller logs ok.
The SQL server is up to date with the SP3a as are the servers (Windows 2K -
SP4). The Transaction logs are 1Gb or over - some are 4Gb+ (this is a
back-end DB for a large web site).
Can someone point me in the right direction please.
PS. I have applied the MS KB813130 hot fix - this has had no effect.
Thanks
Andrew.Andrew,
This suggests that you are overrunning the ability of the target machine to
keep up with your request. We had problems with this at one time (and are
sneaking back up on it again.) Here are a couple posts from Google that
might help you.
http://tinyurl.com/wtm3
http://tinyurl.com/wtmb Jasper Smith, MVP wrote this response about RAID5
drives.
Russell Fields
"andrew" <andrew_loftus@.hotmail.com> wrote in message
news:%23G2XEPRtDHA.1224@.TK2MSFTNGP09.phx.gbl...
> Dear All,
> I have a problem that I cannot seem to find much info on. I am backing up
> SQL 2000 Transaction logs onto a network share (NAS). This share is
> connected to the SQL server by a gigabit switch. When backing up the
> transactions logs overnight - I find that the SQL server reports errors
> "Operating System error 64 (The specified network name is no longer
> available). Error 15457 Severity:0, State:1 on large logs (over 1Gb) - it
> seems to backup smaller logs ok.
> The SQL server is up to date with the SP3a as are the servers (Windows
2K -
> SP4). The Transaction logs are 1Gb or over - some are 4Gb+ (this is a
> back-end DB for a large web site).
> Can someone point me in the right direction please.
> PS. I have applied the MS KB813130 hot fix - this has had no effect.
> Thanks
> Andrew.
>|||Hi Andrew,
My name is Michael and I would like to thank you for using Microsoft
newsgroup.
Before you troubleshoot the SQL Server, I would like you to check if the
network configration is correct and if the status of the network is normal
on your side when the error occurred. I would like to try excluding the
network reason for this problem so that we can narrow down this issue on
SQL Server.
Based on my research, there is a known issue that if the database size is
big, such as 20 GB, and using a network share name for the backup file, it
can fail with Operating System error 64 (The specified network name is no
longer available.) or 121 (The semaphore timeout period has expired.) when
multiple files are used. The workaround is to use the file path instead of
the network share name for the backup file(s).
Does it solve your problem? If not, I would like you to provide more
information so that I can perform further research.
1. How is the issue going on your side now? Did the problem occur before?
Does the problem occur again? Does it occur randomly?
2. Please provide the Sqldiag.txt (by default located in \Mssql\Log) using
Sqldiag utility (Sqldiag.exe by default located in C:\Program
Files\Microsoft SQL Server\MSSQL\Binn). If you are unable to find the
Sqldiag.exe, please search and run the utility. Then please search the
Sqldiag.txt file and provide it to me at v-yshao@.microsoft.com
Also, such issues tend to be complex and take up extensive research time.
I'd like to set your expectations that it may take a while for us to help
you narrow down the problem and we may eventually redirect you to PSS to
continue working with a dedicated Support Professional. If this is
critical, I'd recommend contacting PSS and opening a Support incident to
troubleshoot this further. If you need any help in this regard, please let
me know.
I am standing by for your response.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Michael,
Thank you for the reply.
I seem to be able to backup the Tlogs to the local disk and only have the
problem when trying to backup to the N/W share. I was backing up to local
disk and have recently purchased this NAS to backup onto (over £3,000) - the
errors occured when using this NAS.
My Tlog files are no bigger than 6Gb at the moment and it is only the bigger
ones (over 1Gb) that seem to give the error. I assume MS software can handle
this kind of transfer with proper "handshaking" to ensure the share does not
get swamped with data it cannot handle. The NAS is a DELL 725n with "Windows
2000" powered software. The disks are configured in a RAID5 array. This
problem does occur on a regular daily basis.
Thanks
Andrew.
I have emailed you the SQLDIAG.TXT seperatly to your email address.
"Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
news:EX4O1RYtDHA.2072@.cpmsftngxa06.phx.gbl...
> Hi Andrew,
> My name is Michael and I would like to thank you for using Microsoft
> newsgroup.
> Before you troubleshoot the SQL Server, I would like you to check if the
> network configration is correct and if the status of the network is normal
> on your side when the error occurred. I would like to try excluding the
> network reason for this problem so that we can narrow down this issue on
> SQL Server.
> Based on my research, there is a known issue that if the database size is
> big, such as 20 GB, and using a network share name for the backup file, it
> can fail with Operating System error 64 (The specified network name is no
> longer available.) or 121 (The semaphore timeout period has expired.) when
> multiple files are used. The workaround is to use the file path instead
of
> the network share name for the backup file(s).
> Does it solve your problem? If not, I would like you to provide more
> information so that I can perform further research.
> 1. How is the issue going on your side now? Did the problem occur before?
> Does the problem occur again? Does it occur randomly?
> 2. Please provide the Sqldiag.txt (by default located in \Mssql\Log) using
> Sqldiag utility (Sqldiag.exe by default located in C:\Program
> Files\Microsoft SQL Server\MSSQL\Binn). If you are unable to find the
> Sqldiag.exe, please search and run the utility. Then please search the
> Sqldiag.txt file and provide it to me at v-yshao@.microsoft.com
> Also, such issues tend to be complex and take up extensive research time.
> I'd like to set your expectations that it may take a while for us to help
> you narrow down the problem and we may eventually redirect you to PSS to
> continue working with a dedicated Support Professional. If this is
> critical, I'd recommend contacting PSS and opening a Support incident to
> troubleshoot this further. If you need any help in this regard, please let
> me know.
> I am standing by for your response.
> Regards,
> Michael Shao
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
>|||Hi Andrew,
Thanks for your response. Please perform the following tests on your side
so that I can narrow down this issue.
1. Please perform the problematic backup job manually to see if the problem
also occurs.
2. Have you tried the suggestions in my previous post? Please try to use
the file path instead of the network share name for the backup file(s) on
the destination computer. Can you backup the transaction logs?
3. Please try to change the Write Policy for the RAID controller from
Writethru to Writeback. Does it solve your problem?
If not,please provide the following information so that I can perform
further research.
1. When the backup problem occurred, could you connect to the destination
server using the server name?
2. You wrote "it is only the bigger ones (over 1GB) that seem to give the
error." I am unable to find related information on this in the sqldiag.txt
file. Why do you think so, can you describe it in detail?
3.Do you mean that you are unable to backup transaction logs (over 1GB)
using the server name in any situation?
I am standing by for your response.
Thank you,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Michael,
Good morning and thanks for your last reply.
1. Yes, the error happens when a manual backup is done.
2. I backed up upto the local drive E:\ - is this what you mean by file
path - or do you mean mapping a drive letter to the share and backing up to
the drive letter?
3. I have changed the RAID system on the NAS from RAID5 to RAID1, I am
waiting for results but it looks good so far, in that a manual backup has
completed where it did not before the conversion. I will know tommorow for
good.
If this does solve the problem - it surely means that there is no
handshaking/communication between two computers on the network - so one can
easily "swamp" the other with data and get an error message. I would have
thought MS Windows was able to cope with this sinario - any comments?
I will let you know tommorow when my nightly backup has completed, I am
quietly hopeful.
Thanks again
Andrew.
"Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
news:7rULUm8tDHA.984@.cpmsftngxa06.phx.gbl...
> Hi Andrew,
> Thanks for your response. Please perform the following tests on your side
> so that I can narrow down this issue.
> 1. Please perform the problematic backup job manually to see if the
problem
> also occurs.
> 2. Have you tried the suggestions in my previous post? Please try to use
> the file path instead of the network share name for the backup file(s) on
> the destination computer. Can you backup the transaction logs?
> 3. Please try to change the Write Policy for the RAID controller from
> Writethru to Writeback. Does it solve your problem?
> If not,please provide the following information so that I can perform
> further research.
> 1. When the backup problem occurred, could you connect to the destination
> server using the server name?
> 2. You wrote "it is only the bigger ones (over 1GB) that seem to give the
> error." I am unable to find related information on this in the sqldiag.txt
> file. Why do you think so, can you describe it in detail?
> 3.Do you mean that you are unable to backup transaction logs (over 1GB)
> using the server name in any situation?
> I am standing by for your response.
> Thank you,
> Michael Shao
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
>|||Hi Andrew,
Thanks for your response. I am glad to hear that the manual backup works
after the conversion. Hope the backup job also works fine.
The file path which I mentioned in my previous post indicated the local
path. Generally, it is better to backup the database to the local machine
and run some jobs to copy the backup files to the remote machine. In my
opinion, it is a safer solution.
Also, I am not quite clear your concerns about handshaking/communication on
the network. What does the handshaking/communication indicate? What do you
mean by " swamp"? Can you describe your concerns in detail?
I am standing by for the result on your side.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Michael,
Thanks for your help - all seems to work now I have changed the NAS to RAID
0 from RAID 5.
My concerns are as you mention - surely one windows computer on the network
cannot "swamp" another - surely there is communication between the
computers - like hold on a minute with that data while I just commit what I
have to disk - ok ready now please send the next chunk.
Andrew.
"Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
news:Aq%23G2XLuDHA.1532@.cpmsftngxa06.phx.gbl...
> Hi Andrew,
> Thanks for your response. I am glad to hear that the manual backup works
> after the conversion. Hope the backup job also works fine.
> The file path which I mentioned in my previous post indicated the local
> path. Generally, it is better to backup the database to the local machine
> and run some jobs to copy the backup files to the remote machine. In my
> opinion, it is a safer solution.
> Also, I am not quite clear your concerns about handshaking/communication
on
> the network. What does the handshaking/communication indicate? What do you
> mean by " swamp"? Can you describe your concerns in detail?
> I am standing by for the result on your side.
> Regards,
> Michael Shao
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
>|||Hi Andrew,
Thanks for your update. It is great that the backup job also works fine.
Generally, when taking backup copy on the network, some protocol will be
used. There are certain Timout and Retry mechanism defined in the protocol.
If there is still no response from the target after using the Timout and
Retry mechanism, the error will occur.
Thanks for using MSDN newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
I have a problem that I cannot seem to find much info on. I am backing up
SQL 2000 Transaction logs onto a network share (NAS). This share is
connected to the SQL server by a gigabit switch. When backing up the
transactions logs overnight - I find that the SQL server reports errors
"Operating System error 64 (The specified network name is no longer
available). Error 15457 Severity:0, State:1 on large logs (over 1Gb) - it
seems to backup smaller logs ok.
The SQL server is up to date with the SP3a as are the servers (Windows 2K -
SP4). The Transaction logs are 1Gb or over - some are 4Gb+ (this is a
back-end DB for a large web site).
Can someone point me in the right direction please.
PS. I have applied the MS KB813130 hot fix - this has had no effect.
Thanks
Andrew.Andrew,
This suggests that you are overrunning the ability of the target machine to
keep up with your request. We had problems with this at one time (and are
sneaking back up on it again.) Here are a couple posts from Google that
might help you.
http://tinyurl.com/wtm3
http://tinyurl.com/wtmb Jasper Smith, MVP wrote this response about RAID5
drives.
Russell Fields
"andrew" <andrew_loftus@.hotmail.com> wrote in message
news:%23G2XEPRtDHA.1224@.TK2MSFTNGP09.phx.gbl...
> Dear All,
> I have a problem that I cannot seem to find much info on. I am backing up
> SQL 2000 Transaction logs onto a network share (NAS). This share is
> connected to the SQL server by a gigabit switch. When backing up the
> transactions logs overnight - I find that the SQL server reports errors
> "Operating System error 64 (The specified network name is no longer
> available). Error 15457 Severity:0, State:1 on large logs (over 1Gb) - it
> seems to backup smaller logs ok.
> The SQL server is up to date with the SP3a as are the servers (Windows
2K -
> SP4). The Transaction logs are 1Gb or over - some are 4Gb+ (this is a
> back-end DB for a large web site).
> Can someone point me in the right direction please.
> PS. I have applied the MS KB813130 hot fix - this has had no effect.
> Thanks
> Andrew.
>|||Hi Andrew,
My name is Michael and I would like to thank you for using Microsoft
newsgroup.
Before you troubleshoot the SQL Server, I would like you to check if the
network configration is correct and if the status of the network is normal
on your side when the error occurred. I would like to try excluding the
network reason for this problem so that we can narrow down this issue on
SQL Server.
Based on my research, there is a known issue that if the database size is
big, such as 20 GB, and using a network share name for the backup file, it
can fail with Operating System error 64 (The specified network name is no
longer available.) or 121 (The semaphore timeout period has expired.) when
multiple files are used. The workaround is to use the file path instead of
the network share name for the backup file(s).
Does it solve your problem? If not, I would like you to provide more
information so that I can perform further research.
1. How is the issue going on your side now? Did the problem occur before?
Does the problem occur again? Does it occur randomly?
2. Please provide the Sqldiag.txt (by default located in \Mssql\Log) using
Sqldiag utility (Sqldiag.exe by default located in C:\Program
Files\Microsoft SQL Server\MSSQL\Binn). If you are unable to find the
Sqldiag.exe, please search and run the utility. Then please search the
Sqldiag.txt file and provide it to me at v-yshao@.microsoft.com
Also, such issues tend to be complex and take up extensive research time.
I'd like to set your expectations that it may take a while for us to help
you narrow down the problem and we may eventually redirect you to PSS to
continue working with a dedicated Support Professional. If this is
critical, I'd recommend contacting PSS and opening a Support incident to
troubleshoot this further. If you need any help in this regard, please let
me know.
I am standing by for your response.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Michael,
Thank you for the reply.
I seem to be able to backup the Tlogs to the local disk and only have the
problem when trying to backup to the N/W share. I was backing up to local
disk and have recently purchased this NAS to backup onto (over £3,000) - the
errors occured when using this NAS.
My Tlog files are no bigger than 6Gb at the moment and it is only the bigger
ones (over 1Gb) that seem to give the error. I assume MS software can handle
this kind of transfer with proper "handshaking" to ensure the share does not
get swamped with data it cannot handle. The NAS is a DELL 725n with "Windows
2000" powered software. The disks are configured in a RAID5 array. This
problem does occur on a regular daily basis.
Thanks
Andrew.
I have emailed you the SQLDIAG.TXT seperatly to your email address.
"Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
news:EX4O1RYtDHA.2072@.cpmsftngxa06.phx.gbl...
> Hi Andrew,
> My name is Michael and I would like to thank you for using Microsoft
> newsgroup.
> Before you troubleshoot the SQL Server, I would like you to check if the
> network configration is correct and if the status of the network is normal
> on your side when the error occurred. I would like to try excluding the
> network reason for this problem so that we can narrow down this issue on
> SQL Server.
> Based on my research, there is a known issue that if the database size is
> big, such as 20 GB, and using a network share name for the backup file, it
> can fail with Operating System error 64 (The specified network name is no
> longer available.) or 121 (The semaphore timeout period has expired.) when
> multiple files are used. The workaround is to use the file path instead
of
> the network share name for the backup file(s).
> Does it solve your problem? If not, I would like you to provide more
> information so that I can perform further research.
> 1. How is the issue going on your side now? Did the problem occur before?
> Does the problem occur again? Does it occur randomly?
> 2. Please provide the Sqldiag.txt (by default located in \Mssql\Log) using
> Sqldiag utility (Sqldiag.exe by default located in C:\Program
> Files\Microsoft SQL Server\MSSQL\Binn). If you are unable to find the
> Sqldiag.exe, please search and run the utility. Then please search the
> Sqldiag.txt file and provide it to me at v-yshao@.microsoft.com
> Also, such issues tend to be complex and take up extensive research time.
> I'd like to set your expectations that it may take a while for us to help
> you narrow down the problem and we may eventually redirect you to PSS to
> continue working with a dedicated Support Professional. If this is
> critical, I'd recommend contacting PSS and opening a Support incident to
> troubleshoot this further. If you need any help in this regard, please let
> me know.
> I am standing by for your response.
> Regards,
> Michael Shao
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
>|||Hi Andrew,
Thanks for your response. Please perform the following tests on your side
so that I can narrow down this issue.
1. Please perform the problematic backup job manually to see if the problem
also occurs.
2. Have you tried the suggestions in my previous post? Please try to use
the file path instead of the network share name for the backup file(s) on
the destination computer. Can you backup the transaction logs?
3. Please try to change the Write Policy for the RAID controller from
Writethru to Writeback. Does it solve your problem?
If not,please provide the following information so that I can perform
further research.
1. When the backup problem occurred, could you connect to the destination
server using the server name?
2. You wrote "it is only the bigger ones (over 1GB) that seem to give the
error." I am unable to find related information on this in the sqldiag.txt
file. Why do you think so, can you describe it in detail?
3.Do you mean that you are unable to backup transaction logs (over 1GB)
using the server name in any situation?
I am standing by for your response.
Thank you,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Michael,
Good morning and thanks for your last reply.
1. Yes, the error happens when a manual backup is done.
2. I backed up upto the local drive E:\ - is this what you mean by file
path - or do you mean mapping a drive letter to the share and backing up to
the drive letter?
3. I have changed the RAID system on the NAS from RAID5 to RAID1, I am
waiting for results but it looks good so far, in that a manual backup has
completed where it did not before the conversion. I will know tommorow for
good.
If this does solve the problem - it surely means that there is no
handshaking/communication between two computers on the network - so one can
easily "swamp" the other with data and get an error message. I would have
thought MS Windows was able to cope with this sinario - any comments?
I will let you know tommorow when my nightly backup has completed, I am
quietly hopeful.
Thanks again
Andrew.
"Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
news:7rULUm8tDHA.984@.cpmsftngxa06.phx.gbl...
> Hi Andrew,
> Thanks for your response. Please perform the following tests on your side
> so that I can narrow down this issue.
> 1. Please perform the problematic backup job manually to see if the
problem
> also occurs.
> 2. Have you tried the suggestions in my previous post? Please try to use
> the file path instead of the network share name for the backup file(s) on
> the destination computer. Can you backup the transaction logs?
> 3. Please try to change the Write Policy for the RAID controller from
> Writethru to Writeback. Does it solve your problem?
> If not,please provide the following information so that I can perform
> further research.
> 1. When the backup problem occurred, could you connect to the destination
> server using the server name?
> 2. You wrote "it is only the bigger ones (over 1GB) that seem to give the
> error." I am unable to find related information on this in the sqldiag.txt
> file. Why do you think so, can you describe it in detail?
> 3.Do you mean that you are unable to backup transaction logs (over 1GB)
> using the server name in any situation?
> I am standing by for your response.
> Thank you,
> Michael Shao
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
>|||Hi Andrew,
Thanks for your response. I am glad to hear that the manual backup works
after the conversion. Hope the backup job also works fine.
The file path which I mentioned in my previous post indicated the local
path. Generally, it is better to backup the database to the local machine
and run some jobs to copy the backup files to the remote machine. In my
opinion, it is a safer solution.
Also, I am not quite clear your concerns about handshaking/communication on
the network. What does the handshaking/communication indicate? What do you
mean by " swamp"? Can you describe your concerns in detail?
I am standing by for the result on your side.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Michael,
Thanks for your help - all seems to work now I have changed the NAS to RAID
0 from RAID 5.
My concerns are as you mention - surely one windows computer on the network
cannot "swamp" another - surely there is communication between the
computers - like hold on a minute with that data while I just commit what I
have to disk - ok ready now please send the next chunk.
Andrew.
"Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
news:Aq%23G2XLuDHA.1532@.cpmsftngxa06.phx.gbl...
> Hi Andrew,
> Thanks for your response. I am glad to hear that the manual backup works
> after the conversion. Hope the backup job also works fine.
> The file path which I mentioned in my previous post indicated the local
> path. Generally, it is better to backup the database to the local machine
> and run some jobs to copy the backup files to the remote machine. In my
> opinion, it is a safer solution.
> Also, I am not quite clear your concerns about handshaking/communication
on
> the network. What does the handshaking/communication indicate? What do you
> mean by " swamp"? Can you describe your concerns in detail?
> I am standing by for the result on your side.
> Regards,
> Michael Shao
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
>|||Hi Andrew,
Thanks for your update. It is great that the backup job also works fine.
Generally, when taking backup copy on the network, some protocol will be
used. There are certain Timout and Retry mechanism defined in the protocol.
If there is still no response from the target after using the Timout and
Retry mechanism, the error will occur.
Thanks for using MSDN newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Error 627:Cannot use SAVE TRANSACTION within a distributed transac
Information:
SQL Server 2000 Enterprise Edition
Server Pack 3a
Server Edition is Windows 2000 Advanced Server
Replication Type is Transactional
I am trying to drop a publication. I am getting the error:
Error 627:Cannot use SAVE TRANSACTION within a distributed transaction
This error also is generated when trying to drop the individual subscription.
Can anyone provide insight as to what could be causing this error?
Thank you in advance.
Robert M.Check the "remote proc trans" value from sp_configure. If it's 1, you need
to change it to 0.
"Robert M." <RobertM@.discussions.microsoft.com> wrote in message
news:28E165FE-67B2-44F1-98DC-1F2CB3EA0B7E@.microsoft.com...
> Information:
> SQL Server 2000 Enterprise Edition
> Server Pack 3a
> Server Edition is Windows 2000 Advanced Server
> Replication Type is Transactional
> I am trying to drop a publication. I am getting the error:
> Error 627:Cannot use SAVE TRANSACTION within a distributed transaction
> This error also is generated when trying to drop the individual
subscription.
> Can anyone provide insight as to what could be causing this error?
> Thank you in advance.
> Robert M.
>|||I am assuming that the 'remote proc trans' setting needs to be set to zero on
the publisher (i have separate servers for distributor/publisher/subscriber).
Thanks...Robert M.
"Richard Ding" wrote:
> Check the "remote proc trans" value from sp_configure. If it's 1, you need
> to change it to 0.
>
> "Robert M." <RobertM@.discussions.microsoft.com> wrote in message
> news:28E165FE-67B2-44F1-98DC-1F2CB3EA0B7E@.microsoft.com...
> > Information:
> > SQL Server 2000 Enterprise Edition
> > Server Pack 3a
> > Server Edition is Windows 2000 Advanced Server
> > Replication Type is Transactional
> >
> > I am trying to drop a publication. I am getting the error:
> > Error 627:Cannot use SAVE TRANSACTION within a distributed transaction
> >
> > This error also is generated when trying to drop the individual
> subscription.
> >
> > Can anyone provide insight as to what could be causing this error?
> >
> > Thank you in advance.
> >
> > Robert M.
> >
> >
>
>|||Actually, I believe it should be set to 0 on the subscriber. No need to
touch the values on publisher or distributor.
"Robert M." <RobertM@.discussions.microsoft.com> wrote in message
news:4A56EC3F-0537-441C-990E-0C7D43BD972C@.microsoft.com...
> I am assuming that the 'remote proc trans' setting needs to be set to zero
on
> the publisher (i have separate servers for
distributor/publisher/subscriber).
> Thanks...Robert M.
> "Richard Ding" wrote:
> > Check the "remote proc trans" value from sp_configure. If it's 1, you
need
> > to change it to 0.
> >
> >
> > "Robert M." <RobertM@.discussions.microsoft.com> wrote in message
> > news:28E165FE-67B2-44F1-98DC-1F2CB3EA0B7E@.microsoft.com...
> > > Information:
> > > SQL Server 2000 Enterprise Edition
> > > Server Pack 3a
> > > Server Edition is Windows 2000 Advanced Server
> > > Replication Type is Transactional
> > >
> > > I am trying to drop a publication. I am getting the error:
> > > Error 627:Cannot use SAVE TRANSACTION within a distributed transaction
> > >
> > > This error also is generated when trying to drop the individual
> > subscription.
> > >
> > > Can anyone provide insight as to what could be causing this error?
> > >
> > > Thank you in advance.
> > >
> > > Robert M.
> > >
> > >
> >
> >
> >
SQL Server 2000 Enterprise Edition
Server Pack 3a
Server Edition is Windows 2000 Advanced Server
Replication Type is Transactional
I am trying to drop a publication. I am getting the error:
Error 627:Cannot use SAVE TRANSACTION within a distributed transaction
This error also is generated when trying to drop the individual subscription.
Can anyone provide insight as to what could be causing this error?
Thank you in advance.
Robert M.Check the "remote proc trans" value from sp_configure. If it's 1, you need
to change it to 0.
"Robert M." <RobertM@.discussions.microsoft.com> wrote in message
news:28E165FE-67B2-44F1-98DC-1F2CB3EA0B7E@.microsoft.com...
> Information:
> SQL Server 2000 Enterprise Edition
> Server Pack 3a
> Server Edition is Windows 2000 Advanced Server
> Replication Type is Transactional
> I am trying to drop a publication. I am getting the error:
> Error 627:Cannot use SAVE TRANSACTION within a distributed transaction
> This error also is generated when trying to drop the individual
subscription.
> Can anyone provide insight as to what could be causing this error?
> Thank you in advance.
> Robert M.
>|||I am assuming that the 'remote proc trans' setting needs to be set to zero on
the publisher (i have separate servers for distributor/publisher/subscriber).
Thanks...Robert M.
"Richard Ding" wrote:
> Check the "remote proc trans" value from sp_configure. If it's 1, you need
> to change it to 0.
>
> "Robert M." <RobertM@.discussions.microsoft.com> wrote in message
> news:28E165FE-67B2-44F1-98DC-1F2CB3EA0B7E@.microsoft.com...
> > Information:
> > SQL Server 2000 Enterprise Edition
> > Server Pack 3a
> > Server Edition is Windows 2000 Advanced Server
> > Replication Type is Transactional
> >
> > I am trying to drop a publication. I am getting the error:
> > Error 627:Cannot use SAVE TRANSACTION within a distributed transaction
> >
> > This error also is generated when trying to drop the individual
> subscription.
> >
> > Can anyone provide insight as to what could be causing this error?
> >
> > Thank you in advance.
> >
> > Robert M.
> >
> >
>
>|||Actually, I believe it should be set to 0 on the subscriber. No need to
touch the values on publisher or distributor.
"Robert M." <RobertM@.discussions.microsoft.com> wrote in message
news:4A56EC3F-0537-441C-990E-0C7D43BD972C@.microsoft.com...
> I am assuming that the 'remote proc trans' setting needs to be set to zero
on
> the publisher (i have separate servers for
distributor/publisher/subscriber).
> Thanks...Robert M.
> "Richard Ding" wrote:
> > Check the "remote proc trans" value from sp_configure. If it's 1, you
need
> > to change it to 0.
> >
> >
> > "Robert M." <RobertM@.discussions.microsoft.com> wrote in message
> > news:28E165FE-67B2-44F1-98DC-1F2CB3EA0B7E@.microsoft.com...
> > > Information:
> > > SQL Server 2000 Enterprise Edition
> > > Server Pack 3a
> > > Server Edition is Windows 2000 Advanced Server
> > > Replication Type is Transactional
> > >
> > > I am trying to drop a publication. I am getting the error:
> > > Error 627:Cannot use SAVE TRANSACTION within a distributed transaction
> > >
> > > This error also is generated when trying to drop the individual
> > subscription.
> > >
> > > Can anyone provide insight as to what could be causing this error?
> > >
> > > Thank you in advance.
> > >
> > > Robert M.
> > >
> > >
> >
> >
> >
Wednesday, March 7, 2012
Error 3930, The current transaction cannot be committed and cannot support
I am trying to exectue this store procedure and I am getting this error
Msg 3930, Level 16, State 1, Line 190
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
I am using a linked server "SODIM" and I am trying to execute a distributed transaction between my SQL Server 2005 Express Edition and this linked server SQL Server 2005 standar Edition.
I need to set the isolation level to serializable because I need to block tbl_GuiasDistribuidores table.
I`m executing this SP under sa user and I dont know if a need certanly permissions in the database or in the MS DTC.
I dont know what I`m missing, Could give me some help?
This is the sp code:
ALTERPROCEDURE [dbo].[PCN_ASIGNARGUIAS]
@.Planta varchar(15),
@.DB varchar(50)
AS
BEGIN
DECLARE @.lvaSentencia VARCHAR(8000)
set @.lvaSentencia ='
SET XACT_ABORT on
begin try
begin transaction
declare @.DisCodigo as varchar(15)
declare @.idProceso as int
declare @.guia as numeric
declare @.idDato as int
declare @.Estado as bit
declare @.GuiaFinal as numeric
declare @.AsignarGuia as varchar(2)
delete tbl_ErrorRangosDistribuidores
IF OBJECT_ID('+'''ErrorGuias'''+') IS NOT NULL
BEGIN
drop table ErrorGuias
END
insert into sodim.'+ @.DB +'.dbo.Tbl_ArchivosSubidos
(Ars_Fecha,
Doc_Codigo,
Ars_NombreArchivo,
Ars_NombreArchivoSubido,
Ars_OrdenProduccion,
Ars_Requerimiento,
Ars_OrdenServicio,
Ars_Ciclo,
Ars_UsuarioUltimoCambioEfectuado,
Ars_FechaUltimoCambioEfectuado)
select Ars_Fecha,
Doc_Codigo,
Ars_NombreArchivo,
Ars_NombreArchivoSubido,
Ars_OrdenProduccion,
Ars_Requerimiento,
Ars_OrdenServicio,
Ars_Ciclo,
Ars_UsuarioUltimoCambioEfectuado,
Ars_FechaUltimoCambioEfectuado
from tbl_archivossubidos
SELECT @.idProceso=a.Ars_Codigo FROM sodim.'+ @.DB +'.dbo.Tbl_ArchivosSubidos a, Tbl_ArchivosSubidos b
where a.Ars_FechaUltimoCambioEfectuado = b.Ars_FechaUltimoCambioEfectuado
and a.Ars_Fecha = b.Ars_Fecha
and a.Ars_NombreArchivo = b.Ars_NombreArchivo
and a.Ars_OrdenProduccion = b.Ars_OrdenProduccion
and a.Doc_Codigo = b.Doc_Codigo
declare CursorCodDistribuidor cursor for
select Dis_Codigo,IndiceOLlaveunica from VisOrdenamientoDatosFinalCiudades
order by Dis_Codigo
open CursorCodDistribuidor
fetch next from CursorCodDistribuidor
into @.DisCodigo,@.idDato
set @.Estado = 1
while @.@.fetch_status = 0
begin
SET TRANSACTION ISOLATION LEVEL Read COMMITTED
--SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
select @.Estado = Gdis_Estado from sodim.'+ @.DB +'.dbo.tbl_GuiasDistribuidores with(TABLOCKX SERIALIZABLE)
where
Dis_codigo = @.DisCodigo and plan_Codigo = '+ @.Planta +'
while @.Estado = 1
begin
select @.Estado = Gdis_Estado from sodim.'+ @.DB +'.dbo.tbl_GuiasDistribuidores with(TABLOCKX SERIALIZABLE)
where
Dis_codigo = @.DisCodigo and plan_Codigo = '+ @.Planta +'
end
update sodim.'+ @.DB +'.dbo.tbl_guiasDistribuidores with(TABLOCKX SERIALIZABLE)
set GDis_Estado = 1
where
Dis_codigo = @.DisCodigo
and plan_Codigo = '+ @.Planta +'
select @.AsignarGuia = Dis_ImprimirGuia from sodim.'+ @.DB +'.dbo.tbl_Distribuidores --with(TABLOCKX SERIALIZABLE)
where Dis_Codigo = @.DisCodigo
if @.AsignarGuia = '+'''SI'''+'
begin
set @.guia = -1
--consulta si hay guias disponibles en la tabla Tbl_GuiasActivas
select @.guia = GAct_Guia from sodim.'+ @.DB +'.dbo.Tbl_GuiasActivas
where dis_Codigo = @.DisCodigo
and plan_Codigo = '+ @.Planta +'
order by GAct_Guia desc
--si no hay guias disponibles en la tabla Tbl_GuiasActivas, la variable @.guia = -1
if @.guia = -1
begin
-- consulta el valor de GDis_RangoFinal y lo almacena en la variable @.GuiaFinal
select @.GuiaFinal = GDis_RangoFinal from sodim.'+ @.DB +'.dbo.Tbl_GuiasDistribuidores
where Dis_codigo = @.DisCodigo
and Plan_Codigo = '+ @.Planta +'
-- consulta el valor de la siguiente guia disponible y lo almacena en la variable @.guia
select @.guia = convert(numeric,GDis_ultimaguia) + 1 from sodim.'+ @.DB +'.dbo.Tbl_GuiasDistribuidores
where dis_Codigo = @.DisCodigo
and Plan_Codigo = '+ @.Planta +'
-- si @.guia > @.GuiaFinal no hay guias disponibles
if @.guia > @.GuiaFinal
BEGIN
--PRINT '+'''@.guia > @.GuiaFinal'''+'
--PRINT @.guia
--PRINT @.GuiaFinal
--consulta el valor de GDis_RangoFinalReserva y lo almacena en la variable @.GuiaFinal
select @.GuiaFinal = GDis_RangoFinalReserva from sodim.'+ @.DB +'.dbo.Tbl_GuiasDistribuidores
where Dis_codigo = @.DisCodigo
and Plan_Codigo = '+ @.Planta +'
--si @.GuiaFinal = 0 no existen guias en el rango de reserva (no hay guias para asignar)
if @.GuiaFinal = 0
begin
--PRINT '+'''entro error guias'''+'
insert into Tbl_ErrorRangosDistribuidores
(Dis_Codigo,error)
values
(@.DisCodigo,'+'''Se agot el rango principal y de reserva para el Distribuidor'''+')
--PRINT '+'''despues del insert'''+'
RAISERROR (@.DisCodigo, 16, 1)
end
--no hay guias para asignar pero si hay guias en reserva
else
BEGIN
insert into sodim.'+ @.DB +'.dbo.tbl_HistoricoRangoGuias
(Dis_Codigo,HR_RangoInicial,HR_RangoFinal)
select Dis_Codigo,GDis_RangoInicial,GDis_RangoFinal from
sodim.'+ @.DB +'.dbo.Tbl_GuiasDistribuidores
where dis_Codigo = @.DisCodigo
and Plan_Codigo = '+ @.Planta +'
--asigna las guias de reserva al rango principal
update sodim.'+ @.DB +'.dbo.Tbl_GuiasDistribuidores
set
GDis_RangoInicial = GDis_RangoInicialReserva
,GDis_RangoFinal = GDis_RangoFinalReserva
,GDis_UltimaGuia = GDis_RangoInicialReserva
,GDis_RangoInicialReserva = 0
,GDis_RangoFinalReserva = 0
where dis_Codigo = @.DisCodigo
and Plan_Codigo = '+ @.Planta +'
--selecciona la ultima guia disponible y la almacena en la variable @.guia
select @.guia = GDis_UltimaGuia from sodim.'+ @.DB +'.dbo.Tbl_GuiasDistribuidores
where dis_codigo = @.DisCodigo
and Plan_Codigo = '+ @.Planta +'
update sodim.'+ @.DB +'.dbo.Tbl_GuiasDistribuidores
set
GDis_ultimaguia = @.guia
where dis_Codigo = @.DisCodigo
and Plan_Codigo = '+ @.Planta +'
insert into Tbl_ErrorRangosDistribuidores
(Dis_Codigo,error)
values
(@.DisCodigo,'+'''se agoto el rango principal para el Distribuidor'''+')
end
end
--no hay guias disponibles en la tabla tbl_guiasActivas y si hay guias disponibles del distribuidor
else
BEGIN
--PRINT '+'''graba ultima guia'''+'
--PRINT @.guia
update sodim.'+ @.DB +'.dbo.Tbl_GuiasDistribuidores
set
GDis_ultimaguia = @.guia
where dis_Codigo = @.DisCodigo
and Plan_Codigo = '+ @.Planta +'
end
end
else
BEGIN
--PRINT '+'''elimina Guia activa'''+'
--PRINT @.guia
delete sodim.'+ @.DB +'.dbo.Tbl_GuiasActivas
where GAct_Guia = @.guia
and dis_Codigo = @.DisCodigo
END
--PRINT '+'''guarda la Guia en Guia asignada'''+'
insert into sodim.'+ @.DB +'.dbo.Tbl_GuiasAsignadas
(Ars_Codigo,Dis_Codigo,GA_Guia,Plan_Codigo)
values
(@.idProceso,@.DisCodigo,@.guia,'+ @.Planta +')
--PRINT '+'''guarda la Guia en datos subidos'''+'
--PRINT @.guia
update dbo.Tbl_datosSubidos
set Guia = @.guia
where IndiceOLlaveunica = @.idDato
END
update sodim.'+ @.DB +'.dbo.tbl_guiasDistribuidores with(TABLOCKX SERIALIZABLE)
set GDis_Estado = 0
where
Dis_codigo = @.DisCodigo and plan_Codigo = '+ @.Planta +'
--PRINT '+'''lee otro registro del cursor'''+'
fetch next from CursorCodDistribuidor
into @.DisCodigo,@.idDato
--PRINT '+'''despues del commit'''+'
end
commit transaction
end try
--manejo de errores
begin catch
insert into sodim.'+ @.DB +'.dbo.Tbl_GuiasActivas
(Dis_Codigo,GAct_Guia,Plan_Codigo)
select Dis_Codigo,GA_Guia,plan_Codigo from sodim.'+ @.DB +'.dbo.Tbl_GuiasAsignadas
where Ars_Codigo = @.idProceso
update Tbl_DatosSubidos
set
guia = '+'''SIN DATOS'''+'
update sodim.'+ @.DB +'.dbo.tbl_guiasDistribuidores with(TABLOCKX SERIALIZABLE)
set GDis_Estado = 0
--PRINT '+'''mostrar error'''+'
select
ERROR_NUMBER() AS Numero,
ERROR_LINE() AS Linea,
ERROR_MESSAGE() AS Mensaje
into ErrorGuias
rollback transaction
end catch
close CursorCodDistribuidor
deallocate CursorCodDistribuidor
delete sodim.'+ @.DB +'.dbo.Tbl_GuiasAsignadas
where Ars_Codigo = @.idProceso
delete sodim.'+ @.DB +'.dbo.Tbl_ArchivosSubidos
where Ars_Codigo = @.idProceso
SET TRANSACTION ISOLATION LEVEL Read COMMITTED'
--PRINT @.lvaSentencia
EXEC(@.lvaSentencia)
end
try rolling back the failed transaction before you update data in your catch() block.
e.g.
begin catch
if xact_state()=-1 rollback
insert into sodim.'+ @.DB +'.dbo.Tbl_GuiasActivas
error 3926 , server failed to resume transaction
In a high load asp.net environment, I am getting an error.
The transation active in this session has been committed or aborted by another session.
Here is the code , I am getting error.
1object returnObject =null;2 SqlTransaction sqlTrans =null;3try4 {5//Getting new SqlConnection6 comm.Connection = GetConnection();7//Opening connection8 OpenConn(comm.Connection);9//beginning transaction10 sqlTrans = comm.Connection.BeginTransaction( IsolationLevel.ReadCommitted );11//setting transaction to SqlCommand object12 comm.Transaction = sqlTrans;13//executing operation14 returnObject = comm.ExecuteScalar();15//trying to commit.16 sqlTrans.Commit();171819 }20catch (SqlException sex)21 {22if (sqlTrans !=null)23 {24 sqlTrans.Rollback();25 }26 }27finally28 {29 comm.Connection.Close();30 } Could you please explain , am I doing smthg wrong ?Check out this link: http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2041937&SiteID=17
Good luck.
|||Hi CS4Ever ,
Thanks for the reply, but as far as I understood , Thona just submitted the situation , not a solution and the 3rd reply belongs to me :)
What could I possibly do ? Submit a bug to Microsoft ?
Thanks in advance.
|||Still no answers ?
Error 3627 (Could not create worker thread) - Transaction Log Back
Recieved this error this morning, at the same time are users were kicked out
of SQL Server.
We have anout 1500 connections at a time. There are no SQL Error log entries
other than the Transaction Log failing, no Application Events or System
Events.
The server did not go down, the CPUs (8) went to almost zero.
SQL Server Enterprise Manager was getting a network error message when
trying to connect and within 5 minutes everything was OK.
Any help?
Thanks,
ThomasLL
Could it be that you were over your max worker threads?
http://msdn2.microsoft.com/en-us/library/ms187024.aspx
When all worker threads are active with long running queries, SQL Server may
appear unresponsive until a worker thread completes and becomes available.
Though not a defect, this can sometimes be undesirable. If a process appears
to be unresponsive and no new queries can be processed, then connect to SQL
Server using the dedicated administrator connection (DAC), and kill the
process. To prevent this, increase the number of max worker threads.
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Thomas.LeBlanc@.NoSpam.Com"
<ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote in message
news:759C0C8D-1413-45CB-BE38-743CF952CBF5@.microsoft.com...
> Recieved this error this morning, at the same time are users were kicked
> out
> of SQL Server.
> We have anout 1500 connections at a time. There are no SQL Error log
> entries
> other than the Transaction Log failing, no Application Events or System
> Events.
> The server did not go down, the CPUs (8) went to almost zero.
> SQL Server Enterprise Manager was getting a network error message when
> trying to connect and within 5 minutes everything was OK.
> Any help?
> Thanks,
> ThomasLL
|||Are you using any third party backup software?
You can also get this error if you are running low on system
resources - mostly memory.
You may have too much of a server load at the time you tried
to do the backup. You'd want to get some baselines numbers
using Performance Monitor.
-Sue
On Mon, 9 Oct 2006 09:54:02 -0700, Thomas.LeBlanc@.NoSpam.Com
<ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote:
>Recieved this error this morning, at the same time are users were kicked out
>of SQL Server.
>We have anout 1500 connections at a time. There are no SQL Error log entries
>other than the Transaction Log failing, no Application Events or System
>Events.
>The server did not go down, the CPUs (8) went to almost zero.
>SQL Server Enterprise Manager was getting a network error message when
>trying to connect and within 5 minutes everything was OK.
>Any help?
>Thanks,
>ThomasLL
|||Thanks everyone!!!
Thanks,
ThomasLL, MCDBA
"Thomas.LeBlanc@.NoSpam.Com" wrote:
> Recieved this error this morning, at the same time are users were kicked out
> of SQL Server.
> We have anout 1500 connections at a time. There are no SQL Error log entries
> other than the Transaction Log failing, no Application Events or System
> Events.
> The server did not go down, the CPUs (8) went to almost zero.
> SQL Server Enterprise Manager was getting a network error message when
> trying to connect and within 5 minutes everything was OK.
> Any help?
> Thanks,
> ThomasLL
of SQL Server.
We have anout 1500 connections at a time. There are no SQL Error log entries
other than the Transaction Log failing, no Application Events or System
Events.
The server did not go down, the CPUs (8) went to almost zero.
SQL Server Enterprise Manager was getting a network error message when
trying to connect and within 5 minutes everything was OK.
Any help?
Thanks,
ThomasLL
Could it be that you were over your max worker threads?
http://msdn2.microsoft.com/en-us/library/ms187024.aspx
When all worker threads are active with long running queries, SQL Server may
appear unresponsive until a worker thread completes and becomes available.
Though not a defect, this can sometimes be undesirable. If a process appears
to be unresponsive and no new queries can be processed, then connect to SQL
Server using the dedicated administrator connection (DAC), and kill the
process. To prevent this, increase the number of max worker threads.
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Thomas.LeBlanc@.NoSpam.Com"
<ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote in message
news:759C0C8D-1413-45CB-BE38-743CF952CBF5@.microsoft.com...
> Recieved this error this morning, at the same time are users were kicked
> out
> of SQL Server.
> We have anout 1500 connections at a time. There are no SQL Error log
> entries
> other than the Transaction Log failing, no Application Events or System
> Events.
> The server did not go down, the CPUs (8) went to almost zero.
> SQL Server Enterprise Manager was getting a network error message when
> trying to connect and within 5 minutes everything was OK.
> Any help?
> Thanks,
> ThomasLL
|||Are you using any third party backup software?
You can also get this error if you are running low on system
resources - mostly memory.
You may have too much of a server load at the time you tried
to do the backup. You'd want to get some baselines numbers
using Performance Monitor.
-Sue
On Mon, 9 Oct 2006 09:54:02 -0700, Thomas.LeBlanc@.NoSpam.Com
<ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote:
>Recieved this error this morning, at the same time are users were kicked out
>of SQL Server.
>We have anout 1500 connections at a time. There are no SQL Error log entries
>other than the Transaction Log failing, no Application Events or System
>Events.
>The server did not go down, the CPUs (8) went to almost zero.
>SQL Server Enterprise Manager was getting a network error message when
>trying to connect and within 5 minutes everything was OK.
>Any help?
>Thanks,
>ThomasLL
|||Thanks everyone!!!
Thanks,
ThomasLL, MCDBA
"Thomas.LeBlanc@.NoSpam.Com" wrote:
> Recieved this error this morning, at the same time are users were kicked out
> of SQL Server.
> We have anout 1500 connections at a time. There are no SQL Error log entries
> other than the Transaction Log failing, no Application Events or System
> Events.
> The server did not go down, the CPUs (8) went to almost zero.
> SQL Server Enterprise Manager was getting a network error message when
> trying to connect and within 5 minutes everything was OK.
> Any help?
> Thanks,
> ThomasLL
Error 3627 (Could not create worker thread) - Transaction Log Back
Recieved this error this morning, at the same time are users were kicked out
of SQL Server.
We have anout 1500 connections at a time. There are no SQL Error log entries
other than the Transaction Log failing, no Application Events or System
Events.
The server did not go down, the CPUs (8) went to almost zero.
SQL Server Enterprise Manager was getting a network error message when
trying to connect and within 5 minutes everything was OK.
Any help?
Thanks,
ThomasLLCould it be that you were over your max worker threads?
http://msdn2.microsoft.com/en-us/library/ms187024.aspx
When all worker threads are active with long running queries, SQL Server may
appear unresponsive until a worker thread completes and becomes available.
Though not a defect, this can sometimes be undesirable. If a process appears
to be unresponsive and no new queries can be processed, then connect to SQL
Server using the dedicated administrator connection (DAC), and kill the
process. To prevent this, increase the number of max worker threads.
--
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Thomas.LeBlanc@.NoSpam.Com"
<ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote in message
news:759C0C8D-1413-45CB-BE38-743CF952CBF5@.microsoft.com...
> Recieved this error this morning, at the same time are users were kicked
> out
> of SQL Server.
> We have anout 1500 connections at a time. There are no SQL Error log
> entries
> other than the Transaction Log failing, no Application Events or System
> Events.
> The server did not go down, the CPUs (8) went to almost zero.
> SQL Server Enterprise Manager was getting a network error message when
> trying to connect and within 5 minutes everything was OK.
> Any help?
> Thanks,
> ThomasLL|||Are you using any third party backup software?
You can also get this error if you are running low on system
resources - mostly memory.
You may have too much of a server load at the time you tried
to do the backup. You'd want to get some baselines numbers
using Performance Monitor.
-Sue
On Mon, 9 Oct 2006 09:54:02 -0700, Thomas.LeBlanc@.NoSpam.Com
<ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote:
>Recieved this error this morning, at the same time are users were kicked out
>of SQL Server.
>We have anout 1500 connections at a time. There are no SQL Error log entries
>other than the Transaction Log failing, no Application Events or System
>Events.
>The server did not go down, the CPUs (8) went to almost zero.
>SQL Server Enterprise Manager was getting a network error message when
>trying to connect and within 5 minutes everything was OK.
>Any help?
>Thanks,
>ThomasLL|||We are on SQL Server 2000, no error message that we have exceeded Work Threads.
We do have 8 processors and 64 Gigs, neither are maxed out.
Thanks for your responce.
Most sites I have read say you should NOT increase worker threads because of
overhead with more threads.
Cache Hit Ratio 101.05824
Cache Flushes 0.0
Free Page Scan (Avg) 0.0
Free Page Scan (Max) 0.0
Min Free Buffers 331.0
Cache Size 4362.0
Free Buffers 2261.0
--
Thanks,
ThomasLL
"Warren Brunk" wrote:
> Could it be that you were over your max worker threads?
> http://msdn2.microsoft.com/en-us/library/ms187024.aspx
> When all worker threads are active with long running queries, SQL Server may
> appear unresponsive until a worker thread completes and becomes available.
> Though not a defect, this can sometimes be undesirable. If a process appears
> to be unresponsive and no new queries can be processed, then connect to SQL
> Server using the dedicated administrator connection (DAC), and kill the
> process. To prevent this, increase the number of max worker threads.
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "Thomas.LeBlanc@.NoSpam.Com"
> <ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote in message
> news:759C0C8D-1413-45CB-BE38-743CF952CBF5@.microsoft.com...
> > Recieved this error this morning, at the same time are users were kicked
> > out
> > of SQL Server.
> >
> > We have anout 1500 connections at a time. There are no SQL Error log
> > entries
> > other than the Transaction Log failing, no Application Events or System
> > Events.
> >
> > The server did not go down, the CPUs (8) went to almost zero.
> >
> > SQL Server Enterprise Manager was getting a network error message when
> > trying to connect and within 5 minutes everything was OK.
> >
> > Any help?
> >
> > Thanks,
> > ThomasLL
>
>|||> Are you using any third party backup software?
No
We are on SQL Server 2000, no error message that we have exceeded Work
Threads.
We do have 8 processors and 64 Gigs, neither are maxed out.
Thanks for your responce.
Most sites I have read say you should NOT increase worker threads because of
overhead with more threads.
Cache Hit Ratio 101.05824
Cache Flushes 0.0
Free Page Scan (Avg) 0.0
Free Page Scan (Max) 0.0
Min Free Buffers 331.0
Cache Size 4362.0
Free Buffers 2261.0
--
Thanks,
ThomasLL
--
Thanks,
ThomasLL,
"Sue Hoegemeier" wrote:
> Are you using any third party backup software?
> You can also get this error if you are running low on system
> resources - mostly memory.
> You may have too much of a server load at the time you tried
> to do the backup. You'd want to get some baselines numbers
> using Performance Monitor.
> -Sue
> On Mon, 9 Oct 2006 09:54:02 -0700, Thomas.LeBlanc@.NoSpam.Com
> <ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote:
> >Recieved this error this morning, at the same time are users were kicked out
> >of SQL Server.
> >
> >We have anout 1500 connections at a time. There are no SQL Error log entries
> >other than the Transaction Log failing, no Application Events or System
> >Events.
> >
> >The server did not go down, the CPUs (8) went to almost zero.
> >
> >SQL Server Enterprise Manager was getting a network error message when
> >trying to connect and within 5 minutes everything was OK.
> >
> >Any help?
> >
> >Thanks,
> >ThomasLL
>|||You are correct about the worker threads which is why I
didn't suggest changing that setting. On SQL 2000, you will
almost always have problems if you increase the value from
the default 255. It is not advisable and you would want to
be monitoring UMS stats as well as other indicators before
even considering changing that.
But...most of the time the error you received is really due
to resource constraints. A one time snapshot of a few
performance metrics isn't going to tell you much. The
article on sql-server-performance.com for performance audits
provides some good information on what you want to check:
http://www.sql-server-performance.com/sql_server_performance_audit.asp
-Sue
On Mon, 9 Oct 2006 13:32:01 -0700, Thomas.LeBlanc@.NoSpam.Com
<ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote:
>> Are you using any third party backup software?
>No
>We are on SQL Server 2000, no error message that we have exceeded Work
>Threads.
>We do have 8 processors and 64 Gigs, neither are maxed out.
>Thanks for your responce.
>Most sites I have read say you should NOT increase worker threads because of
>overhead with more threads.
>Cache Hit Ratio 101.05824
>Cache Flushes 0.0
>Free Page Scan (Avg) 0.0
>Free Page Scan (Max) 0.0
>Min Free Buffers 331.0
>Cache Size 4362.0
>Free Buffers 2261.0
>--
>Thanks,
>ThomasLL|||Thanks everyone!!!
--
Thanks,
ThomasLL, MCDBA
"Thomas.LeBlanc@.NoSpam.Com" wrote:
> Recieved this error this morning, at the same time are users were kicked out
> of SQL Server.
> We have anout 1500 connections at a time. There are no SQL Error log entries
> other than the Transaction Log failing, no Application Events or System
> Events.
> The server did not go down, the CPUs (8) went to almost zero.
> SQL Server Enterprise Manager was getting a network error message when
> trying to connect and within 5 minutes everything was OK.
> Any help?
> Thanks,
> ThomasLL
of SQL Server.
We have anout 1500 connections at a time. There are no SQL Error log entries
other than the Transaction Log failing, no Application Events or System
Events.
The server did not go down, the CPUs (8) went to almost zero.
SQL Server Enterprise Manager was getting a network error message when
trying to connect and within 5 minutes everything was OK.
Any help?
Thanks,
ThomasLLCould it be that you were over your max worker threads?
http://msdn2.microsoft.com/en-us/library/ms187024.aspx
When all worker threads are active with long running queries, SQL Server may
appear unresponsive until a worker thread completes and becomes available.
Though not a defect, this can sometimes be undesirable. If a process appears
to be unresponsive and no new queries can be processed, then connect to SQL
Server using the dedicated administrator connection (DAC), and kill the
process. To prevent this, increase the number of max worker threads.
--
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Thomas.LeBlanc@.NoSpam.Com"
<ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote in message
news:759C0C8D-1413-45CB-BE38-743CF952CBF5@.microsoft.com...
> Recieved this error this morning, at the same time are users were kicked
> out
> of SQL Server.
> We have anout 1500 connections at a time. There are no SQL Error log
> entries
> other than the Transaction Log failing, no Application Events or System
> Events.
> The server did not go down, the CPUs (8) went to almost zero.
> SQL Server Enterprise Manager was getting a network error message when
> trying to connect and within 5 minutes everything was OK.
> Any help?
> Thanks,
> ThomasLL|||Are you using any third party backup software?
You can also get this error if you are running low on system
resources - mostly memory.
You may have too much of a server load at the time you tried
to do the backup. You'd want to get some baselines numbers
using Performance Monitor.
-Sue
On Mon, 9 Oct 2006 09:54:02 -0700, Thomas.LeBlanc@.NoSpam.Com
<ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote:
>Recieved this error this morning, at the same time are users were kicked out
>of SQL Server.
>We have anout 1500 connections at a time. There are no SQL Error log entries
>other than the Transaction Log failing, no Application Events or System
>Events.
>The server did not go down, the CPUs (8) went to almost zero.
>SQL Server Enterprise Manager was getting a network error message when
>trying to connect and within 5 minutes everything was OK.
>Any help?
>Thanks,
>ThomasLL|||We are on SQL Server 2000, no error message that we have exceeded Work Threads.
We do have 8 processors and 64 Gigs, neither are maxed out.
Thanks for your responce.
Most sites I have read say you should NOT increase worker threads because of
overhead with more threads.
Cache Hit Ratio 101.05824
Cache Flushes 0.0
Free Page Scan (Avg) 0.0
Free Page Scan (Max) 0.0
Min Free Buffers 331.0
Cache Size 4362.0
Free Buffers 2261.0
--
Thanks,
ThomasLL
"Warren Brunk" wrote:
> Could it be that you were over your max worker threads?
> http://msdn2.microsoft.com/en-us/library/ms187024.aspx
> When all worker threads are active with long running queries, SQL Server may
> appear unresponsive until a worker thread completes and becomes available.
> Though not a defect, this can sometimes be undesirable. If a process appears
> to be unresponsive and no new queries can be processed, then connect to SQL
> Server using the dedicated administrator connection (DAC), and kill the
> process. To prevent this, increase the number of max worker threads.
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "Thomas.LeBlanc@.NoSpam.Com"
> <ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote in message
> news:759C0C8D-1413-45CB-BE38-743CF952CBF5@.microsoft.com...
> > Recieved this error this morning, at the same time are users were kicked
> > out
> > of SQL Server.
> >
> > We have anout 1500 connections at a time. There are no SQL Error log
> > entries
> > other than the Transaction Log failing, no Application Events or System
> > Events.
> >
> > The server did not go down, the CPUs (8) went to almost zero.
> >
> > SQL Server Enterprise Manager was getting a network error message when
> > trying to connect and within 5 minutes everything was OK.
> >
> > Any help?
> >
> > Thanks,
> > ThomasLL
>
>|||> Are you using any third party backup software?
No
We are on SQL Server 2000, no error message that we have exceeded Work
Threads.
We do have 8 processors and 64 Gigs, neither are maxed out.
Thanks for your responce.
Most sites I have read say you should NOT increase worker threads because of
overhead with more threads.
Cache Hit Ratio 101.05824
Cache Flushes 0.0
Free Page Scan (Avg) 0.0
Free Page Scan (Max) 0.0
Min Free Buffers 331.0
Cache Size 4362.0
Free Buffers 2261.0
--
Thanks,
ThomasLL
--
Thanks,
ThomasLL,
"Sue Hoegemeier" wrote:
> Are you using any third party backup software?
> You can also get this error if you are running low on system
> resources - mostly memory.
> You may have too much of a server load at the time you tried
> to do the backup. You'd want to get some baselines numbers
> using Performance Monitor.
> -Sue
> On Mon, 9 Oct 2006 09:54:02 -0700, Thomas.LeBlanc@.NoSpam.Com
> <ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote:
> >Recieved this error this morning, at the same time are users were kicked out
> >of SQL Server.
> >
> >We have anout 1500 connections at a time. There are no SQL Error log entries
> >other than the Transaction Log failing, no Application Events or System
> >Events.
> >
> >The server did not go down, the CPUs (8) went to almost zero.
> >
> >SQL Server Enterprise Manager was getting a network error message when
> >trying to connect and within 5 minutes everything was OK.
> >
> >Any help?
> >
> >Thanks,
> >ThomasLL
>|||You are correct about the worker threads which is why I
didn't suggest changing that setting. On SQL 2000, you will
almost always have problems if you increase the value from
the default 255. It is not advisable and you would want to
be monitoring UMS stats as well as other indicators before
even considering changing that.
But...most of the time the error you received is really due
to resource constraints. A one time snapshot of a few
performance metrics isn't going to tell you much. The
article on sql-server-performance.com for performance audits
provides some good information on what you want to check:
http://www.sql-server-performance.com/sql_server_performance_audit.asp
-Sue
On Mon, 9 Oct 2006 13:32:01 -0700, Thomas.LeBlanc@.NoSpam.Com
<ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote:
>> Are you using any third party backup software?
>No
>We are on SQL Server 2000, no error message that we have exceeded Work
>Threads.
>We do have 8 processors and 64 Gigs, neither are maxed out.
>Thanks for your responce.
>Most sites I have read say you should NOT increase worker threads because of
>overhead with more threads.
>Cache Hit Ratio 101.05824
>Cache Flushes 0.0
>Free Page Scan (Avg) 0.0
>Free Page Scan (Max) 0.0
>Min Free Buffers 331.0
>Cache Size 4362.0
>Free Buffers 2261.0
>--
>Thanks,
>ThomasLL|||Thanks everyone!!!
--
Thanks,
ThomasLL, MCDBA
"Thomas.LeBlanc@.NoSpam.Com" wrote:
> Recieved this error this morning, at the same time are users were kicked out
> of SQL Server.
> We have anout 1500 connections at a time. There are no SQL Error log entries
> other than the Transaction Log failing, no Application Events or System
> Events.
> The server did not go down, the CPUs (8) went to almost zero.
> SQL Server Enterprise Manager was getting a network error message when
> trying to connect and within 5 minutes everything was OK.
> Any help?
> Thanks,
> ThomasLL
Subscribe to:
Posts (Atom)