2PC transaction replication problem

I created a 2PC transaction replication on a simple database for testing.
Changes in the publisher are successfully replicated to the subscriber.
However, when changes are made to the subscriber, the following error
message is shown:
"Another user has modified the contents of this table or view; the database
row you are modifying no longer exists in the database."
Could somebody help to solve the problem?
Thanks in advance!
KM
KM,
can you do a search on hte publisher for the PK value of the row you are
changing. Presumably it is not there and sp_browsereplcmds on the
distributor should reveal a relevant delete statement.
HTH,
Paul Ibison
|||where are you seeing this message? DataGrid?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"krygim" <krygim@.hotmail.com> wrote in message
news:OAZEcl2hEHA.3348@.TK2MSFTNGP12.phx.gbl...
> I created a 2PC transaction replication on a simple database for testing.
> Changes in the publisher are successfully replicated to the subscriber.
> However, when changes are made to the subscriber, the following error
> message is shown:
>
> "Another user has modified the contents of this table or view; the
database
> row you are modifying no longer exists in the database."
>
> Could somebody help to solve the problem?
>
> Thanks in advance!
>
> KM
>
|||Hi Paul,
There are only 3 rows in my table. The PK values in the subscriber and
publisher are exactly the same. The PK column of both the subscriber and the
publisher are marked as Identity (Not For Replication). I got the same
message even if I add a new row in the subscriber.
In the Query Analyzer on the publishing database, I tried the
sp_browsereplcmds and got the message:
"Could not find stored procedure 'sp_browsereplcmds'."
KM
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:em3ZXU3hEHA.4064@.TK2MSFTNGP12.phx.gbl...
> KM,
> can you do a search on hte publisher for the PK value of the row you are
> changing. Presumably it is not there and sp_browsereplcmds on the
> distributor should reveal a relevant delete statement.
> HTH,
> Paul Ibison
>
|||Hi Hilary,
I opened the subscriber database table by selecting "Open Table | Return All
Rows" in the Enterprise Manager. Made change to one of the rows. When I
tried to leave the row, a dialog box popped up which showed the message:
Another user has modified the contents of this table or view; the database
row you are modifying no longer exists in the database.
Database error: '[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB
provider returned message: New transaction cannont enlist in the specified
transaction coordinator]...
.... The operation could not be performed because the OLE DB provider
'SQKOLEDB' was unable to begin a distributed transaction'
KM
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:%23XSgUZ3hEHA.244@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> where are you seeing this message? DataGrid?
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "krygim" <krygim@.hotmail.com> wrote in message
> news:OAZEcl2hEHA.3348@.TK2MSFTNGP12.phx.gbl...
testing.
> database
>
|||try running this command in the distributor.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Krygim" <krygim@.hotmail.com> wrote in message
news:u24De94hEHA.3476@.tk2msftngp13.phx.gbl...
> Hi Paul,
> There are only 3 rows in my table. The PK values in the subscriber and
> publisher are exactly the same. The PK column of both the subscriber and
the
> publisher are marked as Identity (Not For Replication). I got the same
> message even if I add a new row in the subscriber.
> In the Query Analyzer on the publishing database, I tried the
> sp_browsereplcmds and got the message:
> "Could not find stored procedure 'sp_browsereplcmds'."
> KM
>
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:em3ZXU3hEHA.4064@.TK2MSFTNGP12.phx.gbl...
>
|||Hi Hilary,
I get the same message when running the command in both the distributor and
the subscriber.
KM
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:uCyeWO6hEHA.3548@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> try running this command in the distributor.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Krygim" <krygim@.hotmail.com> wrote in message
> news:u24De94hEHA.3476@.tk2msftngp13.phx.gbl...
> the
are
>
|||KM,
I wasn't very precise but by distributor I meant distribution database on
the distributor - the sp should be there. Please post back after running it
to tell us if the rows you refer to are have been modified on the publisher.
TIA,
Paul Ibison
|||Hi Paul,
I found the stored procedure in BOL. I think I must have done something
wrong. Please let me know if I have carried out the steps correctly or not:
1. In the Query Analyzer, connect to the distribution SQL server.
2. Select the distribution database in the dropdown list
3. Enter exec sp_browsereplcmds and press F5
The following message was displayed in the message pane:
Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'sp_browsereplcmds'.
Thanks in advance.
KM
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23qrm%23XCiEHA.592@.TK2MSFTNGP11.phx.gbl...
> KM,
> I wasn't very precise but by distributor I meant distribution database on
> the distributor - the sp should be there. Please post back after running
it
> to tell us if the rows you refer to are have been modified on the
publisher.
> TIA,
> Paul Ibison
>
|||Krygim,
everything you've done looks correct, but I've never heard of this before.
Can you run this:
sp_browsereplcmds
go
select db_name()
(just to check that you are in the correct database). If it returns
'distribution' then perhaps a service-pack install didn't succeed and you
could check sqlsp.log file from the c:\windows directory to see if this is
the case.
HTH,
Paul Ibison

