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
>

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 Error

Anyone seen this before? What causes it? How do I fix it?
Thanks!!
Richard
DBCC results for 'ivTransitItems'.
Msg 2537, Level 16, State 24, Line 5
Table error: object ID 295528682, index ID 0, partition ID 19367767703552,
alloc unit ID 19367767703552 (type In-row data), page (1:22320), row 0. The
record check (valid record length) failed. The values are 112 and 108.
Msg 8929, Level 16, State 1, Line 5
Object ID 295528682, index ID 0, partition ID 19367767703552, alloc unit ID
19367767703552 (type In-row data): Errors found in off-row data with ID
3755409408 owned by data record identified by RID = (1:22320:0)
Msg 2537, Level 16, State 24, Line 5
Hi,
Can you try doing these:-
1. Do a DBCC DBREINDEX and after that run a DBCC CHECKTABLE and see if error
persists
2. If error Persists then do a DBCC CHECKTABLE with REPAIR_REBUILD
3. If you still have error then take a backup of database and then execute
DBCC CHECKDB WITH REPAIR_ALLOW_DATA_LOSS
To use the repair optiion database must be set to single user mode.
Thanks
Hari
SQL Server MVP
"Richard Douglass" wrote:

> Anyone seen this before? What causes it? How do I fix it?
> Thanks!!
> Richard
>
> DBCC results for 'ivTransitItems'.
> Msg 2537, Level 16, State 24, Line 5
> Table error: object ID 295528682, index ID 0, partition ID 19367767703552,
> alloc unit ID 19367767703552 (type In-row data), page (1:22320), row 0. The
> record check (valid record length) failed. The values are 112 and 108.
> Msg 8929, Level 16, State 1, Line 5
> Object ID 295528682, index ID 0, partition ID 19367767703552, alloc unit ID
> 19367767703552 (type In-row data): Errors found in off-row data with ID
> 3755409408 owned by data record identified by RID = (1:22320:0)
> Msg 2537, Level 16, State 24, Line 5
>
>
|||Actually, before you do any of these, if you have a backup you should
restore from backup. This is much safer than running the DBCC commands you
list below (especially the third one), as those can result in dataloss
which could make your database totally inconsistent.
Thanks,
Marcel.
On Wed, 13 Sep 2006 16:11:01 -0700, Hari Prasad wrote:
[vbcol=seagreen]
> Hi,
> Can you try doing these:-
> 1. Do a DBCC DBREINDEX and after that run a DBCC CHECKTABLE and see if error
> persists
> 2. If error Persists then do a DBCC CHECKTABLE with REPAIR_REBUILD
> 3. If you still have error then take a backup of database and then execute
> DBCC CHECKDB WITH REPAIR_ALLOW_DATA_LOSS
> To use the repair optiion database must be set to single user mode.
> Thanks
> Hari
> SQL Server MVP
> "Richard Douglass" wrote:

DBCC Error

