How to reinitialize a table in transactional publication.

I have set-up replication using transactional publication and I have
configured subscription to be near continuous.
I have this issue. When setting up subscription I have specified that the
subscription database already had data. So when the snapshot agent is
initialized it does not recreate the tables on the subscription database.
This is ok. But, now if I was to re-initialize only one of the tables, how
do I achieve this without breaking replication.
-Nags
You can't really do this.
If you start with a no-sync, you can't re-initialize. Only subscriptions
that were set for automatic synchronization can be re-initialized.
What I do in situations like this is to drop the article, and then recreate
it in a new publication.
"Nags" <nags@.DontSpamMe.com> wrote in message
news:eQMRViYIEHA.828@.TK2MSFTNGP10.phx.gbl...
> I have set-up replication using transactional publication and I have
> configured subscription to be near continuous.
> I have this issue. When setting up subscription I have specified that the
> subscription database already had data. So when the snapshot agent is
> initialized it does not recreate the tables on the subscription database.
> This is ok. But, now if I was to re-initialize only one of the tables,
how
> do I achieve this without breaking replication.
> -Nags
>
|||Can we drop an article once the tables are published and also subscribed ?
I thought that once there is a subscription on a publication (at least in
transactional publication) we cannot drop the article.
-Nags
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:OIi$glcIEHA.3528@.TK2MSFTNGP09.phx.gbl...
> You can't really do this.
> If you start with a no-sync, you can't re-initialize. Only subscriptions
> that were set for automatic synchronization can be re-initialized.
> What I do in situations like this is to drop the article, and then
recreate[vbcol=seagreen]
> it in a new publication.
>
> "Nags" <nags@.DontSpamMe.com> wrote in message
> news:eQMRViYIEHA.828@.TK2MSFTNGP10.phx.gbl...
the[vbcol=seagreen]
database.
> how
>
|||You will have to drop the subscription for both automatic and nosync
subscriptions, then you can drop the article.
"Nags" <nags@.DontSpamMe.com> wrote in message
news:uSGXCLhIEHA.3440@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Can we drop an article once the tables are published and also subscribed ?
> I thought that once there is a subscription on a publication (at least in
> transactional publication) we cannot drop the article.
> -Nags
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:OIi$glcIEHA.3528@.TK2MSFTNGP09.phx.gbl...
> recreate
> the
> database.
tables,
>
|||But, I do not want to break replication
-Nags
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:eg$wjYkIEHA.3536@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> You will have to drop the subscription for both automatic and nosync
> subscriptions, then you can drop the article.
> "Nags" <nags@.DontSpamMe.com> wrote in message
> news:uSGXCLhIEHA.3440@.TK2MSFTNGP09.phx.gbl...
?[vbcol=seagreen]
in[vbcol=seagreen]
subscriptions[vbcol=seagreen]
that[vbcol=seagreen]
is
> tables,
>
|||Nags wrote:
> I have set-up replication using transactional publication and I have
> configured subscription to be near continuous.
> I have this issue. When setting up subscription I have specified that the
> subscription database already had data. So when the snapshot agent is
> initialized it does not recreate the tables on the subscription database.
> This is ok. But, now if I was to re-initialize only one of the tables, how
> do I achieve this without breaking replication.
> -Nags
>
You are not very well..
However, there are several tricks you can try:
a) you can afford having no transaction on the source table for a
certain time
=> put a trigger for insert / update / delete on the table which
always raise error and rollback
=> wait the log reader agent and the distribution agent have purged
any pending transaction on this table
=> bulk copy your table from publisher to subscriber
=> remove the trigger
b) you can also create a new publication on the table ( I never tried to
publish twice a table on the same database, but you can always create a
"mirror" table on the publishing database that you keep in sync with
triggers )
On the subscriber, subscribe to this new publication and wait for
synchronisation
Now, here is the trick:
On the publisher you have articles A and A2 which you know are in sync
On the subscriber you have tables Arep and A2rep and you know that A2rep
is in sync
So, stop the log reader agent.
Wait long enough so that the distributing agents tell "no more transactions"
Copy A2rep into Arep
restart log reader agent.
Now Arep is sync'ed and you can drop all the A2 stuff.
If you are not familiar with these operations, you might make a
rehearsal on a test server before trying your production server!

How to reduce the log file ?

Hi,
I found that there is a setting to set the maximum log file, however,
I haven't enable it. Therefore, current log file is 1G. How can I
reduce it to 500MB so that I can set the max. log file to 500MB.
Happy new year to all
Ivan
GinolaIn the future you might post something like this to the
public.SQLserver.server newsgroups. To reduce the log you have two options,
(1) if you do not require point in time recovery you can issue the command
"backup log dbname with truncate_only" Then issue a dbcc shrinkfile command
to shrink the log (see BOL for full syntax). Also, if you do not require
point in time recovery you might set your recovery mode to simple to avoid
the log from growing.
If you do require point in time recovery, setup regular log backups and
don't shrink your logfile unless you have to for space reasons. Having empty
space in the log file is better than having SQL Server reallocate more disk
space to the log during the production day.
HTH
Ray Higdon MCSE, MCDBA, CCNA
--
"Ginola" <ginola@.fake.email.com> wrote in message
news:3ff3933f.24975482@.msnews.microsoft.com...

quote:

---

