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]

Rebuild Index helps temporarily

I have a query that grinds to a halt after a heavy load. I have
discovered, through many variations and trials, that if I rebuild the
index of a certain table, my performance improves incredibly. Once the
load increases, the performance degrades. We rebuild our indexes daily
(overkill, but we are not 24 x 7). If you look at the table's indexes
the fragmentation is about 30%. It does NOT change after a rebuild
even though the performance does. Any ideas? (It is not a huge table
~ 5000 rows. Medium on inserts and updates)
These are the results of the SHOWCONTIG sproc
DBCC SHOWCONTIG scanning 'InventoryCount' table...
Table: 'InventoryCount' (747149707); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned........................: 28
- Extents Scanned.......................: 10
- Extent Switches.......................: 9
- Avg. Pages per Extent..................: 2.8
- Scan Density [Best Count:Actual Count]......: 40.00% [4:10]
- Logical Scan Fragmentation ..............: 25.00%
- Extent Scan Fragmentation ...............: 70.00%
- Avg. Bytes Free per Page................: 2499.0
- Avg. Page Density (full)................: 69.13%What is the fill factor of the rebuild command you are using and how does the
query looks like?
"magkip@.hotmail.com" wrote:
> I have a query that grinds to a halt after a heavy load. I have
> discovered, through many variations and trials, that if I rebuild the
> index of a certain table, my performance improves incredibly. Once the
> load increases, the performance degrades. We rebuild our indexes daily
> (overkill, but we are not 24 x 7). If you look at the table's indexes
> the fragmentation is about 30%. It does NOT change after a rebuild
> even though the performance does. Any ideas? (It is not a huge table
> ~ 5000 rows. Medium on inserts and updates)
> These are the results of the SHOWCONTIG sproc
> DBCC SHOWCONTIG scanning 'InventoryCount' table...
> Table: 'InventoryCount' (747149707); index ID: 1, database ID: 11
> TABLE level scan performed.
> - Pages Scanned........................: 28
> - Extents Scanned.......................: 10
> - Extent Switches.......................: 9
> - Avg. Pages per Extent..................: 2.8
> - Scan Density [Best Count:Actual Count]......: 40.00% [4:10]
> - Logical Scan Fragmentation ..............: 25.00%
> - Extent Scan Fragmentation ...............: 70.00%
> - Avg. Bytes Free per Page................: 2499.0
> - Avg. Page Density (full)................: 69.13%
>|||The query is long and complicated. We are optimizing it now. It can
run in <2 seconds after the rebuild.
I set the fill factor to 80%.
Edgardo wrote:
> What is the fill factor of the rebuild command you are using and how does the
> query looks like?
> "magkip@.hotmail.com" wrote:
> > I have a query that grinds to a halt after a heavy load. I have
> > discovered, through many variations and trials, that if I rebuild the
> > index of a certain table, my performance improves incredibly. Once the
> > load increases, the performance degrades. We rebuild our indexes daily
> > (overkill, but we are not 24 x 7). If you look at the table's indexes
> > the fragmentation is about 30%. It does NOT change after a rebuild
> > even though the performance does. Any ideas? (It is not a huge table
> > ~ 5000 rows. Medium on inserts and updates)
> >
> > These are the results of the SHOWCONTIG sproc
> > DBCC SHOWCONTIG scanning 'InventoryCount' table...
> > Table: 'InventoryCount' (747149707); index ID: 1, database ID: 11
> > TABLE level scan performed.
> > - Pages Scanned........................: 28
> > - Extents Scanned.......................: 10
> > - Extent Switches.......................: 9
> > - Avg. Pages per Extent..................: 2.8
> > - Scan Density [Best Count:Actual Count]......: 40.00% [4:10]
> > - Logical Scan Fragmentation ..............: 25.00%
> > - Extent Scan Fragmentation ...............: 70.00%
> > - Avg. Bytes Free per Page................: 2499.0
> > - Avg. Page Density (full)................: 69.13%
> >
> >|||Oops. I misunderstood. I just use 'Rebuild All indexes'.
mag...@.hotmail.com wrote:
> The query is long and complicated. We are optimizing it now. It can
> run in <2 seconds after the rebuild.
> I set the fill factor to 80%.
> Edgardo wrote:
> > What is the fill factor of the rebuild command you are using and how does the
> > query looks like?
> >
> > "magkip@.hotmail.com" wrote:
> >
> > > I have a query that grinds to a halt after a heavy load. I have
> > > discovered, through many variations and trials, that if I rebuild the
> > > index of a certain table, my performance improves incredibly. Once the
> > > load increases, the performance degrades. We rebuild our indexes daily
> > > (overkill, but we are not 24 x 7). If you look at the table's indexes
> > > the fragmentation is about 30%. It does NOT change after a rebuild
> > > even though the performance does. Any ideas? (It is not a huge table
> > > ~ 5000 rows. Medium on inserts and updates)
> > >
> > > These are the results of the SHOWCONTIG sproc
> > > DBCC SHOWCONTIG scanning 'InventoryCount' table...
> > > Table: 'InventoryCount' (747149707); index ID: 1, database ID: 11
> > > TABLE level scan performed.
> > > - Pages Scanned........................: 28
> > > - Extents Scanned.......................: 10
> > > - Extent Switches.......................: 9
> > > - Avg. Pages per Extent..................: 2.8
> > > - Scan Density [Best Count:Actual Count]......: 40.00% [4:10]
> > > - Logical Scan Fragmentation ..............: 25.00%
> > > - Extent Scan Fragmentation ...............: 70.00%
> > > - Avg. Bytes Free per Page................: 2499.0
> > > - Avg. Page Density (full)................: 69.13%
> > >
> > >|||Edgardo and Magkip,
I found this interesting and have tried to increase the fill factor on some
tables in the past. In my case however, I boosted it only slightly (from 10
percent to 15 percent). Magkip, you have boosted it to 80 percent.
Is there a method to determine just how much to change this fill amount?
For example, would a change to 80 percent mean that the table will grow by as
much as 80 percent of the original size each time it grows or is this more of
a way of forcing the disk allocation to be large enough to hold any temporary
space required during the growth? Or is it some of both?
--
Regards,
Jamie
"Edgardo Valdez, MCTS / MCITP" wrote:
> What is the fill factor of the rebuild command you are using and how does the
> query looks like?
> "magkip@.hotmail.com" wrote:
> > I have a query that grinds to a halt after a heavy load. I have
> > discovered, through many variations and trials, that if I rebuild the
> > index of a certain table, my performance improves incredibly. Once the
> > load increases, the performance degrades. We rebuild our indexes daily
> > (overkill, but we are not 24 x 7). If you look at the table's indexes
> > the fragmentation is about 30%. It does NOT change after a rebuild
> > even though the performance does. Any ideas? (It is not a huge table
> > ~ 5000 rows. Medium on inserts and updates)
> >
> > These are the results of the SHOWCONTIG sproc
> > DBCC SHOWCONTIG scanning 'InventoryCount' table...
> > Table: 'InventoryCount' (747149707); index ID: 1, database ID: 11
> > TABLE level scan performed.
> > - Pages Scanned........................: 28
> > - Extents Scanned.......................: 10
> > - Extent Switches.......................: 9
> > - Avg. Pages per Extent..................: 2.8
> > - Scan Density [Best Count:Actual Count]......: 40.00% [4:10]
> > - Logical Scan Fragmentation ..............: 25.00%
> > - Extent Scan Fragmentation ...............: 70.00%
> > - Avg. Bytes Free per Page................: 2499.0
> > - Avg. Page Density (full)................: 69.13%
> >
> >