Holy hordes of transactional data Batman

I did the DBCC Shrinkfile and I get an error saying that
some part of the file is in use and cannot continue.
Is this DB suppost to be off line ?

>--Original Message--
>I have a LOG file of 113Gig with only 29 Gig of free
space
>available on the drive, I have no media that will hold
>this as a backup.
>I ran the "Backup Log" "with_truncate" and "no_truncate"
>but nothing seems to be making this thing disappear.
>What is my next step to dissolving this beast?
>.
>
No, but if you have active transactions, you'll have to wait.
http://www.aspfaq.com/
(Reverse address to reply.)
"Vidro" <Vid_ro@.myalias.postalias.com> wrote in message
news:a49d01c4d71c$5a1db910$a401280a@.phx.gbl...[vbcol=seagreen]
> I did the DBCC Shrinkfile and I get an error saying that
> some part of the file is in use and cannot continue.
> Is this DB suppost to be off line ?
> space

Holy hordes of transactional data Batman

I have a LOG file of 113Gig with only 29 Gig of free space
available on the drive, I have no media that will hold
this as a backup.
I ran the "Backup Log" "with_truncate" and "no_truncate"
but nothing seems to be making this thing disappear.
What is my next step to dissolving this beast?
Truncating the log only marks space within the log as reusable. It does not
affect the physical size of the file.
Please read about DBCC SHRINKFILE to actually shrink the log file.
Also, always tell us what version you are using, and there are some specific
guidelines for shrinking that we can give you depending on the version.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"VidRo" <vid_ro@.myalias.postalias.com> wrote in message
news:a80301c4d65d$96f2be90$a601280a@.phx.gbl...
>I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?
|||To shrink it you should use DBCC SHRINKFILE().
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
Andrew J. Kelly SQL MVP
"VidRo" <vid_ro@.myalias.postalias.com> wrote in message
news:a80301c4d65d$96f2be90$a601280a@.phx.gbl...
>I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?
|||http://www.aspfaq.com/2471
http://www.aspfaq.com/
(Reverse address to reply.)
"VidRo" <vid_ro@.myalias.postalias.com> wrote in message
news:a80301c4d65d$96f2be90$a601280a@.phx.gbl...
> I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?
|||test
"Kalen Delaney" wrote:

> Truncating the log only marks space within the log as reusable. It does not
> affect the physical size of the file.
> Please read about DBCC SHRINKFILE to actually shrink the log file.
> Also, always tell us what version you are using, and there are some specific
> guidelines for shrinking that we can give you depending on the version.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "VidRo" <vid_ro@.myalias.postalias.com> wrote in message
> news:a80301c4d65d$96f2be90$a601280a@.phx.gbl...
>
>
|||DBCC shrinkdatabase or DBCC shrinkfile will do it. If you want to prevent
from reoccuring, set up maintenance plan and check appropriate selctions
(Optimization tab).
"VidRo" wrote:

> I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?
>
|||DBCC shrinkdatabase or DBCC shrinkfile will do it; however if you want to
prevent it from reoccuring, set up maintenance plan properly (Optimization
tab).
"VidRo" wrote:

> I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?
>
|||Hi Adam
I suggested DBCC SHRINKFILE because you can control the files to be
affected. DBCC SHRINKDATABASE will shrink all the files. Shrinking and data
file is a very different operation than shrinking a log file, and it is NOT
something to be done on a regular basis. The overhead is far too high. I
would not recommended putting a shrink of the whole database in any kind of
regular maintenance plan.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Locus Adam" <Locus Adam@.discussions.microsoft.com> wrote in message
news:5132814B-3EEE-4AC7-A8DB-4588ADAE5D87@.microsoft.com...[vbcol=seagreen]
> DBCC shrinkdatabase or DBCC shrinkfile will do it. If you want to prevent
> from reoccuring, set up maintenance plan and check appropriate selctions
> (Optimization tab).
> "VidRo" wrote:

Holy hordes of transactional data Batman

I did the DBCC Shrinkfile and I get an error saying that
some part of the file is in use and cannot continue.
Is this DB suppost to be off line ?

