Can Update Statistics with fullscan cause contention problems?

I would like to run Update Stats with fullscan on our transactional database
but I want to make sure that there will be no issues with concurrency while
this is running. I realize that the elapsed time will be much higher but I
just want to ensure that this doesn't take some locks that will cause issues
with concurrency.
Any comments would be greatly appreciated.
Thanks!
Update stats will not cause blocking, but it can be CPU intensive. For more
info on what type of locks are employed during statistics updation, see:
http://support.microsoft.com/default...b;en-us;195565
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:Os3tkWcZEHA.3988@.tk2msftngp13.phx.gbl...
I would like to run Update Stats with fullscan on our transactional database
but I want to make sure that there will be no issues with concurrency while
this is running. I realize that the elapsed time will be much higher but I
just want to ensure that this doesn't take some locks that will cause issues
with concurrency.
Any comments would be greatly appreciated.
Thanks!

Can Update Statistics with fullscan cause contention problems?

I would like to run Update Stats with fullscan on our transactional database
but I want to make sure that there will be no issues with concurrency while
this is running. I realize that the elapsed time will be much higher but I
just want to ensure that this doesn't take some locks that will cause issues
with concurrency.
Any comments would be greatly appreciated.
Thanks!Update stats will not cause blocking, but it can be CPU intensive. For more
info on what type of locks are employed during statistics updation, see:
http://support.microsoft.com/default.aspx?scid=kb;en-us;195565
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:Os3tkWcZEHA.3988@.tk2msftngp13.phx.gbl...
I would like to run Update Stats with fullscan on our transactional database
but I want to make sure that there will be no issues with concurrency while
this is running. I realize that the elapsed time will be much higher but I
just want to ensure that this doesn't take some locks that will cause issues
with concurrency.
Any comments would be greatly appreciated.
Thanks!

Can sp_changearticle help when needing to change the primary key?

We need to add a new column to a table that is part of a multi-table
publication replicated via transactional replication. We've read (and
re-read) the sp_changearticle page in Books Online without fully
understanding how we might be able to use this command to help with our task.
Can someone please provide two important answers -- 1) can we use
sp_changearticle to make this kind of change to our publication? 2) can you
offer an example of how sp_changearticle is coded for such purposes.
Actually, we'd be interested in seeing how sp_changearticle is coded in
general, even if it cannot be used for our particular task.
Thanks,
Barry Spiegel
barry.spiegel@.eds.com
2) sp_changearticle 'pubs', 'jobs','description','this is the new
description'
1) no, you use use sp_repladdcolumn like this:
sp_repladdcolumn 'jobs','intcol','int not null default(1)'
This column will be modified in all publications and their subscribers.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Now available on Amazon.com
http://www.amazon.com/gp/product/off...?condition=all
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Barry Spiegel" <Barry Spiegel@.discussions.microsoft.com> wrote in message
news:732D9DB5-C5A6-4A65-AEB7-37384C791401@.microsoft.com...
> We need to add a new column to a table that is part of a multi-table
> publication replicated via transactional replication. We've read (and
> re-read) the sp_changearticle page in Books Online without fully
> understanding how we might be able to use this command to help with our
task.
> Can someone please provide two important answers -- 1) can we use
> sp_changearticle to make this kind of change to our publication? 2) can
you
> offer an example of how sp_changearticle is coded for such purposes.
> Actually, we'd be interested in seeing how sp_changearticle is coded in
> general, even if it cannot be used for our particular task.
> Thanks,
> Barry Spiegel
> barry.spiegel@.eds.com
>

can only set up snapshot & merge replication

