Reinitializing transactional subscribers

In SQL2000, SP3, We want to periodically refresh our read-only
subscribers with the latest schema and data (including FK's, indexes,
etc) whenever we role out a new software release. The database is
relatively small and we can currently disable publishing\remove
subscribers, and add them back in about 15 minutes. As the database
grows, I'm not sure if this would be the best method of synchronizing
the subscribers, but management likes the idea of a complete snapshot.
Is there a typical approach to updating subscribers with all
differences that makes more sense? The subscribers are primarily for
Disaster Recovery, but are also used to reduce the need for our
European customers to come to the US for reads. When I tried the
"start synchronizing" option in the EM gui, it has difficulty
dropping/replacing tables because it can't drop tables w FK's.
Also, can anyone explain the difference between reinitializing and
synchronizing?
thx much!
Gary,
if you have a need to drop FKs then it is possible to apply a prescript
before the snapshot is applied (botom of snapshot tab on publication
properties).
Some schema changes can be carried out using the 'plumbing' of replication,
eg sp_repladdcolumn and sp_repldropcolumn. Some other changes eg addition of
an index, can be done using sp_addscriptexec.
As for the difference between initialization and synchronization, right at
the start when the snapshot is sent to the subscriber there isn't really
one. Once the snapshot has been applied, sending down transactions in the
case of transactional replication is referred to as synchronization, and
reinitialization would refer to a new snapshot being created and propagated.
HTH,
Paul Ibison
|||Thanks Paul... But rather than running a pre-script, and individual
system sp's, do you know if this methodology - dropping/re-establishing
replication as new releases are rolled out is a common approach to
keeping things in-sync?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||I have seen it used, but sp_droparticle and sp_addarticle, along with
sp_repladdcolumn and sp_repldropcolumn are more common for small changes.
Regards,
Paul

Reindexing tables with computed columns

I need to reindex a table with a computed column. The column is not
included in any indexes, but when I run the DBCC it crashes with the
following error:
DBCC failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER'.
Any ideas on how I can reindex these tables?
Thanks!
Richard
*** Sent via Developersdex http://www.codecomments.com ***
Richard,
Sounds like the QUOTED_IDENTIFIER option needs to be ON.
Try the last section of this link:
Creating Indexes on Computed Columns
http://msdn.microsoft.com/library/de...es_05_8os3.asp
HTH
Jerry
"Richard" <nospam@.devdex.com> wrote in message
news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>I need to reindex a table with a computed column. The column is not
> included in any indexes, but when I run the DBCC it crashes with the
> following error:
> DBCC failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> Any ideas on how I can reindex these tables?
> Thanks!
> Richard
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||If you're using a non-named instance and are running SP4, you can use
a -supportcomputedcolumn parameter in the first step of the job. If you're
using < SP4 or a named instance, you'll have to create a separate job to
execute the integrity/optimizations. See
http://support.microsoft.com/default...b;en-us;902388
I had this trouble in a Sharepoint database. I created a separate job with
two steps, one for integrity checks and one for reorg on all tables. This
KB will give you the script to reorg all tables
http://support.microsoft.com/kb/301292/
HTH
--Lori
"Richard" <nospam@.devdex.com> wrote in message
news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>I need to reindex a table with a computed column. The column is not
> included in any indexes, but when I run the DBCC it crashes with the
> following error:
> DBCC failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> Any ideas on how I can reindex these tables?
> Thanks!
> Richard
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||Note that the scripts reorgs whether the index is fragmented or not (just as maint wiz does). If you
only want to reorg if there is any fragmentation in the first place, you should use the sample code
provided in Books Online, DBCC SHOWCONTIG.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Lori Clark" <lclark@.dbadvisor.com> wrote in message news:eCYr0NM2FHA.3864@.TK2MSFTNGP12.phx.gbl...
> If you're using a non-named instance and are running SP4, you can use a -supportcomputedcolumn
> parameter in the first step of the job. If you're using < SP4 or a named instance, you'll have to
> create a separate job to execute the integrity/optimizations. See
> http://support.microsoft.com/default...b;en-us;902388
> I had this trouble in a Sharepoint database. I created a separate job with two steps, one for
> integrity checks and one for reorg on all tables. This KB will give you the script to reorg all
> tables
> http://support.microsoft.com/kb/301292/
>
> HTH
> --Lori
> "Richard" <nospam@.devdex.com> wrote in message news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>

Reindexing tables with computed columns