> Hi,
> I found that there is a setting to set the maximum log file, however,
> I haven't enable it. Therefore, current log file is 1G. How can I
> reduce it to 500MB so that I can set the max. log file to 500MB.
> Happy new year to all
> Ivan
> Ginola

---

|||To shrink the transaction log, you merely need to back it up and execute
dbcc shrinkfile.
If you don't need the backup, do
backup tran <database_name> with no_log
go
dbcc shrinkfile (<database_name>,target size)
To shrink as far as possible, set target_size to 0.
For SQL 7, it is dump tran instead of backup tran and dbcc shrinkfile
(<database_name> )
Whatever you do, don't delete the transaction log. You were lucky that
there were no transactions running or recovering at the time, otherwise, you
would have wound up with a suspect database.
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************
"Ginola" <ginola@.fake.email.com> wrote in message
news:3ff3933f.24975482@.msnews.microsoft.com...

quote:

---

> Hi,
> I found that there is a setting to set the maximum log file, however,
> I haven't enable it. Therefore, current log file is 1G. How can I
> reduce it to 500MB so that I can set the max. log file to 500MB.
> Happy new year to all
> Ivan
> Ginola

---

|||thanks for replying
On Fri, 2 Jan 2004 09:58:56 -0500, "Andy Svendsen"
<andymcdba1@.NOMORESPAM.yahoo.com> wrote:

quote:

---

>To shrink the transaction log, you merely need to back it up and execute
>dbcc shrinkfile.
>If you don't need the backup, do
>backup tran <database_name> with no_log
>go
>dbcc shrinkfile (<database_name>,target size)
>To shrink as far as possible, set target_size to 0.
>For SQL 7, it is dump tran instead of backup tran and dbcc shrinkfile
>(<database_name> )
>Whatever you do, don't delete the transaction log. You were lucky that
>there were no transactions running or recovering at the time, otherwise, yo
u
>would have wound up with a suspect database.

---

Ginola|||I am a beginner in SQL server and I am using SQL server 2000
where should I type
backup tran <database_name> with no_log
go
dbcc shrinkfile (<database_name>,target size)
To shrink the transaction log.
I know how to shrink the database ( because it has the option to
option when right click, but not the log file )
On Fri, 2 Jan 2004 09:58:56 -0500, "Andy Svendsen"
<andymcdba1@.NOMORESPAM.yahoo.com> wrote:

quote:

---

>To shrink the transaction log, you merely need to back it up and execute
>dbcc shrinkfile.
>If you don't need the backup, do
>backup tran <database_name> with no_log
>go
>dbcc shrinkfile (<database_name>,target size)
>To shrink as far as possible, set target_size to 0.
>For SQL 7, it is dump tran instead of backup tran and dbcc shrinkfile
>(<database_name> )
>Whatever you do, don't delete the transaction log. You were lucky that
>there were no transactions running or recovering at the time, otherwise, yo
u
>would have wound up with a suspect database.

---

Ginola|||Go to start/programs - microsoft SQL Server - Query Analyzer. You will
need to make a connection to the SQL Server you need to issue the
commands on.
HTH
Ray Higdon MCSE, MCDBA, CCNA
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||thanks, got it
On Sun, 04 Jan 2004 18:10:24 -0800, Ray Higdon
<rayhigdon@.higdonconsulting.com> wrote:

quote:

---

>Go to start/programs - microsoft SQL Server - Query Analyzer. You will
>need to make a connection to the SQL Server you need to issue the
>commands on.
>HTH
>Ray Higdon MCSE, MCDBA, CCNA
>*** Sent via Developersdex http://www.examnotes.net ***
>Don't just participate in USENET...get rewarded for it!

---

Ginola|||Hi,
I have tried
backup tran <database_name> with no_log
go
dbcc shrinkfile (<database_name>,target size)
but the error message display
"Could not locate file 'sqlBackup' in sysfiles.
DBCC execution completeted. If DBCC printed error message, contact
your system administrator"
sqlBackup is the database name,
When I delete the line "dbcc shrinkfile ... " it works fine. However,
when I apply the third line, the error message display.
Any clue how to solve the problem.
thanks

quote:

---

>To shrink the transaction log, you merely need to back it up and execute
>dbcc shrinkfile.
>If you don't need the backup, do
>backup tran <database_name> with no_log
>go
>dbcc shrinkfile (<database_name>,target size)
>To shrink as far as possible, set target_size to 0.
>For SQL 7, it is dump tran instead of backup tran and dbcc shrinkfile
>(<database_name> )
>Whatever you do, don't delete the transaction log. You were lucky that
>there were no transactions running or recovering at the time, otherwise, yo
u
>would have wound up with a suspect database.

---

Ginola|||You need to specify the actual name of the log file for that operation
once you are in the database, try this:
backup log sqlbackup with no_log
go
use sqlbackup
go
dbcc shrinkfile (logical name of your log file,0)
You can find the logical name by right-clicking your database in EM and
clicking the log tab and using the logical name (not the physical MDF
name)
HTH
Ray Higdon MCSE, MCDBA, CCNA
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!

How to read not for replication setting from meta info

Hello!
I am trying to read the not for replication setting of an identity
column from a Sql Server 7.0 database but I cannot find that piece of
information.
I check the information_schema tables that look approriate and the
sp_pkeys procedure. But no luck.
Has anyone an idea?
Thanks for you help. Take care, Peter.Hello again,
Sorry but I just found the solution: sp_help returns it.
Thanks, Peter.