24hrs Transactional Tables

I have setup a pull transactional replications which replicates every 24 hrs.
Tables I am replicating has heavy transaction activity, like we make atleast
100 thousand transactions on those tables.
When distributor agnt tries to run on schedule every day morning at 2:00 am
, it just keeps on running thru out the day...is there option i can check so
that it replicates faster..?
have a look at replicating the execution of stored procedures.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"SQL Replication Guy" <SQLReplicationGuy@.discussions.microsoft.com> wrote in
message news:F7C0361D-3B8D-4D06-AB03-A214BA8F7217@.microsoft.com...
> I have setup a pull transactional replications which replicates every 24
hrs.
> Tables I am replicating has heavy transaction activity, like we make
atleast
> 100 thousand transactions on those tables.
> When distributor agnt tries to run on schedule every day morning at 2:00
am
> , it just keeps on running thru out the day...is there option i can check
so
> that it replicates faster..?

21 gig Transaction Log

Team,
I have a 21 gig Transaction Log and need to reduce the size. Can someone
tell me the best way to reduce this? We are using SQL7 and my knowledge of
it is limited since it was forced upon me. Thanks for any help you can
provide.
ChrisRefer to this KB articles.
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
--
-Vishal
"Chris S" <csemones@.nuvox*nospam*_.com> wrote in message
news:vks4vqkm56o5f9@.corp.supernews.com...
> Team,
> I have a 21 gig Transaction Log and need to reduce the size. Can someone
> tell me the best way to reduce this? We are using SQL7 and my knowledge
of
> it is limited since it was forced upon me. Thanks for any help you can
> provide.
> Chris
>|||More info on this...I do a full backup every night and tried doing a log
backup today to see if it would reduce it but it did now work. The database
size is about 120 meg.
Thanks agian,
Chris
"Chris S" <csemones@.nuvox*nospam*_.com> wrote in message
news:vks4vqkm56o5f9@.corp.supernews.com...
> Team,
> I have a 21 gig Transaction Log and need to reduce the size. Can someone
> tell me the best way to reduce this? We are using SQL7 and my knowledge
of
> it is limited since it was forced upon me. Thanks for any help you can
> provide.
> Chris
>|||SQL Server can not truncte the log if the active portion of the transaction
log contains transactions that are still running and have not yet
completed.Backup log command does not physically shrink the size of the
transaction log file of the database. When old log records no longer
necessary for recovering or restoring a database they are deleted to make
way for new log records.This process of deleting these log records is
truncating the log.
To physically reduce the size of the tran. log you will have to run dbcc
shrinkfile.
You have various options to keep transaction log size manageable:
1.Keep taking transaction log backups at particular intervals this will help
you keep optimal transaction log size.
When SQL Server finishes backing up the transaction log, it automatically
truncates the inactive portion of the transaction log.
2.Keep transaction size as small as possible.
--
-Vishal
"Chris S" <csemones@.nuvox*nospam*_.com> wrote in message
news:vks5b26nrik79a@.corp.supernews.com...
> More info on this...I do a full backup every night and tried doing a log
> backup today to see if it would reduce it but it did now work. The
database
> size is about 120 meg.
> Thanks agian,
> Chris
>
> "Chris S" <csemones@.nuvox*nospam*_.com> wrote in message
> news:vks4vqkm56o5f9@.corp.supernews.com...
> > Team,
> > I have a 21 gig Transaction Log and need to reduce the size. Can
someone
> > tell me the best way to reduce this? We are using SQL7 and my knowledge
> of
> > it is limited since it was forced upon me. Thanks for any help you can
> > provide.
> > Chris
> >
> >
>