>--Original Message--
>I have a LOG file of 113Gig with only 29 Gig of free
space
>available on the drive, I have no media that will hold
>this as a backup.
>I ran the "Backup Log" "with_truncate" and "no_truncate"
>but nothing seems to be making this thing disappear.
>What is my next step to dissolving this beast?
>.
>No, but if you have active transactions, you'll have to wait.
http://www.aspfaq.com/
(Reverse address to reply.)
"Vidro" <Vid_ro@.myalias.postalias.com> wrote in message
news:a49d01c4d71c$5a1db910$a401280a@.phx.gbl...[vbcol=seagreen]
> I did the DBCC Shrinkfile and I get an error saying that
> some part of the file is in use and cannot continue.
> Is this DB suppost to be off line ?
>
> space

Holy hordes of transactional data Batman

I have a LOG file of 113Gig with only 29 Gig of free space
available on the drive, I have no media that will hold
this as a backup.
I ran the "Backup Log" "with_truncate" and "no_truncate"
but nothing seems to be making this thing disappear.
What is my next step to dissolving this beast?Truncating the log only marks space within the log as reusable. It does not
affect the physical size of the file.
Please read about DBCC SHRINKFILE to actually shrink the log file.
Also, always tell us what version you are using, and there are some specific
guidelines for shrinking that we can give you depending on the version.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"VidRo" <vid_ro@.myalias.postalias.com> wrote in message
news:a80301c4d65d$96f2be90$a601280a@.phx.gbl...
>I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?|||To shrink it you should use DBCC SHRINKFILE().
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
Andrew J. Kelly SQL MVP
"VidRo" <vid_ro@.myalias.postalias.com> wrote in message
news:a80301c4d65d$96f2be90$a601280a@.phx.gbl...
>I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?|||http://www.aspfaq.com/2471
http://www.aspfaq.com/
(Reverse address to reply.)
"VidRo" <vid_ro@.myalias.postalias.com> wrote in message
news:a80301c4d65d$96f2be90$a601280a@.phx.gbl...
> I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?|||test
"Kalen Delaney" wrote:

> Truncating the log only marks space within the log as reusable. It does no
t
> affect the physical size of the file.
> Please read about DBCC SHRINKFILE to actually shrink the log file.
> Also, always tell us what version you are using, and there are some specif
ic
> guidelines for shrinking that we can give you depending on the version.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "VidRo" <vid_ro@.myalias.postalias.com> wrote in message
> news:a80301c4d65d$96f2be90$a601280a@.phx.gbl...
>
>|||DBCC shrinkdatabase or DBCC shrinkfile will do it. If you want to prevent
from reoccuring, set up maintenance plan and check appropriate selctions
(Optimization tab).
"VidRo" wrote:

> I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?
>|||DBCC shrinkdatabase or DBCC shrinkfile will do it; however if you want to
prevent it from reoccuring, set up maintenance plan properly (Optimization
tab).
"VidRo" wrote:

> I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?
>|||Hi Adam
I suggested DBCC SHRINKFILE because you can control the files to be
affected. DBCC SHRINKDATABASE will shrink all the files. Shrinking and data
file is a very different operation than shrinking a log file, and it is NOT
something to be done on a regular basis. The overhead is far too high. I
would not recommended putting a shrink of the whole database in any kind of
regular maintenance plan.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Locus Adam" <Locus Adam@.discussions.microsoft.com> wrote in message
news:5132814B-3EEE-4AC7-A8DB-4588ADAE5D87@.microsoft.com...[vbcol=seagreen]
> DBCC shrinkdatabase or DBCC shrinkfile will do it. If you want to prevent
> from reoccuring, set up maintenance plan and check appropriate selctions
> (Optimization tab).
> "VidRo" wrote:
>sql

High-transaction replication

