My disk space is full on my server. My SQL Server modellog.ldf and
modellog$4IDR are both over 8 GB each and growing exponentially. Can I
delete these files safely?Hi
You would have to find out why these are changin! Normally you would not
touch the model database at all. You may want to swap in a model database
from an instance that has the same collation. Check out
http://support.microsoft.com/kb/224071/
John
<heebe@.hotmail.com> wrote in message
news:1154872503.159164.104260@.h48g2000cwc.googlegroups.com...
> My disk space is full on my server. My SQL Server modellog.ldf and
> modellog$4IDR are both over 8 GB each and growing exponentially. Can I
> delete these files safely?
>|||Thanks for the message. I tried shrinking the database, but no luck.
The tables internally look uneventful but the database says its size is
8 GB. SQL Server won't allow the detach of the Model database. Any
thoughts?
John Bell wrote:
> Hi
> You would have to find out why these are changin! Normally you would not
> touch the model database at all. You may want to swap in a model database
> from an instance that has the same collation. Check out
> http://support.microsoft.com/kb/224071/
> John
> <heebe@.hotmail.com> wrote in message
> news:1154872503.159164.104260@.h48g2000cwc.googlegroups.com...
> > My disk space is full on my server. My SQL Server modellog.ldf and
> > modellog$4IDR are both over 8 GB each and growing exponentially. Can I
> > delete these files safely?
> >|||Try setting the recovery model to SIMPLE and shrinking the file:
ALTER DATABASE model SET RECOVERY SIMPLE
GO
USE model
GO
DBCC SHRINKFILE('modellog', 2)
GO
It is very unusual that the model database data or log files would grow at
all. The database is normally used only as a template for creating new
databases. Make sure you don't have any renegade applications using the
database.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<heebe@.hotmail.com> wrote in message
news:1154888521.842024.194280@.i42g2000cwa.googlegroups.com...
> Thanks for the message. I tried shrinking the database, but no luck.
> The tables internally look uneventful but the database says its size is
> 8 GB. SQL Server won't allow the detach of the Model database. Any
> thoughts?
>
> John Bell wrote:
>> Hi
>> You would have to find out why these are changin! Normally you would not
>> touch the model database at all. You may want to swap in a model database
>> from an instance that has the same collation. Check out
>> http://support.microsoft.com/kb/224071/
>> John
>> <heebe@.hotmail.com> wrote in message
>> news:1154872503.159164.104260@.h48g2000cwc.googlegroups.com...
>> > My disk space is full on my server. My SQL Server modellog.ldf and
>> > modellog$4IDR are both over 8 GB each and growing exponentially. Can I
>> > delete these files safely?
>> >
>|||Thanks. Tried that. I even looked each of the tables in model and
nothing has more than 300 records. Nonetheless, it is still at 8 GB.
I ran a spyware check, nothing.
Dan Guzman wrote:
> Try setting the recovery model to SIMPLE and shrinking the file:
> ALTER DATABASE model SET RECOVERY SIMPLE
> GO
> USE model
> GO
> DBCC SHRINKFILE('modellog', 2)
> GO
> It is very unusual that the model database data or log files would grow at
> all. The database is normally used only as a template for creating new
> databases. Make sure you don't have any renegade applications using the
> database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <heebe@.hotmail.com> wrote in message
> news:1154888521.842024.194280@.i42g2000cwa.googlegroups.com...
> > Thanks for the message. I tried shrinking the database, but no luck.
> > The tables internally look uneventful but the database says its size is
> > 8 GB. SQL Server won't allow the detach of the Model database. Any
> > thoughts?
> >
> >
> > John Bell wrote:
> >> Hi
> >>
> >> You would have to find out why these are changin! Normally you would not
> >> touch the model database at all. You may want to swap in a model database
> >> from an instance that has the same collation. Check out
> >> http://support.microsoft.com/kb/224071/
> >>
> >> John
> >>
> >> <heebe@.hotmail.com> wrote in message
> >> news:1154872503.159164.104260@.h48g2000cwc.googlegroups.com...
> >> > My disk space is full on my server. My SQL Server modellog.ldf and
> >> > modellog$4IDR are both over 8 GB each and growing exponentially. Can I
> >> > delete these files safely?
> >> >
> >|||So DBCC SHRINKFILE didn't reduce the file size? Strange...what messages
were returned?
--
Hope this helps.
Dan Guzman
SQL Server MVP
<heebe@.hotmail.com> wrote in message
news:1154917305.948015.219490@.m79g2000cwm.googlegroups.com...
> Thanks. Tried that. I even looked each of the tables in model and
> nothing has more than 300 records. Nonetheless, it is still at 8 GB.
> I ran a spyware check, nothing.
>
> Dan Guzman wrote:
>> Try setting the recovery model to SIMPLE and shrinking the file:
>> ALTER DATABASE model SET RECOVERY SIMPLE
>> GO
>> USE model
>> GO
>> DBCC SHRINKFILE('modellog', 2)
>> GO
>> It is very unusual that the model database data or log files would grow
>> at
>> all. The database is normally used only as a template for creating new
>> databases. Make sure you don't have any renegade applications using the
>> database.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> <heebe@.hotmail.com> wrote in message
>> news:1154888521.842024.194280@.i42g2000cwa.googlegroups.com...
>> > Thanks for the message. I tried shrinking the database, but no luck.
>> > The tables internally look uneventful but the database says its size is
>> > 8 GB. SQL Server won't allow the detach of the Model database. Any
>> > thoughts?
>> >
>> >
>> > John Bell wrote:
>> >> Hi
>> >>
>> >> You would have to find out why these are changin! Normally you would
>> >> not
>> >> touch the model database at all. You may want to swap in a model
>> >> database
>> >> from an instance that has the same collation. Check out
>> >> http://support.microsoft.com/kb/224071/
>> >>
>> >> John
>> >>
>> >> <heebe@.hotmail.com> wrote in message
>> >> news:1154872503.159164.104260@.h48g2000cwc.googlegroups.com...
>> >> > My disk space is full on my server. My SQL Server modellog.ldf and
>> >> > modellog$4IDR are both over 8 GB each and growing exponentially.
>> >> > Can I
>> >> > delete these files safely?
>> >> >
>> >
>|||You may need to back up the transaction log before shrinking.
Then backup log again with the truncate only option i.e
BACKUP LOG MODEL WITH TRUNCATE_ONLY
The try the DBCC Shrinkfile command again.
<heebe@.hotmail.com> wrote in message
news:1154917305.948015.219490@.m79g2000cwm.googlegroups.com...
> Thanks. Tried that. I even looked each of the tables in model and
> nothing has more than 300 records. Nonetheless, it is still at 8 GB.
> I ran a spyware check, nothing.
>
> Dan Guzman wrote:
>> Try setting the recovery model to SIMPLE and shrinking the file:
>> ALTER DATABASE model SET RECOVERY SIMPLE
>> GO
>> USE model
>> GO
>> DBCC SHRINKFILE('modellog', 2)
>> GO
>> It is very unusual that the model database data or log files would grow
>> at
>> all. The database is normally used only as a template for creating new
>> databases. Make sure you don't have any renegade applications using the
>> database.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> <heebe@.hotmail.com> wrote in message
>> news:1154888521.842024.194280@.i42g2000cwa.googlegroups.com...
>> > Thanks for the message. I tried shrinking the database, but no luck.
>> > The tables internally look uneventful but the database says its size is
>> > 8 GB. SQL Server won't allow the detach of the Model database. Any
>> > thoughts?
>> >
>> >
>> > John Bell wrote:
>> >> Hi
>> >>
>> >> You would have to find out why these are changin! Normally you would
>> >> not
>> >> touch the model database at all. You may want to swap in a model
>> >> database
>> >> from an instance that has the same collation. Check out
>> >> http://support.microsoft.com/kb/224071/
>> >>
>> >> John
>> >>
>> >> <heebe@.hotmail.com> wrote in message
>> >> news:1154872503.159164.104260@.h48g2000cwc.googlegroups.com...
>> >> > My disk space is full on my server. My SQL Server modellog.ldf and
>> >> > modellog$4IDR are both over 8 GB each and growing exponentially.
>> >> > Can I
>> >> > delete these files safely?
>> >> >
>> >
>|||Hi
You may want to check that these files are actually being used by the
model database also check for currently open transactions:
USE MODEL
GO
EXEC sp_helpfile
GO
DBCC OPENTRAN ( 'Model' )
GO
John
heebe@.hotmail.com wrote:
> Thanks. Tried that. I even looked each of the tables in model and
> nothing has more than 300 records. Nonetheless, it is still at 8 GB.
> I ran a spyware check, nothing.
>
> Dan Guzman wrote:
> > Try setting the recovery model to SIMPLE and shrinking the file:
> >
> > ALTER DATABASE model SET RECOVERY SIMPLE
> > GO
> > USE model
> > GO
> > DBCC SHRINKFILE('modellog', 2)
> > GO
> >
> > It is very unusual that the model database data or log files would grow at
> > all. The database is normally used only as a template for creating new
> > databases. Make sure you don't have any renegade applications using the
> > database.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > <heebe@.hotmail.com> wrote in message
> > news:1154888521.842024.194280@.i42g2000cwa.googlegroups.com...
> > > Thanks for the message. I tried shrinking the database, but no luck.
> > > The tables internally look uneventful but the database says its size is
> > > 8 GB. SQL Server won't allow the detach of the Model database. Any
> > > thoughts?
> > >
> > >
> > > John Bell wrote:
> > >> Hi
> > >>
> > >> You would have to find out why these are changin! Normally you would not
> > >> touch the model database at all. You may want to swap in a model database
> > >> from an instance that has the same collation. Check out
> > >> http://support.microsoft.com/kb/224071/
> > >>
> > >> John
> > >>
> > >> <heebe@.hotmail.com> wrote in message
> > >> news:1154872503.159164.104260@.h48g2000cwc.googlegroups.com...
> > >> > My disk space is full on my server. My SQL Server modellog.ldf and
> > >> > modellog$4IDR are both over 8 GB each and growing exponentially. Can I
> > >> > delete these files safely?
> > >> >
> > >
Showing posts with label delete. Show all posts
Showing posts with label delete. Show all posts
Monday, March 26, 2012
Model Database Log Huge - Can I delete?
My disk space is full on my server. My SQL Server modellog.ldf and
modellog$4IDR are both over 8 GB each and growing exponentially. Can I
delete these files safely?Hi
You would have to find out why these are changin! Normally you would not
touch the model database at all. You may want to swap in a model database
from an instance that has the same collation. Check out
http://support.microsoft.com/kb/224071/
John
<heebe@.hotmail.com> wrote in message
news:1154872503.159164.104260@.h48g2000cwc.googlegroups.com...
> My disk space is full on my server. My SQL Server modellog.ldf and
> modellog$4IDR are both over 8 GB each and growing exponentially. Can I
> delete these files safely?
>|||Thanks for the message. I tried shrinking the database, but no luck.
The tables internally look uneventful but the database says its size is
8 GB. SQL Server won't allow the detach of the Model database. Any
thoughts?
John Bell wrote:[vbcol=seagreen]
> Hi
> You would have to find out why these are changin! Normally you would not
> touch the model database at all. You may want to swap in a model database
> from an instance that has the same collation. Check out
> http://support.microsoft.com/kb/224071/
> John
> <heebe@.hotmail.com> wrote in message
> news:1154872503.159164.104260@.h48g2000cwc.googlegroups.com...|||Try setting the recovery model to SIMPLE and shrinking the file:
ALTER DATABASE model SET RECOVERY SIMPLE
GO
USE model
GO
DBCC SHRINKFILE('modellog', 2)
GO
It is very unusual that the model database data or log files would grow at
all. The database is normally used only as a template for creating new
databases. Make sure you don't have any renegade applications using the
database.
Hope this helps.
Dan Guzman
SQL Server MVP
<heebe@.hotmail.com> wrote in message
news:1154888521.842024.194280@.i42g2000cwa.googlegroups.com...
> Thanks for the message. I tried shrinking the database, but no luck.
> The tables internally look uneventful but the database says its size is
> 8 GB. SQL Server won't allow the detach of the Model database. Any
> thoughts?
>
> John Bell wrote:
>|||Thanks. Tried that. I even looked each of the tables in model and
nothing has more than 300 records. Nonetheless, it is still at 8 GB.
I ran a spyware check, nothing.
Dan Guzman wrote:[vbcol=seagreen]
> Try setting the recovery model to SIMPLE and shrinking the file:
> ALTER DATABASE model SET RECOVERY SIMPLE
> GO
> USE model
> GO
> DBCC SHRINKFILE('modellog', 2)
> GO
> It is very unusual that the model database data or log files would grow at
> all. The database is normally used only as a template for creating new
> databases. Make sure you don't have any renegade applications using the
> database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <heebe@.hotmail.com> wrote in message
> news:1154888521.842024.194280@.i42g2000cwa.googlegroups.com...|||So DBCC SHRINKFILE didn't reduce the file size? Strange...what messages
were returned?
Hope this helps.
Dan Guzman
SQL Server MVP
<heebe@.hotmail.com> wrote in message
news:1154917305.948015.219490@.m79g2000cwm.googlegroups.com...
> Thanks. Tried that. I even looked each of the tables in model and
> nothing has more than 300 records. Nonetheless, it is still at 8 GB.
> I ran a spyware check, nothing.
>
> Dan Guzman wrote:
>|||You may need to back up the transaction log before shrinking.
Then backup log again with the truncate only option i.e
BACKUP LOG MODEL WITH TRUNCATE_ONLY
The try the DBCC Shrinkfile command again.
<heebe@.hotmail.com> wrote in message
news:1154917305.948015.219490@.m79g2000cwm.googlegroups.com...
> Thanks. Tried that. I even looked each of the tables in model and
> nothing has more than 300 records. Nonetheless, it is still at 8 GB.
> I ran a spyware check, nothing.
>
> Dan Guzman wrote:
>|||Hi
You may want to check that these files are actually being used by the
model database also check for currently open transactions:
USE MODEL
GO
EXEC sp_helpfile
GO
DBCC OPENTRAN ( 'Model' )
GO
John
heebe@.hotmail.com wrote:[vbcol=seagreen]
> Thanks. Tried that. I even looked each of the tables in model and
> nothing has more than 300 records. Nonetheless, it is still at 8 GB.
> I ran a spyware check, nothing.
>
> Dan Guzman wrote:sql
modellog$4IDR are both over 8 GB each and growing exponentially. Can I
delete these files safely?Hi
You would have to find out why these are changin! Normally you would not
touch the model database at all. You may want to swap in a model database
from an instance that has the same collation. Check out
http://support.microsoft.com/kb/224071/
John
<heebe@.hotmail.com> wrote in message
news:1154872503.159164.104260@.h48g2000cwc.googlegroups.com...
> My disk space is full on my server. My SQL Server modellog.ldf and
> modellog$4IDR are both over 8 GB each and growing exponentially. Can I
> delete these files safely?
>|||Thanks for the message. I tried shrinking the database, but no luck.
The tables internally look uneventful but the database says its size is
8 GB. SQL Server won't allow the detach of the Model database. Any
thoughts?
John Bell wrote:[vbcol=seagreen]
> Hi
> You would have to find out why these are changin! Normally you would not
> touch the model database at all. You may want to swap in a model database
> from an instance that has the same collation. Check out
> http://support.microsoft.com/kb/224071/
> John
> <heebe@.hotmail.com> wrote in message
> news:1154872503.159164.104260@.h48g2000cwc.googlegroups.com...|||Try setting the recovery model to SIMPLE and shrinking the file:
ALTER DATABASE model SET RECOVERY SIMPLE
GO
USE model
GO
DBCC SHRINKFILE('modellog', 2)
GO
It is very unusual that the model database data or log files would grow at
all. The database is normally used only as a template for creating new
databases. Make sure you don't have any renegade applications using the
database.
Hope this helps.
Dan Guzman
SQL Server MVP
<heebe@.hotmail.com> wrote in message
news:1154888521.842024.194280@.i42g2000cwa.googlegroups.com...
> Thanks for the message. I tried shrinking the database, but no luck.
> The tables internally look uneventful but the database says its size is
> 8 GB. SQL Server won't allow the detach of the Model database. Any
> thoughts?
>
> John Bell wrote:
>|||Thanks. Tried that. I even looked each of the tables in model and
nothing has more than 300 records. Nonetheless, it is still at 8 GB.
I ran a spyware check, nothing.
Dan Guzman wrote:[vbcol=seagreen]
> Try setting the recovery model to SIMPLE and shrinking the file:
> ALTER DATABASE model SET RECOVERY SIMPLE
> GO
> USE model
> GO
> DBCC SHRINKFILE('modellog', 2)
> GO
> It is very unusual that the model database data or log files would grow at
> all. The database is normally used only as a template for creating new
> databases. Make sure you don't have any renegade applications using the
> database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <heebe@.hotmail.com> wrote in message
> news:1154888521.842024.194280@.i42g2000cwa.googlegroups.com...|||So DBCC SHRINKFILE didn't reduce the file size? Strange...what messages
were returned?
Hope this helps.
Dan Guzman
SQL Server MVP
<heebe@.hotmail.com> wrote in message
news:1154917305.948015.219490@.m79g2000cwm.googlegroups.com...
> Thanks. Tried that. I even looked each of the tables in model and
> nothing has more than 300 records. Nonetheless, it is still at 8 GB.
> I ran a spyware check, nothing.
>
> Dan Guzman wrote:
>|||You may need to back up the transaction log before shrinking.
Then backup log again with the truncate only option i.e
BACKUP LOG MODEL WITH TRUNCATE_ONLY
The try the DBCC Shrinkfile command again.
<heebe@.hotmail.com> wrote in message
news:1154917305.948015.219490@.m79g2000cwm.googlegroups.com...
> Thanks. Tried that. I even looked each of the tables in model and
> nothing has more than 300 records. Nonetheless, it is still at 8 GB.
> I ran a spyware check, nothing.
>
> Dan Guzman wrote:
>|||Hi
You may want to check that these files are actually being used by the
model database also check for currently open transactions:
USE MODEL
GO
EXEC sp_helpfile
GO
DBCC OPENTRAN ( 'Model' )
GO
John
heebe@.hotmail.com wrote:[vbcol=seagreen]
> Thanks. Tried that. I even looked each of the tables in model and
> nothing has more than 300 records. Nonetheless, it is still at 8 GB.
> I ran a spyware check, nothing.
>
> Dan Guzman wrote:sql
Monday, March 12, 2012
Missing Transactions - REPOSTED.
Larry,
How about if you add a row manually to S2, does it work
then? If it does, then delete this row, run the merge
agent then add the same row on S1 and see if it
propagates through. Also, check that when the
transactional replication puts the record onto S2 is
there a resulting corresponding record in
msmerge_contents on s2? Finally please check the conflict
tables to confirm there's nothing there.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Paul,
Thanks for you response. To answer your questions....
I reinitialized the data to make sure everything was in sync. Then I
inserted a row into S2 and ran the merge agent and the row appeared on the
remote server. I delete the record from S2 and it was deleted off the remote
server.
Next I inserted a row, for the same remote location in to S1, ran the agents
(trans on S1 & merge on the remote location and the row appeared. The delete
worked fine also.
Next I ran my app that inserts data in to S1. A total of 263 rows were
inserted into 2 of the tables. I ran the trans agent on S1 and all the new
records were replicated to S2. Next I ran the merge agent on the remote
location and nothing.
I checked msmerge_contents before and after my app ran and it had the same
number of rows.
Finally, I repeated the insert as I did in the first paragraph and nothing.
As I was typing this message, I checked something. The remote locations did
not have merge replication enabled for the dB that was the subscriber. I
enabled them then delete the data from paragraph 3 (from above) and reran my
application and nothing.
I checked the conflict tables on the publisher (S2) and they were empty. Not
sure if they are suppose to exist, but there are no conflict tables on the
subscriber.
To make things even stranger, the changes being made on the remote
location/tables are making their way back to S2
TIA,
Larry...
|||Larry,
somehow, your app is able to force the replication stored procedures to run
on the subscriber without firing the triggers. This shouldn't be possible!
I'd run profiler on the transactional subscriber to see how this is
occurring and to confirm that the merge triggers aren't firing.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
I am at such a loss. The manual insertions are not working now either.
This is what is strange...the updates that are being done at remote
locations are making it back to S2, but nothing is making it from S2 to the
remote locations. In fact, I have having to reinitialize the data daily and
the inserts into S2 are not being sent to the remote locations unless the
snapshot agent is run.
I know in trans repl, there is a log reader that gets the pending
transactions. What does this in merge? Could this be the problem?
I have disabled replication for this dB and deleted all the publications. I
recreated one and tried it as a push and a pull subscription and no luck.
HELP!!!!
Larry...
|||Larry,
please script out your publications/subscriptions and
post them up here. Also, please confirm that you are
using the same service pack (sp3/a) on all 3 computers
involved.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||The scrips below are from servers all running 8.00.534 (SP2)
~~~~
Transactional Publication & Subscription from S1 to S2
~~~~
-- Enabling the replication database
use master
GO
exec sp_replicationdboption @.dbname = N'EDI_On_RouseMain', @.optname =
N'publish', @.value = N'true'
GO
use [EDI_On_RouseMain]
GO
-- Adding the transactional publication
exec sp_addpublication @.publication = N'EDI_On_RouseMain', @.restricted =
N'false', @.sync_method = N'native', @.repl_freq = N'continuous', @.description
= N'Transactional publication of EDI_On_RouseMain database from Publisher
ROUSEMAIN.', @.status = N'active', @.allow_push = N'true', @.allow_pull =
N'true', @.allow_anonymous = N'true', @.enabled_for_internet = N'false',
@.independent_agent = N'true', @.immediate_sync = N'true', @.allow_sync_tran =
N'false', @.autogen_sync_procs = N'false', @.retention = 336,
@.allow_queued_tran = N'false', @.snapshot_in_defaultfolder = N'true',
@.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login = N'anonymous',
@.allow_dts = N'false', @.allow_subscription_copy = N'false',
@.add_to_active_directory = N'false', @.logreader_job_name =
N'ROUSEMAIN-EDI_On_RouseMain-2'
exec sp_addpublication_snapshot @.publication =
N'EDI_On_RouseMain',@.frequency_type = 4, @.frequency_interval = 1,
@.frequency_relative_interval = 1, @.frequency_recurrence_factor = 0,
@.frequency_subday = 8, @.frequency_subday_interval = 1, @.active_start_date =
0, @.active_end_date = 0, @.active_start_time_of_day = 0,
@.active_end_time_of_day = 235959, @.snapshot_job_name =
N'ROUSEMAIN-EDI_On_RouseMain-EDI_On_RouseMain-2'
GO
exec sp_grant_publication_access @.publication = N'EDI_On_RouseMain', @.login
= N'BUILTIN\Administrators'
GO
exec sp_grant_publication_access @.publication = N'EDI_On_RouseMain', @.login
= N'distributor_admin'
GO
exec sp_grant_publication_access @.publication = N'EDI_On_RouseMain', @.login
= N'EDI'
GO
exec sp_grant_publication_access @.publication = N'EDI_On_RouseMain', @.login
= N'sa'
GO
-- Adding the transactional articles
exec sp_addarticle @.publication = N'EDI_On_RouseMain', @.article =
N'tbl880Detail', @.source_owner = N'dbo', @.source_object = N'tbl880Detail',
@.destination_table = N'tbl880Detail', @.type = N'logbased', @.creation_script =
null, @.description = null, @.pre_creation_cmd = N'drop', @.schema_option =
0x00000000000000F3, @.status = 16, @.vertical_partition = N'false', @.ins_cmd =
N'CALL sp_MSins_tbl880Detail', @.del_cmd = N'CALL sp_MSdel_tbl880Detail',
@.upd_cmd = N'MCALL sp_MSupd_tbl880Detail', @.filter = null, @.sync_object =
null, @.auto_identity_range = N'false'
GO
exec sp_addarticle @.publication = N'EDI_On_RouseMain', @.article =
N'tbl880DetailAllow', @.source_owner = N'dbo', @.source_object =
N'tbl880DetailAllow', @.destination_table = N'tbl880DetailAllow', @.type =
N'logbased', @.creation_script = null, @.description = null, @.pre_creation_cmd
= N'drop', @.schema_option = 0x00000000000000F3, @.status = 16,
@.vertical_partition = N'false', @.ins_cmd = N'CALL
sp_MSins_tbl880DetailAllow', @.del_cmd = N'CALL sp_MSdel_tbl880DetailAllow',
@.upd_cmd = N'MCALL sp_MSupd_tbl880DetailAllow', @.filter = null, @.sync_object
= null, @.auto_identity_range = N'false'
GO
exec sp_addarticle @.publication = N'EDI_On_RouseMain', @.article =
N'tbl880Header', @.source_owner = N'dbo', @.source_object = N'tbl880Header',
@.destination_table = N'tbl880Header', @.type = N'logbased', @.creation_script =
null, @.description = null, @.pre_creation_cmd = N'drop', @.schema_option =
0x00000000000000F3, @.status = 16, @.vertical_partition = N'false', @.ins_cmd =
N'CALL sp_MSins_tbl880Header', @.del_cmd = N'CALL sp_MSdel_tbl880Header',
@.upd_cmd = N'MCALL sp_MSupd_tbl880Header', @.filter = null, @.sync_object =
null, @.auto_identity_range = N'false'
GO
exec sp_addarticle @.publication = N'EDI_On_RouseMain', @.article =
N'tbl880HeaderAllow', @.source_owner = N'dbo', @.source_object =
N'tbl880HeaderAllow', @.destination_table = N'tbl880HeaderAllow', @.type =
N'logbased', @.creation_script = null, @.description = null, @.pre_creation_cmd
= N'drop', @.schema_option = 0x00000000000000F3, @.status = 16,
@.vertical_partition = N'false', @.ins_cmd = N'CALL
sp_MSins_tbl880HeaderAllow', @.del_cmd = N'CALL sp_MSdel_tbl880HeaderAllow',
@.upd_cmd = N'MCALL sp_MSupd_tbl880HeaderAllow', @.filter = null, @.sync_object
= null, @.auto_identity_range = N'false'
GO
-- Adding the transactional subscription
exec sp_addsubscription @.publication = N'EDI_On_RouseMain', @.article =
N'all', @.subscriber = N'ROUSEONE', @.destination_db = N'EDI', @.sync_type =
N'automatic', @.update_mode = N'read only', @.offloadagent = 0,
@.dts_package_location = N'distributor'
GO
~~~~
Merge publication and push subscription from S2 to Remote location #18
~~~~
-- Enabling the replication database
use master
GO
exec sp_replicationdboption @.dbname = N'EDI', @.optname = N'merge publish',
@.value = N'true'
GO
use [EDI]
GO
-- Adding the merge publication
exec sp_addmergepublication @.publication = N'EDI - 18', @.description =
N'Merge publication of EDI database from Publisher ROUSEONE.', @.retention =
14, @.sync_mode = N'native', @.allow_push = N'true', @.allow_pull = N'true',
@.allow_anonymous = N'true', @.enabled_for_internet = N'false',
@.centralized_conflicts = N'true', @.dynamic_filters = N'false',
@.snapshot_in_defaultfolder = N'true', @.compress_snapshot = N'false',
@.ftp_port = 21, @.ftp_login = N'anonymous', @.conflict_retention = 14,
@.keep_partition_changes = N'true', @.allow_subscription_copy = N'false',
@.allow_synctoalternate = N'false', @.add_to_active_directory = N'false',
@.max_concurrent_merge = 0, @.max_concurrent_dynamic_snapshots = 0
exec sp_addpublication_snapshot @.publication = N'EDI - 18',@.frequency_type =
4, @.frequency_interval = 1, @.frequency_relative_interval = 1,
@.frequency_recurrence_factor = 0, @.frequency_subday = 1,
@.frequency_subday_interval = 5, @.active_start_date = 0, @.active_end_date = 0,
@.active_start_time_of_day = 500, @.active_end_time_of_day = 235959,
@.snapshot_job_name = N'ROUSEONE-EDI-EDI - 18-95'
GO
exec sp_grant_publication_access @.publication = N'EDI - 18', @.login =
N'BUILTIN\Administrators'
GO
exec sp_grant_publication_access @.publication = N'EDI - 18', @.login =
N'distributor_admin'
GO
exec sp_grant_publication_access @.publication = N'EDI - 18', @.login = N'sa'
GO
-- Adding the merge articles
exec sp_addmergearticle @.publication = N'EDI - 18', @.article =
N'tbl880Detail', @.source_owner = N'dbo', @.source_object = N'tbl880Detail',
@.type = N'table', @.description = null, @.column_tracking = N'true',
@.pre_creation_cmd = N'drop', @.creation_script = null, @.schema_option =
0x000000000000CFF1, @.article_resolver = null, @.subset_filterclause = N'loc =
18', @.vertical_partition = N'false', @.destination_owner = N'dbo',
@.auto_identity_range = N'false', @.verify_resolver_signature = 0,
@.allow_interactive_resolver = N'false', @.fast_multicol_updateproc = N'true',
@.check_permissions = 0
GO
exec sp_addmergearticle @.publication = N'EDI - 18', @.article =
N'tbl880DetailAllow', @.source_owner = N'dbo', @.source_object =
N'tbl880DetailAllow', @.type = N'table', @.description = null, @.column_tracking
= N'true', @.pre_creation_cmd = N'drop', @.creation_script = null,
@.schema_option = 0x000000000000CFF1, @.article_resolver = null,
@.subset_filterclause = N'loc = 18', @.vertical_partition = N'false',
@.destination_owner = N'dbo', @.auto_identity_range = N'false',
@.verify_resolver_signature = 0, @.allow_interactive_resolver = N'false',
@.fast_multicol_updateproc = N'true', @.check_permissions = 0
GO
exec sp_addmergearticle @.publication = N'EDI - 18', @.article =
N'tbl880Header', @.source_owner = N'dbo', @.source_object = N'tbl880Header',
@.type = N'table', @.description = null, @.column_tracking = N'true',
@.pre_creation_cmd = N'drop', @.creation_script = null, @.schema_option =
0x000000000000CFF1, @.article_resolver = null, @.subset_filterclause = N'loc =
18', @.vertical_partition = N'false', @.destination_owner = N'dbo',
@.auto_identity_range = N'false', @.verify_resolver_signature = 0,
@.allow_interactive_resolver = N'false', @.fast_multicol_updateproc = N'true',
@.check_permissions = 0
GO
exec sp_addmergearticle @.publication = N'EDI - 18', @.article =
N'tbl880HeaderAllow', @.source_owner = N'dbo', @.source_object =
N'tbl880HeaderAllow', @.type = N'table', @.description = null, @.column_tracking
= N'true', @.pre_creation_cmd = N'drop', @.creation_script = null,
@.schema_option = 0x000000000000CFF1, @.article_resolver = null,
@.subset_filterclause = N'loc = 18', @.vertical_partition = N'false',
@.destination_owner = N'dbo', @.auto_identity_range = N'false',
@.verify_resolver_signature = 0, @.allow_interactive_resolver = N'false',
@.fast_multicol_updateproc = N'true', @.check_permissions = 0
GO
-- Adding the merge subscription
exec sp_addmergesubscription @.publication = N'EDI - 18', @.subscriber =
N'ROUSE18SERVER', @.subscriber_db = N'EDI', @.subscription_type = N'push',
@.subscriber_type = N'local', @.subscription_priority = 0.000000, @.sync_type =
N'automatic', @.frequency_type = 4, @.frequency_interval = 1,
@.frequency_relative_interval = 1, @.frequency_recurrence_factor = 0,
@.frequency_subday = 8, @.frequency_subday_interval = 1, @.active_start_date =
0, @.active_end_date = 0, @.active_start_time_of_day = 0,
@.active_end_time_of_day = 235959, @.enabled_for_syncmgr = N'false',
@.offloadagent = 0, @.use_interactive_resolver = N'false'
GO
How about if you add a row manually to S2, does it work
then? If it does, then delete this row, run the merge
agent then add the same row on S1 and see if it
propagates through. Also, check that when the
transactional replication puts the record onto S2 is
there a resulting corresponding record in
msmerge_contents on s2? Finally please check the conflict
tables to confirm there's nothing there.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Paul,
Thanks for you response. To answer your questions....
I reinitialized the data to make sure everything was in sync. Then I
inserted a row into S2 and ran the merge agent and the row appeared on the
remote server. I delete the record from S2 and it was deleted off the remote
server.
Next I inserted a row, for the same remote location in to S1, ran the agents
(trans on S1 & merge on the remote location and the row appeared. The delete
worked fine also.
Next I ran my app that inserts data in to S1. A total of 263 rows were
inserted into 2 of the tables. I ran the trans agent on S1 and all the new
records were replicated to S2. Next I ran the merge agent on the remote
location and nothing.
I checked msmerge_contents before and after my app ran and it had the same
number of rows.
Finally, I repeated the insert as I did in the first paragraph and nothing.
As I was typing this message, I checked something. The remote locations did
not have merge replication enabled for the dB that was the subscriber. I
enabled them then delete the data from paragraph 3 (from above) and reran my
application and nothing.
I checked the conflict tables on the publisher (S2) and they were empty. Not
sure if they are suppose to exist, but there are no conflict tables on the
subscriber.
To make things even stranger, the changes being made on the remote
location/tables are making their way back to S2
TIA,
Larry...
|||Larry,
somehow, your app is able to force the replication stored procedures to run
on the subscriber without firing the triggers. This shouldn't be possible!
I'd run profiler on the transactional subscriber to see how this is
occurring and to confirm that the merge triggers aren't firing.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
I am at such a loss. The manual insertions are not working now either.
This is what is strange...the updates that are being done at remote
locations are making it back to S2, but nothing is making it from S2 to the
remote locations. In fact, I have having to reinitialize the data daily and
the inserts into S2 are not being sent to the remote locations unless the
snapshot agent is run.
I know in trans repl, there is a log reader that gets the pending
transactions. What does this in merge? Could this be the problem?
I have disabled replication for this dB and deleted all the publications. I
recreated one and tried it as a push and a pull subscription and no luck.
HELP!!!!
Larry...
|||Larry,
please script out your publications/subscriptions and
post them up here. Also, please confirm that you are
using the same service pack (sp3/a) on all 3 computers
involved.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||The scrips below are from servers all running 8.00.534 (SP2)
~~~~
Transactional Publication & Subscription from S1 to S2
~~~~
-- Enabling the replication database
use master
GO
exec sp_replicationdboption @.dbname = N'EDI_On_RouseMain', @.optname =
N'publish', @.value = N'true'
GO
use [EDI_On_RouseMain]
GO
-- Adding the transactional publication
exec sp_addpublication @.publication = N'EDI_On_RouseMain', @.restricted =
N'false', @.sync_method = N'native', @.repl_freq = N'continuous', @.description
= N'Transactional publication of EDI_On_RouseMain database from Publisher
ROUSEMAIN.', @.status = N'active', @.allow_push = N'true', @.allow_pull =
N'true', @.allow_anonymous = N'true', @.enabled_for_internet = N'false',
@.independent_agent = N'true', @.immediate_sync = N'true', @.allow_sync_tran =
N'false', @.autogen_sync_procs = N'false', @.retention = 336,
@.allow_queued_tran = N'false', @.snapshot_in_defaultfolder = N'true',
@.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login = N'anonymous',
@.allow_dts = N'false', @.allow_subscription_copy = N'false',
@.add_to_active_directory = N'false', @.logreader_job_name =
N'ROUSEMAIN-EDI_On_RouseMain-2'
exec sp_addpublication_snapshot @.publication =
N'EDI_On_RouseMain',@.frequency_type = 4, @.frequency_interval = 1,
@.frequency_relative_interval = 1, @.frequency_recurrence_factor = 0,
@.frequency_subday = 8, @.frequency_subday_interval = 1, @.active_start_date =
0, @.active_end_date = 0, @.active_start_time_of_day = 0,
@.active_end_time_of_day = 235959, @.snapshot_job_name =
N'ROUSEMAIN-EDI_On_RouseMain-EDI_On_RouseMain-2'
GO
exec sp_grant_publication_access @.publication = N'EDI_On_RouseMain', @.login
= N'BUILTIN\Administrators'
GO
exec sp_grant_publication_access @.publication = N'EDI_On_RouseMain', @.login
= N'distributor_admin'
GO
exec sp_grant_publication_access @.publication = N'EDI_On_RouseMain', @.login
= N'EDI'
GO
exec sp_grant_publication_access @.publication = N'EDI_On_RouseMain', @.login
= N'sa'
GO
-- Adding the transactional articles
exec sp_addarticle @.publication = N'EDI_On_RouseMain', @.article =
N'tbl880Detail', @.source_owner = N'dbo', @.source_object = N'tbl880Detail',
@.destination_table = N'tbl880Detail', @.type = N'logbased', @.creation_script =
null, @.description = null, @.pre_creation_cmd = N'drop', @.schema_option =
0x00000000000000F3, @.status = 16, @.vertical_partition = N'false', @.ins_cmd =
N'CALL sp_MSins_tbl880Detail', @.del_cmd = N'CALL sp_MSdel_tbl880Detail',
@.upd_cmd = N'MCALL sp_MSupd_tbl880Detail', @.filter = null, @.sync_object =
null, @.auto_identity_range = N'false'
GO
exec sp_addarticle @.publication = N'EDI_On_RouseMain', @.article =
N'tbl880DetailAllow', @.source_owner = N'dbo', @.source_object =
N'tbl880DetailAllow', @.destination_table = N'tbl880DetailAllow', @.type =
N'logbased', @.creation_script = null, @.description = null, @.pre_creation_cmd
= N'drop', @.schema_option = 0x00000000000000F3, @.status = 16,
@.vertical_partition = N'false', @.ins_cmd = N'CALL
sp_MSins_tbl880DetailAllow', @.del_cmd = N'CALL sp_MSdel_tbl880DetailAllow',
@.upd_cmd = N'MCALL sp_MSupd_tbl880DetailAllow', @.filter = null, @.sync_object
= null, @.auto_identity_range = N'false'
GO
exec sp_addarticle @.publication = N'EDI_On_RouseMain', @.article =
N'tbl880Header', @.source_owner = N'dbo', @.source_object = N'tbl880Header',
@.destination_table = N'tbl880Header', @.type = N'logbased', @.creation_script =
null, @.description = null, @.pre_creation_cmd = N'drop', @.schema_option =
0x00000000000000F3, @.status = 16, @.vertical_partition = N'false', @.ins_cmd =
N'CALL sp_MSins_tbl880Header', @.del_cmd = N'CALL sp_MSdel_tbl880Header',
@.upd_cmd = N'MCALL sp_MSupd_tbl880Header', @.filter = null, @.sync_object =
null, @.auto_identity_range = N'false'
GO
exec sp_addarticle @.publication = N'EDI_On_RouseMain', @.article =
N'tbl880HeaderAllow', @.source_owner = N'dbo', @.source_object =
N'tbl880HeaderAllow', @.destination_table = N'tbl880HeaderAllow', @.type =
N'logbased', @.creation_script = null, @.description = null, @.pre_creation_cmd
= N'drop', @.schema_option = 0x00000000000000F3, @.status = 16,
@.vertical_partition = N'false', @.ins_cmd = N'CALL
sp_MSins_tbl880HeaderAllow', @.del_cmd = N'CALL sp_MSdel_tbl880HeaderAllow',
@.upd_cmd = N'MCALL sp_MSupd_tbl880HeaderAllow', @.filter = null, @.sync_object
= null, @.auto_identity_range = N'false'
GO
-- Adding the transactional subscription
exec sp_addsubscription @.publication = N'EDI_On_RouseMain', @.article =
N'all', @.subscriber = N'ROUSEONE', @.destination_db = N'EDI', @.sync_type =
N'automatic', @.update_mode = N'read only', @.offloadagent = 0,
@.dts_package_location = N'distributor'
GO
~~~~
Merge publication and push subscription from S2 to Remote location #18
~~~~
-- Enabling the replication database
use master
GO
exec sp_replicationdboption @.dbname = N'EDI', @.optname = N'merge publish',
@.value = N'true'
GO
use [EDI]
GO
-- Adding the merge publication
exec sp_addmergepublication @.publication = N'EDI - 18', @.description =
N'Merge publication of EDI database from Publisher ROUSEONE.', @.retention =
14, @.sync_mode = N'native', @.allow_push = N'true', @.allow_pull = N'true',
@.allow_anonymous = N'true', @.enabled_for_internet = N'false',
@.centralized_conflicts = N'true', @.dynamic_filters = N'false',
@.snapshot_in_defaultfolder = N'true', @.compress_snapshot = N'false',
@.ftp_port = 21, @.ftp_login = N'anonymous', @.conflict_retention = 14,
@.keep_partition_changes = N'true', @.allow_subscription_copy = N'false',
@.allow_synctoalternate = N'false', @.add_to_active_directory = N'false',
@.max_concurrent_merge = 0, @.max_concurrent_dynamic_snapshots = 0
exec sp_addpublication_snapshot @.publication = N'EDI - 18',@.frequency_type =
4, @.frequency_interval = 1, @.frequency_relative_interval = 1,
@.frequency_recurrence_factor = 0, @.frequency_subday = 1,
@.frequency_subday_interval = 5, @.active_start_date = 0, @.active_end_date = 0,
@.active_start_time_of_day = 500, @.active_end_time_of_day = 235959,
@.snapshot_job_name = N'ROUSEONE-EDI-EDI - 18-95'
GO
exec sp_grant_publication_access @.publication = N'EDI - 18', @.login =
N'BUILTIN\Administrators'
GO
exec sp_grant_publication_access @.publication = N'EDI - 18', @.login =
N'distributor_admin'
GO
exec sp_grant_publication_access @.publication = N'EDI - 18', @.login = N'sa'
GO
-- Adding the merge articles
exec sp_addmergearticle @.publication = N'EDI - 18', @.article =
N'tbl880Detail', @.source_owner = N'dbo', @.source_object = N'tbl880Detail',
@.type = N'table', @.description = null, @.column_tracking = N'true',
@.pre_creation_cmd = N'drop', @.creation_script = null, @.schema_option =
0x000000000000CFF1, @.article_resolver = null, @.subset_filterclause = N'loc =
18', @.vertical_partition = N'false', @.destination_owner = N'dbo',
@.auto_identity_range = N'false', @.verify_resolver_signature = 0,
@.allow_interactive_resolver = N'false', @.fast_multicol_updateproc = N'true',
@.check_permissions = 0
GO
exec sp_addmergearticle @.publication = N'EDI - 18', @.article =
N'tbl880DetailAllow', @.source_owner = N'dbo', @.source_object =
N'tbl880DetailAllow', @.type = N'table', @.description = null, @.column_tracking
= N'true', @.pre_creation_cmd = N'drop', @.creation_script = null,
@.schema_option = 0x000000000000CFF1, @.article_resolver = null,
@.subset_filterclause = N'loc = 18', @.vertical_partition = N'false',
@.destination_owner = N'dbo', @.auto_identity_range = N'false',
@.verify_resolver_signature = 0, @.allow_interactive_resolver = N'false',
@.fast_multicol_updateproc = N'true', @.check_permissions = 0
GO
exec sp_addmergearticle @.publication = N'EDI - 18', @.article =
N'tbl880Header', @.source_owner = N'dbo', @.source_object = N'tbl880Header',
@.type = N'table', @.description = null, @.column_tracking = N'true',
@.pre_creation_cmd = N'drop', @.creation_script = null, @.schema_option =
0x000000000000CFF1, @.article_resolver = null, @.subset_filterclause = N'loc =
18', @.vertical_partition = N'false', @.destination_owner = N'dbo',
@.auto_identity_range = N'false', @.verify_resolver_signature = 0,
@.allow_interactive_resolver = N'false', @.fast_multicol_updateproc = N'true',
@.check_permissions = 0
GO
exec sp_addmergearticle @.publication = N'EDI - 18', @.article =
N'tbl880HeaderAllow', @.source_owner = N'dbo', @.source_object =
N'tbl880HeaderAllow', @.type = N'table', @.description = null, @.column_tracking
= N'true', @.pre_creation_cmd = N'drop', @.creation_script = null,
@.schema_option = 0x000000000000CFF1, @.article_resolver = null,
@.subset_filterclause = N'loc = 18', @.vertical_partition = N'false',
@.destination_owner = N'dbo', @.auto_identity_range = N'false',
@.verify_resolver_signature = 0, @.allow_interactive_resolver = N'false',
@.fast_multicol_updateproc = N'true', @.check_permissions = 0
GO
-- Adding the merge subscription
exec sp_addmergesubscription @.publication = N'EDI - 18', @.subscriber =
N'ROUSE18SERVER', @.subscriber_db = N'EDI', @.subscription_type = N'push',
@.subscriber_type = N'local', @.subscription_priority = 0.000000, @.sync_type =
N'automatic', @.frequency_type = 4, @.frequency_interval = 1,
@.frequency_relative_interval = 1, @.frequency_recurrence_factor = 0,
@.frequency_subday = 8, @.frequency_subday_interval = 1, @.active_start_date =
0, @.active_end_date = 0, @.active_start_time_of_day = 0,
@.active_end_time_of_day = 235959, @.enabled_for_syncmgr = N'false',
@.offloadagent = 0, @.use_interactive_resolver = N'false'
GO
Subscribe to:
Posts (Atom)