Rebuilding Replication

Today I discovered that the sunbscriptions were missing from my replication
setup.
I tried to rebuild replication, but when I tried to drop the publications, I
received a message saying:
SQL Server Enterprise Manager could not create publication 'TFWallChart'
from database 'TFWallChart'.
Error 14005: Could not drop publication. A subscription exisits to it.
What should I do?
Thanks
MG
You probably have some subscriptions which have expired. Change your history
retention to match your transaction retention - you should use something
greater than 3 days-to account for long weekends.
Then right click on your the publications folder, and ensure the show
anonymous subscribers is checked. Do any subscribers show up now?
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
"MGeles" <michael.geles@.thomson.com> wrote in message
news:0304FFFF-3490-4443-88A4-422B382BFDCE@.microsoft.com...
> Today I discovered that the sunbscriptions were missing from my
replication
> setup.
> I tried to rebuild replication, but when I tried to drop the publications,
I
> received a message saying:
> SQL Server Enterprise Manager could not create publication 'TFWallChart'
> from database 'TFWallChart'.
> Error 14005: Could not drop publication. A subscription exisits to it.
> What should I do?
> Thanks
> --
> MG
|||I think that I know where to go to set the subscription retention.
Right click on the publication, goto the general tab and set the retention
there.
Where do I need to go to set the transaction retention?
Thanks
MG
"Hilary Cotter" wrote:

> You probably have some subscriptions which have expired. Change your history
> retention to match your transaction retention - you should use something
> greater than 3 days-to account for long weekends.
> Then right click on your the publications folder, and ensure the show
> anonymous subscribers is checked. Do any subscribers show up now?
> --
> 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
> "MGeles" <michael.geles@.thomson.com> wrote in message
> news:0304FFFF-3490-4443-88A4-422B382BFDCE@.microsoft.com...
> replication
> I
>
>
|||Hilary,
When we go to the publication folder, there is no entry in there at all.
The problem is
"Hilary Cotter" wrote:

> You probably have some subscriptions which have expired. Change your history
> retention to match your transaction retention - you should use something
> greater than 3 days-to account for long weekends.
> Then right click on your the publications folder, and ensure the show
> anonymous subscribers is checked. Do any subscribers show up now?
> --
> 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
> "MGeles" <michael.geles@.thomson.com> wrote in message
> news:0304FFFF-3490-4443-88A4-422B382BFDCE@.microsoft.com...
> replication
> I
>
>
|||Hilary,
I right click on the publisher folder (primary), and chose Configure
Publishing, subscribers, and distribution. Then I click subscriber and
uncheck the subscriber. and click apply.
Now when I try to recreate a new transactional publication again w/ the same
name, I get a different error msg:
Error 14294: Supply either @.job_id or @.job_name to idendity the job.
Please help
john
"Hilary Cotter" wrote:

> You probably have some subscriptions which have expired. Change your history
> retention to match your transaction retention - you should use something
> greater than 3 days-to account for long weekends.
> Then right click on your the publications folder, and ensure the show
> anonymous subscribers is checked. Do any subscribers show up now?
> --
> 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
> "MGeles" <michael.geles@.thomson.com> wrote in message
> news:0304FFFF-3490-4443-88A4-422B382BFDCE@.microsoft.com...
> replication
> I
>
>
|||right click on replication monitor, go to distributor properties, click the
properties button.
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
"MGeles" <michael.geles@.thomson.com> wrote in message
news:04D33046-F2BF-4FBC-82C8-265A2EFABA66@.microsoft.com...[vbcol=seagreen]
> I think that I know where to go to set the subscription retention.
> Right click on the publication, goto the general tab and set the retention
> there.
> Where do I need to go to set the transaction retention?
> Thanks
> --
> MG
>
> "Hilary Cotter" wrote:
history[vbcol=seagreen]
publications,[vbcol=seagreen]
'TFWallChart'[vbcol=seagreen]
|||there is something wrong here. Can you query select * from syspublications
in your publication database?
If there is nothing returned from this query someone must have deleted the
publications.
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
"John" <John@.discussions.microsoft.com> wrote in message
news:62B7D3AA-72AE-459F-9ECF-4D4913740392@.microsoft.com...[vbcol=seagreen]
> Hilary,
> When we go to the publication folder, there is no entry in there at all.
> The problem is
> "Hilary Cotter" wrote:
history[vbcol=seagreen]
publications,[vbcol=seagreen]
'TFWallChart'[vbcol=seagreen]
|||sounds like there is some residual meta data in some of the system tables
which you will have to clean up.
I would get the scripts and re-edit them making sure you are using different
log reader, snapshot, and distribution agent name, or delete the parameters
and their values.
Also change the publication name slightly. For example change it from pubs1
to NewPubs1
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
"John" <John@.discussions.microsoft.com> wrote in message
news:7BBE201F-256D-431E-96DB-B8F5F9800F5D@.microsoft.com...
> Hilary,
> I right click on the publisher folder (primary), and chose Configure
> Publishing, subscribers, and distribution. Then I click subscriber and
> uncheck the subscriber. and click apply.
> Now when I try to recreate a new transactional publication again w/ the
same[vbcol=seagreen]
> name, I get a different error msg:
> Error 14294: Supply either @.job_id or @.job_name to idendity the job.
> Please help
> john
>
> "Hilary Cotter" wrote:
history[vbcol=seagreen]
publications,[vbcol=seagreen]
'TFWallChart'[vbcol=seagreen]