2005 Shared Nothing

I though 2005 was going to have a feature where you could face multiple
instances of a DB towards your customers with all the lock and transaction
bits accomplished in the background? The most common question I get about
Clustering is whether it is HA or really is a scalibility solution as well.
Avokia makes a piece that effectively accomplishes this.Are you referring to database snapshots?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:2C6CD6C9-2655-4E5F-8DD7-83B21DE43F12@.microsoft.com...
> I though 2005 was going to have a feature where you could face multiple
> instances of a DB towards your customers with all the lock and transaction
> bits accomplished in the background? The most common question I get about
> Clustering is whether it is HA or really is a scalibility solution as
well.
> Avokia makes a piece that effectively accomplishes this.|||Hi
Clustering is HA and no Windows Clustering implantation is intended for
scale out.
SQL server 2005 has never has a scale out feature in it's feature list.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:2C6CD6C9-2655-4E5F-8DD7-83B21DE43F12@.microsoft.com...
>I though 2005 was going to have a feature where you could face multiple
> instances of a DB towards your customers with all the lock and transaction
> bits accomplished in the background? The most common question I get about
> Clustering is whether it is HA or really is a scalibility solution as
> well.
> Avokia makes a piece that effectively accomplishes this.|||Database mirroring seems to be like what I thought I heard 2005 to have.
This would be more like having a separate server instance to improve
performance.
"Adam Machanic" wrote:
> Are you referring to database snapshots?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> message news:2C6CD6C9-2655-4E5F-8DD7-83B21DE43F12@.microsoft.com...
> > I though 2005 was going to have a feature where you could face multiple
> > instances of a DB towards your customers with all the lock and transaction
> > bits accomplished in the background? The most common question I get about
> > Clustering is whether it is HA or really is a scalibility solution as
> well.
> > Avokia makes a piece that effectively accomplishes this.
>
>|||Database mirroring, with a snapshot running against the mirror gives you a
real-time read only copy of the data.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:D22D9678-ACC0-4A67-B9B0-ECB89990694E@.microsoft.com...
> Database mirroring seems to be like what I thought I heard 2005 to have.
> This would be more like having a separate server instance to improve
> performance.
> "Adam Machanic" wrote:
>> Are you referring to database snapshots?
>>
>> --
>> Adam Machanic
>> SQL Server MVP
>> http://www.datamanipulation.net
>> --
>>
>> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
>> message news:2C6CD6C9-2655-4E5F-8DD7-83B21DE43F12@.microsoft.com...
>> > I though 2005 was going to have a feature where you could face multiple
>> > instances of a DB towards your customers with all the lock and
>> > transaction
>> > bits accomplished in the background? The most common question I get
>> > about
>> > Clustering is whether it is HA or really is a scalibility solution as
>> well.
>> > Avokia makes a piece that effectively accomplishes this.
>>|||As Mike mentioned, database mirroring really doesn't accomplish that.
Sounds like a merge replication scenario to me...
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:D22D9678-ACC0-4A67-B9B0-ECB89990694E@.microsoft.com...
> Database mirroring seems to be like what I thought I heard 2005 to have.
> This would be more like having a separate server instance to improve
> performance.
> "Adam Machanic" wrote:
> > Are you referring to database snapshots?
> >
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.datamanipulation.net
> > --
> >
> >
> > "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote
in
> > message news:2C6CD6C9-2655-4E5F-8DD7-83B21DE43F12@.microsoft.com...
> > > I though 2005 was going to have a feature where you could face
multiple
> > > instances of a DB towards your customers with all the lock and
transaction
> > > bits accomplished in the background? The most common question I get
about
> > > Clustering is whether it is HA or really is a scalibility solution as
> > well.
> > > Avokia makes a piece that effectively accomplishes this.
> >
> >
> >

2005 -lock being held by an orphan transaction?

SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
We have a problem that started with an index rebuild job getting hung and
blocking other users. After digging into the problem I found that there is
a
lock being held on a table by a particular transaction id which doesn't seem
to be tied to a process (spid) any longer. By querying certain DMVs like
sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables ar
e
locked by transaction_id XXXX but the spid listed is 0. Is there any way to
kill a transaction that has no spid without recycling the SQL Server service
(this is a production box)? Any idea how this sort of thing happens? Below
is the output of some of the queries:
--sp_lock (only columns with info)
spid dbid ObjId IndId Type Mode Status
0 5 377872513 0 TAB IS GRANT
0 5 73871430 0 TAB IS GRANT
0 5 9871202 0 TAB IS GRANT
--sys.dm_tran_locks (only the columns with info)
resource_type resource_associated_entity_id request_mode request_type
request_status request_owner_type request_owner_id
OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
--sys.dm_tran_active_transactions (only the columns with info)
transaction_id name transaction_begin_time transaction_type
transaction_state transaction_status
645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32You could trying killing it by unit of work(UOW) ID instead
of spid.
You can get the UOW from sys.syslockinfo.
-Sue
On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:

>SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
>We have a problem that started with an index rebuild job getting hung and
>blocking other users. After digging into the problem I found that there is
a
>lock being held on a table by a particular transaction id which doesn't see
m
>to be tied to a process (spid) any longer. By querying certain DMVs like
>sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables a
re
>locked by transaction_id XXXX but the spid listed is 0. Is there any way t
o
>kill a transaction that has no spid without recycling the SQL Server servic
e
>(this is a production box)? Any idea how this sort of thing happens? Belo
w
>is the output of some of the queries:
>--sp_lock (only columns with info)
>spid dbid ObjId IndId Type Mode Status
>0 5 377872513 0 TAB IS GRANT
>0 5 73871430 0 TAB IS GRANT
>0 5 9871202 0 TAB IS GRANT
>--sys.dm_tran_locks (only the columns with info)
>resource_type resource_associated_entity_id request_mode request_type
>request_status request_owner_type request_owner_id
>OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
>--sys.dm_tran_active_transactions (only the columns with info)
>transaction_id name transaction_begin_time transaction_type
>transaction_state transaction_status
>645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32|||Sue,
Thanks for responding. Unfortunately I tried to go down that route too but
the transaction has no UOW. From what I have read, those are assigned only
for distrbuted transactions. The UOW listed in that view is:
00000000-0000-0000-0000-000000000000
Chris
"Sue Hoegemeier" wrote:

> You could trying killing it by unit of work(UOW) ID instead
> of spid.
> You can get the UOW from sys.syslockinfo.
> -Sue
> On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
> <ChrisC@.discussions.microsoft.com> wrote:
>
>|||Normally yes...and the orphaned spid usually is -2 not 0. I
just thought it would be worth a shot to see if you could
kill it by UOW.
-Sue
On Thu, 26 Jul 2007 06:16:04 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue,
>Thanks for responding. Unfortunately I tried to go down that route too but
>the transaction has no UOW. From what I have read, those are assigned only
>for distrbuted transactions. The UOW listed in that view is:
>00000000-0000-0000-0000-000000000000
>Chris
>"Sue Hoegemeier" wrote:
>

2005 -lock being held by an orphan transaction?

SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
We have a problem that started with an index rebuild job getting hung and
blocking other users. After digging into the problem I found that there is a
lock being held on a table by a particular transaction id which doesn't seem
to be tied to a process (spid) any longer. By querying certain DMVs like
sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
locked by transaction_id XXXX but the spid listed is 0. Is there any way to
kill a transaction that has no spid without recycling the SQL Server service
(this is a production box)? Any idea how this sort of thing happens? Below
is the output of some of the queries:
--sp_lock (only columns with info)
spid dbid ObjId IndId Type Mode Status
0 5 377872513 0 TAB IS GRANT
0 5 73871430 0 TAB IS GRANT
0 5 9871202 0 TAB IS GRANT
--sys.dm_tran_locks (only the columns with info)
resource_type resource_associated_entity_id request_mode request_type
request_status request_owner_type request_owner_id
OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
--sys.dm_tran_active_transactions (only the columns with info)
transaction_id name transaction_begin_time transaction_type
transaction_state transaction_status
645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32
You could trying killing it by unit of work(UOW) ID instead
of spid.
You can get the UOW from sys.syslockinfo.
-Sue
On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:

>SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
>We have a problem that started with an index rebuild job getting hung and
>blocking other users. After digging into the problem I found that there is a
>lock being held on a table by a particular transaction id which doesn't seem
>to be tied to a process (spid) any longer. By querying certain DMVs like
>sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
>locked by transaction_id XXXX but the spid listed is 0. Is there any way to
>kill a transaction that has no spid without recycling the SQL Server service
>(this is a production box)? Any idea how this sort of thing happens? Below
>is the output of some of the queries:
>--sp_lock (only columns with info)
>spid dbid ObjId IndId Type Mode Status
>0 5 377872513 0 TAB IS GRANT
>0 5 73871430 0 TAB IS GRANT
>0 5 9871202 0 TAB IS GRANT
>--sys.dm_tran_locks (only the columns with info)
>resource_type resource_associated_entity_id request_mode request_type
>request_status request_owner_type request_owner_id
>OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
>--sys.dm_tran_active_transactions (only the columns with info)
>transaction_id name transaction_begin_time transaction_type
>transaction_state transaction_status
>645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32
|||Sue,
Thanks for responding. Unfortunately I tried to go down that route too but
the transaction has no UOW. From what I have read, those are assigned only
for distrbuted transactions. The UOW listed in that view is:
00000000-0000-0000-0000-000000000000
Chris
"Sue Hoegemeier" wrote:

> You could trying killing it by unit of work(UOW) ID instead
> of spid.
> You can get the UOW from sys.syslockinfo.
> -Sue
> On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
> <ChrisC@.discussions.microsoft.com> wrote:
>
>
|||Normally yes...and the orphaned spid usually is -2 not 0. I
just thought it would be worth a shot to see if you could
kill it by UOW.
-Sue
On Thu, 26 Jul 2007 06:16:04 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue,
>Thanks for responding. Unfortunately I tried to go down that route too but
>the transaction has no UOW. From what I have read, those are assigned only
>for distrbuted transactions. The UOW listed in that view is:
>00000000-0000-0000-0000-000000000000
>Chris
>"Sue Hoegemeier" wrote:

2005 -lock being held by an orphan transaction?

SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
We have a problem that started with an index rebuild job getting hung and
blocking other users. After digging into the problem I found that there is a
lock being held on a table by a particular transaction id which doesn't seem
to be tied to a process (spid) any longer. By querying certain DMVs like
sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
locked by transaction_id XXXX but the spid listed is 0. Is there any way to
kill a transaction that has no spid without recycling the SQL Server service
(this is a production box)? Any idea how this sort of thing happens? Below
is the output of some of the queries:
--sp_lock (only columns with info)
spid dbid ObjId IndId Type Mode Status
0 5 377872513 0 TAB IS GRANT
0 5 73871430 0 TAB IS GRANT
0 5 9871202 0 TAB IS GRANT
--sys.dm_tran_locks (only the columns with info)
resource_type resource_associated_entity_id request_mode request_type
request_status request_owner_type request_owner_id
OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
--sys.dm_tran_active_transactions (only the columns with info)
transaction_id name transaction_begin_time transaction_type
transaction_state transaction_status
645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32You could trying killing it by unit of work(UOW) ID instead
of spid.
You can get the UOW from sys.syslockinfo.
-Sue
On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:
>SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
>We have a problem that started with an index rebuild job getting hung and
>blocking other users. After digging into the problem I found that there is a
>lock being held on a table by a particular transaction id which doesn't seem
>to be tied to a process (spid) any longer. By querying certain DMVs like
>sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
>locked by transaction_id XXXX but the spid listed is 0. Is there any way to
>kill a transaction that has no spid without recycling the SQL Server service
>(this is a production box)? Any idea how this sort of thing happens? Below
>is the output of some of the queries:
>--sp_lock (only columns with info)
>spid dbid ObjId IndId Type Mode Status
>0 5 377872513 0 TAB IS GRANT
>0 5 73871430 0 TAB IS GRANT
>0 5 9871202 0 TAB IS GRANT
>--sys.dm_tran_locks (only the columns with info)
>resource_type resource_associated_entity_id request_mode request_type
>request_status request_owner_type request_owner_id
>OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
>--sys.dm_tran_active_transactions (only the columns with info)
>transaction_id name transaction_begin_time transaction_type
>transaction_state transaction_status
>645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32|||Sue,
Thanks for responding. Unfortunately I tried to go down that route too but
the transaction has no UOW. From what I have read, those are assigned only
for distrbuted transactions. The UOW listed in that view is:
00000000-0000-0000-0000-000000000000
Chris
"Sue Hoegemeier" wrote:
> You could trying killing it by unit of work(UOW) ID instead
> of spid.
> You can get the UOW from sys.syslockinfo.
> -Sue
> On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
> <ChrisC@.discussions.microsoft.com> wrote:
> >SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
> >
> >We have a problem that started with an index rebuild job getting hung and
> >blocking other users. After digging into the problem I found that there is a
> >lock being held on a table by a particular transaction id which doesn't seem
> >to be tied to a process (spid) any longer. By querying certain DMVs like
> >sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
> >locked by transaction_id XXXX but the spid listed is 0. Is there any way to
> >kill a transaction that has no spid without recycling the SQL Server service
> >(this is a production box)? Any idea how this sort of thing happens? Below
> >is the output of some of the queries:
> >
> >--sp_lock (only columns with info)
> >spid dbid ObjId IndId Type Mode Status
> >0 5 377872513 0 TAB IS GRANT
> >0 5 73871430 0 TAB IS GRANT
> >0 5 9871202 0 TAB IS GRANT
> >
> >--sys.dm_tran_locks (only the columns with info)
> >resource_type resource_associated_entity_id request_mode request_type
> >request_status request_owner_type request_owner_id
> >OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
> >OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
> >OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
> >
> >--sys.dm_tran_active_transactions (only the columns with info)
> >transaction_id name transaction_begin_time transaction_type
> >transaction_state transaction_status
> >645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32
>|||Normally yes...and the orphaned spid usually is -2 not 0. I
just thought it would be worth a shot to see if you could
kill it by UOW.
-Sue
On Thu, 26 Jul 2007 06:16:04 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:
>Sue,
>Thanks for responding. Unfortunately I tried to go down that route too but
>the transaction has no UOW. From what I have read, those are assigned only
>for distrbuted transactions. The UOW listed in that view is:
>00000000-0000-0000-0000-000000000000
>Chris
>"Sue Hoegemeier" wrote:
>> You could trying killing it by unit of work(UOW) ID instead
>> of spid.
>> You can get the UOW from sys.syslockinfo.
>> -Sue
>> On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
>> <ChrisC@.discussions.microsoft.com> wrote:
>> >SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
>> >
>> >We have a problem that started with an index rebuild job getting hung and
>> >blocking other users. After digging into the problem I found that there is a
>> >lock being held on a table by a particular transaction id which doesn't seem
>> >to be tied to a process (spid) any longer. By querying certain DMVs like
>> >sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
>> >locked by transaction_id XXXX but the spid listed is 0. Is there any way to
>> >kill a transaction that has no spid without recycling the SQL Server service
>> >(this is a production box)? Any idea how this sort of thing happens? Below
>> >is the output of some of the queries:
>> >
>> >--sp_lock (only columns with info)
>> >spid dbid ObjId IndId Type Mode Status
>> >0 5 377872513 0 TAB IS GRANT
>> >0 5 73871430 0 TAB IS GRANT
>> >0 5 9871202 0 TAB IS GRANT
>> >
>> >--sys.dm_tran_locks (only the columns with info)
>> >resource_type resource_associated_entity_id request_mode request_type
>> >request_status request_owner_type request_owner_id
>> >OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
>> >OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
>> >OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
>> >
>> >--sys.dm_tran_active_transactions (only the columns with info)
>> >transaction_id name transaction_begin_time transaction_type
>> >transaction_state transaction_status
>> >645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32
>>