I need to reindex a table with a computed column. The column is not
included in any indexes, but when I run the DBCC it crashes with the
following error:
DBCC failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER'.
Any ideas on how I can reindex these tables'
Thanks!
Richard
*** Sent via Developersdex http://www.codecomments.com ***Richard,
Sounds like the QUOTED_IDENTIFIER option needs to be ON.
Try the last section of this link:
Creating Indexes on Computed Columns
http://msdn.microsoft.com/library/d...>
_05_8os3.asp
HTH
Jerry
"Richard" <nospam@.devdex.com> wrote in message
news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>I need to reindex a table with a computed column. The column is not
> included in any indexes, but when I run the DBCC it crashes with the
> following error:
> DBCC failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> Any ideas on how I can reindex these tables'
> Thanks!
> Richard
>
> *** Sent via Developersdex http://www.codecomments.com ***|||If you're using a non-named instance and are running SP4, you can use
a -supportcomputedcolumn parameter in the first step of the job. If you're
using < SP4 or a named instance, you'll have to create a separate job to
execute the integrity/optimizations. See
http://support.microsoft.com/defaul...kb;en-us;902388
I had this trouble in a Sharepoint database. I created a separate job with
two steps, one for integrity checks and one for reorg on all tables. This
KB will give you the script to reorg all tables
http://support.microsoft.com/kb/301292/
HTH
--Lori
"Richard" <nospam@.devdex.com> wrote in message
news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>I need to reindex a table with a computed column. The column is not
> included in any indexes, but when I run the DBCC it crashes with the
> following error:
> DBCC failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> Any ideas on how I can reindex these tables'
> Thanks!
> Richard
>
> *** Sent via Developersdex http://www.codecomments.com ***|||Note that the scripts reorgs whether the index is fragmented or not (just as
maint wiz does). If you
only want to reorg if there is any fragmentation in the first place, you sho
uld use the sample code
provided in Books Online, DBCC SHOWCONTIG.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Lori Clark" <lclark@.dbadvisor.com> wrote in message news:eCYr0NM2FHA.3864@.TK2MSFTNGP12.phx.
gbl...
> If you're using a non-named instance and are running SP4, you can use a -s
upportcomputedcolumn
> parameter in the first step of the job. If you're using < SP4 or a named
instance, you'll have to
> create a separate job to execute the integrity/optimizations. See
> http://support.microsoft.com/defaul...kb;en-us;902388
> I had this trouble in a Sharepoint database. I created a separate job wit
h two steps, one for
> integrity checks and one for reorg on all tables. This KB will give you t
he script to reorg all
> tables
> http://support.microsoft.com/kb/301292/
>
> HTH
> --Lori
> "Richard" <nospam@.devdex.com> wrote in message news:%23Qes6HM2FHA.460@.TK2M
SFTNGP15.phx.gbl...
>

Reindexing tables with computed columns

I need to reindex a table with a computed column. The column is not
included in any indexes, but when I run the DBCC it crashes with the
following error:
DBCC failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER'.
Any ideas on how I can reindex these tables'
Thanks!
Richard
*** Sent via Developersdex http://www.developersdex.com ***Richard,
Sounds like the QUOTED_IDENTIFIER option needs to be ON.
Try the last section of this link:
Creating Indexes on Computed Columns
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_8os3.asp
HTH
Jerry
"Richard" <nospam@.devdex.com> wrote in message
news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>I need to reindex a table with a computed column. The column is not
> included in any indexes, but when I run the DBCC it crashes with the
> following error:
> DBCC failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> Any ideas on how I can reindex these tables'
> Thanks!
> Richard
>
> *** Sent via Developersdex http://www.developersdex.com ***|||If you're using a non-named instance and are running SP4, you can use
a -supportcomputedcolumn parameter in the first step of the job. If you're
using < SP4 or a named instance, you'll have to create a separate job to
execute the integrity/optimizations. See
http://support.microsoft.com/default.aspx?scid=kb;en-us;902388
I had this trouble in a Sharepoint database. I created a separate job with
two steps, one for integrity checks and one for reorg on all tables. This
KB will give you the script to reorg all tables
http://support.microsoft.com/kb/301292/
HTH
--Lori
"Richard" <nospam@.devdex.com> wrote in message
news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>I need to reindex a table with a computed column. The column is not
> included in any indexes, but when I run the DBCC it crashes with the
> following error:
> DBCC failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> Any ideas on how I can reindex these tables'
> Thanks!
> Richard
>
> *** Sent via Developersdex http://www.developersdex.com ***|||Note that the scripts reorgs whether the index is fragmented or not (just as maint wiz does). If you
only want to reorg if there is any fragmentation in the first place, you should use the sample code
provided in Books Online, DBCC SHOWCONTIG.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Lori Clark" <lclark@.dbadvisor.com> wrote in message news:eCYr0NM2FHA.3864@.TK2MSFTNGP12.phx.gbl...
> If you're using a non-named instance and are running SP4, you can use a -supportcomputedcolumn
> parameter in the first step of the job. If you're using < SP4 or a named instance, you'll have to
> create a separate job to execute the integrity/optimizations. See
> http://support.microsoft.com/default.aspx?scid=kb;en-us;902388
> I had this trouble in a Sharepoint database. I created a separate job with two steps, one for
> integrity checks and one for reorg on all tables. This KB will give you the script to reorg all
> tables
> http://support.microsoft.com/kb/301292/
>
> HTH
> --Lori
> "Richard" <nospam@.devdex.com> wrote in message news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>>I need to reindex a table with a computed column. The column is not
>> included in any indexes, but when I run the DBCC it crashes with the
>> following error:
>> DBCC failed because the following SET options have incorrect settings:
>> 'QUOTED_IDENTIFIER'.
>> Any ideas on how I can reindex these tables'
>> Thanks!
>> Richard
>>
>> *** Sent via Developersdex http://www.developersdex.com ***
>

