Linked server errors

• dimadd

  SSC-Addicted

  Points: 450

  More actions

  May 28, 2003 at 9:19 am

  #64815

  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].

• Allen Cui-55137

  SSC Guru

  Points: 51650

  More actions

  May 28, 2003 at 9:51 am

  #458867

  This KB may help.

  http://support.microsoft.com/default.aspx?scid=kb;en-us;306649

• dimadd

  SSC-Addicted

  Points: 450

  More actions

  May 28, 2003 at 11:15 am

  #458883

  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

• dimadd

  SSC-Addicted

  Points: 450

  More actions

  May 28, 2003 at 11:20 am

  #458884

  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;

• dimadd

  SSC-Addicted

  Points: 450

  More actions

  June 2, 2003 at 9:16 am

  #459357

  I did job that kill dormant connections

  and runt every minute

  This job give me temporary quite good solution of this problem