Hi ,
I found that i could only set up a db in the server as either a
snapshot/merger replication with the transactional replication greyed out
any reason why i am not able to make it a transactional replication ?
tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...ation/200512/1
are you running msde?
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
"maxzsim via droptable.com" <u14644@.uwe> wrote in message
news:587958988abb4@.uwe...
> Hi ,
> I found that i could only set up a db in the server as either a
> snapshot/merger replication with the transactional replication greyed out
>
> any reason why i am not able to make it a transactional replication ?
> tks & rdgs
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...ation/200512/1
|||... or personal edition?
Paul Ibison
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23lC3Vey%23FHA.3068@.TK2MSFTNGP09.phx.gbl...
> are you running msde?
> --
> 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
> "maxzsim via droptable.com" <u14644@.uwe> wrote in message
> news:587958988abb4@.uwe...
>
|||Hi ,
i am running sql server 2000 standard edition
rdgs
Paul Ibison wrote:[vbcol=seagreen]
>... or personal edition?
>Paul Ibison
>[quoted text clipped - 6 lines]
Message posted via http://www.droptable.com
|||Is there an error when you run this:
sp_dboption @.dbname = 'database'
, @.optname = 'published'
, @.optvalue = 'true'
Or are you possibly referring to the articles being greyed out (this is
because of a lack of a Primary key).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul,
there's no error and i got "Published" returned
tks & rdgs
Paul Ibison wrote:
>Is there an error when you run this:
>sp_dboption @.dbname = 'database'
> , @.optname = 'published'
> , @.optvalue = 'true'
>Or are you possibly referring to the articles being greyed out (this is
>because of a lack of a Primary key).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
>http://www.nwsu.com/0974973602p.html)
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...ation/200512/1
|||Please can you run the following code in your database. All you need to do
is a find and replace - change Region to a table name that has a PK. If
there are any messages, please post them back.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
-- Adding the transactional publication
exec sp_addpublication @.publication = N'TestPublication', @.restricted =
N'false', @.sync_method = N'native', @.repl_freq = N'continuous', @.description
= 'test', @.status = N'active', @.allow_push = N'true', @.allow_pull = N'true',
@.allow_anonymous = N'false', @.enabled_for_internet = N'false',
@.independent_agent = N'false', @.immediate_sync = N'false', @.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'TestPI-4'
exec sp_addpublication_snapshot @.publication =
N'TestPublication',@.frequency_type = 4, @.frequency_interval = 1,
@.frequency_relative_interval = 0, @.frequency_recurrence_factor = 1,
@.frequency_subday = 1, @.frequency_subday_interval = 0, @.active_start_date =
0, @.active_end_date = 0, @.active_start_time_of_day = 230000,
@.active_end_time_of_day = 0, @.snapshot_job_name =
N'TestPI-TestPublication-11'
GO
exec sp_grant_publication_access @.publication = N'TestPublication', @.login =
N'BUILTIN\Administrators'
GO
exec sp_grant_publication_access @.publication = N'TestPublication', @.login =
N'distributor_admin'
GO
exec sp_grant_publication_access @.publication = N'TestPublication', @.login =
N'sa'
GO
-- Adding the transactional articles
exec sp_addarticle @.publication = N'TestPublication', @.article = N'Region',
@.source_owner = N'dbo', @.source_object = N'Region', @.destination_table =
N'Region', @.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_Region', @.del_cmd = N'CALL sp_MSdel_Region', @.upd_cmd = N'MCALL
sp_MSupd_Region', @.filter = null, @.sync_object = null, @.auto_identity_range
= N'false'
GO
|||Hi Paul , when i ran ur script against the db that i want to create the
publication i got the following.
i am not sure abt the part that this edition does not support because i am
using the standard edition or how can i check the edition of my SQL Server ?
could be service pack issue ?
================================================== =====
Server: Msg 21108, Level 16, State 1, Procedure sp_addpublication, Line 275
This edition of SQL Server does not support transactional publications.
Server: Msg 15001, Level 11, State 1, Procedure sp_addpublication_snapshot,
Line 117
Object 'TestPublication' does not exist or is not a valid object for this
operation.
Server: Msg 20026, Level 16, State 1, Procedure sp_MSpublication_access, Line
42
The publication 'TestPublication' does not exist.
Server: Msg 20026, Level 16, State 1, Procedure sp_MSpublication_access, Line
42
The publication 'TestPublication' does not exist.
Server: Msg 20026, Level 16, State 1, Procedure sp_MSpublication_access, Line
42
The publication 'TestPublication' does not exist.
Server: Msg 14027, Level 11, State 1, Procedure sp_addarticle, Line 478
TestPublication does not exist in the current database.
================================================== =====
tks & rdgs
Paul Ibison wrote:
>Please can you run the following code in your database. All you need to do
>is a find and replace - change Region to a table name that has a PK. If
>there are any messages, please post them back.
>Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
>http://www.nwsu.com/0974973602p.html)
>-- Adding the transactional publication
>exec sp_addpublication @.publication = N'TestPublication', @.restricted =
>N'false', @.sync_method = N'native', @.repl_freq = N'continuous', @.description
>= 'test', @.status = N'active', @.allow_push = N'true', @.allow_pull = N'true',
>@.allow_anonymous = N'false', @.enabled_for_internet = N'false',
>@.independent_agent = N'false', @.immediate_sync = N'false', @.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'TestPI-4'
>exec sp_addpublication_snapshot @.publication =
>N'TestPublication',@.frequency_type = 4, @.frequency_interval = 1,
>@.frequency_relative_interval = 0, @.frequency_recurrence_factor = 1,
>@.frequency_subday = 1, @.frequency_subday_interval = 0, @.active_start_date =
>0, @.active_end_date = 0, @.active_start_time_of_day = 230000,
>@.active_end_time_of_day = 0, @.snapshot_job_name =
>N'TestPI-TestPublication-11'
>GO
>exec sp_grant_publication_access @.publication = N'TestPublication', @.login =
>N'BUILTIN\Administrators'
>GO
>exec sp_grant_publication_access @.publication = N'TestPublication', @.login =
>N'distributor_admin'
>GO
>exec sp_grant_publication_access @.publication = N'TestPublication', @.login =
>N'sa'
>GO
>-- Adding the transactional articles
>exec sp_addarticle @.publication = N'TestPublication', @.article = N'Region',
>@.source_owner = N'dbo', @.source_object = N'Region', @.destination_table =
>N'Region', @.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_Region', @.del_cmd = N'CALL sp_MSdel_Region', @.upd_cmd = N'MCALL
>sp_MSupd_Region', @.filter = null, @.sync_object = null, @.auto_identity_range
>= N'false'
>GO
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...ation/200512/1
|||Hi
i am not too sure where to check the edition of the SQL server but below is
the version of the SQL from "SELECT @.@.version"
Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 2)
tks & rdgs
maxzsim wrote:[vbcol=seagreen]
>Hi Paul , when i ran ur script against the db that i want to create the
>publication i got the following.
>i am not sure abt the part that this edition does not support because i am
>using the standard edition or how can i check the edition of my SQL Server ?
>could be service pack issue ?
>================================================= ======
>Server: Msg 21108, Level 16, State 1, Procedure sp_addpublication, Line 275
>This edition of SQL Server does not support transactional publications.
>Server: Msg 15001, Level 11, State 1, Procedure sp_addpublication_snapshot,
>Line 117
>Object 'TestPublication' does not exist or is not a valid object for this
>operation.
>Server: Msg 20026, Level 16, State 1, Procedure sp_MSpublication_access, Line
>42
>The publication 'TestPublication' does not exist.
>Server: Msg 20026, Level 16, State 1, Procedure sp_MSpublication_access, Line
>42
>The publication 'TestPublication' does not exist.
>Server: Msg 20026, Level 16, State 1, Procedure sp_MSpublication_access, Line
>42
>The publication 'TestPublication' does not exist.
>Server: Msg 14027, Level 11, State 1, Procedure sp_addarticle, Line 478
>TestPublication does not exist in the current database.
>================================================= ======
>tks & rdgs
>[quoted text clipped - 45 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...ation/200512/1
|||You're using MSDE (not 'Standard').
You'll need to change edition of SQL Server.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)