We are trying to set up replication of a large (over 200GB) database,
attempting to break the transactional processing and reporting apart,
obviously replicating from the processing database. (This is a temporary fix
until we can implement our data warehouse.)
There are approximately 70 tables that will need to be replicated for this
to work for us, and I have broken these tables into logical grouping by
publication. We ran into some errors initially ("The process could not
execute 'sp_replcmds'..." errors), and found some references to assist with
this.
The next issue we have run into and are struggling with now are errors in
the log reader stating "The process could not execute
'sp_MSadd_repl_commands27hp'..." The descriptions reference that there was
deadlocking and the logreader was killed. The Distribution cleanup agent
appears to be the other process that was locking the table(s), with the job
taking up to 58 seconds to complete (48k transactions and 60k statements
deleted). I took and modified the schedule of the cleanup job to run every
minute between 6 and 6, and every 5 minutes during the night - thinking there
would be fewer transactions to delete, and allowing the table to be locked
for a shorter period of time. My thinking was correct, but the deadlocking
still happens, but recovers quicker than previously.
The worry we have is that we have not created the publications for all the
tables, and are already sending over 500k transactions/hour through. Once we
get all the tables back up and going we are projecting about another 500k
transactions, and are worried about the deadlocking. Are there any
ideas/settings we should be looking at to enable SQL to handle transactional
replication of 1 million+ transactions an hour, or are we really looking for
SQL to do what it cannot handle?
Thanks in advance for any ideas!
You should migrate to a remote distributor. You should also look at
replicating the execution of stored procedures. This will improve the
performance of your solution dramatically.
I would also look at decreasing your transaction retention period to perhaps
a day.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Rob C" <RobC@.discussions.microsoft.com> wrote in message
news:1A4C62BB-A2E9-445D-8FE7-A9339E2EC209@.microsoft.com...
> We are trying to set up replication of a large (over 200GB) database,
> attempting to break the transactional processing and reporting apart,
> obviously replicating from the processing database. (This is a temporary
fix
> until we can implement our data warehouse.)
> There are approximately 70 tables that will need to be replicated for this
> to work for us, and I have broken these tables into logical grouping by
> publication. We ran into some errors initially ("The process could not
> execute 'sp_replcmds'..." errors), and found some references to assist
with
> this.
> The next issue we have run into and are struggling with now are errors in
> the log reader stating "The process could not execute
> 'sp_MSadd_repl_commands27hp'..." The descriptions reference that there
was
> deadlocking and the logreader was killed. The Distribution cleanup agent
> appears to be the other process that was locking the table(s), with the
job
> taking up to 58 seconds to complete (48k transactions and 60k statements
> deleted). I took and modified the schedule of the cleanup job to run
every
> minute between 6 and 6, and every 5 minutes during the night - thinking
there
> would be fewer transactions to delete, and allowing the table to be locked
> for a shorter period of time. My thinking was correct, but the
deadlocking
> still happens, but recovers quicker than previously.
> The worry we have is that we have not created the publications for all the
> tables, and are already sending over 500k transactions/hour through. Once
we
> get all the tables back up and going we are projecting about another 500k
> transactions, and are worried about the deadlocking. Are there any
> ideas/settings we should be looking at to enable SQL to handle
transactional
> replication of 1 million+ transactions an hour, or are we really looking
for
> SQL to do what it cannot handle?
> Thanks in advance for any ideas!
|||You can also take a look the Microsoft White Paper Replication Performance
Tuning. I have found some very interesting results with much larger databases
than that with implementing some of the modifications in there particulairly
the (-maxcmdsintran ) switch in the log reader agent and creating a
performance profile for the distribution agent. I can give you examples or
better documentation if you require. Please email - Richard.Hale@.MM-Games.com
with any questions
"Rob C" wrote:

> We are trying to set up replication of a large (over 200GB) database,
> attempting to break the transactional processing and reporting apart,
> obviously replicating from the processing database. (This is a temporary fix
> until we can implement our data warehouse.)
> There are approximately 70 tables that will need to be replicated for this
> to work for us, and I have broken these tables into logical grouping by
> publication. We ran into some errors initially ("The process could not
> execute 'sp_replcmds'..." errors), and found some references to assist with
> this.
> The next issue we have run into and are struggling with now are errors in
> the log reader stating "The process could not execute
> 'sp_MSadd_repl_commands27hp'..." The descriptions reference that there was
> deadlocking and the logreader was killed. The Distribution cleanup agent
> appears to be the other process that was locking the table(s), with the job
> taking up to 58 seconds to complete (48k transactions and 60k statements
> deleted). I took and modified the schedule of the cleanup job to run every
> minute between 6 and 6, and every 5 minutes during the night - thinking there
> would be fewer transactions to delete, and allowing the table to be locked
> for a shorter period of time. My thinking was correct, but the deadlocking
> still happens, but recovers quicker than previously.
> The worry we have is that we have not created the publications for all the
> tables, and are already sending over 500k transactions/hour through. Once we
> get all the tables back up and going we are projecting about another 500k
> transactions, and are worried about the deadlocking. Are there any
> ideas/settings we should be looking at to enable SQL to handle transactional
> replication of 1 million+ transactions an hour, or are we really looking for
> SQL to do what it cannot handle?
> Thanks in advance for any ideas!

High CPU utilization

Could anyone help me in finding out why the cpu utilization is very high?

I have two servers say, Server A , server B. There is a transactional replication going on from server A to B

There is a table say Table A on server A, which is being replicated to server B.

I created a trigger insert and update trigger on Table A on server B (i.e. on subscriber). Since then, the CPU utilization for server B is very high 80-90%

when i used profiler, i could see .whenever replication stored proc for insert or update executes..cpu utilization goes up..

trigger just insert the updated/inserted rows into some other table.

Could anyone tell me why the cpu utilization has gone up so much? i am using sql server 2005

thanx

If you see spiked CPU usage after adding the trigger, then it's obvious the trigger is the problem. Trigger does add overhead, how much depends on what the trigger does. Also, what was CPU utilization before trigger, 0%? 50%? Are you doing any queries inside the trigger? If so you may want to look at the plans to see if your queries are optimized, in profiler you should also be able to look at reads, writes, cpu, duration for each statement as well.

High CPU utilization

Could anyone help me in finding out why the cpu utilization is very
high'
I have two servers say, Server A , server B. There is a transactional
replication going on from server A to B
There is a table say Table A on server A, which is being replicated to
server B.
I created a trigger insert and update trigger on Table A on server B
(i.e. on subscriber). Since then, the CPU utilization for server B is
very high 80-90%
when i used profiler, i could see .whenever replication stored proc for
insert or update executes..cpu utilization goes up..
trigger just insert the updated/inserted rows into some other table.
Could anyone tell me why the cpu utilization has gone up so much' Does
Creating trigger on the subscriber cause performance problem' i am
using sql server 2005
thanxVishal
> trigger just insert the updated/inserted rows into some other table.
> Could anyone tell me why the cpu utilization has gone up so much' Does
> Creating trigger on the subscriber cause performance problem' i am
> using sql server 2005
Yes it is
http://www.sql-server-performance.com/performance_monitor_counters.asp
"Vishal" <rvishal1305@.gmail.com> wrote in message
news:1161069980.591285.61830@.b28g2000cwb.googlegroups.com...
> Could anyone help me in finding out why the cpu utilization is very
> high'
> I have two servers say, Server A , server B. There is a transactional
> replication going on from server A to B
> There is a table say Table A on server A, which is being replicated to
> server B.
> I created a trigger insert and update trigger on Table A on server B
> (i.e. on subscriber). Since then, the CPU utilization for server B is
> very high 80-90%
> when i used profiler, i could see .whenever replication stored proc for
> insert or update executes..cpu utilization goes up..
> trigger just insert the updated/inserted rows into some other table.
> Could anyone tell me why the cpu utilization has gone up so much' Does
> Creating trigger on the subscriber cause performance problem' i am
> using sql server 2005
> thanx
>|||What is this trigger doing? Frequently when you want a trigger fired as part
of a replication process you should place the triggering action in a
different table. So if you want the trigger fired when you have DML against
table a, and the trigger writes to table b, you get better performance by
creating a separate article which writes to table b, and encapsulate the
trigger logic in the stored procedure.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Vishal" <rvishal1305@.gmail.com> wrote in message
news:1161069980.591285.61830@.b28g2000cwb.googlegroups.com...
> Could anyone help me in finding out why the cpu utilization is very
> high'
> I have two servers say, Server A , server B. There is a transactional
> replication going on from server A to B
> There is a table say Table A on server A, which is being replicated to
> server B.
> I created a trigger insert and update trigger on Table A on server B
> (i.e. on subscriber). Since then, the CPU utilization for server B is
> very high 80-90%
> when i used profiler, i could see .whenever replication stored proc for
> insert or update executes..cpu utilization goes up..
> trigger just insert the updated/inserted rows into some other table.
> Could anyone tell me why the cpu utilization has gone up so much' Does
> Creating trigger on the subscriber cause performance problem' i am
> using sql server 2005
> thanx
>|||Thank you for your reply.
Let me explain you lil further. What i have is a server A and Server B.
On server A I have a database say dbA which is being replicated to
server B using transactional replication. In dbA, i have a table say
table A which is being replicated to server B (ReplTableA) .
Now I am interested in tracking the changes occring to the records of
table A. So whenever there is an insert in table A, I want to insert
that record in history table. Whenever there is an update in table A, I
want to insert that updated record in the history table. So for this i
am using a trigger. but instead of creating a trigger on table A on dbA
(on server A), I am creating a trigger on the replicated
table(ReplTableA) on server B (I thoght this will improve the
performance on server A) . So whenever replication procedure (for
insert & update for ReplTableA) executes, the trigger fires.
Beacause of this cpu utilization of server B has gone up so much (80-90
%)
So is it good thing to write a trigger on the ReplTableA or should i
write a trigger on the original table Table A on server A?
Or is there any thing i can do to reduce the CPU utilization? Could you
please elaborate on creating a seperate artical and encapsulating the
trigger logic in a stored proc ?
Thanks
--Vishal
Hilary Cotter wrote:
> What is this trigger doing? Frequently when you want a trigger fired as part
> of a replication process you should place the triggering action in a
> different table. So if you want the trigger fired when you have DML against
> table a, and the trigger writes to table b, you get better performance by
> creating a separate article which writes to table b, and encapsulate the
> trigger logic in the stored procedure.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "Vishal" <rvishal1305@.gmail.com> wrote in message
> news:1161069980.591285.61830@.b28g2000cwb.googlegroups.com...
> > Could anyone help me in finding out why the cpu utilization is very
> > high'
> >
> > I have two servers say, Server A , server B. There is a transactional
> > replication going on from server A to B
> >
> > There is a table say Table A on server A, which is being replicated to
> > server B.
> >
> > I created a trigger insert and update trigger on Table A on server B
> > (i.e. on subscriber). Since then, the CPU utilization for server B is
> > very high 80-90%
> >
> > when i used profiler, i could see .whenever replication stored proc for
> > insert or update executes..cpu utilization goes up..
> >
> > trigger just insert the updated/inserted rows into some other table.
> >
> > Could anyone tell me why the cpu utilization has gone up so much' Does
> > Creating trigger on the subscriber cause performance problem' i am
> > using sql server 2005
> >
> > thanx
> >|||On 17 Oct 2006 10:12:13 -0700, "Vishal" <rvishal1305@.gmail.com> wrote:
>So is it good thing to write a trigger on the ReplTableA or should i
>write a trigger on the original table Table A on server A?
Should be OK on serverB.
>Or is there any thing i can do to reduce the CPU utilization? Could you
>please elaborate on creating a seperate artical and encapsulating the
>trigger logic in a stored proc ?
Aha, he seems to be suggesting using a redundant replication article
instead of a trigger. Elegant idea!
But really, the trigger should work, too.
Can you post the SQL for the trigger and the DDL for the table and
indexes? Odds are it's just a missing index or something along those
lines.
J.
>Thanks
>--Vishal
>
>Hilary Cotter wrote:
>> What is this trigger doing? Frequently when you want a trigger fired as part
>> of a replication process you should place the triggering action in a
>> different table. So if you want the trigger fired when you have DML against
>> table a, and the trigger writes to table b, you get better performance by
>> creating a separate article which writes to table b, and encapsulate the
>> trigger logic in the stored procedure.
>> --
>> Hilary Cotter
>> Director of Text Mining and Database Strategy
>> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
>> This posting is my own and doesn't necessarily represent RelevantNoise's
>> positions, strategies or opinions.
>> 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
>>
>> "Vishal" <rvishal1305@.gmail.com> wrote in message
>> news:1161069980.591285.61830@.b28g2000cwb.googlegroups.com...
>> > Could anyone help me in finding out why the cpu utilization is very
>> > high'
>> >
>> > I have two servers say, Server A , server B. There is a transactional
>> > replication going on from server A to B
>> >
>> > There is a table say Table A on server A, which is being replicated to
>> > server B.
>> >
>> > I created a trigger insert and update trigger on Table A on server B
>> > (i.e. on subscriber). Since then, the CPU utilization for server B is
>> > very high 80-90%
>> >
>> > when i used profiler, i could see .whenever replication stored proc for
>> > insert or update executes..cpu utilization goes up..
>> >
>> > trigger just insert the updated/inserted rows into some other table.
>> >
>> > Could anyone tell me why the cpu utilization has gone up so much' Does
>> > Creating trigger on the subscriber cause performance problem' i am
>> > using sql server 2005
>> >
>> > thanx
>> >

High Availibility SQL 2000

Hi
We are setting up a soloution with 2 sql 2000 servers as a manuel
failover soloution.
My question is: What is best, to use transactional replication or log
shipping?
My old book about sql2000 says only logshipping for this situation but
i cant't figure out why not to use replication.
And please, I'ts not my desission to use this layout I only follow
orders so its not possible to use SQL clustering or SQL2005 for
example.
Best Regards Henrik Alstersj=F6Replication copies data elements, not the entire database. Foreign keys,
stored procedures, unique constraints, and user-defined functions are not
transferred in replication.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<alstersjo@.hotmail.com> wrote in message
news:1183648932.226829.223710@.m36g2000hse.googlegroups.com...
Hi
We are setting up a soloution with 2 sql 2000 servers as a manuel
failover soloution.
My question is: What is best, to use transactional replication or log
shipping?
My old book about sql2000 says only logshipping for this situation but
i cant't figure out why not to use replication.
And please, I'ts not my desission to use this layout I only follow
orders so its not possible to use SQL clustering or SQL2005 for
example.
Best Regards Henrik Alstersjö|||On Jul 5, 11:46 am, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
> Replication copies data elements, not the entire database. Foreign keys,
> stored procedures, unique constraints, and user-defined functions are not
> transferred in replication.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> <alster...@.hotmail.com> wrote in message
> news:1183648932.226829.223710@.m36g2000hse.googlegroups.com...
> Hi
> We are setting up a soloution with 2 sql 2000 servers as a manuel
> failover soloution.
> My question is: What is best, to use transactional replication or log
> shipping?
> My old book about sql2000 says only logshipping for this situation but
> i cant't figure out why not to use replication.
> And please, I'ts not my desission to use this layout I only follow
> orders so its not possible to use SQL clustering or SQL2005 for
> example.
> Best Regards Henrik Alstersj=F6
Just eo ensure I'm on the right page, when you state that you are
utilizing a manual failover, does that mean you will not be utilizing
Microsoft Clustering Services (MSCS)? Using this would take care of
any necessity for log shipping or replication and you could simply
utilize an active/passive configuration.
Otherwise, if you aren't utilizing this, what would the damage be of
simply backing up your database and when a manual failover is
necessary, simply restore the backup to your secondary server? Of
course, this may be a bit time consuming, however, with your current
situation, it doesn't sound like high availability is a top priority
and the time to restore your database should be fairly quick.
Aaron|||On 5 Juli, 22:06, acorcoran <acorco...@.gmail.com> wrote:
> On Jul 5, 11:46 am, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
>
>
> > Replication copies data elements, not the entire database. Foreign key=s,
> > stored procedures, unique constraints, and user-defined functions are n=ot
> > transferred in replication.
> > --
> > Geoff N. Hiten
> > Senior Database Administrator
> > Microsoft SQL Server MVP
> > <alster...@.hotmail.com> wrote in message
> >news:1183648932.226829.223710@.m36g2000hse.googlegroups.com...
> > Hi
> > We are setting up a soloutionwith 2 sql 2000servers as a manuel
> > failover soloution.
> > My question is: What is best, to use transactional replication or log
> > shipping?
> > My old book about sql2000 says only logshipping for this situation but
> > i cant't figure out why not to use replication.
> > And please, I'ts not my desission to use this layout I only follow
> > orders so its not possible to use SQL clustering or SQL2005 for
> > example.
> > Best Regards Henrik Alstersj=F6
> Just eo ensure I'm on the right page, when you state that you are
> utilizing a manual failover, does that mean you will not be utilizing
> Microsoft Clustering Services (MSCS)? Using this would take care of
> any necessity for log shipping or replication and you could simply
> utilize an active/passive configuration.
> Otherwise, if you aren't utilizing this, what would the damage be of
> simply backing up your database and when a manual failover is
> necessary, simply restore the backup to your secondary server? Of
> course, this may be a bit time consuming, however, with your current
> situation, it doesn't sound like high availability is a top priority
> and the time to restore your database should be fairly quick.
> Aaron- D=F6lj citerad text -
> - Visa citerad text -
Hi
Thanks for your input, both of you.
The reason that we don't want to use backup restore functionality is
that the failovertime is not needed to bee quick but the data must be
up to date to the crash. So if we have a sevear servercrash we cant
get so fresh data from the sql server.
Regards Henrik

High Availibility SQL 2000

Hi
We are setting up a soloution with 2 sql 2000 servers as a manuel
failover soloution.
My question is: What is best, to use transactional replication or log
shipping?
My old book about sql2000 says only logshipping for this situation but
i cant't figure out why not to use replication.
And please, I'ts not my desission to use this layout I only follow
orders so its not possible to use SQL clustering or SQL2005 for
example.
Best Regards Henrik Alstersj=F6Replication copies data elements, not the entire database. Foreign keys,
stored procedures, unique constraints, and user-defined functions are not
transferred in replication.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<alstersjo@.hotmail.com> wrote in message
news:1183648932.226829.223710@.m36g2000hse.googlegroups.com...
Hi
We are setting up a soloution with 2 sql 2000 servers as a manuel
failover soloution.
My question is: What is best, to use transactional replication or log
shipping?
My old book about sql2000 says only logshipping for this situation but
i cant't figure out why not to use replication.
And please, I'ts not my desission to use this layout I only follow
orders so its not possible to use SQL clustering or SQL2005 for
example.
Best Regards Henrik Alstersj|||On Jul 5, 11:46 am, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
> Replication copies data elements, not the entire database. Foreign keys,
> stored procedures, unique constraints, and user-defined functions are not
> transferred in replication.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> <alster...@.hotmail.com> wrote in message
> news:1183648932.226829.223710@.m36g2000hse.googlegroups.com...
> Hi
> We are setting up a soloution with 2 sql 2000 servers as a manuel
> failover soloution.
> My question is: What is best, to use transactional replication or log
> shipping?
> My old book about sql2000 says only logshipping for this situation but
> i cant't figure out why not to use replication.
> And please, I'ts not my desission to use this layout I only follow
> orders so its not possible to use SQL clustering or SQL2005 for
> example.
> Best Regards Henrik Alstersj=F6
Just eo ensure I'm on the right page, when you state that you are
utilizing a manual failover, does that mean you will not be utilizing
Microsoft Clustering Services (MSCS)? Using this would take care of
any necessity for log shipping or replication and you could simply
utilize an active/passive configuration.
Otherwise, if you aren't utilizing this, what would the damage be of
simply backing up your database and when a manual failover is
necessary, simply restore the backup to your secondary server? Of
course, this may be a bit time consuming, however, with your current
situation, it doesn't sound like high availability is a top priority
and the time to restore your database should be fairly quick.
Aaron|||On 5 Juli, 22:06, acorcoran <acorco...@.gmail.com> wrote:
> On Jul 5, 11:46 am, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
>
>
>
s,[vbcol=seagreen]
ot[vbcol=seagreen]
>
>
>
>
> Just eo ensure I'm on the right page, when you state that you are
> utilizing a manual failover, does that mean you will not be utilizing
> Microsoft Clustering Services (MSCS)? Using this would take care of
> any necessity for log shipping or replication and you could simply
> utilize an active/passive configuration.
> Otherwise, if you aren't utilizing this, what would the damage be of
> simply backing up your database and when a manual failover is
> necessary, simply restore the backup to your secondary server? Of
> course, this may be a bit time consuming, however, with your current
> situation, it doesn't sound like high availability is a top priority
> and the time to restore your database should be fairly quick.
> Aaron- D=F6lj citerad text -
> - Visa citerad text -
Hi
Thanks for your input, both of you.
The reason that we don't want to use backup restore functionality is
that the failovertime is not needed to bee quick but the data must be
up to date to the crash. So if we have a sevear servercrash we cant
get so fresh data from the sql server.
Regards Henrik

High Availibility SQL 2000

Hi
We are setting up a soloution with 2 sql 2000 servers as a manuel
failover soloution.
My question is: What is best, to use transactional replication or log
shipping?
My old book about sql2000 says only logshipping for this situation but
i cant't figure out why not to use replication.
And please, I'ts not my desission to use this layout I only follow
orders so its not possible to use SQL clustering or SQL2005 for
example.
Best Regards Henrik Alstersj
Replication copies data elements, not the entire database. Foreign keys,
stored procedures, unique constraints, and user-defined functions are not
transferred in replication.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<alstersjo@.hotmail.com> wrote in message
news:1183648932.226829.223710@.m36g2000hse.googlegr oups.com...
Hi
We are setting up a soloution with 2 sql 2000 servers as a manuel
failover soloution.
My question is: What is best, to use transactional replication or log
shipping?
My old book about sql2000 says only logshipping for this situation but
i cant't figure out why not to use replication.
And please, I'ts not my desission to use this layout I only follow
orders so its not possible to use SQL clustering or SQL2005 for
example.
Best Regards Henrik Alstersj
|||On Jul 5, 11:46 am, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
> Replication copies data elements, not the entire database. Foreign keys,
> stored procedures, unique constraints, and user-defined functions are not
> transferred in replication.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> <alster...@.hotmail.com> wrote in message
> news:1183648932.226829.223710@.m36g2000hse.googlegr oups.com...
> Hi
> We are setting up a soloution with 2 sql 2000 servers as a manuel
> failover soloution.
> My question is: What is best, to use transactional replication or log
> shipping?
> My old book about sql2000 says only logshipping for this situation but
> i cant't figure out why not to use replication.
> And please, I'ts not my desission to use this layout I only follow
> orders so its not possible to use SQL clustering or SQL2005 for
> example.
> Best Regards Henrik Alstersj
Just eo ensure I'm on the right page, when you state that you are
utilizing a manual failover, does that mean you will not be utilizing
Microsoft Clustering Services (MSCS)? Using this would take care of
any necessity for log shipping or replication and you could simply
utilize an active/passive configuration.
Otherwise, if you aren't utilizing this, what would the damage be of
simply backing up your database and when a manual failover is
necessary, simply restore the backup to your secondary server? Of
course, this may be a bit time consuming, however, with your current
situation, it doesn't sound like high availability is a top priority
and the time to restore your database should be fairly quick.
Aaron
|||On 5 Juli, 22:06, acorcoran <acorco...@.gmail.com> wrote:
> On Jul 5, 11:46 am, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
>
>
>
>
>
> Just eo ensure I'm on the right page, when you state that you are
> utilizing a manual failover, does that mean you will not be utilizing
> Microsoft Clustering Services (MSCS)? Using this would take care of
> any necessity for log shipping or replication and you could simply
> utilize an active/passive configuration.
> Otherwise, if you aren't utilizing this, what would the damage be of
> simply backing up your database and when a manual failover is
> necessary, simply restore the backup to your secondary server? Of
> course, this may be a bit time consuming, however, with your current
> situation, it doesn't sound like high availability is a top priority
> and the time to restore your database should be fairly quick.
> Aaron- Dlj citerad text -
> - Visa citerad text -
Hi
Thanks for your input, both of you.
The reason that we don't want to use backup restore functionality is
that the failovertime is not needed to bee quick but the data must be
up to date to the crash. So if we have a sevear servercrash we cant
get so fresh data from the sql server.
Regards Henrik

High availability at subscribers using database mirroring

I have a mirrored database with transactional replication publishing
to 2 databases on different servers.
The amount of data written to the principal is fairly low.
The requirement is that at least one of the subscribers must be
available at all times with a latency of no greater than five minutes.
I thus have redundancy at the publisher via mirroring and at the
subscribers via multiple databases containing the same data, but I
can't see how to achieve redundancy at the distributor, since the
distribution database cannot be mirrored.
What is the recommended method of making the distributor highly
available? At this site there is reluctance to pursue SQL clustering
and to rename servers as described in BOL. At the moment it looks like
a need a set of scripts to completely set up replication from scratch
if the machine where the distributor DB lives goes offline.
Or, will Katmai offer new features to make all of the replication
components as highly available as the publisher can now be via
mirroring?
Hi Garry,
I understand that you would like to implement a high availability SQL
Server replication. You implemented Database mirroring for your principal
server and multiple databases with same data at the subscribers, but you
would like to know how to implement redundancy at the distributor since the
distribution database cannot be mirrored.
If I have misunderstood, please let me know.
I recommend that you can create a failover cluster for your distributer.
Deploy the distribution database to the virtual server instance. Also, for
your scenario, I think that it is better to use failover cluster for your
publisher since it just need two servers, but to implement a high
availability database mirroring, it needs three servers, principal server,
witness server and mirror server.
For how to setup a SQL Server 2005 failover cluster, you may refer to:
How to: Create a New SQL Server 2005 Failover Cluster (Setup)
http://msdn2.microsoft.com/en-us/library/ms179530.aspx
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====