Long running SQLTransaction

Hi all,
we have a strange problem in SQL2000 SP3a with SQLTransaction's.
When running SQL Profiler the duration for some SQLTransactions (Commit) are
over six seconds, but the individual SQL statements (sp's) are executed at no
time at all.
I've used sp_lock but there are no locks.
Here's an extract from SQLProfiler:
SQLTransaction Commit 6796 5 2005-03-04 15:30:27.887 2005-03-04
15:30:34.683 DTCXact 16335154 sa
Any Ideas ?
Regards Tommy S.Check out master..sysprocesses and find out what the waittypes are
to determine what is causing the extended commit operation.
is the Tlog file on the same disk as the data file . .how busy is the server?
is this constantly reproduceable? or is it intermittent?
HTH
"Tommy Söderkvist" wrote:
> Hi all,
> we have a strange problem in SQL2000 SP3a with SQLTransaction's.
> When running SQL Profiler the duration for some SQLTransactions (Commit) are
> over six seconds, but the individual SQL statements (sp's) are executed at no
> time at all.
> I've used sp_lock but there are no locks.
> Here's an extract from SQLProfiler:
> SQLTransaction Commit 6796 5 2005-03-04 15:30:27.887 2005-03-04
> 15:30:34.683 DTCXact 16335154 sa
> Any Ideas ?
> Regards Tommy S.
>|||Thanks,
I will try to check out the waittypes the next time I see a long running
transaction.
Yes, the log and the datafile are located on the same disk.
The CPU's (4 of them) are almost idle.
The problem is intermittent and it doesn't seem to be dependent on the
number of users, it can occur at early evening when the number of users
decrease significant.
/Tommy
"Olu Adedeji" wrote:
> Check out master..sysprocesses and find out what the waittypes are
> to determine what is causing the extended commit operation.
> is the Tlog file on the same disk as the data file . .how busy is the server?
> is this constantly reproduceable? or is it intermittent?
> HTH
> "Tommy Söderkvist" wrote:
> > Hi all,
> > we have a strange problem in SQL2000 SP3a with SQLTransaction's.
> > When running SQL Profiler the duration for some SQLTransactions (Commit) are
> > over six seconds, but the individual SQL statements (sp's) are executed at no
> > time at all.
> > I've used sp_lock but there are no locks.
> > Here's an extract from SQLProfiler:
> > SQLTransaction Commit 6796 5 2005-03-04 15:30:27.887 2005-03-04
> > 15:30:34.683 DTCXact 16335154 sa
> > Any Ideas ?
> > Regards Tommy S.
> >|||"Tommy Söderkvist" <TommySderkvist@.discussions.microsoft.com> schrieb im
Newsbeitrag news:72BF587D-E3CF-41FE-9EC8-5681C62E472B@.microsoft.com...
> Thanks,
> I will try to check out the waittypes the next time I see a long running
> transaction.
> Yes, the log and the datafile are located on the same disk.
> The CPU's (4 of them) are almost idle.
> The problem is intermittent and it doesn't seem to be dependent on the
> number of users, it can occur at early evening when the number of users
> decrease significant.
My guess would be that you have an IO bottleneck which makes tx log
writing slow. You could use perfmon to verify this theory.
robert
> /Tommy
> "Olu Adedeji" wrote:
> > Check out master..sysprocesses and find out what the waittypes are
> > to determine what is causing the extended commit operation.
> >
> > is the Tlog file on the same disk as the data file . .how busy is the
server?
> > is this constantly reproduceable? or is it intermittent?
> >
> > HTH
> >
> > "Tommy Söderkvist" wrote:
> >
> > > Hi all,
> > > we have a strange problem in SQL2000 SP3a with SQLTransaction's.
> > > When running SQL Profiler the duration for some SQLTransactions
(Commit) are
> > > over six seconds, but the individual SQL statements (sp's) are
executed at no
> > > time at all.
> > > I've used sp_lock but there are no locks.
> > > Here's an extract from SQLProfiler:
> > > SQLTransaction Commit 6796 5 2005-03-04 15:30:27.887 2005-03-04
> > > 15:30:34.683 DTCXact 16335154 sa
> > > Any Ideas ?
> > > Regards Tommy S.
> > >