Rebuilding indexes in SQL 2000

All
Is there any way to estimate how long it would take to drop and rebuild the
indexes on a database. I have a farily tight maintenance window on a payroll
database and need to be sure that any work would be completed in time. I
assume the database size and amount of tables has a major bearing but not
sure where I would go from that info.
Thanks
Si
Simon
You can determine what tables are heavely defragmented and run rebuilt
index only on them
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:5D6C2F49-E817-4E24-893A-B108549F5331@.microsoft.com...
> All
> Is there any way to estimate how long it would take to drop and rebuild
> the
> indexes on a database. I have a farily tight maintenance window on a
> payroll
> database and need to be sure that any work would be completed in time. I
> assume the database size and amount of tables has a major bearing but not
> sure where I would go from that info.
> Thanks
> Si
|||On Feb 27, 9:37 am, Simon <S...@.discussions.microsoft.com> wrote:
> All
> Is there any way to estimate how long it would take to drop and rebuild the
> indexes on a database. I have a farily tight maintenance window on a payroll
> database and need to be sure that any work would be completed in time. I
> assume the database size and amount of tables has a major bearing but not
> sure where I would go from that info.
> Thanks
> Si
Rather than rebuild ALL of the indexes, consider rebuilding only those
that are badly fragmented. Rebuilding all of them is usually
unnecessary. Here's a script that might help you:
http://realsqlguy.blogspot.com/2007/02/smart-index-defragmentation.html
|||Simon,
there are too many variables in your case for an estimation from someone
external to work.
You could generate your own decent estimate if you restore a recent backup
onto a test server of similar specs and run the rebuild there.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||The problem I have is the database has around 600 tables, of which 350 need
rebuilding. There is also a high level of fragmentation so I thought that
Reindexing would be quicker.
"Tracy McKibben" wrote:

> On Feb 27, 9:37 am, Simon <S...@.discussions.microsoft.com> wrote:
> Rather than rebuild ALL of the indexes, consider rebuilding only those
> that are badly fragmented. Rebuilding all of them is usually
> unnecessary. Here's a script that might help you:
> http://realsqlguy.blogspot.com/2007/02/smart-index-defragmentation.html
>

Rebuilding indexes in SQL 2000

All
Is there any way to estimate how long it would take to drop and rebuild the
indexes on a database. I have a farily tight maintenance window on a payroll
database and need to be sure that any work would be completed in time. I
assume the database size and amount of tables has a major bearing but not
sure where I would go from that info.
Thanks
SiSimon
You can determine what tables are heavely defragmented and run rebuilt
index only on them
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:5D6C2F49-E817-4E24-893A-B108549F5331@.microsoft.com...
> All
> Is there any way to estimate how long it would take to drop and rebuild
> the
> indexes on a database. I have a farily tight maintenance window on a
> payroll
> database and need to be sure that any work would be completed in time. I
> assume the database size and amount of tables has a major bearing but not
> sure where I would go from that info.
> Thanks
> Si|||On Feb 27, 9:37 am, Simon <S...@.discussions.microsoft.com> wrote:
> All
> Is there any way to estimate how long it would take to drop and rebuild th
e
> indexes on a database. I have a farily tight maintenance window on a payro
ll
> database and need to be sure that any work would be completed in time. I
> assume the database size and amount of tables has a major bearing but not
> sure where I would go from that info.
> Thanks
> Si
Rather than rebuild ALL of the indexes, consider rebuilding only those
that are badly fragmented. Rebuilding all of them is usually
unnecessary. Here's a script that might help you:
http://realsqlguy.blogspot.com/2007...gmentation.html|||Simon,
there are too many variables in your case for an estimation from someone
external to work.
You could generate your own decent estimate if you restore a recent backup
onto a test server of similar specs and run the rebuild there.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||The problem I have is the database has around 600 tables, of which 350 need
rebuilding. There is also a high level of fragmentation so I thought that
Reindexing would be quicker.
"Tracy McKibben" wrote:

> On Feb 27, 9:37 am, Simon <S...@.discussions.microsoft.com> wrote:
> Rather than rebuild ALL of the indexes, consider rebuilding only those
> that are badly fragmented. Rebuilding all of them is usually
> unnecessary. Here's a script that might help you:
> http://realsqlguy.blogspot.com/2007...gmentation.html
>sql

Rebuilding indexes in SQL 2000

All
Is there any way to estimate how long it would take to drop and rebuild the
indexes on a database. I have a farily tight maintenance window on a payroll
database and need to be sure that any work would be completed in time. I
assume the database size and amount of tables has a major bearing but not
sure where I would go from that info.
Thanks
SiSimon
You can determine what tables are heavely defragmented and run rebuilt
index only on them
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:5D6C2F49-E817-4E24-893A-B108549F5331@.microsoft.com...
> All
> Is there any way to estimate how long it would take to drop and rebuild
> the
> indexes on a database. I have a farily tight maintenance window on a
> payroll
> database and need to be sure that any work would be completed in time. I
> assume the database size and amount of tables has a major bearing but not
> sure where I would go from that info.
> Thanks
> Si|||On Feb 27, 9:37 am, Simon <S...@.discussions.microsoft.com> wrote:
> All
> Is there any way to estimate how long it would take to drop and rebuild the
> indexes on a database. I have a farily tight maintenance window on a payroll
> database and need to be sure that any work would be completed in time. I
> assume the database size and amount of tables has a major bearing but not
> sure where I would go from that info.
> Thanks
> Si
Rather than rebuild ALL of the indexes, consider rebuilding only those
that are badly fragmented. Rebuilding all of them is usually
unnecessary. Here's a script that might help you:
http://realsqlguy.blogspot.com/2007/02/smart-index-defragmentation.html|||Simon,
there are too many variables in your case for an estimation from someone
external to work.
You could generate your own decent estimate if you restore a recent backup
onto a test server of similar specs and run the rebuild there.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||The problem I have is the database has around 600 tables, of which 350 need
rebuilding. There is also a high level of fragmentation so I thought that
Reindexing would be quicker.
"Tracy McKibben" wrote:
> On Feb 27, 9:37 am, Simon <S...@.discussions.microsoft.com> wrote:
> > All
> >
> > Is there any way to estimate how long it would take to drop and rebuild the
> > indexes on a database. I have a farily tight maintenance window on a payroll
> > database and need to be sure that any work would be completed in time. I
> > assume the database size and amount of tables has a major bearing but not
> > sure where I would go from that info.
> >
> > Thanks
> >
> > Si
> Rather than rebuild ALL of the indexes, consider rebuilding only those
> that are badly fragmented. Rebuilding all of them is usually
> unnecessary. Here's a script that might help you:
> http://realsqlguy.blogspot.com/2007/02/smart-index-defragmentation.html
>

Rebuild clustered index and change filegroup

How can I rebuild my clustered index and change its filegroup, but don't want
to use the drop existing clause, which will drop and recreate all my
non-clustered indexes of that table ?
Thanks,
Ranga
Try:
CREATE CLUSTERED INDEX IX_MyTable on MyTable (MyCol) WITH DROP_EXISTING ON
[MyFileGroup]
It will not drop and recreated the nonclustered indexes.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"Ranga" <Ranga@.discussions.microsoft.com> wrote in message
news:20A15DB9-FEF8-426A-A2E2-8B2C69633E9F@.microsoft.com...
How can I rebuild my clustered index and change its filegroup, but don't
want
to use the drop existing clause, which will drop and recreate all my
non-clustered indexes of that table ?
Thanks,
Ranga

Rebuild clustered index and change filegroup

How can I rebuild my clustered index and change its filegroup, but don't wan
t
to use the drop existing clause, which will drop and recreate all my
non-clustered indexes of that table ?
Thanks,
RangaTry:
CREATE CLUSTERED INDEX IX_MyTable on MyTable (MyCol) WITH DROP_EXISTING ON
[MyFileGroup]
It will not drop and recreated the nonclustered indexes.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Ranga" <Ranga@.discussions.microsoft.com> wrote in message
news:20A15DB9-FEF8-426A-A2E2-8B2C69633E9F@.microsoft.com...
How can I rebuild my clustered index and change its filegroup, but don't
want
to use the drop existing clause, which will drop and recreate all my
non-clustered indexes of that table ?
Thanks,
Ranga

Rebuild clustered index and change filegroup

How can I rebuild my clustered index and change its filegroup, but don't want
to use the drop existing clause, which will drop and recreate all my
non-clustered indexes of that table ?
Thanks,
RangaTry:
CREATE CLUSTERED INDEX IX_MyTable on MyTable (MyCol) WITH DROP_EXISTING ON
[MyFileGroup]
It will not drop and recreated the nonclustered indexes.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Ranga" <Ranga@.discussions.microsoft.com> wrote in message
news:20A15DB9-FEF8-426A-A2E2-8B2C69633E9F@.microsoft.com...
How can I rebuild my clustered index and change its filegroup, but don't
want
to use the drop existing clause, which will drop and recreate all my
non-clustered indexes of that table ?
Thanks,
Ranga