distribution database having problems??

Something is wrong with my distribtion database. When the distribution clean
up agent runs (or I run dbo.sp_MSdistribution_cleanup manually)
I get the following error message:
Executed as user: sa. Table error: Database ID 9, object ID 5575058, index
ID 1. Chain linkage mismatch. (1:89299)->next = (1:59339), but
(1:59339)->prev = (0:0). [SQLSTATE HY000] (Error 8908). The step failed.
SO then I run
DBCC CHECKDB
on the distribution database. The MSrepl_commands is the table with the
problem with repeated messages such as:
Table error: Object ID 5575058, index ID 1. B-tree page (1:91019) has two
parent nodes (1:93624), slot 103 and (1:89299), slot 1.
I run this:
dbcc checktable ('MSrepl_commands')
and get the same type of error message.
I have no idea how to fix this. Any advise?? (If I try and use the repair
options of the dbcc commands than it says I have to have the DB in single
user mode. Not sure if you are even suppose to do that with a distribution
db?)
Thanks so much for any and all help,
Kristy
Believe it or not these problems crop up from time to time on replicated
databases. Stop the sql server agent on the publisher, yank the network
cable (if possible), and put this database in single user mode and do the
repair.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Kristy" <pleasereplyby@.posting.com> wrote in message
news:OCFMw7kPGHA.1696@.TK2MSFTNGP14.phx.gbl...
> Something is wrong with my distribtion database. When the distribution
> clean
> up agent runs (or I run dbo.sp_MSdistribution_cleanup manually)
> I get the following error message:
> Executed as user: sa. Table error: Database ID 9, object ID 5575058, index
> ID 1. Chain linkage mismatch. (1:89299)->next = (1:59339), but
> (1:59339)->prev = (0:0). [SQLSTATE HY000] (Error 8908). The step failed.
> SO then I run
> DBCC CHECKDB
> on the distribution database. The MSrepl_commands is the table with the
> problem with repeated messages such as:
> Table error: Object ID 5575058, index ID 1. B-tree page (1:91019) has two
> parent nodes (1:93624), slot 103 and (1:89299), slot 1.
> I run this:
> dbcc checktable ('MSrepl_commands')
> and get the same type of error message.
> I have no idea how to fix this. Any advise?? (If I try and use the
> repair
> options of the dbcc commands than it says I have to have the DB in single
> user mode. Not sure if you are even suppose to do that with a distribution
> db?)
> Thanks so much for any and all help,
> Kristy
>
>
|||Thanks Hilary. I will check to see if we can yank the network cable. Not
sure if we can though because it is on a remote server hosted by another
company in another state.
If not, is there something else I can do? (Besides redo replication and drop
the distribution DB.)
As always, thanks!
--Kristy
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eODseSmPGHA.5516@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Believe it or not these problems crop up from time to time on replicated
> databases. Stop the sql server agent on the publisher, yank the network
> cable (if possible), and put this database in single user mode and do the
> repair.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "Kristy" <pleasereplyby@.posting.com> wrote in message
> news:OCFMw7kPGHA.1696@.TK2MSFTNGP14.phx.gbl...
index[vbcol=seagreen]
failed.[vbcol=seagreen]
two[vbcol=seagreen]
single[vbcol=seagreen]
distribution
>

Distribution Cleanup Job Fails

The distribution cleanup job that runs for our transactional replication on
sql server 2005 fails.
The job is:
EXEC dbo.sp_MSdistribution_cleanup @.min_distretention = 0,
@.max_distretention = 72
The Error is:
Msg 20015, Level 16, State 1, Procedure sp_MSreplremoveuncdir, Line 83
Could not remove directory
'\\sprs76\rysrepldata\unc\SPRS76_RYS_RYS_OPERATION ALTOREPLICAHIST\20060713140760\'.
Check the security context of xp_cmdshell and close other processes that may
be accessing the directory.
Replication-@.rowcount_only parameter must be the value 0,1, or 2. 0=7.0
compatible checksum. 1=only check rowcou: agent
RYSSprs76_distribution@.rowcount_only parameter must be the value 0,1, or 2.
0=7.0 compatible checksu scheduled for retry. Could not clean up the
distribution transaction tables.
If I remove this directory manually, would it cause the replication to
fail? I have tried this on a Test server and it seems that the replication
fails.
I tried to run this job in the Query window and it return this same error.
Our data file is growing and I cannot shrink it because of this issue.
What can i do to resolve this?
Thanks,
George Gopie
There's an MMC snapin to look at shared folders and their connections and
open files. This snapin could be used to drop the connections where needed.
If you have the folder open yourself locally, this snapin won't pick it up,
so you'd have to use the task manager to see what is likely to be accessing
it.
Another possibility is to drop the folder yourself then recreate it for the
distribution agent to later delete.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||No it would not cause replication to fail, only remove this portion
20060713140760
Make sure that xp_cmdshell is enabled and the SQL Serer agent account has
rights to list files and folders and delete files on the root of the
repldata directory and it subdirectories.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"georgeg" <ggg@.hotamil.com> wrote in message
news:DFE68859-01CF-4236-AC2A-F9CA596B9482@.microsoft.com...
> The distribution cleanup job that runs for our transactional replication
> on
> sql server 2005 fails.
> The job is:
> EXEC dbo.sp_MSdistribution_cleanup @.min_distretention = 0,
> @.max_distretention = 72
> The Error is:
> Msg 20015, Level 16, State 1, Procedure sp_MSreplremoveuncdir, Line 83
> Could not remove directory
> '\\sprs76\rysrepldata\unc\SPRS76_RYS_RYS_OPERATION ALTOREPLICAHIST\20060713140760\'.
> Check the security context of xp_cmdshell and close other processes that
> may
> be accessing the directory.
> Replication-@.rowcount_only parameter must be the value 0,1, or 2. 0=7.0
> compatible checksum. 1=only check rowcou: agent
> RYSSprs76_distribution@.rowcount_only parameter must be the value 0,1, or
> 2.
> 0=7.0 compatible checksu scheduled for retry. Could not clean up the
> distribution transaction tables.
>
> If I remove this directory manually, would it cause the replication to
> fail? I have tried this on a Test server and it seems that the replication
> fails.
> I tried to run this job in the Query window and it return this same error.
> Our data file is growing and I cannot shrink it because of this issue.
> What can i do to resolve this?
> Thanks,
> --
> George Gopie
>