Reindexation

I have a very large database, in wich the indexes are constantly "getting lost". I execute DBCC Reindex and everything goes back to normal. Still, I don't know why this happens. I have to run this process once or twice a week, sometimes as a scheduled task, some others manually.
Why are the indexes 'getting lost'?
What could be wrong with the database?
Thanks in advance

Can you elaborate on what you mean by "indexes are getting lost"...are you saying the index no longer exists? That cannot be the case because dbcc reindex implies you have an index that is being reindexed.

Thx

Ajay

|||

I'm deeply sorry for not being able to put this in other way. It's just that i do not understand what is happening, even less how to ask it or explain it... someone told me that "the indexes get corrupted". Still, I don't know what this means, or why...

Thans again

Eduardo

|||

You might find some help with the DBCC commands. They will help you with physical and logical errors. Here is a good link for an introduction to those:

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=30&rl=1

And here is a more in-depth explanation of the DBCC CHECKDB command:

http://msdn2.microsoft.com/en-us/library/aa258278(SQL.80).aspx

re-Index advice

We periodically rebuild our indexes when we notice defragmentation of
indexes however after rebuilding the indexes we do not recompile the
stored procedure. My experience has been that after rebuiling the
indexes we ususlly notice a performance gain right away however I was
wondering if it is also necessary to recompile all our stored
procedures after the indexes are rebuilt. What is the best practise?
ThanksWhen you reindex an index it will by default update the stats which
invalidate the plans for anything that references them. That in turn will
force the recompile of the plan the next time it is used. So in short you do
not need to recompile after an index rebuild as it is already done for you.
--
Andrew J. Kelly SQL MVP
"shub" <shubtech@.gmail.com> wrote in message
news:1154619914.120880.28460@.b28g2000cwb.googlegroups.com...
> We periodically rebuild our indexes when we notice defragmentation of
> indexes however after rebuilding the indexes we do not recompile the
> stored procedure. My experience has been that after rebuiling the
> indexes we ususlly notice a performance gain right away however I was
> wondering if it is also necessary to recompile all our stored
> procedures after the indexes are rebuilt. What is the best practise?
> Thanks
>|||shub wrote:
> We periodically rebuild our indexes when we notice defragmentation of
> indexes however after rebuilding the indexes we do not recompile the
> stored procedure. My experience has been that after rebuiling the
> indexes we ususlly notice a performance gain right away however I was
> wondering if it is also necessary to recompile all our stored
> procedures after the indexes are rebuilt. What is the best practise?
> Thanks
>
Defragging the indexes shouldn't require recompiling anything, unless
the indexes were so badly fragmented that the optimize was ignoring them
and using a sub-optimal execution plan. I personally have never had to
do this.
You don't say how you "notice" fragmentation, but you might consider an
automated approach that runs nightly or weekly. See my site for one
possible method:
http://realsqlguy.com/twiki/bin/view/RealSQLGuy/DefragIndexesAsNeeded
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks Andrew that makes sense. What would be the best way to check to
make sure that the stored proc's are indeed getting recompiled?
Thanks
shub wrote:
> We periodically rebuild our indexes when we notice defragmentation of
> indexes however after rebuilding the indexes we do not recompile the
> stored procedure. My experience has been that after rebuiling the
> indexes we ususlly notice a performance gain right away however I was
> wondering if it is also necessary to recompile all our stored
> procedures after the indexes are rebuilt. What is the best practise?
> Thanks|||Well it is not something one generally has to check on. If you know the
stats are getting updated (you can see with DBCC SHOWSTTISTICS) then you can
count on any plans that reference those stats to get recompiled the next
time they are run. This is true in all cases unless you have used the KEEP
PLAN or KEEP FIXED PLAN hints in the query.
--
Andrew J. Kelly SQL MVP
"shub" <shubtech@.gmail.com> wrote in message
news:1154694889.877119.181210@.p79g2000cwp.googlegroups.com...
> Thanks Andrew that makes sense. What would be the best way to check to
> make sure that the stored proc's are indeed getting recompiled?
> Thanks
>
> shub wrote:
>> We periodically rebuild our indexes when we notice defragmentation of
>> indexes however after rebuilding the indexes we do not recompile the
>> stored procedure. My experience has been that after rebuiling the
>> indexes we ususlly notice a performance gain right away however I was
>> wondering if it is also necessary to recompile all our stored
>> procedures after the indexes are rebuilt. What is the best practise?
>> Thanks
>

