As part of my previous thread on migrating the publisher to a new server, I
had scripted the drop and create replication scripts and I have around 100
articles and a few subscribers and what i want to do is to create the trans
replication with the "nosync" option as i ensured that the publisher and
subscribers are in sync.
So what i noticed is that although the drop replication runs in a min or 2,
the create runs for almost 30 mins
And looking into the script, I see that for each article,
it first runs
1) sp_add_article
and then for every subscriber runs
1) sp_addsubscription
2) sp_addpushsubscription_agent
and it runs all this in serial.
So the question is, can i somehow speed this up ? Is there a way to run
sp_addsubscription that say run it for "all" articles
Also I noticed that sp_addpushsubscription_agent just run the same set of
parameters except the subscriber name is different and it repeats it every
time for every article
Can someone please let me know if I could somehow avoid those default
scripted sql and make this all work at the end faster with less stored
procs?
I am using SQL 2005
Thanks
Replication puts schema locks on tables momentarily as part of the
publication creation process. You will get much faster publication creation
times if you kick all your users out of the database before creating the
publication(s).
http://www.zetainteractive.com - Shift Happens!
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
"Hassan" <hassan@.hotmail.com> wrote in message
news:eMmCKuUPIHA.4440@.TK2MSFTNGP06.phx.gbl...
> As part of my previous thread on migrating the publisher to a new server,
> I had scripted the drop and create replication scripts and I have around
> 100 articles and a few subscribers and what i want to do is to create the
> trans replication with the "nosync" option as i ensured that the publisher
> and subscribers are in sync.
> So what i noticed is that although the drop replication runs in a min or
> 2, the create runs for almost 30 mins
> And looking into the script, I see that for each article,
> it first runs
> 1) sp_add_article
> and then for every subscriber runs
> 1) sp_addsubscription
> 2) sp_addpushsubscription_agent
> and it runs all this in serial.
> So the question is, can i somehow speed this up ? Is there a way to run
> sp_addsubscription that say run it for "all" articles
> Also I noticed that sp_addpushsubscription_agent just run the same set of
> parameters except the subscriber name is different and it repeats it every
> time for every article
> Can someone please let me know if I could somehow avoid those default
> scripted sql and make this all work at the end faster with less stored
> procs?
> I am using SQL 2005
> Thanks
>
|||So what i tried yesterday and would like to validate with the experts here
is I first ran the sp_addarticle for each and every article
and then ran once for every subscriber the following 2 stored procs
sp_addsubscription -- I specified all articles here
sp_addpushsubscription_agent
If you script replication the above 2 stored procs runs for every article,
hence has 1000 more calls to these sprocs as opposed to what i did above and
run it once for every subscriber
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eYEdzKaPIHA.1756@.TK2MSFTNGP06.phx.gbl...
> Replication puts schema locks on tables momentarily as part of the
> publication creation process. You will get much faster publication
> creation times if you kick all your users out of the database before
> creating the publication(s).
> --
> http://www.zetainteractive.com - Shift Happens!
> 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
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:eMmCKuUPIHA.4440@.TK2MSFTNGP06.phx.gbl...
>
2012年3月20日星期二
2012年3月8日星期四
Any setting that would reduce locking during a SQL script?
I am running an upgrade script to update a database from one release to
another. There are a lot of SQL scripts that run during this process.
While this process is running - there should be no other users accessing the
database.
With this in mind - I am hoping to reduce the amount of locking that occurs.
Since no one else will be accessing the system - I don't need all of the
locks at the granular level (I am fine with table locks). Is there some way
that I could make it so that I get table locks instead of row-level locking?
Thanks in advance.You can specify the TABLOCKX hint to acquire an exclusive table lock. For
example:
INSERT INTO MyTable WITH (TABLOCKX)
SELECT * FROM MyOtherTable WITH (TABLOCKX)
Hope this helps.
Dan Guzman
SQL Server MVP
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:%23yBrPIH4DHA.2380@.TK2MSFTNGP10.phx.gbl...
the
occurs.
way
locking?
I'm not sure whether it causes locks to escalate, but admin work such as
you're performing is one of it's indended usage scenarios.
You can set a db into single_user by using the alter database command, eg:
alter database [dbname] set single_user
Regards,
Greg Linwood
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:%23yBrPIH4DHA.2380@.TK2MSFTNGP10.phx.gbl...
the
occurs.
way
locking?
another. There are a lot of SQL scripts that run during this process.
While this process is running - there should be no other users accessing the
database.
With this in mind - I am hoping to reduce the amount of locking that occurs.
Since no one else will be accessing the system - I don't need all of the
locks at the granular level (I am fine with table locks). Is there some way
that I could make it so that I get table locks instead of row-level locking?
Thanks in advance.You can specify the TABLOCKX hint to acquire an exclusive table lock. For
example:
INSERT INTO MyTable WITH (TABLOCKX)
SELECT * FROM MyOtherTable WITH (TABLOCKX)
Hope this helps.
Dan Guzman
SQL Server MVP
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:%23yBrPIH4DHA.2380@.TK2MSFTNGP10.phx.gbl...
quote:
> I am running an upgrade script to update a database from one release to
> another. There are a lot of SQL scripts that run during this process.
> While this process is running - there should be no other users accessing
the
quote:
> database.
> With this in mind - I am hoping to reduce the amount of locking that
occurs.
quote:
> Since no one else will be accessing the system - I don't need all of the
> locks at the granular level (I am fine with table locks). Is there some
way
quote:
> that I could make it so that I get table locks instead of row-level
locking?
quote:|||One to look at will be setting the database into single user mode.
> Thanks in advance.
>
I'm not sure whether it causes locks to escalate, but admin work such as
you're performing is one of it's indended usage scenarios.
You can set a db into single_user by using the alter database command, eg:
alter database [dbname] set single_user
Regards,
Greg Linwood
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:%23yBrPIH4DHA.2380@.TK2MSFTNGP10.phx.gbl...
quote:
> I am running an upgrade script to update a database from one release to
> another. There are a lot of SQL scripts that run during this process.
> While this process is running - there should be no other users accessing
the
quote:
> database.
> With this in mind - I am hoping to reduce the amount of locking that
occurs.
quote:
> Since no one else will be accessing the system - I don't need all of the
> locks at the granular level (I am fine with table locks). Is there some
way
quote:
> that I could make it so that I get table locks instead of row-level
locking?
quote:
> Thanks in advance.
>
Any setting that would reduce locking during a SQL script?
I am running an upgrade script to update a database from one release to
another. There are a lot of SQL scripts that run during this process.
While this process is running - there should be no other users accessing the
database.
With this in mind - I am hoping to reduce the amount of locking that occurs.
Since no one else will be accessing the system - I don't need all of the
locks at the granular level (I am fine with table locks). Is there some way
that I could make it so that I get table locks instead of row-level locking?
Thanks in advance.You can specify the TABLOCKX hint to acquire an exclusive table lock. For
example:
INSERT INTO MyTable WITH (TABLOCKX)
SELECT * FROM MyOtherTable WITH (TABLOCKX)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:%23yBrPIH4DHA.2380@.TK2MSFTNGP10.phx.gbl...
> I am running an upgrade script to update a database from one release to
> another. There are a lot of SQL scripts that run during this process.
> While this process is running - there should be no other users accessing
the
> database.
> With this in mind - I am hoping to reduce the amount of locking that
occurs.
> Since no one else will be accessing the system - I don't need all of the
> locks at the granular level (I am fine with table locks). Is there some
way
> that I could make it so that I get table locks instead of row-level
locking?
> Thanks in advance.
>|||One to look at will be setting the database into single user mode.
I'm not sure whether it causes locks to escalate, but admin work such as
you're performing is one of it's indended usage scenarios.
You can set a db into single_user by using the alter database command, eg:
alter database [dbname] set single_user
Regards,
Greg Linwood
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:%23yBrPIH4DHA.2380@.TK2MSFTNGP10.phx.gbl...
> I am running an upgrade script to update a database from one release to
> another. There are a lot of SQL scripts that run during this process.
> While this process is running - there should be no other users accessing
the
> database.
> With this in mind - I am hoping to reduce the amount of locking that
occurs.
> Since no one else will be accessing the system - I don't need all of the
> locks at the granular level (I am fine with table locks). Is there some
way
> that I could make it so that I get table locks instead of row-level
locking?
> Thanks in advance.
>
another. There are a lot of SQL scripts that run during this process.
While this process is running - there should be no other users accessing the
database.
With this in mind - I am hoping to reduce the amount of locking that occurs.
Since no one else will be accessing the system - I don't need all of the
locks at the granular level (I am fine with table locks). Is there some way
that I could make it so that I get table locks instead of row-level locking?
Thanks in advance.You can specify the TABLOCKX hint to acquire an exclusive table lock. For
example:
INSERT INTO MyTable WITH (TABLOCKX)
SELECT * FROM MyOtherTable WITH (TABLOCKX)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:%23yBrPIH4DHA.2380@.TK2MSFTNGP10.phx.gbl...
> I am running an upgrade script to update a database from one release to
> another. There are a lot of SQL scripts that run during this process.
> While this process is running - there should be no other users accessing
the
> database.
> With this in mind - I am hoping to reduce the amount of locking that
occurs.
> Since no one else will be accessing the system - I don't need all of the
> locks at the granular level (I am fine with table locks). Is there some
way
> that I could make it so that I get table locks instead of row-level
locking?
> Thanks in advance.
>|||One to look at will be setting the database into single user mode.
I'm not sure whether it causes locks to escalate, but admin work such as
you're performing is one of it's indended usage scenarios.
You can set a db into single_user by using the alter database command, eg:
alter database [dbname] set single_user
Regards,
Greg Linwood
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:%23yBrPIH4DHA.2380@.TK2MSFTNGP10.phx.gbl...
> I am running an upgrade script to update a database from one release to
> another. There are a lot of SQL scripts that run during this process.
> While this process is running - there should be no other users accessing
the
> database.
> With this in mind - I am hoping to reduce the amount of locking that
occurs.
> Since no one else will be accessing the system - I don't need all of the
> locks at the granular level (I am fine with table locks). Is there some
way
> that I could make it so that I get table locks instead of row-level
locking?
> Thanks in advance.
>
Any scripts that will create maintenance plans in SQL 2005
Hi
Using SQL Server 2005 SP1
Are ther any way to create scripts that will recreate the maintenance plans
that are on the server?
We need to rebuild the server with a different collation, so the master,
msdb and model will get recreated.
Also where are the configurations for database mail kept. No scripts to
create them.
Any place that has scripts that can regenerate the data that one requires?
Thanks
You can connect Object Explorer to Integration Services and Export the maint plan to a file. After
the rebuild, import that file, and just re-create the jobs (or script the Agent jobs as well...).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Raji Arulambalam" <rajian0spam@.ebop.gov0t.nz> wrote in message
news:O7P4Kd44GHA.4616@.TK2MSFTNGP05.phx.gbl...
> Hi
> Using SQL Server 2005 SP1
> Are ther any way to create scripts that will recreate the maintenance plans that are on the
> server?
> We need to rebuild the server with a different collation, so the master, msdb and model will get
> recreated.
> Also where are the configurations for database mail kept. No scripts to create them.
> Any place that has scripts that can regenerate the data that one requires?
> Thanks
>
Using SQL Server 2005 SP1
Are ther any way to create scripts that will recreate the maintenance plans
that are on the server?
We need to rebuild the server with a different collation, so the master,
msdb and model will get recreated.
Also where are the configurations for database mail kept. No scripts to
create them.
Any place that has scripts that can regenerate the data that one requires?
Thanks
You can connect Object Explorer to Integration Services and Export the maint plan to a file. After
the rebuild, import that file, and just re-create the jobs (or script the Agent jobs as well...).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Raji Arulambalam" <rajian0spam@.ebop.gov0t.nz> wrote in message
news:O7P4Kd44GHA.4616@.TK2MSFTNGP05.phx.gbl...
> Hi
> Using SQL Server 2005 SP1
> Are ther any way to create scripts that will recreate the maintenance plans that are on the
> server?
> We need to rebuild the server with a different collation, so the master, msdb and model will get
> recreated.
> Also where are the configurations for database mail kept. No scripts to create them.
> Any place that has scripts that can regenerate the data that one requires?
> Thanks
>
Any scripts that will create maintenance plans in SQL 2005
Hi
Using SQL Server 2005 SP1
Are ther any way to create scripts that will recreate the maintenance plans
that are on the server?
We need to rebuild the server with a different collation, so the master,
msdb and model will get recreated.
Also where are the configurations for database mail kept. No scripts to
create them.
Any place that has scripts that can regenerate the data that one requires?
ThanksYou can connect Object Explorer to Integration Services and Export the maint
plan to a file. After
the rebuild, import that file, and just re-create the jobs (or script the Ag
ent jobs as well...).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Raji Arulambalam" <rajian0spam@.ebop.gov0t.nz> wrote in message
news:O7P4Kd44GHA.4616@.TK2MSFTNGP05.phx.gbl...
> Hi
> Using SQL Server 2005 SP1
> Are ther any way to create scripts that will recreate the maintenance plan
s that are on the
> server?
> We need to rebuild the server with a different collation, so the master, m
sdb and model will get
> recreated.
> Also where are the configurations for database mail kept. No scripts to cr
eate them.
> Any place that has scripts that can regenerate the data that one requires?
> Thanks
>
Using SQL Server 2005 SP1
Are ther any way to create scripts that will recreate the maintenance plans
that are on the server?
We need to rebuild the server with a different collation, so the master,
msdb and model will get recreated.
Also where are the configurations for database mail kept. No scripts to
create them.
Any place that has scripts that can regenerate the data that one requires?
ThanksYou can connect Object Explorer to Integration Services and Export the maint
plan to a file. After
the rebuild, import that file, and just re-create the jobs (or script the Ag
ent jobs as well...).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Raji Arulambalam" <rajian0spam@.ebop.gov0t.nz> wrote in message
news:O7P4Kd44GHA.4616@.TK2MSFTNGP05.phx.gbl...
> Hi
> Using SQL Server 2005 SP1
> Are ther any way to create scripts that will recreate the maintenance plan
s that are on the
> server?
> We need to rebuild the server with a different collation, so the master, m
sdb and model will get
> recreated.
> Also where are the configurations for database mail kept. No scripts to cr
eate them.
> Any place that has scripts that can regenerate the data that one requires?
> Thanks
>
Any scripts that will create maintenance plans in SQL 2005
Hi
Using SQL Server 2005 SP1
Are ther any way to create scripts that will recreate the maintenance plans
that are on the server?
We need to rebuild the server with a different collation, so the master,
msdb and model will get recreated.
Also where are the configurations for database mail kept. No scripts to
create them.
Any place that has scripts that can regenerate the data that one requires?
ThanksYou can connect Object Explorer to Integration Services and Export the maint plan to a file. After
the rebuild, import that file, and just re-create the jobs (or script the Agent jobs as well...).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Raji Arulambalam" <rajian0spam@.ebop.gov0t.nz> wrote in message
news:O7P4Kd44GHA.4616@.TK2MSFTNGP05.phx.gbl...
> Hi
> Using SQL Server 2005 SP1
> Are ther any way to create scripts that will recreate the maintenance plans that are on the
> server?
> We need to rebuild the server with a different collation, so the master, msdb and model will get
> recreated.
> Also where are the configurations for database mail kept. No scripts to create them.
> Any place that has scripts that can regenerate the data that one requires?
> Thanks
>
Using SQL Server 2005 SP1
Are ther any way to create scripts that will recreate the maintenance plans
that are on the server?
We need to rebuild the server with a different collation, so the master,
msdb and model will get recreated.
Also where are the configurations for database mail kept. No scripts to
create them.
Any place that has scripts that can regenerate the data that one requires?
ThanksYou can connect Object Explorer to Integration Services and Export the maint plan to a file. After
the rebuild, import that file, and just re-create the jobs (or script the Agent jobs as well...).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Raji Arulambalam" <rajian0spam@.ebop.gov0t.nz> wrote in message
news:O7P4Kd44GHA.4616@.TK2MSFTNGP05.phx.gbl...
> Hi
> Using SQL Server 2005 SP1
> Are ther any way to create scripts that will recreate the maintenance plans that are on the
> server?
> We need to rebuild the server with a different collation, so the master, msdb and model will get
> recreated.
> Also where are the configurations for database mail kept. No scripts to create them.
> Any place that has scripts that can regenerate the data that one requires?
> Thanks
>
订阅：
博文 (Atom)