DBfile usage: opentran

Showing posts with label opentran. Show all posts

Wednesday, March 21, 2012

dbcc opentran with tableresults

sql2k sp3
How do I get the results of "dbcc opentran with
tableresults" into an actual table? Something along the
lines of;
create table #temp(c1 varchar(50),c2 varchar(50))
insert into #temp (c1,c2) values
(dbcc opentran with tableresults)
select * from #temp
drop table #temp
TIA, ChrisHi Chris
Once you have the table created, you can use the INSERT/EXEC syntax to
populate it:
insert into #temp
exec ('dbcc opentran(pubs) with tableresults')
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:09d701c3ce6b$37e9d640$a001280a@.phx.gbl...
> sql2k sp3
> How do I get the results of "dbcc opentran with
> tableresults" into an actual table? Something along the
> lines of;
> create table #temp(c1 varchar(50),c2 varchar(50))
> insert into #temp (c1,c2) values
> (dbcc opentran with tableresults)
> select * from #temp
> drop table #temp
> TIA, Chris|||create table #temp(c1 varchar(50),c2 varchar(50))
insert into #temp (c1,c2)
exec ('dbcc opentran with tableresults')
select * from #temp
drop table #temp
>--Original Message--
>sql2k sp3
>How do I get the results of "dbcc opentran with
>tableresults" into an actual table? Something along the
>lines of;
>create table #temp(c1 varchar(50),c2 varchar(50))
>insert into #temp (c1,c2) values
>(dbcc opentran with tableresults)
>select * from #temp
>drop table #temp
>TIA, Chris
>.
>

DBCC OPENTRAN shows "REPLICATION" on a server that is not configured for replication

Hello,

I have this problem on a Production database.

DBCC OPENTRAN shows "REPLICATION" on a server that is not configured for replication. The transaction log is almost as large as the database (40GB) with a Simple recovery model. I would like to find out how the log can be truncated in such a situation.

Thank you.

I missed some information. DBCC OPENTRAN showed that we had open replication transactions. When I ran

SELECT name, database_id, log_reuse_wait_desc

FROM sys.databases

the log_reuse_wait_desc showed "REPLICATION". We do not have Replication set up at all (it was never set up on the server).

Can you please advise on how to truncate and make the log file reusable in this case?

I tested the sp_removedbreplication on a test copy of the database and it seems to allow the log to then shrink.

1. Is it safe to run sp_removedbreplication on a server that is not set up at all for replication?

2. Why should these "replication transaction" logs be there in the first place?

Advice/suggestions on this will be greatly appreciated!

Thank you.

|||

You must have restored a database backup that was previously published? If so, running sp_removedbreplication against the database will remove all replication bits, so yes it's safe.

|||

The database was never published previously.

Can a failed backup cause this? Is there any other reason "transaction replication" records can be in the log?

Even after running sp_dbremovedbreplication, we are unable to shrink the logs on the Production database. The transactions still seem to be there .

What are the options to retrieve space from the log?

Thank you.

|||Can you issue a sp_repldone to see if this solves the problem?

For example, in your problem database issue the following:

EXEC sp_repldone @.xactid = NULL, @.xact_segno = NULL, @.numtrans = 0, @.time = 0, @.reset = 1

dbcc opentran results