Anyone seen this before? What causes it? How do I fix it?
Thanks!!
Richard
DBCC results for 'ivTransitItems'.
Msg 2537, Level 16, State 24, Line 5
Table error: object ID 295528682, index ID 0, partition ID 19367767703552,
alloc unit ID 19367767703552 (type In-row data), page (1:22320), row 0. The
record check (valid record length) failed. The values are 112 and 108.
Msg 8929, Level 16, State 1, Line 5
Object ID 295528682, index ID 0, partition ID 19367767703552, alloc unit ID
19367767703552 (type In-row data): Errors found in off-row data with ID
3755409408 owned by data record identified by RID = (1:22320:0)
Msg 2537, Level 16, State 24, Line 5Hi,
Can you try doing these:-
1. Do a DBCC DBREINDEX and after that run a DBCC CHECKTABLE and see if error
persists
2. If error Persists then do a DBCC CHECKTABLE with REPAIR_REBUILD
3. If you still have error then take a backup of database and then execute
DBCC CHECKDB WITH REPAIR_ALLOW_DATA_LOSS
To use the repair optiion database must be set to single user mode.
Thanks
Hari
SQL Server MVP
"Richard Douglass" wrote:
> Anyone seen this before? What causes it? How do I fix it?
> Thanks!!
> Richard
>
> DBCC results for 'ivTransitItems'.
> Msg 2537, Level 16, State 24, Line 5
> Table error: object ID 295528682, index ID 0, partition ID 19367767703552,
> alloc unit ID 19367767703552 (type In-row data), page (1:22320), row 0. The
> record check (valid record length) failed. The values are 112 and 108.
> Msg 8929, Level 16, State 1, Line 5
> Object ID 295528682, index ID 0, partition ID 19367767703552, alloc unit ID
> 19367767703552 (type In-row data): Errors found in off-row data with ID
> 3755409408 owned by data record identified by RID = (1:22320:0)
> Msg 2537, Level 16, State 24, Line 5
>
>|||Actually, before you do any of these, if you have a backup you should
restore from backup. This is much safer than running the DBCC commands you
list below (especially the third one), as those can result in dataloss
which could make your database totally inconsistent.
Thanks,
Marcel.
On Wed, 13 Sep 2006 16:11:01 -0700, Hari Prasad wrote:
> Hi,
> Can you try doing these:-
> 1. Do a DBCC DBREINDEX and after that run a DBCC CHECKTABLE and see if error
> persists
> 2. If error Persists then do a DBCC CHECKTABLE with REPAIR_REBUILD
> 3. If you still have error then take a backup of database and then execute
> DBCC CHECKDB WITH REPAIR_ALLOW_DATA_LOSS
> To use the repair optiion database must be set to single user mode.
> Thanks
> Hari
> SQL Server MVP
> "Richard Douglass" wrote:
>> Anyone seen this before? What causes it? How do I fix it?
>> Thanks!!
>> Richard
>>
>> DBCC results for 'ivTransitItems'.
>> Msg 2537, Level 16, State 24, Line 5
>> Table error: object ID 295528682, index ID 0, partition ID 19367767703552,
>> alloc unit ID 19367767703552 (type In-row data), page (1:22320), row 0. The
>> record check (valid record length) failed. The values are 112 and 108.
>> Msg 8929, Level 16, State 1, Line 5
>> Object ID 295528682, index ID 0, partition ID 19367767703552, alloc unit ID
>> 19367767703552 (type In-row data): Errors found in off-row data with ID
>> 3755409408 owned by data record identified by RID = (1:22320:0)
>> Msg 2537, Level 16, State 24, Line 5
>>

DBCC Error

Anyone seen this before? What causes it? How do I fix it?
Thanks!!
Richard
DBCC results for 'ivTransitItems'.
Msg 2537, Level 16, State 24, Line 5
Table error: object ID 295528682, index ID 0, partition ID 19367767703552,
alloc unit ID 19367767703552 (type In-row data), page (1:22320), row 0. The
record check (valid record length) failed. The values are 112 and 108.
Msg 8929, Level 16, State 1, Line 5
Object ID 295528682, index ID 0, partition ID 19367767703552, alloc unit ID
19367767703552 (type In-row data): Errors found in off-row data with ID
3755409408 owned by data record identified by RID = (1:22320:0)
Msg 2537, Level 16, State 24, Line 5Hi,
Can you try doing these:-
1. Do a DBCC DBREINDEX and after that run a DBCC CHECKTABLE and see if error
persists
2. If error Persists then do a DBCC CHECKTABLE with REPAIR_REBUILD
3. If you still have error then take a backup of database and then execute
DBCC CHECKDB WITH REPAIR_ALLOW_DATA_LOSS
To use the repair optiion database must be set to single user mode.
Thanks
Hari
SQL Server MVP
"Richard Douglass" wrote:

> Anyone seen this before? What causes it? How do I fix it?
> Thanks!!
> Richard
>
> DBCC results for 'ivTransitItems'.
> Msg 2537, Level 16, State 24, Line 5
> Table error: object ID 295528682, index ID 0, partition ID 19367767703552,
> alloc unit ID 19367767703552 (type In-row data), page (1:22320), row 0. Th
e
> record check (valid record length) failed. The values are 112 and 108.
> Msg 8929, Level 16, State 1, Line 5
> Object ID 295528682, index ID 0, partition ID 19367767703552, alloc unit I
D
> 19367767703552 (type In-row data): Errors found in off-row data with ID
> 3755409408 owned by data record identified by RID = (1:22320:0)
> Msg 2537, Level 16, State 24, Line 5
>
>|||Actually, before you do any of these, if you have a backup you should
restore from backup. This is much safer than running the DBCC commands you
list below (especially the third one), as those can result in dataloss
which could make your database totally inconsistent.
Thanks,
Marcel.
On Wed, 13 Sep 2006 16:11:01 -0700, Hari Prasad wrote:
[vbcol=seagreen]
> Hi,
> Can you try doing these:-
> 1. Do a DBCC DBREINDEX and after that run a DBCC CHECKTABLE and see if err
or
> persists
> 2. If error Persists then do a DBCC CHECKTABLE with REPAIR_REBUILD
> 3. If you still have error then take a backup of database and then execute
> DBCC CHECKDB WITH REPAIR_ALLOW_DATA_LOSS
> To use the repair optiion database must be set to single user mode.
> Thanks
> Hari
> SQL Server MVP
> "Richard Douglass" wrote:
>