re-Index advice

We periodically rebuild our indexes when we notice defragmentation of
indexes however after rebuilding the indexes we do not recompile the
stored procedure. My experience has been that after rebuiling the
indexes we ususlly notice a performance gain right away however I was
wondering if it is also necessary to recompile all our stored
procedures after the indexes are rebuilt. What is the best practise?
ThanksWhen you reindex an index it will by default update the stats which
invalidate the plans for anything that references them. That in turn will
force the recompile of the plan the next time it is used. So in short you do
not need to recompile after an index rebuild as it is already done for you.
Andrew J. Kelly SQL MVP
"shub" <shubtech@.gmail.com> wrote in message
news:1154619914.120880.28460@.b28g2000cwb.googlegroups.com...
> We periodically rebuild our indexes when we notice defragmentation of
> indexes however after rebuilding the indexes we do not recompile the
> stored procedure. My experience has been that after rebuiling the
> indexes we ususlly notice a performance gain right away however I was
> wondering if it is also necessary to recompile all our stored
> procedures after the indexes are rebuilt. What is the best practise?
> Thanks
>|||shub wrote:
> We periodically rebuild our indexes when we notice defragmentation of
> indexes however after rebuilding the indexes we do not recompile the
> stored procedure. My experience has been that after rebuiling the
> indexes we ususlly notice a performance gain right away however I was
> wondering if it is also necessary to recompile all our stored
> procedures after the indexes are rebuilt. What is the best practise?
> Thanks
>
Defragging the indexes shouldn't require recompiling anything, unless
the indexes were so badly fragmented that the optimize was ignoring them
and using a sub-optimal execution plan. I personally have never had to
do this.
You don't say how you "notice" fragmentation, but you might consider an
automated approach that runs nightly or weekly. See my site for one
possible method:
http://realsqlguy.com/twiki/bin/vie...IndexesAsNeeded
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks Andrew that makes sense. What would be the best way to check to
make sure that the stored proc's are indeed getting recompiled?
Thanks
shub wrote:
> We periodically rebuild our indexes when we notice defragmentation of
> indexes however after rebuilding the indexes we do not recompile the
> stored procedure. My experience has been that after rebuiling the
> indexes we ususlly notice a performance gain right away however I was
> wondering if it is also necessary to recompile all our stored
> procedures after the indexes are rebuilt. What is the best practise?
> Thanks|||Well it is not something one generally has to check on. If you know the
stats are getting updated (you can see with DBCC SHOWSTTISTICS) then you can
count on any plans that reference those stats to get recompiled the next
time they are run. This is true in all cases unless you have used the KEEP
PLAN or KEEP FIXED PLAN hints in the query.
Andrew J. Kelly SQL MVP
"shub" <shubtech@.gmail.com> wrote in message
news:1154694889.877119.181210@.p79g2000cwp.googlegroups.com...
> Thanks Andrew that makes sense. What would be the best way to check to
> make sure that the stored proc's are indeed getting recompiled?
> Thanks
>
> shub wrote:
>