May 28, 2003 at 9:19 am
I have 2 SQL servers in the same network
One is the linked server of second
and when it do a simple query as
select top 1 * from db2.dbname.dbo.table
sometime get error like this
Server: Msg 7392, Level 16, State 2, Line 1
Could not start a transaction for OLE DB provider 'SQLOLEDB'.
[OLE/DB provider returned message: Cannot create new transaction because capacity was exceeded.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionLocal::StartTransaction returned 0x8004d01d: ISOLEVEL=4096].
May 28, 2003 at 9:51 am
This KB may help.
http://support.microsoft.com/default.aspx?scid=kb;en-us;306649
May 28, 2003 at 11:15 am
what is means of process status
Dormant ?
I have many processes in db2
with this status and with hostname
of db1
db1 usualy query db2
May 28, 2003 at 11:20 am
I killed this processes
It help me for 10-15 minutes
declare @SPID int, @STR varchar(50);
declare cUser cursor local static forward_only for
select spid from master..sysprocesses
where Status = 'dormant' and HostName = 'db1'
open cUser;
fetch next from cUser into @SPID;
while (@@FETCH_STATUS = 0)
begin
set @STR = 'kill ' + cast(@SPID as varchar(50));
exec(@Str)
end
fetch next from cUser into @SPID;
end
close cUser;
deallocate cUser;
June 2, 2003 at 9:16 am
I did job that kill dormant connections
and runt every minute
This job give me temporary quite good solution of this problem
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply