Attach And Detach Replication

I would like to attach and detach my database due to change the log file
since that logfile already contain too large data.
But the problem occur, It's said that must drop the replication before do
any attach on that data .But i can't drop the replication since i still need
replicate the data from the other server back to local server.
so, anyone can give me any suggestion to do the attach withour drop the
replication?
For a supported solution, you'll need to disable replication before the
detach and attach
(http://msdn2.microsoft.com/en-us/library/ms188769.aspx). Ideally script out
the publication and do a nosync initialization if you can prevent any data
access.
For an unsupported solution that doesn't involve dropping the publication,
search this newsgroup for Ponnu...
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
"sam" wrote:

> I would like to attach and detach my database due to change the log file
> since that logfile already contain too large data.
> But the problem occur, It's said that must drop the replication before do
> any attach on that data .But i can't drop the replication since i still need
> replicate the data from the other server back to local server.
> so, anyone can give me any suggestion to do the attach withour drop the
> replication?
>
sql

Ater table transactional replication

I have SQL Server 2000 transactional replication and it
works fine.
I need to break the replication to apply the vendor script
to the database. The vendor will modify database
objects.
I delete the transaction replication subscription.
I tried to alter a table and received an error stating
that the table could not be altered because it was
replicated.
What is the easiest procedure so that can apply the vendor
script.
Thanks,
Mark
Mark,
it depends on your particular modification, but in most cases you could use
sp_repladdcolumn and sp_repldropcolumn. Using these procedures there's no
need to drop the subscription. For more radical changes, you could also drop
the article from the publication and add it back in.
HTH,
Paul Ibison
|||And for your specific one, run sp_removedbreplication. If for some reason
it still gives you that error, turn on allow updates, update the replinfo
column in sysobjects and set it to 0, turn off allow updates.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||This seems a little drastic just to modify the schema. I think Paul's
response is more appropriate.
Rand
This posting is provided "as is" with no warranties and confers no rights.
sql

Asynchronous ActiveX Replication Problems

Hello,
I have a VB.NET service that has a timer. On certain timer ticks, a
merge replication is called.
I have a wrapper class that asynchronously starts the merge replication
process (using the .BeginInvoke() method). I did this so that my
service could continue to do other things while the merge replication
took place.
Through logging, I know that a separate thread is indeed handling the
replication process.
The problem is that the merge replication appears to block the calling
thread as soon as replication begins. I can see this happening because
I have logging statements inside the merge status event. I see the
calling thread continue to do work until the merge thread begins to
initialize. As soon as the merge is complete, the calling thread once
again continues.
At first I thought that maybe the entire process was blocked by the
merge replication ActiveX object, but the timer event continues to fire.
So it appears that only the calling thread is blocked.
Any ideas?
Thanks,
Jeff
are you using an MSDE database? MSDE is throttled at 8 simultaneous
workloads.
Perhaps this is the problem you are running into.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Jeff Hedlund" <jeff.hedlund_NOSPAM_@._NOSPAM_elsym.com> wrote in message
news:Nu02d.286$DY.238@.chiapp18.algx.net...
> Hello,
> I have a VB.NET service that has a timer. On certain timer ticks, a
> merge replication is called.
> I have a wrapper class that asynchronously starts the merge replication
> process (using the .BeginInvoke() method). I did this so that my
> service could continue to do other things while the merge replication
> took place.
> Through logging, I know that a separate thread is indeed handling the
> replication process.
> The problem is that the merge replication appears to block the calling
> thread as soon as replication begins. I can see this happening because
> I have logging statements inside the merge status event. I see the
> calling thread continue to do work until the merge thread begins to
> initialize. As soon as the merge is complete, the calling thread once
> again continues.
> At first I thought that maybe the entire process was blocked by the
> merge replication ActiveX object, but the timer event continues to fire.
> So it appears that only the calling thread is blocked.
> Any ideas?
> Thanks,
> Jeff
|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.replication:56175
Hilary Cotter wrote:
> are you using an MSDE database? MSDE is throttled at 8 simultaneous
> workloads.
No, SQL Server 2000 on both publisher/distributor and subscriber.
But besides that, even if it were being throttled - it shouldn't block
the other thread from continue to process non-SQL Server instructions.
Thanks,
Jeff
|||Can you find out what the merge replication process is doing when it blocks
the other process?
If it is applying a snapshot you can expect blocking. For the type of
singleton transactions that merge replication uses you should not
experieince this level of locking, unless perhaps you are updating columns
which have indexes on them.
You might want to run sp_lock on the client to get an idea of what sort of
locks the replication process is applying.
You might also want to see if perhaps the locking is occuring due to your
code. For instance, I do not experience any locking when using databases
which are part of a merge publication or subscription when the merge
replication agents are running and when I am running the replication process
via tsql or em. This will rule out replication and give you a better window
into what is occuring.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Jeff Hedlund" <jeff.hedlund_NOSPAM_@._NOSPAM_elsym.com> wrote in message
news:7b12d.297$DY.106@.chiapp18.algx.net...
> Hilary Cotter wrote:
> No, SQL Server 2000 on both publisher/distributor and subscriber.
> But besides that, even if it were being throttled - it shouldn't block
> the other thread from continue to process non-SQL Server instructions.
> Thanks,
> Jeff
|||Hilary Cotter wrote:
> Can you find out what the merge replication process is doing when it blocks
> the other process?
Yes: It blocks the calling thread as soon as I get the "Initializing"
status until I get the "Complete" status.

> If it is applying a snapshot you can expect blocking. For the type of
> singleton transactions that merge replication uses you should not
> experieince this level of locking, unless perhaps you are updating columns
> which have indexes on them.
> You might want to run sp_lock on the client to get an idea of what sort of
> locks the replication process is applying.
It sounds like you are describing a possible lock on the database. This
is not what is happening. What I am describing is my main thread in my
application is getting blocked until the merge thread (separate from the
main thread) is complete. The main thread is not doing any database
work at all when it gets blocked by the merge thread.

> You might also want to see if perhaps the locking is occuring due to your
> code. For instance, I do not experience any locking when using databases
> which are part of a merge publication or subscription when the merge
> replication agents are running and when I am running the replication process
> via tsql or em. This will rule out replication and give you a better window
> into what is occuring.
I am sure that if I were to run the replication from tsql or em it would
not block - because of my above paragraph. It's the actual ActiveX COM
replication object that is blocking my main thread for some reason. And
like I said in my original post, I am positive that the main thread is
properly spawning a new thread for the merge.
Thanks for the ideas!
Jeff
|||If you want to send the code to me, offline I'll try to repro it.
Otherwise I suggest you open a support incident with PSS.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Jeff Hedlund" <jeff.hedlund_NOSPAM_@._NOSPAM_elsym.com> wrote in message
news:K%g2d.520$DY.90@.chiapp18.algx.net...[vbcol=seagreen]
> Hilary Cotter wrote:
blocks[vbcol=seagreen]
> Yes: It blocks the calling thread as soon as I get the "Initializing"
> status until I get the "Complete" status.
columns[vbcol=seagreen]
of[vbcol=seagreen]
> It sounds like you are describing a possible lock on the database. This
> is not what is happening. What I am describing is my main thread in my
> application is getting blocked until the merge thread (separate from the
> main thread) is complete. The main thread is not doing any database
> work at all when it gets blocked by the merge thread.
your[vbcol=seagreen]
process[vbcol=seagreen]
window
> I am sure that if I were to run the replication from tsql or em it would
> not block - because of my above paragraph. It's the actual ActiveX COM
> replication object that is blocking my main thread for some reason. And
> like I said in my original post, I am positive that the main thread is
> properly spawning a new thread for the merge.
> Thanks for the ideas!
> Jeff
|||Jeff Hedlund wrote:
> The problem is that the merge replication appears to block the calling
> thread as soon as replication begins.
FYI - I have found the source of my problem. When using BeginInvoke()
to create a separate thread for the replication object, VB.NET uses a
thread from the thread pool.
The thread pool operates with the MTA apartment state. The SQL
replication ActiveX object apparently uses STA, so it was blocking the
MTA apartment while it ran. Make sure I used an STA thread to run the
SQL replication object on solved the blocking problem.
Thanks,
Jeff
sql

async_network_io wait during replication

I have re-initialize some subscription. After generated snapshot successfully, the synchronization view showing 'The process is running and is waiting for a response from the server.' and I found the replication process is under 'async_network_io wait'. The job has run for more than 8 hrs. Any idea to improve it? Thanks in advaise.

Are you running the sync agent (like merge agent/distribution agent) in continuous mode? If yes, it will show running forever, although the submitted transactions/commands have been replicated, agent job is just keeping waiting for next actions.

Thanks

-Yunjing

|||Yes, in continuous mode. But the replication hadn't completed in 8 hrs. Any idea? Thanks|||

If an continuous sync complete for a session, its current history log usually shows something like "No replicated transactions are available" and sync job is still showing as running.

As for your case, could you open sqlmonitor to check the current status for the job, is it still running or fail already? If it's running, what's the current history shown? Does the history get updated every a few minutes?

-Yunjing

Assist needed in creating a replication with sql 2005

I tried to create a local snapshot replication from data A to database
B for sql 2005. I followed the wizard and it was created successfully.
But,nothing written to the replication folder and the job failed.
I manually executed the sqls and it always failed on
sp_addpublication_snapshot and the error was:
'DB4\Administrator' is a member of sysadmin server role and cannot be
granted to or revoked from the proxy. Members of sysadmin server role
are allowed to use any proxy.
I log in to windows 2003 as administrator and the replication account
id dbsnap. What I have to do to avoid the error?
Is there a detailed step-by=step guide to create a snapshot
replication?
Can someone provide a set of sqls that I can just use to create a local
(or remote) snapshot?
Thanks,
Andy
The scripts are:
use [T2]
exec sp_replicationdboption @.dbname = N'T2', @.optname = N'publish',
@.value = N'true'
GO
-- Adding the snapshot publication
use [T2]
exec sp_addpublication @.publication = N'T2', @.description = N'Snapshot
publication of
database ''T2'' from Publisher ''DB4''.', @.sync_method = N'native',
@.retention = 0,
@.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous =
N'true',
@.enabled_for_internet = N'false', @.snapshot_in_defaultfolder = N'true',
@.compress_snapshot =
N'false', @.ftp_port = 21, @.ftp_login = N'anonymous',
@.allow_subscription_copy = N'false',
@.add_to_active_directory = N'false', @.repl_freq = N'snapshot', @.status
= N'active',
@.independent_agent = N'true', @.immediate_sync = N'true',
@.allow_sync_tran = N'false',
@.autogen_sync_procs = N'false', @.allow_queued_tran = N'false',
@.allow_dts = N'false',
@.replicate_ddl = 1
GO
exec sp_addpublication_snapshot @.publication = N'T2', @.frequency_type =
1,
@.frequency_interval = 0, @.frequency_relative_interval = 0,
@.frequency_recurrence_factor = 0,
@.frequency_subday = 0, @.frequency_subday_interval = 0,
@.active_start_time_of_day = 0,
@.active_end_time_of_day = 235959, @.active_start_date = 0,
@.active_end_date = 0, @.job_login =
N'db4\dbsnap', @.job_password = N'wenhua', @.publisher_security_mode = 0,
@.publisher_login =
N'sa', @.publisher_password = N'chang5911'
use [T2]
exec sp_addarticle @.publication = N'T2', @.article = N'RETURN_REASON',
@.source_owner =
N'dbo', @.source_object = N'RETURN_REASON', @.type = N'logbased',
@.description = null,
@.creation_script = null, @.pre_creation_cmd = N'drop', @.schema_option =
0x000000000803509D,
@.identityrangemanagementoption = N'manual', @.destination_table =
N'RETURN_REASON',
@.destination_owner = N'dbo', @.vertical_partition = N'false'
GO
Can you extract the code from the job and pass it to the snapshot.exe found
in c:\program files\Microsoft SQL Server\90\com?
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
"AH" <hhhsu7a@.yahoo.com> wrote in message
news:1168824792.916342.291270@.11g2000cwr.googlegro ups.com...
>I tried to create a local snapshot replication from data A to database
> B for sql 2005. I followed the wizard and it was created successfully.
> But,nothing written to the replication folder and the job failed.
> I manually executed the sqls and it always failed on
> sp_addpublication_snapshot and the error was:
> 'DB4\Administrator' is a member of sysadmin server role and cannot be
> granted to or revoked from the proxy. Members of sysadmin server role
> are allowed to use any proxy.
>
> I log in to windows 2003 as administrator and the replication account
> id dbsnap. What I have to do to avoid the error?
>
> Is there a detailed step-by=step guide to create a snapshot
> replication?
>
> Can someone provide a set of sqls that I can just use to create a local
> (or remote) snapshot?
>
> Thanks,
> Andy
>
> The scripts are:
> use [T2]
> exec sp_replicationdboption @.dbname = N'T2', @.optname = N'publish',
> @.value = N'true'
> GO
> -- Adding the snapshot publication
> use [T2]
> exec sp_addpublication @.publication = N'T2', @.description = N'Snapshot
> publication of
>
> database ''T2'' from Publisher ''DB4''.', @.sync_method = N'native',
> @.retention = 0,
>
> @.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous =
> N'true',
>
> @.enabled_for_internet = N'false', @.snapshot_in_defaultfolder = N'true',
> @.compress_snapshot =
>
> N'false', @.ftp_port = 21, @.ftp_login = N'anonymous',
> @.allow_subscription_copy = N'false',
>
> @.add_to_active_directory = N'false', @.repl_freq = N'snapshot', @.status
> = N'active',
>
> @.independent_agent = N'true', @.immediate_sync = N'true',
> @.allow_sync_tran = N'false',
>
> @.autogen_sync_procs = N'false', @.allow_queued_tran = N'false',
> @.allow_dts = N'false',
>
> @.replicate_ddl = 1
> GO
>
> exec sp_addpublication_snapshot @.publication = N'T2', @.frequency_type =
> 1,
>
> @.frequency_interval = 0, @.frequency_relative_interval = 0,
> @.frequency_recurrence_factor = 0,
>
> @.frequency_subday = 0, @.frequency_subday_interval = 0,
> @.active_start_time_of_day = 0,
>
> @.active_end_time_of_day = 235959, @.active_start_date = 0,
> @.active_end_date = 0, @.job_login =
>
> N'db4\dbsnap', @.job_password = N'wenhua', @.publisher_security_mode = 0,
> @.publisher_login =
>
> N'sa', @.publisher_password = N'chang5911'
>
> use [T2]
> exec sp_addarticle @.publication = N'T2', @.article = N'RETURN_REASON',
> @.source_owner =
>
> N'dbo', @.source_object = N'RETURN_REASON', @.type = N'logbased',
> @.description = null,
>
> @.creation_script = null, @.pre_creation_cmd = N'drop', @.schema_option =
> 0x000000000803509D,
>
> @.identityrangemanagementoption = N'manual', @.destination_table =
> N'RETURN_REASON',
>
> @.destination_owner = N'dbo', @.vertical_partition = N'false'
> GO
>
|||Hilary,
I switched to use administor, instead of a windows user, and the
replication went fine. Does the windows user id require to have
administion privilege?
Andy
Hilary Cotter wrote:[vbcol=seagreen]
> Can you extract the code from the job and pass it to the snapshot.exe found
> in c:\program files\Microsoft SQL Server\90\com?
> --
> 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
>
> "AH" <hhhsu7a@.yahoo.com> wrote in message
> news:1168824792.916342.291270@.11g2000cwr.googlegro ups.com...

Assist needed in creating a replication with sql 2005

I tried to create a local snapshot replication from data A to database
B for sql 2005. I followed the wizard and it was created successfully.
But,nothing written to the replication folder and the job failed.
I manually executed the sqls and it always failed on
sp_addpublication_snapshot and the error was:
'DB4\Administrator' is a member of sysadmin server role and cannot be
granted to or revoked from the proxy. Members of sysadmin server role
are allowed to use any proxy.
I log in to windows 2003 as administrator and the replication account
id dbsnap. What I have to do to avoid the error?
Is there a detailed step-by=step guide to create a snapshot
replication?
Can someone provide a set of sqls that I can just use to create a local
(or remote) snapshot?
Thanks,
Andy
The scripts are:
use [T2]
exec sp_replicationdboption @.dbname = N'T2', @.optname = N'publish',
@.value = N'true'
GO
-- Adding the snapshot publication
use [T2]
exec sp_addpublication @.publication = N'T2', @.description = N'Snapshot
publication of
database ''T2'' from Publisher ''DB4''.', @.sync_method = N'native',
@.retention = 0,
@.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous = N'true',
@.enabled_for_internet = N'false', @.snapshot_in_defaultfolder = N'true',
@.compress_snapshot =
N'false', @.ftp_port = 21, @.ftp_login = N'anonymous',
@.allow_subscription_copy = N'false',
@.add_to_active_directory = N'false', @.repl_freq = N'snapshot', @.status
= N'active',
@.independent_agent = N'true', @.immediate_sync = N'true',
@.allow_sync_tran = N'false',
@.autogen_sync_procs = N'false', @.allow_queued_tran = N'false',
@.allow_dts = N'false',
@.replicate_ddl = 1
GO
exec sp_addpublication_snapshot @.publication = N'T2', @.frequency_type =
1,
@.frequency_interval = 0, @.frequency_relative_interval = 0,
@.frequency_recurrence_factor = 0,
@.frequency_subday = 0, @.frequency_subday_interval = 0,
@.active_start_time_of_day = 0,
@.active_end_time_of_day = 235959, @.active_start_date = 0,
@.active_end_date = 0, @.job_login =
N'db4\dbsnap', @.job_password = N'wenhua', @.publisher_security_mode = 0,
@.publisher_login =
N'sa', @.publisher_password = N'chang5911'
use [T2]
exec sp_addarticle @.publication = N'T2', @.article = N'RETURN_REASON',
@.source_owner =
N'dbo', @.source_object = N'RETURN_REASON', @.type = N'logbased',
@.description = null,
@.creation_script = null, @.pre_creation_cmd = N'drop', @.schema_option = 0x000000000803509D,
@.identityrangemanagementoption = N'manual', @.destination_table = N'RETURN_REASON',
@.destination_owner = N'dbo', @.vertical_partition = N'false'
GOCan you extract the code from the job and pass it to the snapshot.exe found
in c:\program files\Microsoft SQL Server\90\com?
--
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
"AH" <hhhsu7a@.yahoo.com> wrote in message
news:1168824792.916342.291270@.11g2000cwr.googlegroups.com...
>I tried to create a local snapshot replication from data A to database
> B for sql 2005. I followed the wizard and it was created successfully.
> But,nothing written to the replication folder and the job failed.
> I manually executed the sqls and it always failed on
> sp_addpublication_snapshot and the error was:
> 'DB4\Administrator' is a member of sysadmin server role and cannot be
> granted to or revoked from the proxy. Members of sysadmin server role
> are allowed to use any proxy.
>
> I log in to windows 2003 as administrator and the replication account
> id dbsnap. What I have to do to avoid the error?
>
> Is there a detailed step-by=step guide to create a snapshot
> replication?
>
> Can someone provide a set of sqls that I can just use to create a local
> (or remote) snapshot?
>
> Thanks,
> Andy
>
> The scripts are:
> use [T2]
> exec sp_replicationdboption @.dbname = N'T2', @.optname = N'publish',
> @.value = N'true'
> GO
> -- Adding the snapshot publication
> use [T2]
> exec sp_addpublication @.publication = N'T2', @.description = N'Snapshot
> publication of
>
> database ''T2'' from Publisher ''DB4''.', @.sync_method = N'native',
> @.retention = 0,
>
> @.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous => N'true',
>
> @.enabled_for_internet = N'false', @.snapshot_in_defaultfolder = N'true',
> @.compress_snapshot =>
> N'false', @.ftp_port = 21, @.ftp_login = N'anonymous',
> @.allow_subscription_copy = N'false',
>
> @.add_to_active_directory = N'false', @.repl_freq = N'snapshot', @.status
> = N'active',
>
> @.independent_agent = N'true', @.immediate_sync = N'true',
> @.allow_sync_tran = N'false',
>
> @.autogen_sync_procs = N'false', @.allow_queued_tran = N'false',
> @.allow_dts = N'false',
>
> @.replicate_ddl = 1
> GO
>
> exec sp_addpublication_snapshot @.publication = N'T2', @.frequency_type => 1,
>
> @.frequency_interval = 0, @.frequency_relative_interval = 0,
> @.frequency_recurrence_factor = 0,
>
> @.frequency_subday = 0, @.frequency_subday_interval = 0,
> @.active_start_time_of_day = 0,
>
> @.active_end_time_of_day = 235959, @.active_start_date = 0,
> @.active_end_date = 0, @.job_login =>
> N'db4\dbsnap', @.job_password = N'wenhua', @.publisher_security_mode = 0,
> @.publisher_login =>
> N'sa', @.publisher_password = N'chang5911'
>
> use [T2]
> exec sp_addarticle @.publication = N'T2', @.article = N'RETURN_REASON',
> @.source_owner =>
> N'dbo', @.source_object = N'RETURN_REASON', @.type = N'logbased',
> @.description = null,
>
> @.creation_script = null, @.pre_creation_cmd = N'drop', @.schema_option => 0x000000000803509D,
>
> @.identityrangemanagementoption = N'manual', @.destination_table => N'RETURN_REASON',
>
> @.destination_owner = N'dbo', @.vertical_partition = N'false'
> GO
>|||Hilary,
I switched to use administor, instead of a windows user, and the
replication went fine. Does the windows user id require to have
administion privilege?
Andy
Hilary Cotter wrote:
> Can you extract the code from the job and pass it to the snapshot.exe found
> in c:\program files\Microsoft SQL Server\90\com?
> --
> 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
>
> "AH" <hhhsu7a@.yahoo.com> wrote in message
> news:1168824792.916342.291270@.11g2000cwr.googlegroups.com...
> >I tried to create a local snapshot replication from data A to database
> > B for sql 2005. I followed the wizard and it was created successfully.
> > But,nothing written to the replication folder and the job failed.
> >
> > I manually executed the sqls and it always failed on
> > sp_addpublication_snapshot and the error was:
> > 'DB4\Administrator' is a member of sysadmin server role and cannot be
> > granted to or revoked from the proxy. Members of sysadmin server role
> > are allowed to use any proxy.
> >
> >
> > I log in to windows 2003 as administrator and the replication account
> > id dbsnap. What I have to do to avoid the error?
> >
> >
> > Is there a detailed step-by=step guide to create a snapshot
> > replication?
> >
> >
> > Can someone provide a set of sqls that I can just use to create a local
> >
> > (or remote) snapshot?
> >
> >
> > Thanks,
> > Andy
> >
> >
> > The scripts are:
> > use [T2]
> > exec sp_replicationdboption @.dbname = N'T2', @.optname = N'publish',
> > @.value = N'true'
> > GO
> > -- Adding the snapshot publication
> > use [T2]
> > exec sp_addpublication @.publication = N'T2', @.description = N'Snapshot
> > publication of
> >
> >
> > database ''T2'' from Publisher ''DB4''.', @.sync_method = N'native',
> > @.retention = 0,
> >
> >
> > @.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous => > N'true',
> >
> >
> > @.enabled_for_internet = N'false', @.snapshot_in_defaultfolder = N'true',
> >
> > @.compress_snapshot => >
> >
> > N'false', @.ftp_port = 21, @.ftp_login = N'anonymous',
> > @.allow_subscription_copy = N'false',
> >
> >
> > @.add_to_active_directory = N'false', @.repl_freq = N'snapshot', @.status
> > = N'active',
> >
> >
> > @.independent_agent = N'true', @.immediate_sync = N'true',
> > @.allow_sync_tran = N'false',
> >
> >
> > @.autogen_sync_procs = N'false', @.allow_queued_tran = N'false',
> > @.allow_dts = N'false',
> >
> >
> > @.replicate_ddl = 1
> > GO
> >
> >
> > exec sp_addpublication_snapshot @.publication = N'T2', @.frequency_type => >
> > 1,
> >
> >
> > @.frequency_interval = 0, @.frequency_relative_interval = 0,
> > @.frequency_recurrence_factor = 0,
> >
> >
> > @.frequency_subday = 0, @.frequency_subday_interval = 0,
> > @.active_start_time_of_day = 0,
> >
> >
> > @.active_end_time_of_day = 235959, @.active_start_date = 0,
> > @.active_end_date = 0, @.job_login => >
> >
> > N'db4\dbsnap', @.job_password = N'wenhua', @.publisher_security_mode = 0,
> >
> > @.publisher_login => >
> >
> > N'sa', @.publisher_password = N'chang5911'
> >
> >
> > use [T2]
> > exec sp_addarticle @.publication = N'T2', @.article = N'RETURN_REASON',
> > @.source_owner => >
> >
> > N'dbo', @.source_object = N'RETURN_REASON', @.type = N'logbased',
> > @.description = null,
> >
> >
> > @.creation_script = null, @.pre_creation_cmd = N'drop', @.schema_option => > 0x000000000803509D,
> >
> >
> > @.identityrangemanagementoption = N'manual', @.destination_table => > N'RETURN_REASON',
> >
> >
> > @.destination_owner = N'dbo', @.vertical_partition = N'false'
> > GO
> >

Assist needed in creating a replication with sql 2005

I tried to create a local snapshot replication from data A to database
B for sql 2005. I followed the wizard and it was created successfully.
But,nothing written to the replication folder and the job failed.
I manually executed the sqls and it always failed on
sp_addpublication_snapshot and the error was:
'DB4\Administrator' is a member of sysadmin server role and cannot be
granted to or revoked from the proxy. Members of sysadmin server role
are allowed to use any proxy.
I log in to windows 2003 as administrator and the replication account
id dbsnap. What I have to do to avoid the error?
Is there a detailed step-by=step guide to create a snapshot
replication?
Can someone provide a set of sqls that I can just use to create a local
(or remote) snapshot?
Thanks,
Andy
The scripts are:
use [T2]
exec sp_replicationdboption @.dbname = N'T2', @.optname = N'publish',
@.value = N'true'
GO
-- Adding the snapshot publication
use [T2]
exec sp_addpublication @.publication = N'T2', @.description = N'Snapshot
publication of
database ''T2'' from Publisher ''DB4''.', @.sync_method = N'native',
@.retention = 0,
@.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous =
N'true',
@.enabled_for_internet = N'false', @.snapshot_in_defaultfolder = N'true',
@.compress_snapshot =
N'false', @.ftp_port = 21, @.ftp_login = N'anonymous',
@.allow_subscription_copy = N'false',
@.add_to_active_directory = N'false', @.repl_freq = N'snapshot', @.status
= N'active',
@.independent_agent = N'true', @.immediate_sync = N'true',
@.allow_sync_tran = N'false',
@.autogen_sync_procs = N'false', @.allow_queued_tran = N'false',
@.allow_dts = N'false',
@.replicate_ddl = 1
GO
exec sp_addpublication_snapshot @.publication = N'T2', @.frequency_type =
1,
@.frequency_interval = 0, @.frequency_relative_interval = 0,
@.frequency_recurrence_factor = 0,
@.frequency_subday = 0, @.frequency_subday_interval = 0,
@.active_start_time_of_day = 0,
@.active_end_time_of_day = 235959, @.active_start_date = 0,
@.active_end_date = 0, @.job_login =
N'db4\dbsnap', @.job_password = N'wenhua', @.publisher_security_mode = 0,
@.publisher_login =
N'sa', @.publisher_password = N'chang5911'
use [T2]
exec sp_addarticle @.publication = N'T2', @.article = N'RETURN_REASON',
@.source_owner =
N'dbo', @.source_object = N'RETURN_REASON', @.type = N'logbased',
@.description = null,
@.creation_script = null, @.pre_creation_cmd = N'drop', @.schema_option =
0x000000000803509D,
@.identityrangemanagementoption = N'manual', @.destination_table =
N'RETURN_REASON',
@.destination_owner = N'dbo', @.vertical_partition = N'false'
GOCan you extract the code from the job and pass it to the snapshot.exe found
in c:\program files\Microsoft SQL Server\90\com?
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
"AH" <hhhsu7a@.yahoo.com> wrote in message
news:1168824792.916342.291270@.11g2000cwr.googlegroups.com...
>I tried to create a local snapshot replication from data A to database
> B for sql 2005. I followed the wizard and it was created successfully.
> But,nothing written to the replication folder and the job failed.
> I manually executed the sqls and it always failed on
> sp_addpublication_snapshot and the error was:
> 'DB4\Administrator' is a member of sysadmin server role and cannot be
> granted to or revoked from the proxy. Members of sysadmin server role
> are allowed to use any proxy.
>
> I log in to windows 2003 as administrator and the replication account
> id dbsnap. What I have to do to avoid the error?
>
> Is there a detailed step-by=step guide to create a snapshot
> replication?
>
> Can someone provide a set of sqls that I can just use to create a local
> (or remote) snapshot?
>
> Thanks,
> Andy
>
> The scripts are:
> use [T2]
> exec sp_replicationdboption @.dbname = N'T2', @.optname = N'publish',
> @.value = N'true'
> GO
> -- Adding the snapshot publication
> use [T2]
> exec sp_addpublication @.publication = N'T2', @.description = N'Snapshot
> publication of
>
> database ''T2'' from Publisher ''DB4''.', @.sync_method = N'native',
> @.retention = 0,
>
> @.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous =
> N'true',
>
> @.enabled_for_internet = N'false', @.snapshot_in_defaultfolder = N'true',
> @.compress_snapshot =
>
> N'false', @.ftp_port = 21, @.ftp_login = N'anonymous',
> @.allow_subscription_copy = N'false',
>
> @.add_to_active_directory = N'false', @.repl_freq = N'snapshot', @.status
> = N'active',
>
> @.independent_agent = N'true', @.immediate_sync = N'true',
> @.allow_sync_tran = N'false',
>
> @.autogen_sync_procs = N'false', @.allow_queued_tran = N'false',
> @.allow_dts = N'false',
>
> @.replicate_ddl = 1
> GO
>
> exec sp_addpublication_snapshot @.publication = N'T2', @.frequency_type =
> 1,
>
> @.frequency_interval = 0, @.frequency_relative_interval = 0,
> @.frequency_recurrence_factor = 0,
>
> @.frequency_subday = 0, @.frequency_subday_interval = 0,
> @.active_start_time_of_day = 0,
>
> @.active_end_time_of_day = 235959, @.active_start_date = 0,
> @.active_end_date = 0, @.job_login =
>
> N'db4\dbsnap', @.job_password = N'wenhua', @.publisher_security_mode = 0,
> @.publisher_login =
>
> N'sa', @.publisher_password = N'chang5911'
>
> use [T2]
> exec sp_addarticle @.publication = N'T2', @.article = N'RETURN_REASON',
> @.source_owner =
>
> N'dbo', @.source_object = N'RETURN_REASON', @.type = N'logbased',
> @.description = null,
>
> @.creation_script = null, @.pre_creation_cmd = N'drop', @.schema_option =
> 0x000000000803509D,
>
> @.identityrangemanagementoption = N'manual', @.destination_table =
> N'RETURN_REASON',
>
> @.destination_owner = N'dbo', @.vertical_partition = N'false'
> GO
>|||Hilary,
I switched to use administor, instead of a windows user, and the
replication went fine. Does the windows user id require to have
administion privilege?
Andy
Hilary Cotter wrote:[vbcol=seagreen]
> Can you extract the code from the job and pass it to the snapshot.exe foun
d
> in c:\program files\Microsoft SQL Server\90\com?
> --
> 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
>
> "AH" <hhhsu7a@.yahoo.com> wrote in message
> news:1168824792.916342.291270@.11g2000cwr.googlegroups.com...

Assertion errors? Error: 3624, Severity: 20, State: 1

I've had a sudden rush of about 22 assertion errors come out in the SQL
Server log, that stopped the active replication dead in it's tracks. Most of
the other clients and processes seemed unaffected but I can't understand the
source of some of these errors...
- This first one has been identified under kb 828337 BUT that doesn't help
me drill down to the source of the problem! It occurred the most - about 19
times in 4 minutes.
SQL Server Assertion: File: <p:\sql\ntdbms\storeng\drs\include\record.inl>,
line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
- This next one is mentioned in kb 324630 BUT I was not running DBCC
INDEXDEFRAG as suggested and the SQL Server is SP 3.
Location: page.cpp:1668
Expression: pageFull == 0
SPID: 6
Process ID: 2228
- This one seems more interesting since I cannot find any suitable
information about it on-line
Location: page.cpp:2610
Expression: spaceNeeded <= spaceContig && spaceNeeded <= space_usable
SPID: 6
Process ID: 2228
Any help to track down potential causes and or silent problems resulting
from this would be most appreciated - I am unwilling to run DBCC CHECKDB on a
database of this size (~100GB). The server has 13GB free disk space as well
so I can't see it being a problem related to anything so simple!
This really looks like a bug. Open a support incident with MS on this one.
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
"Robert" <MSDNNospam209@.nospam.nospam> wrote in message
news:50F9478D-E5AA-473F-B79C-0C42CD4F16DD@.microsoft.com...
> I've had a sudden rush of about 22 assertion errors come out in the SQL
> Server log, that stopped the active replication dead in it's tracks. Most
> of
> the other clients and processes seemed unaffected but I can't understand
> the
> source of some of these errors...
> - This first one has been identified under kb 828337 BUT that doesn't help
> me drill down to the source of the problem! It occurred the most - about
> 19
> times in 4 minutes.
> SQL Server Assertion: File:
> <p:\sql\ntdbms\storeng\drs\include\record.inl>,
> line=1447
> Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
> - This next one is mentioned in kb 324630 BUT I was not running DBCC
> INDEXDEFRAG as suggested and the SQL Server is SP 3.
> Location: page.cpp:1668
> Expression: pageFull == 0
> SPID: 6
> Process ID: 2228
> - This one seems more interesting since I cannot find any suitable
> information about it on-line
> Location: page.cpp:2610
> Expression: spaceNeeded <= spaceContig && spaceNeeded <= space_usable
> SPID: 6
> Process ID: 2228
> Any help to track down potential causes and or silent problems resulting
> from this would be most appreciated - I am unwilling to run DBCC CHECKDB
> on a
> database of this size (~100GB). The server has 13GB free disk space as
> well
> so I can't see it being a problem related to anything so simple!
>
|||Hello Robert,
I understand that you encounter many asssertion errors in SQL logs. I'd
like to confirm if you have SQL 2000 SP4 and latest culmulative patch
installed. There is a related known issue was fixed in SP4
841776FIX: Additional diagnostics have been added to SQL Server 2000 to
detect unreported read operation failures
http://support.microsoft.com/default.aspx?scid=kb;EN-US;841776
Please note this issue is most likely caused by hardware or driver related
issues, you may want to enable trace flag 806 as descirbed in 841776 to see
more detailed errors.
Also, as Hilary mentioned, since the issue is related to internal error, if
you cannot solve the issue by above method, to find out the root cause of
this issue we may need to analyze memory dumps, this work has to be done by
contacting Microsoft Product Support Services. Therefore, we probably will
not be able to resolve the issue through the newsgroups. If the issue is
urgent, I recommend that you open a Support incident with Microsoft Product
Support Services so that a dedicated Support Professional can assist with
this case. If you need any help in this regard, please let me know.
For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp
Please let's know if you ahve any questions or concerns. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Robert,
You may have to run the DBCC CHECKDB afterall..
The KBA Peter has below talks about check that SQL will perform after you
install the fix and turn on the trace flag. And it also appears that you'd
probably take some degree of performance hit with the trace flag
enabled...because every read from the disk will be audited by SQL for
consistency once you turnon the trace flag.
But I'd think if the damage has been done already, in some form or fashion,
you'd want to know about it. You could may be restore the backup of the
database on another server and run CHECKDB there.
Thanks
Emaniel
"Peter Yang [MSFT]" wrote:

> Hello Robert,
> I understand that you encounter many asssertion errors in SQL logs. I'd
> like to confirm if you have SQL 2000 SP4 and latest culmulative patch
> installed. There is a related known issue was fixed in SP4
> 841776FIX: Additional diagnostics have been added to SQL Server 2000 to
> detect unreported read operation failures
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;841776
> Please note this issue is most likely caused by hardware or driver related
> issues, you may want to enable trace flag 806 as descirbed in 841776 to see
> more detailed errors.
> Also, as Hilary mentioned, since the issue is related to internal error, if
> you cannot solve the issue by above method, to find out the root cause of
> this issue we may need to analyze memory dumps, this work has to be done by
> contacting Microsoft Product Support Services. Therefore, we probably will
> not be able to resolve the issue through the newsgroups. If the issue is
> urgent, I recommend that you open a Support incident with Microsoft Product
> Support Services so that a dedicated Support Professional can assist with
> this case. If you need any help in this regard, please let me know.
> For a complete list of Microsoft Product Support Services phone numbers,
> please go to the following address on the World Wide Web:
> http://support.microsoft.com/directory/overview.asp
> Please let's know if you ahve any questions or concerns. Thank you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
>

Assertion errors? Error: 3624, Severity: 20, State: 1

I've had a sudden rush of about 22 assertion errors come out in the SQL
Server log, that stopped the active replication dead in it's tracks. Most of
the other clients and processes seemed unaffected but I can't understand the
source of some of these errors...
- This first one has been identified under kb 828337 BUT that doesn't help
me drill down to the source of the problem! It occurred the most - about 19
times in 4 minutes.
SQL Server Assertion: File: < p:\sql\ntdbms\storeng\drs\include\record
.inl>,
line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
- This next one is mentioned in kb 324630 BUT I was not running DBCC
INDEXDEFRAG as suggested and the SQL Server is SP 3.
Location: page.cpp:1668
Expression: pageFull == 0
SPID: 6
Process ID: 2228
- This one seems more interesting since I cannot find any suitable
information about it on-line
Location: page.cpp:2610
Expression: spaceNeeded <= spaceContig && spaceNeeded <= space_usable
SPID: 6
Process ID: 2228
Any help to track down potential causes and or silent problems resulting
from this would be most appreciated - I am unwilling to run DBCC CHECKDB on
a
database of this size (~100GB). The server has 13GB free disk space as well
so I can't see it being a problem related to anything so simple!This really looks like a bug. Open a support incident with MS on this one.
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
"Robert" <MSDNNospam209@.nospam.nospam> wrote in message
news:50F9478D-E5AA-473F-B79C-0C42CD4F16DD@.microsoft.com...
> I've had a sudden rush of about 22 assertion errors come out in the SQL
> Server log, that stopped the active replication dead in it's tracks. Most
> of
> the other clients and processes seemed unaffected but I can't understand
> the
> source of some of these errors...
> - This first one has been identified under kb 828337 BUT that doesn't help
> me drill down to the source of the problem! It occurred the most - about
> 19
> times in 4 minutes.
> SQL Server Assertion: File:
> < p:\sql\ntdbms\storeng\drs\include\record
.inl>,
> line=1447
> Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
> - This next one is mentioned in kb 324630 BUT I was not running DBCC
> INDEXDEFRAG as suggested and the SQL Server is SP 3.
> Location: page.cpp:1668
> Expression: pageFull == 0
> SPID: 6
> Process ID: 2228
> - This one seems more interesting since I cannot find any suitable
> information about it on-line
> Location: page.cpp:2610
> Expression: spaceNeeded <= spaceContig && spaceNeeded <= space_usable
> SPID: 6
> Process ID: 2228
> Any help to track down potential causes and or silent problems resulting
> from this would be most appreciated - I am unwilling to run DBCC CHECKDB
> on a
> database of this size (~100GB). The server has 13GB free disk space as
> well
> so I can't see it being a problem related to anything so simple!
>

Assertion errors? Error: 3624, Severity: 20, State: 1

I've had a sudden rush of about 22 assertion errors come out in the SQL
Server log, that stopped the active replication dead in it's tracks. Most of
the other clients and processes seemed unaffected but I can't understand the
source of some of these errors...
- This first one has been identified under kb 828337 BUT that doesn't help
me drill down to the source of the problem! It occurred the most - about 19
times in 4 minutes.
SQL Server Assertion: File: <p:\sql\ntdbms\storeng\drs\include\record.inl>,
line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
- This next one is mentioned in kb 324630 BUT I was not running DBCC
INDEXDEFRAG as suggested and the SQL Server is SP 3.
Location: page.cpp:1668
Expression: pageFull == 0
SPID: 6
Process ID: 2228
- This one seems more interesting since I cannot find any suitable
information about it on-line
Location: page.cpp:2610
Expression: spaceNeeded <= spaceContig && spaceNeeded <= space_usable
SPID: 6
Process ID: 2228
Any help to track down potential causes and or silent problems resulting
from this would be most appreciated - I am unwilling to run DBCC CHECKDB on a
database of this size (~100GB). The server has 13GB free disk space as well
so I can't see it being a problem related to anything so simple!
This really looks like a bug. Open a support incident with MS on this one.
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
"Robert" <MSDNNospam209@.nospam.nospam> wrote in message
news:50F9478D-E5AA-473F-B79C-0C42CD4F16DD@.microsoft.com...
> I've had a sudden rush of about 22 assertion errors come out in the SQL
> Server log, that stopped the active replication dead in it's tracks. Most
> of
> the other clients and processes seemed unaffected but I can't understand
> the
> source of some of these errors...
> - This first one has been identified under kb 828337 BUT that doesn't help
> me drill down to the source of the problem! It occurred the most - about
> 19
> times in 4 minutes.
> SQL Server Assertion: File:
> <p:\sql\ntdbms\storeng\drs\include\record.inl>,
> line=1447
> Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
> - This next one is mentioned in kb 324630 BUT I was not running DBCC
> INDEXDEFRAG as suggested and the SQL Server is SP 3.
> Location: page.cpp:1668
> Expression: pageFull == 0
> SPID: 6
> Process ID: 2228
> - This one seems more interesting since I cannot find any suitable
> information about it on-line
> Location: page.cpp:2610
> Expression: spaceNeeded <= spaceContig && spaceNeeded <= space_usable
> SPID: 6
> Process ID: 2228
> Any help to track down potential causes and or silent problems resulting
> from this would be most appreciated - I am unwilling to run DBCC CHECKDB
> on a
> database of this size (~100GB). The server has 13GB free disk space as
> well
> so I can't see it being a problem related to anything so simple!
>