I have a database with a large (and growing) transaction log. Running
dbcc opentran yields the following two rows:
REPL_DIST_OLD_LSN (0:0:0)
REPL_NONDIST_OLD_LSN (508734:17171:1)
A search through BOL and the news groups yields no information on the
meaning of these values. The database is published nightly using
snapshot replication. Any help interpreting these values would be
greatly appreciated. My goal is to truncate the log back to a more
reasonable size. Thanks.
it is very rare to see the REPL_NONDIST_OLD_LSN value.
What it means is that 1) your log reader agent is stopped, 2) your log
reader agent is way behind.
What is the status of your log reader agent?
Can you also run this in your publication database?
DBCC traceon(3604)
DBCC log(databasename)
DBCC traceoff
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Larry Myers" <lmyers@.swinformatics.com> wrote in message
news:73e8147a.0408190922.3dc6706d@.posting.google.c om...
> I have a database with a large (and growing) transaction log. Running
> dbcc opentran yields the following two rows:
> REPL_DIST_OLD_LSN (0:0:0)
> REPL_NONDIST_OLD_LSN (508734:17171:1)
> A search through BOL and the news groups yields no information on the
> meaning of these values. The database is published nightly using
> snapshot replication. Any help interpreting these values would be
> greatly appreciated. My goal is to truncate the log back to a more
> reasonable size. Thanks.
|||one more thing, are you running DataMirror?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Larry Myers" <lmyers@.swinformatics.com> wrote in message
news:73e8147a.0408190922.3dc6706d@.posting.google.c om...
> I have a database with a large (and growing) transaction log. Running
> dbcc opentran yields the following two rows:
> REPL_DIST_OLD_LSN (0:0:0)
> REPL_NONDIST_OLD_LSN (508734:17171:1)
> A search through BOL and the news groups yields no information on the
> meaning of these values. The database is published nightly using
> snapshot replication. Any help interpreting these values would be
> greatly appreciated. My goal is to truncate the log back to a more
> reasonable size. Thanks.
|||Thanks for the prompt reply.
I am confused. We are using snapshot replication. I wasn't aware that a
log reader agent was part of the snapshot replication implementation.
There are no log reader agents showing in the replication monitor.
I ran the dbcc log(database) command against the publication database.
It returned a huge number of rows, too many to include here. Is there
something specific you are looking for?
Thanks again.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||I missed that fact that you are using snapshot replication. The log reader
is not used in snapshot replication, as you so kindly point out.
What the traceflag will allow you to do is to get an idea of where your log
reader is at in reading the transaction log, i.e. how many more rows it has
to read, or how far behind it is.
Again with snapshot replication it won't really tell you anything.
you can take the value 508734:17171:1, convert it to hex
0007C33E:00017171:0001 and see where this hex value is in the Current LSN of
the results set returned from the trace flag.
It is possible that this corresponds to an open transaction which you might
want to kill. DBCC opentran should give you the spid for this. You might
also want to review some of the kb articles on shrinking the transaction
log, ie
http://support.microsoft.com/default...&Product=sql2k
http://support.microsoft.com/default...b;EN-US;256650
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Larry Myers" <lmyers@.swnformtics.com> wrote in message
news:%2309dhrhhEHA.1888@.TK2MSFTNGP10.phx.gbl...
> Thanks for the prompt reply.
> I am confused. We are using snapshot replication. I wasn't aware that a
> log reader agent was part of the snapshot replication implementation.
> There are no log reader agents showing in the replication monitor.
> I ran the dbcc log(database) command against the publication database.
> It returned a huge number of rows, too many to include here. Is there
> something specific you are looking for?
> Thanks again.
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
sql

DBCC OpenTran results

Hi
Periodically my SQL 2k snapshot publication database starts to grow it's log
file without truncating.
- The recovery model is 'simple'
- There is no logreader agent, as it's snapshot only.
- DBCC OPENTRAN, returns non-zero values for non-distributed LSN, but never
returns any SPID!
All of my searching on the 'net has led me back to executing DBCC opentran,
to find the offending spid, to kill it ... but DBCC OPENtran has never
returned any Spids! it returns this sort of thing:
"Transaction information for database '<myDB>'.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (202506:21682:1)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator."
Dropping the publication, and shrinking the log file always works, but is a
bit extreme, and difficult to do on a production DB. This issue occurs about
once a month, with no apparent pattern that I can figure out...yet.
Any help is appreciated.
Thanks
what this means is that the log reader has not picked up some transactions
yes in the log and written them to the distribution database.
It is rare that the log reader cannot keep up, but you might want to
increase the readbatchsize (try 1000), and drop the pollinginterval to 1.
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>
|||Thanks Hilary, but as mentioned, there is no log reader. It is snapshot
replication only. Where would I change the polling interval and
readbatchsize? The snapshot agent profile has neither of these settings, and
the distribution agent only has polling interval.
Secondly, can you shed any light on the usefulness iof the results of DBCC
OpenTran?
Thanks.
"hilary.cotter@.gmail.com" wrote:

> what this means is that the log reader has not picked up some transactions
> yes in the log and written them to the distribution database.
> It is rare that the log reader cannot keep up, but you might want to
> increase the readbatchsize (try 1000), and drop the pollinginterval to 1.
> --
> 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>
>
|||oops, I missed the fact you were using snapshot replication.
For simple recovery model the tlog will be recycled when the last active
transaction is committed in the vlf (virtual log file). There are some
conditions which will prevent the tlog from being re-used and you will have
to run a bunch of large inserts into a wide table to have the log wrap
around.
So ignore my comments on the batchsize and pollinginterval.
Try to manually shrink the log using the shrink command. You can put your
database in full recover model, and backup the log to a dump device with
trunacate only, and then put it back into simple.
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
"Mark J" <MarkJ@.discussions.microsoft.com> wrote in message
news:C4C4C1DA-D842-4EB9-A9FA-990858011069@.microsoft.com...[vbcol=seagreen]
> Thanks Hilary, but as mentioned, there is no log reader. It is snapshot
> replication only. Where would I change the polling interval and
> readbatchsize? The snapshot agent profile has neither of these settings,
> and
> the distribution agent only has polling interval.
> Secondly, can you shed any light on the usefulness iof the results of DBCC
> OpenTran?
> Thanks.
> "hilary.cotter@.gmail.com" wrote:

dbcc opentran results

dbcc opentran is not helping me on "waiting for backend connection"

What else could be causing this?
I keep getting waiting on backend connection on a lot of my log readers
connected to this one publisher. The publisher has no blocking and was
just rebooted last night. Everything on the publisher is working fine.
however. I get "waiting for backend connection" and "waiting for log
backup" a lot on the errors of the log reader.
I have also had to had to put the -readbatchsize level to a (1) on all
of these log readers connected to this publisher. Once i do this, they
at least start going 1 transaction at a time. These are not like huge
transactions.
dbcc opentran shows
Transaction information for database 'pet01'.
Replicated Transaction Information:
Oldest distributed LSN : (25212765:36114:75)
Oldest non-distributed LSN : (25212765:36145:1)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Any help would be appreciated.
-comb
Basically this is telling you that the log reader has transactions to write
to the distribution database.
I think your problem is with depleted buffers on SQL Server. You need to
reboot the box. You may be able to get away with merly stopping and starting
SQL Server.
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
"combfilter" <asdf@.adsf.com> wrote in message
news:MPG.1dd16198660765d99896c2@.news.newsreader.co m...
> What else could be causing this?
> I keep getting waiting on backend connection on a lot of my log readers
> connected to this one publisher. The publisher has no blocking and was
> just rebooted last night. Everything on the publisher is working fine.
> however. I get "waiting for backend connection" and "waiting for log
> backup" a lot on the errors of the log reader.
> I have also had to had to put the -readbatchsize level to a (1) on all
> of these log readers connected to this publisher. Once i do this, they
> at least start going 1 transaction at a time. These are not like huge
> transactions.
> dbcc opentran shows
> Transaction information for database 'pet01'.
> Replicated Transaction Information:
> Oldest distributed LSN : (25212765:36114:75)
> Oldest non-distributed LSN : (25212765:36145:1)
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Any help would be appreciated.
> -comb
|||In article <ehBTGvw3FHA.1276@.TK2MSFTNGP09.phx.gbl>,
hilary.cotter@.gmail.com says...
> Basically this is telling you that the log reader has transactions to write
> to the distribution database.
> I think your problem is with depleted buffers on SQL Server. You need to
> reboot the box. You may be able to get away with merly stopping and starting
> SQL Server.
>
We rebooted it last night. It's part of a cluster, and we moved the
instance over and rebooted the box. What else should I check Hilary or
Paul?
tia.
-comb

DBCC Opentran - possible causes for results

Have you tried sp_removedbreplication?
I'm assuming the database has been restored from another copy where it was
replicated, for the replication metadata to have got in ther in the first
place.
Here's a link for removedbreplication (from BOL 2005, but applies to 2000 as
well)
http://msdn2.microsoft.com/en-us/library/ms188734.aspx
you won't lose any objects. It just removes replication information which
will hopefully stop your t-log growing.
It doesn't drop any objects.
|||I thought you said it wasn't replicated? Now you're telling me it's your
publisher? You should make your mind up.
Running sp_removedbreplication will NOT remove any of your user objects. It
ONLY removes replication metadata. You can run it with a clause, e.g. 'tran',
'merge' etc. if you only want to remove transactional- or merge- replication
metadata.
I think you need to reassure yourself what is actually going on with your
database, i.e. is it being replicated or not. 'Cos the signals you're sending
out are somewhat mixed.
"Susanne Wenzel" wrote:

> Am Tue, 28 Nov 2006 00:54:01 -0800 schrieb thomarse:
>
> Are you sure about that? I've looked it up again in BOL and it says there
> it deletes all replication objects in a database. I would assume that means
> that all replication tables and stored procedures for replication will be
> removed. As the said database is our publisher that wouldn't come too
> good..
> Greetings
> Susanne
>

Monday, March 19, 2012

DBCC Opentran - possible causes for results

Hi to everyone,
sorry for the inadequate subject (could not find a better one), but it's
not easy to squeeze my problem into a few words...
I'm very close to opening a ticket with PSS, but before I do I wanted to
plough the field in every possible direction so to make sure I didn't
overlook any other possibilities.
Can somebody here tell me whether there are other known causes besides a
transactional replication problem that can produce the following result
when you run a DBCC OPENTRAN WITH TABLERESULTS?:
REPL_DIST_OLD_LSN (0:0:0)
REPL_NONDIST_OLD_LSN (41204:61:1)
I've googled quite a lot and already described and discussed this problem
in the sqlserver.replication-NG (for anybody who is interested):
*(den folgenden Link bitte in /eine/ Zeile)*
http://groups.google.de/group/microsoft.public.sqlserver.replication/browse_frm/thread/90fd927b02972f93/b7c9d84c7e2cb5bd?lnk=st&q=dbcc+opentran+susanne+wenzel&rnum=2#b7c9d84c7e2cb5bd
It arises out of a transaction log that grows out of hand and can't be
shrunk in the usual way, I have to use a what I'd call steamroller tactics,
involving sp_repldone and the lot. In the end I did so last time and shrank
the log from 6,7 GB to 0,6 MB. And what I've predicted in the other thread
has come true now, I again have an open transaction. My nice small
transaction log remained so for just 3 weeks, it' s growing again. And
still I do not have any transactional replication...
Any help would be greatly appreciated.
SQLServer 2000, SP4
Thanks for reading and
Greetings from Germany
SusanneHave you tried sp_removedbreplication?
I'm assuming the database has been restored from another copy where it was
replicated, for the replication metadata to have got in ther in the first
place.
Here's a link for removedbreplication (from BOL 2005, but applies to 2000 as
well)
http://msdn2.microsoft.com/en-us/library/ms188734.aspx|||Am Mon, 27 Nov 2006 04:14:01 -0800 schrieb thomarse:
> Have you tried sp_removedbreplication?
Not yet. From what I've learned about this sp in BOL it does not make any
difference in which replication objects it removes so we would lose *all*
our objects (and there are quite a lot of them, all in use)...
But I'll have a look into this.
> I'm assuming the database has been restored from another copy where it was
> replicated, for the replication metadata to have got in ther in the first
> place.
Which would mean that at one time we had a transactional replication and
the metadata never got cleaned. Well I know you should never say never but
I really don't recall having *ever* worked with transactional replication.
> Here's a link for removedbreplication (from BOL 2005, but applies to 2000 as
> well)
> http://msdn2.microsoft.com/en-us/library/ms188734.aspx
Thanks for your help
Greetings from Germany
Susanne|||you won't lose any objects. It just removes replication information which
will hopefully stop your t-log growing.
It doesn't drop any objects.|||Am Tue, 28 Nov 2006 00:54:01 -0800 schrieb thomarse:
> you won't lose any objects. It just removes replication information which
> will hopefully stop your t-log growing.
> It doesn't drop any objects.
Are you sure about that? I've looked it up again in BOL and it says there
it deletes all replication objects in a database. I would assume that means
that all replication tables and stored procedures for replication will be
removed. As the said database is our publisher that wouldn't come too
good..
Greetings
Susanne|||Am Thu, 30 Nov 2006 04:11:02 -0800 schrieb thomarse:
> I thought you said it wasn't replicated? Now you're telling me it's your
> publisher? You should make your mind up.
I'm really sorry if I confused you (or anybody else). That surely isn't my
intent. Where in what line did you get the impression that I was talking
about a replicated database? I've looked up all I've posted here and
couldn't find a line that would point to that conclusion. So please tell me
so next time I try to do better.
This database I'm talking about is the publisher database. It has 4
publications with some 60-odd subscribers. And it contains a lot of what
*I* would call replication objects such as special tables (for example
conflict tables) or a whole bunch of stored procedures used solely for
replicating to/with our subscribers.
> Running sp_removedbreplication will NOT remove any of your user objects. It
> ONLY removes replication metadata. You can run it with a clause, e.g. 'tran',
> 'merge' etc. if you only want to remove transactional- or merge- replication
> metadata.
I looked it up again in my BOL (SQLServer 2000) and it says there you run
it on the publisher for the publisher database and on the subscriber for
the subscriber database. So I assumed that when you run it on the publisher
you among other things delete those objects I mentioned above. And the sp
has just one parameter, the name of the database.
> I think you need to reassure yourself what is actually going on with your
> database, i.e. is it being replicated or not.
Ok, I try again. Some tables are replicated *from* this database I'm
talking about *to* (better *with* as it is a merge replication) our
subscribers.
> 'Cos the signals you're sending out are somewhat mixed.
My apologies, once again I didn't intend that. I hope I've made everything
clear now.
Greetings
Susanne

DBCC Opentran - possible causes for results

Hi to everyone,
sorry for the inadequate subject (could not find a better one), but it's
not easy to squeeze my problem into a few words...
I'm very close to opening a ticket with PSS, but before I do I wanted to
plough the field in every possible direction so to make sure I didn't
overlook any other possibilities.
Can somebody here tell me whether there are other known causes besides a
transactional replication problem that can produce the following result
when you run a DBCC OPENTRAN WITH TABLERESULTS?:
REPL_DIST_OLD_LSN (0:0:0)
REPL_NONDIST_OLD_LSN (41204:61:1)
I've googled quite a lot and already described and discussed this problem
in the sqlserver.replication-NG (for anybody who is interested):
*(den folgenden Link bitte in /eine/ Zeile)*
http://groups.google.de/group/micro...7c9d84c7e2cb5bd
It arises out of a transaction log that grows out of hand and can't be
shrunk in the usual way, I have to use a what I'd call steamroller tactics,
involving sp_repldone and the lot. In the end I did so last time and shrank
the log from 6,7 GB to 0,6 MB. And what I've predicted in the other thread
has come true now, I again have an open transaction. My nice small
transaction log remained so for just 3 weeks, it' s growing again. And
still I do not have any transactional replication...
Any help would be greatly appreciated.
SQLServer 2000, SP4
Thanks for reading and
Greetings from Germany
SusanneHave you tried sp_removedbreplication?
I'm assuming the database has been restored from another copy where it was
replicated, for the replication metadata to have got in ther in the first
place.
Here's a link for removedbreplication (from BOL 2005, but applies to 2000 as
well)
http://msdn2.microsoft.com/en-us/library/ms188734.aspx|||Am Mon, 27 Nov 2006 04:14:01 -0800 schrieb thomarse:

> Have you tried sp_removedbreplication?
Not yet. From what I've learned about this sp in BOL it does not make any
difference in which replication objects it removes so we would lose *all*
our objects (and there are quite a lot of them, all in use)...
But I'll have a look into this.

> I'm assuming the database has been restored from another copy where it was
> replicated, for the replication metadata to have got in ther in the first
> place.
Which would mean that at one time we had a transactional replication and
the metadata never got cleaned. Well I know you should never say never but
I really don't recall having *ever* worked with transactional replication.

> Here's a link for removedbreplication (from BOL 2005, but applies to 2000
as
> well)
> http://msdn2.microsoft.com/en-us/library/ms188734.aspx
Thanks for your help
Greetings from Germany
Susanne|||you won't lose any objects. It just removes replication information which
will hopefully stop your t-log growing.
It doesn't drop any objects.|||Am Tue, 28 Nov 2006 00:54:01 -0800 schrieb thomarse:

> you won't lose any objects. It just removes replication information which
> will hopefully stop your t-log growing.
> It doesn't drop any objects.
Are you sure about that? I've looked it up again in BOL and it says there
it deletes all replication objects in a database. I would assume that means
that all replication tables and stored procedures for replication will be
removed. As the said database is our publisher that wouldn't come too
good..
Greetings
Susanne|||I thought you said it wasn't replicated? Now you're telling me it's your
publisher? You should make your mind up.
Running sp_removedbreplication will NOT remove any of your user objects. It
ONLY removes replication metadata. You can run it with a clause, e.g. 'tran'
,
'merge' etc. if you only want to remove transactional- or merge- replication
metadata.
I think you need to reassure yourself what is actually going on with your
database, i.e. is it being replicated or not. 'Cos the signals you're sendin
g
out are somewhat mixed.
"Susanne Wenzel" wrote:

> Am Tue, 28 Nov 2006 00:54:01 -0800 schrieb thomarse:
>
> Are you sure about that? I've looked it up again in BOL and it says there
> it deletes all replication objects in a database. I would assume that mean
s
> that all replication tables and stored procedures for replication will be
> removed. As the said database is our publisher that wouldn't come too
> good..
> Greetings
> Susanne
>|||Am Thu, 30 Nov 2006 04:11:02 -0800 schrieb thomarse:

> I thought you said it wasn't replicated? Now you're telling me it's your
> publisher? You should make your mind up.
I'm really sorry if I confused you (or anybody else). That surely isn't my
intent. Where in what line did you get the impression that I was talking
about a replicated database? I've looked up all I've posted here and
couldn't find a line that would point to that conclusion. So please tell me
so next time I try to do better.
This database I'm talking about is the publisher database. It has 4
publications with some 60-odd subscribers. And it contains a lot of what
*I* would call replication objects such as special tables (for example
conflict tables) or a whole bunch of stored procedures used solely for
replicating to/with our subscribers.

> Running sp_removedbreplication will NOT remove any of your user objects. I
t
> ONLY removes replication metadata. You can run it with a clause, e.g. 'tra
n',
> 'merge' etc. if you only want to remove transactional- or merge- replicati
on
> metadata.
I looked it up again in my BOL (SQLServer 2000) and it says there you run
it on the publisher for the publisher database and on the subscriber for
the subscriber database. So I assumed that when you run it on the publisher
you among other things delete those objects I mentioned above. And the sp
has just one parameter, the name of the database.

> I think you need to reassure yourself what is actually going on with your
> database, i.e. is it being replicated or not.
Ok, I try again. Some tables are replicated *from* this database I'm
talking about *to* (better *with* as it is a merge replication) our
subscribers.

> 'Cos the signals you're sending out are somewhat mixed.
My apologies, once again I didn't intend that. I hope I've made everything
clear now.
Greetings
Susanne

dbcc opentran

How do i read this ?
Replicated Transaction Information:
Oldest distributed LSN : (494721:133301:236)
Oldest non-distributed LSN : (494721:133372:1)
What are those values ? What should i watch for between the distributed and
non distributed LSN ?
Which table in the distribution db holds the LSN info so that it needs where
to start from i.e. which record it needs to fetch from the log file to
insert into the distribution db ?
Thanks
Hassan,
this is my take on the DBCC OPENTRAN -
if your log reader is keeping up, the Oldest non-distributed LSN is (0:0:0):
Oldest distributed LSN : (10:384:4)
Oldest non-distributed LSN : (0:0:0)
if it's not able to keep up or is disabled, the output will be in the
following format:
Oldest distributed LSN : (10:388:4)
Oldest non-distributed LSN : (10:390:1)
If you have an open transaction, the format of the output will include a
section at the top:
Oldest active transaction:
SPID (server process ID) : 55
UID (user ID) : 1
Name : mytran
LSN : (10:391:1)
Start time : May 16 2005 10:10:28:920AM
Replicated Transaction Information:
Oldest distributed LSN : (10:388:4)
Oldest non-distributed LSN : (10:390:1)
Using this, you can use dbcc inputbuffer (55) to find the open transaction's
TSQL, and then decide to kill it if necessary.
So, in your case you don't have any open transactions, and your log reader
agent has not read some (committed) transactions from the transaction log,
so they're not yet written to the distribution database. The numbers in
brackets are the log sequence numbers. You can run select * from
::fn_dblog(null,null) to have a look at the list of these, or for more
details you can see them in LogExplorer.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

dbcc opentran

How do I do anything with the results of this guy? How do I find out what
these LSN's are doing?
Replicated Transaction Information:
Oldest distributed LSN : (574262:1479:4)
Oldest non-distributed LSN : (574262:1498:1)
SQL2K SP3
TIA, ChrisR
I think it means, that you have transactional replication setup in this
database. And the logreader agent has not yet picked up that transaction.
Make sure your log reader agent for this published database is running. If
it isn't running or failed, then you have to start it and fix any errors it
is encountering. Once log reader picks up all the replicable transactions,
there won't be any replication open transactions.
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"ChrisR" <bla@.noemail.com> wrote in message
news:%23ODpR8R5EHA.3124@.TK2MSFTNGP11.phx.gbl...
> How do I do anything with the results of this guy? How do I find out what
> these LSN's are doing?
> Replicated Transaction Information:
> Oldest distributed LSN : (574262:1479:4)
> Oldest non-distributed LSN : (574262:1498:1)
> --
> SQL2K SP3
> TIA, ChrisR
>

dbcc opentran

How do I do anything with the results of this guy? How do I find out what
these LSN's are doing?
Replicated Transaction Information:
Oldest distributed LSN : (574262:1479:4)
Oldest non-distributed LSN : (574262:1498:1)
--
SQL2K SP3
TIA, ChrisRI think it means, that you have transactional replication setup in this
database. And the logreader agent has not yet picked up that transaction.
Make sure your log reader agent for this published database is running. If
it isn't running or failed, then you have to start it and fix any errors it
is encountering. Once log reader picks up all the replicable transactions,
there won't be any replication open transactions.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"ChrisR" <bla@.noemail.com> wrote in message
news:%23ODpR8R5EHA.3124@.TK2MSFTNGP11.phx.gbl...
> How do I do anything with the results of this guy? How do I find out what
> these LSN's are doing?
> Replicated Transaction Information:
> Oldest distributed LSN : (574262:1479:4)
> Oldest non-distributed LSN : (574262:1498:1)
> --
> SQL2K SP3
> TIA, ChrisR
>

Wednesday, March 21, 2012

dbcc opentran with tableresults

DBCC OPENTRAN shows "REPLICATION" on a server that is not configured for replication

dbcc opentran results

dbcc opentran results

dbcc opentran results

DBCC OpenTran results

dbcc opentran results

dbcc opentran is not helping me on "waiting for backend connection"

DBCC Opentran - possible causes for results

Monday, March 19, 2012

DBCC Opentran - possible causes for results

DBCC Opentran - possible causes for results

dbcc opentran

dbcc opentran

dbcc opentran

DBfile usage

Blog Archive

About Me