Log shipping alerts: February 2012

Friday, February 24, 2012

Log Shipping and loading service packs

Hi All
Does anyone know if there will be any issues with
upgrading a SQL service pack on a SQL server that is using
log shipping? I want to upgrade the standby server last.
Is this order correct?
Thanks
H
Hi,
No problem, You can update the service pack in production server first and
later the stand by servers.
Thanks
Hari
MCDBA
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:743301c430ab$29489520$a501280a@.phx.gbl...
> Hi All
> Does anyone know if there will be any issues with
> upgrading a SQL service pack on a SQL server that is using
> log shipping? I want to upgrade the standby server last.
> Is this order correct?
> Thanks
> H

Log Shipping and loading service packs

Hi All
Does anyone know if there will be any issues with
upgrading a SQL service pack on a SQL server that is using
log shipping? I want to upgrade the standby server last.
Is this order correct?
Thanks
HHi,
No problem, You can update the service pack in production server first and
later the stand by servers.
Thanks
Hari
MCDBA
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:743301c430ab$29489520$a501280a@.phx.gbl...
> Hi All
> Does anyone know if there will be any issues with
> upgrading a SQL service pack on a SQL server that is using
> log shipping? I want to upgrade the standby server last.
> Is this order correct?
> Thanks
> H

Log Shipping and loading service packs

Hi All
Does anyone know if there will be any issues with
upgrading a SQL service pack on a SQL server that is using
log shipping? I want to upgrade the standby server last.
Is this order correct?
Thanks
HHi,
No problem, You can update the service pack in production server first and
later the stand by servers.
Thanks
Hari
MCDBA
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:743301c430ab$29489520$a501280a@.phx.gbl...
> Hi All
> Does anyone know if there will be any issues with
> upgrading a SQL service pack on a SQL server that is using
> log shipping? I want to upgrade the standby server last.
> Is this order correct?
> Thanks
> H

Log shipping and index rebuilds

Hi everyone.
My company is embarking on a DRP project where we will move our production s
ervers to a less geographically insecure area. We'll need to perform log shi
pping back to our DRP site here in Vancouver Canada.
I just learned today that SQL Server logs index rebuilds and that we perform
rebuilds regularly so I have a few questions if anyone can help:
Can we stop index builds from being logged?
If we cannot stop index logging, are there any different practices we can fo
llow to do index maintenance that will not require logging (e.g. defrag)
Are there best practices around log shipping for MS-SQL that will maximize o
ur use of bandwidth?
Does anyone have any experience of this?
If we were to ship index rebuilds, we'll be shipping gigabytes of unnecessar
y data, but apparently SQL Server will not let this be switched off?
Thanks.
David.David,
I have implemented LogShipping at my site and understand your questions. For
the most indepth information on LogShipping and High Availability, check ou
t this book. You will find that it answers all of your questions and way mor
e.
SQL Server 2000 High Availability.
It's from Microsoft Press and it discusses everything you may want to know.
This may be not the answer you were expecting, but I am sure once you pick u
p the book that you will have answers to questions you didn't know you had.
Lisa
"davidbailie" wrote:

> Hi everyone.
> My company is embarking on a DRP project where we will move our production
servers to a less geographically insecure area. We'll need to perform log s
hipping back to our DRP site here in Vancouver Canada.
> I just learned today that SQL Server logs index rebuilds and that we perfo
rm rebuilds regularly so I have a few questions if anyone can help:
> Can we stop index builds from being logged?
> If we cannot stop index logging, are there any different practices we can
follow to do index maintenance that will not require logging (e.g. defrag)
> Are there best practices around log shipping for MS-SQL that will maximize
our use of bandwidth?
> Does anyone have any experience of this?
> If we were to ship index rebuilds, we'll be shipping gigabytes of unnecess
ary data, but apparently SQL Server will not let this be switched off?
> Thanks.
> David.
>|||Lisa, thanks very much for your reply. I happen to have that book (one of th
e many I haven't even opened yet). But I'll take a look through it.
Thanks again,
David.
--
David Bailie
Ernex
Vancouver, BC.
Canada
"Ora/SQL DBA" wrote:
[vbcol=seagreen]
> David,
> I have implemented LogShipping at my site and understand your questions. F
or the most indepth information on LogShipping and High Availability, check
out this book. You will find that it answers all of your questions and way m
ore.
> SQL Server 2000 High Availability.
> It's from Microsoft Press and it discusses everything you may want to know
.
> This may be not the answer you were expecting, but I am sure once you pick
up the book that you will have answers to questions you didn't know you had
.
> Lisa
> "davidbailie" wrote:
>

Log shipping and index rebuilds

Hi everyone.
My company is embarking on a DRP project where we will move our production servers to a less geographically insecure area. We'll need to perform log shipping back to our DRP site here in Vancouver Canada.
I just learned today that SQL Server logs index rebuilds and that we perform rebuilds regularly so I have a few questions if anyone can help:
Can we stop index builds from being logged?
If we cannot stop index logging, are there any different practices we can follow to do index maintenance that will not require logging (e.g. defrag)
Are there best practices around log shipping for MS-SQL that will maximize our use of bandwidth?
Does anyone have any experience of this?
If we were to ship index rebuilds, we'll be shipping gigabytes of unnecessary data, but apparently SQL Server will not let this be switched off?
Thanks.
David.
David,
I have implemented LogShipping at my site and understand your questions. For the most indepth information on LogShipping and High Availability, check out this book. You will find that it answers all of your questions and way more.
SQL Server 2000 High Availability.
It's from Microsoft Press and it discusses everything you may want to know.
This may be not the answer you were expecting, but I am sure once you pick up the book that you will have answers to questions you didn't know you had.
Lisa
"davidbailie" wrote:

> Hi everyone.
> My company is embarking on a DRP project where we will move our production servers to a less geographically insecure area. We'll need to perform log shipping back to our DRP site here in Vancouver Canada.
> I just learned today that SQL Server logs index rebuilds and that we perform rebuilds regularly so I have a few questions if anyone can help:
> Can we stop index builds from being logged?
> If we cannot stop index logging, are there any different practices we can follow to do index maintenance that will not require logging (e.g. defrag)
> Are there best practices around log shipping for MS-SQL that will maximize our use of bandwidth?
> Does anyone have any experience of this?
> If we were to ship index rebuilds, we'll be shipping gigabytes of unnecessary data, but apparently SQL Server will not let this be switched off?
> Thanks.
> David.
>
|||Lisa, thanks very much for your reply. I happen to have that book (one of the many I haven't even opened yet). But I'll take a look through it.
Thanks again,
David.
David Bailie
Ernex
Vancouver, BC.
Canada
"Ora/SQL DBA" wrote:
[vbcol=seagreen]
> David,
> I have implemented LogShipping at my site and understand your questions. For the most indepth information on LogShipping and High Availability, check out this book. You will find that it answers all of your questions and way more.
> SQL Server 2000 High Availability.
> It's from Microsoft Press and it discusses everything you may want to know.
> This may be not the answer you were expecting, but I am sure once you pick up the book that you will have answers to questions you didn't know you had.
> Lisa
> "davidbailie" wrote:

Log shipping and index rebuilds

Hi everyone.
My company is embarking on a DRP project where we will move our production servers to a less geographically insecure area. We'll need to perform log shipping back to our DRP site here in Vancouver Canada.
I just learned today that SQL Server logs index rebuilds and that we perform rebuilds regularly so I have a few questions if anyone can help:
Can we stop index builds from being logged?
If we cannot stop index logging, are there any different practices we can follow to do index maintenance that will not require logging (e.g. defrag)
Are there best practices around log shipping for MS-SQL that will maximize our use of bandwidth?
Does anyone have any experience of this?
If we were to ship index rebuilds, we'll be shipping gigabytes of unnecessary data, but apparently SQL Server will not let this be switched off?
Thanks.
David.David,
I have implemented LogShipping at my site and understand your questions. For the most indepth information on LogShipping and High Availability, check out this book. You will find that it answers all of your questions and way more.
SQL Server 2000 High Availability.
It's from Microsoft Press and it discusses everything you may want to know.
This may be not the answer you were expecting, but I am sure once you pick up the book that you will have answers to questions you didn't know you had.
Lisa
"davidbailie" wrote:
> Hi everyone.
> My company is embarking on a DRP project where we will move our production servers to a less geographically insecure area. We'll need to perform log shipping back to our DRP site here in Vancouver Canada.
> I just learned today that SQL Server logs index rebuilds and that we perform rebuilds regularly so I have a few questions if anyone can help:
> Can we stop index builds from being logged?
> If we cannot stop index logging, are there any different practices we can follow to do index maintenance that will not require logging (e.g. defrag)
> Are there best practices around log shipping for MS-SQL that will maximize our use of bandwidth?
> Does anyone have any experience of this?
> If we were to ship index rebuilds, we'll be shipping gigabytes of unnecessary data, but apparently SQL Server will not let this be switched off?
> Thanks.
> David.
>

log shipping and growing of trans log on backup server

hi all,
we do log shipping to a backup server. Log on the backup server is
growing. It seems ok to truncate log once/day.
Is it!the LOG is growing on your backup server because it must be growing on your
primary server too, you will need backup the log on the primary server first
and then restore the latest log to the backup for the log file to truncate.
--
-- cranfield, DBA
"Zarko Jovanovic" wrote:
> hi all,
> we do log shipping to a backup server. Log on the backup server is
> growing. It seems ok to truncate log once/day.
> Is it!
>

log shipping and Full text indexing

We have FT Indexing service enabled ? How do I know if we have any full text
indexes and when I log ship and/or backup my databases in SQL 2005, does it
take care of it as well or do I have to manually move them to the other
server and back them up ?
Using SQL 2005
ThanksOn your database, go to Storage and Full Text Catalogs. See if you have any
full text catalogs there.
A full database backup also takes care of backing up your full text
catalogs. Since log shipping uses a database backup to initialize the
secondary database, the full text catalog is restored as well on this
database.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Hassan" wrote:

> We have FT Indexing service enabled ? How do I know if we have any full te
xt
> indexes and when I log ship and/or backup my databases in SQL 2005, does i
t
> take care of it as well or do I have to manually move them to the other
> server and back them up ?
> Using SQL 2005
> Thanks
>|||On your database, go to Storage and Full Text Catalogs. See if you have any
full text catalogs there.
A full database backup also takes care of backing up your full text
catalogs. Since log shipping uses a database backup to initialize the
secondary database, the full text catalog is restored as well on this
database.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Hassan" wrote:

> We have FT Indexing service enabled ? How do I know if we have any full te
xt
> indexes and when I log ship and/or backup my databases in SQL 2005, does i
t
> take care of it as well or do I have to manually move them to the other
> server and back them up ?
> Using SQL 2005
> Thanks
>

log shipping and Full text indexing

We have FT Indexing service enabled ? How do I know if we have any full text
indexes and when I log ship and/or backup my databases in SQL 2005, does it
take care of it as well or do I have to manually move them to the other
server and back them up ?
Using SQL 2005
Thanks
On your database, go to Storage and Full Text Catalogs. See if you have any
full text catalogs there.
A full database backup also takes care of backing up your full text
catalogs. Since log shipping uses a database backup to initialize the
secondary database, the full text catalog is restored as well on this
database.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Hassan" wrote:

> We have FT Indexing service enabled ? How do I know if we have any full text
> indexes and when I log ship and/or backup my databases in SQL 2005, does it
> take care of it as well or do I have to manually move them to the other
> server and back them up ?
> Using SQL 2005
> Thanks
>
|||On your database, go to Storage and Full Text Catalogs. See if you have any
full text catalogs there.
A full database backup also takes care of backing up your full text
catalogs. Since log shipping uses a database backup to initialize the
secondary database, the full text catalog is restored as well on this
database.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Hassan" wrote:

> We have FT Indexing service enabled ? How do I know if we have any full text
> indexes and when I log ship and/or backup my databases in SQL 2005, does it
> take care of it as well or do I have to manually move them to the other
> server and back them up ?
> Using SQL 2005
> Thanks
>

log shipping and Full text indexing

We have FT Indexing service enabled ? How do I know if we have any full text
indexes and when I log ship and/or backup my databases in SQL 2005, does it
take care of it as well or do I have to manually move them to the other
server and back them up ?
Using SQL 2005
ThanksOn your database, go to Storage and Full Text Catalogs. See if you have any
full text catalogs there.
A full database backup also takes care of backing up your full text
catalogs. Since log shipping uses a database backup to initialize the
secondary database, the full text catalog is restored as well on this
database.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Hassan" wrote:
> We have FT Indexing service enabled ? How do I know if we have any full text
> indexes and when I log ship and/or backup my databases in SQL 2005, does it
> take care of it as well or do I have to manually move them to the other
> server and back them up ?
> Using SQL 2005
> Thanks
>|||On your database, go to Storage and Full Text Catalogs. See if you have any
full text catalogs there.
A full database backup also takes care of backing up your full text
catalogs. Since log shipping uses a database backup to initialize the
secondary database, the full text catalog is restored as well on this
database.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Hassan" wrote:
> We have FT Indexing service enabled ? How do I know if we have any full text
> indexes and when I log ship and/or backup my databases in SQL 2005, does it
> take care of it as well or do I have to manually move them to the other
> server and back them up ?
> Using SQL 2005
> Thanks
>

Log shipping and Full Text

I setup log shipping on my production server which is a part of Replication
and went ahead and created a report server using log Shipping however my
Report server is missing Full Text and it don't allow me to create it saying
it is read only database.
I am running SQL server 2000 with service Pack 2
Is there any way I can also bring Full text on Report Server my databasde is
huge and full text takes couple of days to create so I was exploring a
solution that can take full txet catalog from my production server and copy
it to Reprot Server.
Thanks
Tanweer
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
"Tanweer" <Tanweer@.discussions.microsoft.com> wrote in message
news:820818D0-7F18-45F0-8B82-9ACBF539F8BE@.microsoft.com...
> I setup log shipping on my production server which is a part of
Replication
> and went ahead and created a report server using log Shipping however my
> Report server is missing Full Text and it don't allow me to create it
saying
> it is read only database.
> I am running SQL server 2000 with service Pack 2
> Is there any way I can also bring Full text on Report Server my databasde
is
> huge and full text takes couple of days to create so I was exploring a
> solution that can take full txet catalog from my production server and
copy
> it to Reprot Server.
> Thanks
> Tanweer
|||oops, that went out prematurely. You could follow these steps every time you
want your database synchronized -
http://support.microsoft.com/default...b;en-us;240867
Or you could use replication. If you use replication you will have to build
your tables and full-text indexes in advance on the subscriber. Then in your
article properties make sure you select the delete all data option.
SQL 2005 replication will support this directly.
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
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u$9GFtsaFHA.3488@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
>
> --
> 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
> "Tanweer" <Tanweer@.discussions.microsoft.com> wrote in message
> news:820818D0-7F18-45F0-8B82-9ACBF539F8BE@.microsoft.com...
> Replication
> saying
databasde
> is
> copy
>
|||However I want this to be automatic every 3 hours the report server need to
have the same catalog as the Production Server.
What I can do to automate this process.
Thanks
Tanweer
"Hilary Cotter" wrote:

> oops, that went out prematurely. You could follow these steps every time you
> want your database synchronized -
> http://support.microsoft.com/default...b;en-us;240867
> Or you could use replication. If you use replication you will have to build
> your tables and full-text indexes in advance on the subscriber. Then in your
> article properties make sure you select the delete all data option.
> SQL 2005 replication will support this directly.
> --
> 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
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:u$9GFtsaFHA.3488@.tk2msftngp13.phx.gbl...
> databasde
>
>
|||This can be automated.
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
"Tanweer" <Tanweer@.discussions.microsoft.com> wrote in message
news:0E2E76B8-B1FE-4A1E-BE73-D1C949A2AB6A@.microsoft.com...
> However I want this to be automatic every 3 hours the report server need
to[vbcol=seagreen]
> have the same catalog as the Production Server.
> What I can do to automate this process.
> Thanks
> Tanweer
> "Hilary Cotter" wrote:
you[vbcol=seagreen]
build[vbcol=seagreen]
your[vbcol=seagreen]
however my[vbcol=seagreen]
it[vbcol=seagreen]
exploring a[vbcol=seagreen]
and[vbcol=seagreen]

Log shipping and error 22029

I am having some trouble using MS SQL Server 2000 log shipping. I can get the
database to fail over and become active on the secondary server, but I want
to also have the primary become immediately available for reverse log
shipping.
After I have run the stored procedures to demote the primary, I have to
detach and attach the database on the secondary to be able to run the stored
procedure to promote the secondary. If I don't the procedure fails (sorry, I
don't have the exact error, but it refers to the databse being unable to
access). I also have to manually run the last copy and restore job to make
sure the final transaction log backup gets applied. Once this is done, the
stored procedure to promote the secondary is successful. I then run the
stored procedure to change the monitor server to reflect the new server roles.
Once the database is failed over, I have to go into the maintenance plan for
the reverse log shipping and assign the new destination server. When doing
this, I receive a message stating that the database on the new destination
server is not in standby mode. If I set the database to Read Only in the
Properties dialog box, I can then configure the new destination server. When
I do this, the log backups work fine and so does the copy job. But the
restore job fails with the following error
Executed as user: DMSMAIN\Administrator. sqlmaint.exe failed. [SQLSTATE
42000] (Error 22029). The step failed.
I can't find any helpful documentation of this message. Listed below are the
stored procedures I am running.
USE master
GO
EXEC msdb.dbo.sp_change_primary_role
@.db_name = 'modelcopy',
@.backup_log = 1,
@.terminate = 1,
@.final_state = 2,
@.access_level = 1
USE master
GO
EXEC msdb.dbo.sp_change_secondary_role
@.db_name = 'Modelcopy',
@.do_load = 1,
@.force_load = 1,
@.final_state = 1,
@.access_level = 1,
@.terminate = 1,
@.keep_replication = 0,
@.stopat = null
USE master
GO
EXEC msdb.dbo.sp_change_monitor_role
@.primary_server = 'sqlserver1' ,
@.secondary_server = 'staging3',
@.database = 'modelcopy',
@.new_source = '\\staging3\logshipping'
Any help is appreciated
TIA,
Ken
Ken,
First of all, do not use the option "Allow database to assume primary role"
when setting up log shipping. It just doesn't work.
Reversing the direction of log shipping is a manual process.
1. Demote the primary database on the Main server using
msdb.dbo.sp_change_primary_role
as you have it below. This will make one last tlog backup to disk.
2. Promote the secondary database on the Standby server using
msdb.dbo.sp_change_secondary_role
as you have it below. This will take that one last tlog backup and apply it
WITH RECOVERY.
3. Remove all log shipping that remains (jobs and rows for that log shipping
pair in the ls tables - if there are any.)
At this point you can install log shipping from scratch, the new direction
now being from the primary database on the Standby server to the secondary
database (in norecovery) on the Main server.
Hope this helps,
Ron
Ron Talmage
SQL Server MVP
"kmkrause2" <kmkrause2@.discussions.microsoft.com> wrote in message
news:3D4C90B5-93A9-4646-ABB2-17110ACF574F@.microsoft.com...
> I am having some trouble using MS SQL Server 2000 log shipping. I can get
the
> database to fail over and become active on the secondary server, but I
want
> to also have the primary become immediately available for reverse log
> shipping.
> After I have run the stored procedures to demote the primary, I have to
> detach and attach the database on the secondary to be able to run the
stored
> procedure to promote the secondary. If I don't the procedure fails (sorry,
I
> don't have the exact error, but it refers to the databse being unable to
> access). I also have to manually run the last copy and restore job to make
> sure the final transaction log backup gets applied. Once this is done, the
> stored procedure to promote the secondary is successful. I then run the
> stored procedure to change the monitor server to reflect the new server
roles.
> Once the database is failed over, I have to go into the maintenance plan
for
> the reverse log shipping and assign the new destination server. When doing
> this, I receive a message stating that the database on the new destination
> server is not in standby mode. If I set the database to Read Only in the
> Properties dialog box, I can then configure the new destination server.
When
> I do this, the log backups work fine and so does the copy job. But the
> restore job fails with the following error
> Executed as user: DMSMAIN\Administrator. sqlmaint.exe failed. [SQLSTATE
> 42000] (Error 22029). The step failed.
> I can't find any helpful documentation of this message. Listed below are
the
> stored procedures I am running.
> USE master
> GO
> EXEC msdb.dbo.sp_change_primary_role
> @.db_name = 'modelcopy',
> @.backup_log = 1,
> @.terminate = 1,
> @.final_state = 2,
> @.access_level = 1
> USE master
> GO
> EXEC msdb.dbo.sp_change_secondary_role
> @.db_name = 'Modelcopy',
> @.do_load = 1,
> @.force_load = 1,
> @.final_state = 1,
> @.access_level = 1,
> @.terminate = 1,
> @.keep_replication = 0,
> @.stopat = null
> USE master
> GO
> EXEC msdb.dbo.sp_change_monitor_role
> @.primary_server = 'sqlserver1' ,
> @.secondary_server = 'staging3',
> @.database = 'modelcopy',
> @.new_source = '\\staging3\logshipping'
> Any help is appreciated
> TIA,
> Ken
|||Ron,
Thanks again for your help. I had just about came to the same conclusion
about the "assume primary role option" but wanted to make sure. In fact, I
was figuring I owuld have to resign myself to the option of simply rebuilding
from the start all over again.
Thanks,
Ken
"Ron Talmage" wrote:

> Ken,
> First of all, do not use the option "Allow database to assume primary role"
> when setting up log shipping. It just doesn't work.
> Reversing the direction of log shipping is a manual process.
> 1. Demote the primary database on the Main server using
> msdb.dbo.sp_change_primary_role
> as you have it below. This will make one last tlog backup to disk.
> 2. Promote the secondary database on the Standby server using
> msdb.dbo.sp_change_secondary_role
> as you have it below. This will take that one last tlog backup and apply it
> WITH RECOVERY.
> 3. Remove all log shipping that remains (jobs and rows for that log shipping
> pair in the ls tables - if there are any.)
> At this point you can install log shipping from scratch, the new direction
> now being from the primary database on the Standby server to the secondary
> database (in norecovery) on the Main server.
> Hope this helps,
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "kmkrause2" <kmkrause2@.discussions.microsoft.com> wrote in message
> news:3D4C90B5-93A9-4646-ABB2-17110ACF574F@.microsoft.com...
> the
> want
> stored
> I
> roles.
> for
> When
> the
>
>
|||Hello Ken
If I understand correctly, there are 2 issues that you have brought up in
this message -
1. You are unable to configure the primary server to reverse log ship at
the time of failover
2. You are having problems with running the sp_change_secondary_role stored
procedure because you get some error that mentions that the database is in
use.
Let me address these -
1. You should use sp_change_primary_role in the following manner if you
would like to leave the primary database in standby(or norecovery) state
for reverse log shipping :
EXEC msdb.dbo.sp_change_primary_role
@.db_name = 'modelcopy',
@.backup_log = 1,
@.terminate = 1,
@.final_state = 3 --3 = Standby, 2 = Norecovery
This performs transaction log backup and leaves the database in a state
where no further changes can be made. PLEASE MAKE SURE THAT THIS STORED
PROCEDURE COMPLETES SUCCESSFULLY OTHERWISE YOU WILL GET ERRORS WHEN YOU
SETUP THIS SERVER AS SECONDARY. If the output indicates any errors, try
running the stored procedure again. If you continue to get errors, please
post the output that you get from this stored procedure in reply to this
message.
2. Please refer to the following KB articles to see if you are running into
one of the known issues :
294397 BUG: sp_change_secondary_role Fails with Error 3101 if There Are
http://support.microsoft.com/?id=294397
308774 FIX: Sp_change_primary_role May Store Transaction Log Backup in the
http://support.microsoft.com/?id=308774
300497 FIX: Log Shipping: Cannot Change Role From Secondary to Primary When
http://support.microsoft.com/?id=300497
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||Pankaj,
You're correct in that there are two basic issues causing problems. However,
the sp_change_primary_role always completed successfully. I would run this in
Query Analyzer and would get successful messages.
Also, the original secondary server would, if I followed the steps outlined
in my message, become a primary with relative ease.
The biggest issue I ran into was that when I tried to configure the original
primary, now the secondary, to accept log shipping so that it again may
become the primary during another role change, the restore jobs would fail.
The backup and copy jobs were always successful.
Thanks,
Ken
"Pankaj Agarwal [MSFT]" wrote:

> Hello Ken
> If I understand correctly, there are 2 issues that you have brought up in
> this message -
> 1. You are unable to configure the primary server to reverse log ship at
> the time of failover
> 2. You are having problems with running the sp_change_secondary_role stored
> procedure because you get some error that mentions that the database is in
> use.
> Let me address these -
> 1. You should use sp_change_primary_role in the following manner if you
> would like to leave the primary database in standby(or norecovery) state
> for reverse log shipping :
> EXEC msdb.dbo.sp_change_primary_role
> @.db_name = 'modelcopy',
> @.backup_log = 1,
> @.terminate = 1,
> @.final_state = 3 --3 = Standby, 2 = Norecovery
> This performs transaction log backup and leaves the database in a state
> where no further changes can be made. PLEASE MAKE SURE THAT THIS STORED
> PROCEDURE COMPLETES SUCCESSFULLY OTHERWISE YOU WILL GET ERRORS WHEN YOU
> SETUP THIS SERVER AS SECONDARY. If the output indicates any errors, try
> running the stored procedure again. If you continue to get errors, please
> post the output that you get from this stored procedure in reply to this
> message.
> 2. Please refer to the following KB articles to see if you are running into
> one of the known issues :
> 294397 BUG: sp_change_secondary_role Fails with Error 3101 if There Are
> http://support.microsoft.com/?id=294397
> 308774 FIX: Sp_change_primary_role May Store Transaction Log Backup in the
> http://support.microsoft.com/?id=308774
> 300497 FIX: Log Shipping: Cannot Change Role From Secondary to Primary When
> http://support.microsoft.com/?id=300497
> Thank you for using Microsoft newsgroups.
> Sincerely
> Pankaj Agarwal
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>

Log shipping and DTS packages update

Hello,
I just implemented log shipping on SQL Server 2000 Developer edition
and I need to keep all modifications to DTS packages on Primery Server
to be replicated on Standby Server. Could you please give me a hint how
to do that?
Thanks,
GBGB wrote:

> DTS packages
From microsoft:
"Copying DTS Packages
DTS packages can be stored in the msdb database or in the file system.
If you have DTS packages that run on the primary server on which your
production database relies, you must manually copy these DTS packages
to each standby server. You cannot simply back up the msdb database and
restore it to a standby server. Doing so overwrites all jobs, alerts,
operators, and DTS packages in the msdb database on the standby server.
You can open DTS packages saved to the msdb database on the primary
server and save them to each standby server. Simply copy DTS packages
saved to the file system on the primary server to a folder on each
standby server. Complete this task before users connect to a standby
server that is promoted to become the new primary server.
Use the following procedure to copy DTS packages that the production
database needs to each standby server.
To copy DTS packages
1.
For each DTS package stored in the file system on the primary server,
copy the DTS package to each standby server using Windows Explorer.
When copying DTS packages to each standby server, use the same drive
and path that the primary server uses. Doing so eliminates potential
path problems.
2.
For each DTS package stored in the msdb database on the primary server,
use SQL Server Enterprise Manager to open the DTS package, and then
save it to the msdb database on each standby server.
Note: To ensure DTS packages execute properly on a subscriber, use an
alias or the dynamic properties task. For more information, see
"Redirecting Client Network Traffic to a Promoted Secondary Server" in
Planning Guide Chapter 5, "Minimizing Downtime by Using Redundant
Components.""
This article might be handy:
http://www.dbazine.com/sql/sql-articles/larsen8
HTH,
Stijn Verrept.

Log shipping and DB reorgs

We're trying to set up log shipping in order to maintain a
backup/fallover database at a remote location. Everything
is looking pretty good, EXCEPT that whenever the nightly
database reorganization takes place on the primary
database, it apparently logs all the
various "transactions" resulting from the reorg activity.
It then ships this log to the secondary database, clogging
the pipeline something awful.
It seems intuitively odd that the reorg would result in
major logging. Be that as it may, can anybody think of a
way to avoid/circumvent/minimize this problem?
Thanks in advance for any advice.Ken,
I'm facing the same problem too, and AFAIK, there's no way to circumvent
this problem. I tried INDEXDEFRAG instead of DBREINDEX and it makes it even
worse sometimes.
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example
"Ken C" <anonymous@.discussions.microsoft.com> wrote in message
news:d5ae01c3ef42$eb8e08e0$a401280a@.phx.gbl...
> We're trying to set up log shipping in order to maintain a
> backup/fallover database at a remote location. Everything
> is looking pretty good, EXCEPT that whenever the nightly
> database reorganization takes place on the primary
> database, it apparently logs all the
> various "transactions" resulting from the reorg activity.
> It then ships this log to the secondary database, clogging
> the pipeline something awful.
> It seems intuitively odd that the reorg would result in
> major logging. Be that as it may, can anybody think of a
> way to avoid/circumvent/minimize this problem?
> Thanks in advance for any advice.|||Hi Carlos.
Would interleaving .diff backups between .tlogs in the shipping process
help? A few years back I rolled a custom log shipping solution to solve
other problems with log shipping, but I'm wonderinng if shipping .diff
backups interleaved with the .tlog backups would mitigate this problem,
assuming that the custom solution didn't transfer redundant .tlogs (the ones
with the dbcc commands)?
Regards,
Greg Linwood
SQL Server MVP
"Carlos Eduardo Rojas" <carloser@.mindspring.com> wrote in message
news:%23XCx%23Y07DHA.1672@.TK2MSFTNGP12.phx.gbl...
> Ken,
> I'm facing the same problem too, and AFAIK, there's no way to circumvent
> this problem. I tried INDEXDEFRAG instead of DBREINDEX and it makes it
even
> worse sometimes.
> --
> Carlos E. Rojas
> SQL Server MVP
> Co-Author SQL Server 2000 Programming by Example
>
> "Ken C" <anonymous@.discussions.microsoft.com> wrote in message
> news:d5ae01c3ef42$eb8e08e0$a401280a@.phx.gbl...
> > We're trying to set up log shipping in order to maintain a
> > backup/fallover database at a remote location. Everything
> > is looking pretty good, EXCEPT that whenever the nightly
> > database reorganization takes place on the primary
> > database, it apparently logs all the
> > various "transactions" resulting from the reorg activity.
> > It then ships this log to the secondary database, clogging
> > the pipeline something awful.
> >
> > It seems intuitively odd that the reorg would result in
> > major logging. Be that as it may, can anybody think of a
> > way to avoid/circumvent/minimize this problem?
> >
> > Thanks in advance for any advice.
>|||Greg,
That's a good idea, but you'd have to restore the whole database before
restoring each differential backup. This generates some overhead on the
destination server, and increases the recovery time.
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:O4pYPe07DHA.2676@.TK2MSFTNGP10.phx.gbl...
> Hi Carlos.
> Would interleaving .diff backups between .tlogs in the shipping process
> help? A few years back I rolled a custom log shipping solution to solve
> other problems with log shipping, but I'm wonderinng if shipping .diff
> backups interleaved with the .tlog backups would mitigate this problem,
> assuming that the custom solution didn't transfer redundant .tlogs (the
ones
> with the dbcc commands)?
> Regards,
> Greg Linwood
> SQL Server MVP
> "Carlos Eduardo Rojas" <carloser@.mindspring.com> wrote in message
> news:%23XCx%23Y07DHA.1672@.TK2MSFTNGP12.phx.gbl...
> > Ken,
> > I'm facing the same problem too, and AFAIK, there's no way to circumvent
> > this problem. I tried INDEXDEFRAG instead of DBREINDEX and it makes it
> even
> > worse sometimes.
> > --
> > Carlos E. Rojas
> > SQL Server MVP
> > Co-Author SQL Server 2000 Programming by Example
> >
> >
> > "Ken C" <anonymous@.discussions.microsoft.com> wrote in message
> > news:d5ae01c3ef42$eb8e08e0$a401280a@.phx.gbl...
> > > We're trying to set up log shipping in order to maintain a
> > > backup/fallover database at a remote location. Everything
> > > is looking pretty good, EXCEPT that whenever the nightly
> > > database reorganization takes place on the primary
> > > database, it apparently logs all the
> > > various "transactions" resulting from the reorg activity.
> > > It then ships this log to the secondary database, clogging
> > > the pipeline something awful.
> > >
> > > It seems intuitively odd that the reorg would result in
> > > major logging. Be that as it may, can anybody think of a
> > > way to avoid/circumvent/minimize this problem?
> > >
> > > Thanks in advance for any advice.
> >
> >
>|||I don't think so. He should be able to apply an appropriate differential
backup and then continue with log restores as long as he sticks to
NORECOVERY or STANDBY.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Carlos Eduardo Rojas" <carloser@.mindspring.com> wrote in message
news:OsmrIw07DHA.2656@.TK2MSFTNGP11.phx.gbl...
> Greg,
> That's a good idea, but you'd have to restore the whole database before
> restoring each differential backup. This generates some overhead on the
> destination server, and increases the recovery time.
> --
> Carlos E. Rojas
> SQL Server MVP
> Co-Author SQL Server 2000 Programming by Example
>
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:O4pYPe07DHA.2676@.TK2MSFTNGP10.phx.gbl...
> > Hi Carlos.
> >
> > Would interleaving .diff backups between .tlogs in the shipping process
> > help? A few years back I rolled a custom log shipping solution to solve
> > other problems with log shipping, but I'm wonderinng if shipping .diff
> > backups interleaved with the .tlog backups would mitigate this problem,
> > assuming that the custom solution didn't transfer redundant .tlogs (the
> ones
> > with the dbcc commands)?
> >
> > Regards,
> > Greg Linwood
> > SQL Server MVP
> >
> > "Carlos Eduardo Rojas" <carloser@.mindspring.com> wrote in message
> > news:%23XCx%23Y07DHA.1672@.TK2MSFTNGP12.phx.gbl...
> > > Ken,
> > > I'm facing the same problem too, and AFAIK, there's no way to
circumvent
> > > this problem. I tried INDEXDEFRAG instead of DBREINDEX and it makes it
> > even
> > > worse sometimes.
> > > --
> > > Carlos E. Rojas
> > > SQL Server MVP
> > > Co-Author SQL Server 2000 Programming by Example
> > >
> > >
> > > "Ken C" <anonymous@.discussions.microsoft.com> wrote in message
> > > news:d5ae01c3ef42$eb8e08e0$a401280a@.phx.gbl...
> > > > We're trying to set up log shipping in order to maintain a
> > > > backup/fallover database at a remote location. Everything
> > > > is looking pretty good, EXCEPT that whenever the nightly
> > > > database reorganization takes place on the primary
> > > > database, it apparently logs all the
> > > > various "transactions" resulting from the reorg activity.
> > > > It then ships this log to the secondary database, clogging
> > > > the pipeline something awful.
> > > >
> > > > It seems intuitively odd that the reorg would result in
> > > > major logging. Be that as it may, can anybody think of a
> > > > way to avoid/circumvent/minimize this problem?
> > > >
> > > > Thanks in advance for any advice.
> > >
> > >
> >
> >
>|||I know, but before applying a diff backup, you have to restore the full
database backup first.
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example
"Geoff N.Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:eStNI007DHA.2300@.TK2MSFTNGP10.phx.gbl...
> I don't think so. He should be able to apply an appropriate differential
> backup and then continue with log restores as long as he sticks to
> NORECOVERY or STANDBY.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Carlos Eduardo Rojas" <carloser@.mindspring.com> wrote in message
> news:OsmrIw07DHA.2656@.TK2MSFTNGP11.phx.gbl...
> > Greg,
> > That's a good idea, but you'd have to restore the whole database before
> > restoring each differential backup. This generates some overhead on the
> > destination server, and increases the recovery time.
> > --
> > Carlos E. Rojas
> > SQL Server MVP
> > Co-Author SQL Server 2000 Programming by Example
> >
> >
> > "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> > news:O4pYPe07DHA.2676@.TK2MSFTNGP10.phx.gbl...
> > > Hi Carlos.
> > >
> > > Would interleaving .diff backups between .tlogs in the shipping
process
> > > help? A few years back I rolled a custom log shipping solution to
solve
> > > other problems with log shipping, but I'm wonderinng if shipping .diff
> > > backups interleaved with the .tlog backups would mitigate this
problem,
> > > assuming that the custom solution didn't transfer redundant .tlogs
(the
> > ones
> > > with the dbcc commands)?
> > >
> > > Regards,
> > > Greg Linwood
> > > SQL Server MVP
> > >
> > > "Carlos Eduardo Rojas" <carloser@.mindspring.com> wrote in message
> > > news:%23XCx%23Y07DHA.1672@.TK2MSFTNGP12.phx.gbl...
> > > > Ken,
> > > > I'm facing the same problem too, and AFAIK, there's no way to
> circumvent
> > > > this problem. I tried INDEXDEFRAG instead of DBREINDEX and it makes
it
> > > even
> > > > worse sometimes.
> > > > --
> > > > Carlos E. Rojas
> > > > SQL Server MVP
> > > > Co-Author SQL Server 2000 Programming by Example
> > > >
> > > >
> > > > "Ken C" <anonymous@.discussions.microsoft.com> wrote in message
> > > > news:d5ae01c3ef42$eb8e08e0$a401280a@.phx.gbl...
> > > > > We're trying to set up log shipping in order to maintain a
> > > > > backup/fallover database at a remote location. Everything
> > > > > is looking pretty good, EXCEPT that whenever the nightly
> > > > > database reorganization takes place on the primary
> > > > > database, it apparently logs all the
> > > > > various "transactions" resulting from the reorg activity.
> > > > > It then ships this log to the secondary database, clogging
> > > > > the pipeline something awful.
> > > > >
> > > > > It seems intuitively odd that the reorg would result in
> > > > > major logging. Be that as it may, can anybody think of a
> > > > > way to avoid/circumvent/minimize this problem?
> > > > >
> > > > > Thanks in advance for any advice.
> > > >
> > > >
> > >
> > >
> >
> >
>|||Yep.
The solution I designed was for sql7, & it basically used control tables on
the server and the target, allowed .fbak, .diff & .tlogs to occur on
whatever schedule, then restored headeronly to get lastlsn & only shipped /
restored the "best" option to the target (a .diff if more recent lsn &
smaller than .tlogs).
Although a bit complex, the design goal for this client was to support
shipping to a much smaller box for ad-hoc reporting (a common goal).. The
client I did this for had implemented an e-commerce solution called
"Interworld" that had a db design which used manual identity generation
tables (update idtable.. select max()+1.. scenario) with ga-zillions of
updates yet a very small table space footprint. So it was a similar problem
to your dbcc problem - lots of .tlog updates to a relative to the db size.
These guys were also doing lots of nightly dbcc & I as I recall we tested /
confirmed a benefit in that area too so I think it might help you..
Regards,
Greg Linwood
SQL Server MVP
"Geoff N.Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:eStNI007DHA.2300@.TK2MSFTNGP10.phx.gbl...
> I don't think so. He should be able to apply an appropriate differential
> backup and then continue with log restores as long as he sticks to
> NORECOVERY or STANDBY.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Carlos Eduardo Rojas" <carloser@.mindspring.com> wrote in message
> news:OsmrIw07DHA.2656@.TK2MSFTNGP11.phx.gbl...
> > Greg,
> > That's a good idea, but you'd have to restore the whole database before
> > restoring each differential backup. This generates some overhead on the
> > destination server, and increases the recovery time.
> > --
> > Carlos E. Rojas
> > SQL Server MVP
> > Co-Author SQL Server 2000 Programming by Example
> >
> >
> > "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> > news:O4pYPe07DHA.2676@.TK2MSFTNGP10.phx.gbl...
> > > Hi Carlos.
> > >
> > > Would interleaving .diff backups between .tlogs in the shipping
process
> > > help? A few years back I rolled a custom log shipping solution to
solve
> > > other problems with log shipping, but I'm wonderinng if shipping .diff
> > > backups interleaved with the .tlog backups would mitigate this
problem,
> > > assuming that the custom solution didn't transfer redundant .tlogs
(the
> > ones
> > > with the dbcc commands)?
> > >
> > > Regards,
> > > Greg Linwood
> > > SQL Server MVP
> > >
> > > "Carlos Eduardo Rojas" <carloser@.mindspring.com> wrote in message
> > > news:%23XCx%23Y07DHA.1672@.TK2MSFTNGP12.phx.gbl...
> > > > Ken,
> > > > I'm facing the same problem too, and AFAIK, there's no way to
> circumvent
> > > > this problem. I tried INDEXDEFRAG instead of DBREINDEX and it makes
it
> > > even
> > > > worse sometimes.
> > > > --
> > > > Carlos E. Rojas
> > > > SQL Server MVP
> > > > Co-Author SQL Server 2000 Programming by Example
> > > >
> > > >
> > > > "Ken C" <anonymous@.discussions.microsoft.com> wrote in message
> > > > news:d5ae01c3ef42$eb8e08e0$a401280a@.phx.gbl...
> > > > > We're trying to set up log shipping in order to maintain a
> > > > > backup/fallover database at a remote location. Everything
> > > > > is looking pretty good, EXCEPT that whenever the nightly
> > > > > database reorganization takes place on the primary
> > > > > database, it apparently logs all the
> > > > > various "transactions" resulting from the reorg activity.
> > > > > It then ships this log to the secondary database, clogging
> > > > > the pipeline something awful.
> > > > >
> > > > > It seems intuitively odd that the reorg would result in
> > > > > major logging. Be that as it may, can anybody think of a
> > > > > way to avoid/circumvent/minimize this problem?
> > > > >
> > > > > Thanks in advance for any advice.
> > > >
> > > >
> > >
> > >
> >
> >
>|||Just to clarify - this solution did operate within weekly full db backups..
but you could do something similar within *whatever* .fbak window - monthly,
quarterly etc..
Regards,
Greg Linwood
SQL Server MVP
"Carlos Eduardo Rojas" <carloser@.mindspring.com> wrote in message
news:%23ZtuO307DHA.3880@.tk2msftngp13.phx.gbl...
> I know, but before applying a diff backup, you have to restore the full
> database backup first.
> --
> Carlos E. Rojas
> SQL Server MVP
> Co-Author SQL Server 2000 Programming by Example
>
> "Geoff N.Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:eStNI007DHA.2300@.TK2MSFTNGP10.phx.gbl...
> > I don't think so. He should be able to apply an appropriate
differential
> > backup and then continue with log restores as long as he sticks to
> > NORECOVERY or STANDBY.
> >
> > --
> > Geoff N. Hiten
> > Microsoft SQL Server MVP
> > Senior Database Administrator
> > Careerbuilder.com
> >
> > I support the Professional Association for SQL Server
> > www.sqlpass.org
> >
> > "Carlos Eduardo Rojas" <carloser@.mindspring.com> wrote in message
> > news:OsmrIw07DHA.2656@.TK2MSFTNGP11.phx.gbl...
> > > Greg,
> > > That's a good idea, but you'd have to restore the whole database
before
> > > restoring each differential backup. This generates some overhead on
the
> > > destination server, and increases the recovery time.
> > > --
> > > Carlos E. Rojas
> > > SQL Server MVP
> > > Co-Author SQL Server 2000 Programming by Example
> > >
> > >
> > > "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> > > news:O4pYPe07DHA.2676@.TK2MSFTNGP10.phx.gbl...
> > > > Hi Carlos.
> > > >
> > > > Would interleaving .diff backups between .tlogs in the shipping
> process
> > > > help? A few years back I rolled a custom log shipping solution to
> solve
> > > > other problems with log shipping, but I'm wonderinng if shipping
.diff
> > > > backups interleaved with the .tlog backups would mitigate this
> problem,
> > > > assuming that the custom solution didn't transfer redundant .tlogs
> (the
> > > ones
> > > > with the dbcc commands)?
> > > >
> > > > Regards,
> > > > Greg Linwood
> > > > SQL Server MVP
> > > >
> > > > "Carlos Eduardo Rojas" <carloser@.mindspring.com> wrote in message
> > > > news:%23XCx%23Y07DHA.1672@.TK2MSFTNGP12.phx.gbl...
> > > > > Ken,
> > > > > I'm facing the same problem too, and AFAIK, there's no way to
> > circumvent
> > > > > this problem. I tried INDEXDEFRAG instead of DBREINDEX and it
makes
> it
> > > > even
> > > > > worse sometimes.
> > > > > --
> > > > > Carlos E. Rojas
> > > > > SQL Server MVP
> > > > > Co-Author SQL Server 2000 Programming by Example
> > > > >
> > > > >
> > > > > "Ken C" <anonymous@.discussions.microsoft.com> wrote in message
> > > > > news:d5ae01c3ef42$eb8e08e0$a401280a@.phx.gbl...
> > > > > > We're trying to set up log shipping in order to maintain a
> > > > > > backup/fallover database at a remote location. Everything
> > > > > > is looking pretty good, EXCEPT that whenever the nightly
> > > > > > database reorganization takes place on the primary
> > > > > > database, it apparently logs all the
> > > > > > various "transactions" resulting from the reorg activity.
> > > > > > It then ships this log to the secondary database, clogging
> > > > > > the pipeline something awful.
> > > > > >
> > > > > > It seems intuitively odd that the reorg would result in
> > > > > > major logging. Be that as it may, can anybody think of a
> > > > > > way to avoid/circumvent/minimize this problem?
> > > > > >
> > > > > > Thanks in advance for any advice.
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Afetr reading this thread, a somewhat othorgonal solution is to consider
cutting down the amount of reorging you're doing every night. Are you
reorging just for the sake of it, or do you have empirical evidence that
increasing fragmentation is slowing down your range scans?
Check out the whitepaper below for more details:
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ken C" <anonymous@.discussions.microsoft.com> wrote in message
news:d5ae01c3ef42$eb8e08e0$a401280a@.phx.gbl...
> We're trying to set up log shipping in order to maintain a
> backup/fallover database at a remote location. Everything
> is looking pretty good, EXCEPT that whenever the nightly
> database reorganization takes place on the primary
> database, it apparently logs all the
> various "transactions" resulting from the reorg activity.
> It then ships this log to the secondary database, clogging
> the pipeline something awful.
> It seems intuitively odd that the reorg would result in
> major logging. Be that as it may, can anybody think of a
> way to avoid/circumvent/minimize this problem?
> Thanks in advance for any advice.|||Each time you rebuild the index all pages that are changed end up in the transaction log that gets shipped to the other SQL Instance. The question is, "do you need to rebuild all these indexes all the time?" Have you checked the rate at which EACH index becomes fragmented? It seems that you probably need multiple reindex jobs that run at different frequencies (ie Weekly those indexes that fragment quickly and seriously impact performance, and Monthly/Bimonthly for those indexes that have less effect on query execution time).|||Look in BOL under DBCC SHOWCONTIG for a noce template procedure that can
drive index defragmentation only when the fragmentation level reaches a
threshold you determine. After a short time, you can dramatically reduce
the amount of data pushed through log shipping by only defragging when
necessary.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Stephen Strong" <anonymous@.discussions.microsoft.com> wrote in message
news:4C8E3875-0731-4F49-9D8C-8652CB631D0B@.microsoft.com...
> Each time you rebuild the index all pages that are changed end up in the
transaction log that gets shipped to the other SQL Instance. The question
is, "do you need to rebuild all these indexes all the time?" Have you
checked the rate at which EACH index becomes fragmented? It seems that you
probably need multiple reindex jobs that run at different frequencies (ie
Weekly those indexes that fragment quickly and seriously impact performance,
and Monthly/Bimonthly for those indexes that have less effect on query
execution time).

Log shipping and DB corruption

Hi,
I've set up two log shipping pairs between three Microsoft SQL Server 2000
SP3 databases: a primary, a main standby, and an auxiliary standby database.
For the main standby database jobs that create, copy, and restore transaction
log backups run one after another without any delay. Now if the primary
database fails it may well be because it has become corrupt. According to
documentation on the Microsoft website and considering my set-up (i.e. no
delay for loading transaction logs), corrupt data may be carried over into
the main standby databaseby means of log shipping (could you please elaborate
on how/ in which cases this can happen?) . So in order to deal with a
situation, when it's not possible to switch over to the main standby
database, because corrupt data has been carried over into it, I maintain a
lagged auxiliary standby database, which could be restored up to the moment
of corruption and used instead. The question is, how can I determine this
moment? Is it the moment a message about the corrupt primary is logged in the
SQL Server error log? Can this moment be determined at all? How would you
deal with the situation I described?
Respectfully,
Oskar
Oskar
First of all yes it will be logged into ERROR.LOG . At my work place I run
DBCC CHECKDB on regular period.
If the database get corrupted but users still have an access ,so run DBCC
CHECKDB (see more in the BOL) .Personally I have not tried to run this
command on "mirrored" database , so worth to test.
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:B6562D0D-99C4-4291-81DB-A5C67D5DE334@.microsoft.com...
> Hi,
> I've set up two log shipping pairs between three Microsoft SQL Server 2000
> SP3 databases: a primary, a main standby, and an auxiliary standby
> database.
> For the main standby database jobs that create, copy, and restore
> transaction
> log backups run one after another without any delay. Now if the primary
> database fails it may well be because it has become corrupt. According to
> documentation on the Microsoft website and considering my set-up (i.e. no
> delay for loading transaction logs), corrupt data may be carried over into
> the main standby databaseby means of log shipping (could you please
> elaborate
> on how/ in which cases this can happen?) . So in order to deal with a
> situation, when it's not possible to switch over to the main standby
> database, because corrupt data has been carried over into it, I maintain a
> lagged auxiliary standby database, which could be restored up to the
> moment
> of corruption and used instead. The question is, how can I determine this
> moment? Is it the moment a message about the corrupt primary is logged in
> the
> SQL Server error log? Can this moment be determined at all? How would you
> deal with the situation I described?
> Respectfully,
> Oskar
>
|||Very often, the cause is data corruption is the user or administration. For
example, someone *thought* they were in test but they were in prod and
deleted or modified data. It's very important to know when the data was
corrupted because that is the point at which you want to stop restoring your
log.
If you copy the log over to your standby server right after it is backed up,
that's a good thing. Putting a delay of the restoration of the log gives
you a bit of time to investigate. At the very least, you can restore WITH
STANDBY to have a look at what the data looked like and then copy that data
back to the primary.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:B6562D0D-99C4-4291-81DB-A5C67D5DE334@.microsoft.com...
Hi,
I've set up two log shipping pairs between three Microsoft SQL Server 2000
SP3 databases: a primary, a main standby, and an auxiliary standby database.
For the main standby database jobs that create, copy, and restore
transaction
log backups run one after another without any delay. Now if the primary
database fails it may well be because it has become corrupt. According to
documentation on the Microsoft website and considering my set-up (i.e. no
delay for loading transaction logs), corrupt data may be carried over into
the main standby databaseby means of log shipping (could you please
elaborate
on how/ in which cases this can happen?) . So in order to deal with a
situation, when it's not possible to switch over to the main standby
database, because corrupt data has been carried over into it, I maintain a
lagged auxiliary standby database, which could be restored up to the moment
of corruption and used instead. The question is, how can I determine this
moment? Is it the moment a message about the corrupt primary is logged in
the
SQL Server error log? Can this moment be determined at all? How would you
deal with the situation I described?
Respectfully,
Oskar
|||Thanks Tom. Though by the word "corruption" here I meant the corruption of
SQL Server's internal storage structures (for lack of a better name),
detected by the CHECKDB command, not the application or user negligence.
The question still remains, how can I determine the moment, just before
corruption was loaded into the main standby database, after which all the
remaining transaction log backups are to be discarded?
"Tom Moreau" wrote:

> Very often, the cause is data corruption is the user or administration. For
> example, someone *thought* they were in test but they were in prod and
> deleted or modified data. It's very important to know when the data was
> corrupted because that is the point at which you want to stop restoring your
> log.
> If you copy the log over to your standby server right after it is backed up,
> that's a good thing. Putting a delay of the restoration of the log gives
> you a bit of time to investigate. At the very least, you can restore WITH
> STANDBY to have a look at what the data looked like and then copy that data
> back to the primary.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:B6562D0D-99C4-4291-81DB-A5C67D5DE334@.microsoft.com...
> Hi,
> I've set up two log shipping pairs between three Microsoft SQL Server 2000
> SP3 databases: a primary, a main standby, and an auxiliary standby database.
> For the main standby database jobs that create, copy, and restore
> transaction
> log backups run one after another without any delay. Now if the primary
> database fails it may well be because it has become corrupt. According to
> documentation on the Microsoft website and considering my set-up (i.e. no
> delay for loading transaction logs), corrupt data may be carried over into
> the main standby databaseby means of log shipping (could you please
> elaborate
> on how/ in which cases this can happen?) . So in order to deal with a
> situation, when it's not possible to switch over to the main standby
> database, because corrupt data has been carried over into it, I maintain a
> lagged auxiliary standby database, which could be restored up to the moment
> of corruption and used instead. The question is, how can I determine this
> moment? Is it the moment a message about the corrupt primary is logged in
> the
> SQL Server error log? Can this moment be determined at all? How would you
> deal with the situation I described?
> Respectfully,
> Oskar
>
|||The error log is your best bet - if SQL Server detected the corruption.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:16E74120-24C8-4C6E-B937-61A32EB727CA@.microsoft.com...
Thanks Tom. Though by the word "corruption" here I meant the corruption of
SQL Server's internal storage structures (for lack of a better name),
detected by the CHECKDB command, not the application or user negligence.
The question still remains, how can I determine the moment, just before
corruption was loaded into the main standby database, after which all the
remaining transaction log backups are to be discarded?
"Tom Moreau" wrote:

> Very often, the cause is data corruption is the user or administration.
> For
> example, someone *thought* they were in test but they were in prod and
> deleted or modified data. It's very important to know when the data was
> corrupted because that is the point at which you want to stop restoring
> your
> log.
> If you copy the log over to your standby server right after it is backed
> up,
> that's a good thing. Putting a delay of the restoration of the log gives
> you a bit of time to investigate. At the very least, you can restore WITH
> STANDBY to have a look at what the data looked like and then copy that
> data
> back to the primary.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:B6562D0D-99C4-4291-81DB-A5C67D5DE334@.microsoft.com...
> Hi,
> I've set up two log shipping pairs between three Microsoft SQL Server 2000
> SP3 databases: a primary, a main standby, and an auxiliary standby
> database.
> For the main standby database jobs that create, copy, and restore
> transaction
> log backups run one after another without any delay. Now if the primary
> database fails it may well be because it has become corrupt. According to
> documentation on the Microsoft website and considering my set-up (i.e. no
> delay for loading transaction logs), corrupt data may be carried over into
> the main standby databaseby means of log shipping (could you please
> elaborate
> on how/ in which cases this can happen?) . So in order to deal with a
> situation, when it's not possible to switch over to the main standby
> database, because corrupt data has been carried over into it, I maintain a
> lagged auxiliary standby database, which could be restored up to the
> moment
> of corruption and used instead. The question is, how can I determine this
> moment? Is it the moment a message about the corrupt primary is logged in
> the
> SQL Server error log? Can this moment be determined at all? How would you
> deal with the situation I described?
> Respectfully,
> Oskar
>
|||Apparently the problem with this is that the error message may be logged only
some time after the corruption. How long does it take to log the message
after the corruption has taken place?
"Tom Moreau" wrote:

> The error log is your best bet - if SQL Server detected the corruption.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:16E74120-24C8-4C6E-B937-61A32EB727CA@.microsoft.com...
> Thanks Tom. Though by the word "corruption" here I meant the corruption of
> SQL Server's internal storage structures (for lack of a better name),
> detected by the CHECKDB command, not the application or user negligence.
> The question still remains, how can I determine the moment, just before
> corruption was loaded into the main standby database, after which all the
> remaining transaction log backups are to be discarded?
>
> "Tom Moreau" wrote:
>
>
|||That depends. If SQL Server notices it, it goes into the log right away.
However, I have seen situations where it does not notice it and you find out
the hard way.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:F4DDD609-5D12-4E92-A6A9-6A437EE5C746@.microsoft.com...
Apparently the problem with this is that the error message may be logged
only
some time after the corruption. How long does it take to log the message
after the corruption has taken place?
"Tom Moreau" wrote:

> The error log is your best bet - if SQL Server detected the corruption.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:16E74120-24C8-4C6E-B937-61A32EB727CA@.microsoft.com...
> Thanks Tom. Though by the word "corruption" here I meant the corruption of
> SQL Server's internal storage structures (for lack of a better name),
> detected by the CHECKDB command, not the application or user negligence.
> The question still remains, how can I determine the moment, just before
> corruption was loaded into the main standby database, after which all the
> remaining transaction log backups are to be discarded?
>
> "Tom Moreau" wrote:
>
>

Log shipping and DB corruption

Hi,
I've set up two log shipping pairs between three Microsoft SQL Server 2000
SP3 databases: a primary, a main standby, and an auxiliary standby database.
For the main standby database jobs that create, copy, and restore transaction
log backups run one after another without any delay. Now if the primary
database fails it may well be because it has become corrupt. According to
documentation on the Microsoft website and considering my set-up (i.e. no
delay for loading transaction logs), corrupt data may be carried over into
the main standby databaseby means of log shipping (could you please elaborate
on how/ in which cases this can happen?) . So in order to deal with a
situation, when it's not possible to switch over to the main standby
database, because corrupt data has been carried over into it, I maintain a
lagged auxiliary standby database, which could be restored up to the moment
of corruption and used instead. The question is, how can I determine this
moment? Is it the moment a message about the corrupt primary is logged in the
SQL Server error log? Can this moment be determined at all? How would you
deal with the situation I described?
Respectfully,
OskarOskar
First of all yes it will be logged into ERROR.LOG . At my work place I run
DBCC CHECKDB on regular period.
If the database get corrupted but users still have an access ,so run DBCC
CHECKDB (see more in the BOL) .Personally I have not tried to run this
command on "mirrored" database , so worth to test.
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:B6562D0D-99C4-4291-81DB-A5C67D5DE334@.microsoft.com...
> Hi,
> I've set up two log shipping pairs between three Microsoft SQL Server 2000
> SP3 databases: a primary, a main standby, and an auxiliary standby
> database.
> For the main standby database jobs that create, copy, and restore
> transaction
> log backups run one after another without any delay. Now if the primary
> database fails it may well be because it has become corrupt. According to
> documentation on the Microsoft website and considering my set-up (i.e. no
> delay for loading transaction logs), corrupt data may be carried over into
> the main standby databaseby means of log shipping (could you please
> elaborate
> on how/ in which cases this can happen?) . So in order to deal with a
> situation, when it's not possible to switch over to the main standby
> database, because corrupt data has been carried over into it, I maintain a
> lagged auxiliary standby database, which could be restored up to the
> moment
> of corruption and used instead. The question is, how can I determine this
> moment? Is it the moment a message about the corrupt primary is logged in
> the
> SQL Server error log? Can this moment be determined at all? How would you
> deal with the situation I described?
> Respectfully,
> Oskar
>|||Very often, the cause is data corruption is the user or administration. For
example, someone *thought* they were in test but they were in prod and
deleted or modified data. It's very important to know when the data was
corrupted because that is the point at which you want to stop restoring your
log.
If you copy the log over to your standby server right after it is backed up,
that's a good thing. Putting a delay of the restoration of the log gives
you a bit of time to investigate. At the very least, you can restore WITH
STANDBY to have a look at what the data looked like and then copy that data
back to the primary.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:B6562D0D-99C4-4291-81DB-A5C67D5DE334@.microsoft.com...
Hi,
I've set up two log shipping pairs between three Microsoft SQL Server 2000
SP3 databases: a primary, a main standby, and an auxiliary standby database.
For the main standby database jobs that create, copy, and restore
transaction
log backups run one after another without any delay. Now if the primary
database fails it may well be because it has become corrupt. According to
documentation on the Microsoft website and considering my set-up (i.e. no
delay for loading transaction logs), corrupt data may be carried over into
the main standby databaseby means of log shipping (could you please
elaborate
on how/ in which cases this can happen?) . So in order to deal with a
situation, when it's not possible to switch over to the main standby
database, because corrupt data has been carried over into it, I maintain a
lagged auxiliary standby database, which could be restored up to the moment
of corruption and used instead. The question is, how can I determine this
moment? Is it the moment a message about the corrupt primary is logged in
the
SQL Server error log? Can this moment be determined at all? How would you
deal with the situation I described?
Respectfully,
Oskar|||Thanks Tom. Though by the word "corruption" here I meant the corruption of
SQL Server's internal storage structures (for lack of a better name),
detected by the CHECKDB command, not the application or user negligence.
The question still remains, how can I determine the moment, just before
corruption was loaded into the main standby database, after which all the
remaining transaction log backups are to be discarded?
"Tom Moreau" wrote:
> Very often, the cause is data corruption is the user or administration. For
> example, someone *thought* they were in test but they were in prod and
> deleted or modified data. It's very important to know when the data was
> corrupted because that is the point at which you want to stop restoring your
> log.
> If you copy the log over to your standby server right after it is backed up,
> that's a good thing. Putting a delay of the restoration of the log gives
> you a bit of time to investigate. At the very least, you can restore WITH
> STANDBY to have a look at what the data looked like and then copy that data
> back to the primary.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:B6562D0D-99C4-4291-81DB-A5C67D5DE334@.microsoft.com...
> Hi,
> I've set up two log shipping pairs between three Microsoft SQL Server 2000
> SP3 databases: a primary, a main standby, and an auxiliary standby database.
> For the main standby database jobs that create, copy, and restore
> transaction
> log backups run one after another without any delay. Now if the primary
> database fails it may well be because it has become corrupt. According to
> documentation on the Microsoft website and considering my set-up (i.e. no
> delay for loading transaction logs), corrupt data may be carried over into
> the main standby databaseby means of log shipping (could you please
> elaborate
> on how/ in which cases this can happen?) . So in order to deal with a
> situation, when it's not possible to switch over to the main standby
> database, because corrupt data has been carried over into it, I maintain a
> lagged auxiliary standby database, which could be restored up to the moment
> of corruption and used instead. The question is, how can I determine this
> moment? Is it the moment a message about the corrupt primary is logged in
> the
> SQL Server error log? Can this moment be determined at all? How would you
> deal with the situation I described?
> Respectfully,
> Oskar
>|||The error log is your best bet - if SQL Server detected the corruption.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:16E74120-24C8-4C6E-B937-61A32EB727CA@.microsoft.com...
Thanks Tom. Though by the word "corruption" here I meant the corruption of
SQL Server's internal storage structures (for lack of a better name),
detected by the CHECKDB command, not the application or user negligence.
The question still remains, how can I determine the moment, just before
corruption was loaded into the main standby database, after which all the
remaining transaction log backups are to be discarded?
"Tom Moreau" wrote:
> Very often, the cause is data corruption is the user or administration.
> For
> example, someone *thought* they were in test but they were in prod and
> deleted or modified data. It's very important to know when the data was
> corrupted because that is the point at which you want to stop restoring
> your
> log.
> If you copy the log over to your standby server right after it is backed
> up,
> that's a good thing. Putting a delay of the restoration of the log gives
> you a bit of time to investigate. At the very least, you can restore WITH
> STANDBY to have a look at what the data looked like and then copy that
> data
> back to the primary.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:B6562D0D-99C4-4291-81DB-A5C67D5DE334@.microsoft.com...
> Hi,
> I've set up two log shipping pairs between three Microsoft SQL Server 2000
> SP3 databases: a primary, a main standby, and an auxiliary standby
> database.
> For the main standby database jobs that create, copy, and restore
> transaction
> log backups run one after another without any delay. Now if the primary
> database fails it may well be because it has become corrupt. According to
> documentation on the Microsoft website and considering my set-up (i.e. no
> delay for loading transaction logs), corrupt data may be carried over into
> the main standby databaseby means of log shipping (could you please
> elaborate
> on how/ in which cases this can happen?) . So in order to deal with a
> situation, when it's not possible to switch over to the main standby
> database, because corrupt data has been carried over into it, I maintain a
> lagged auxiliary standby database, which could be restored up to the
> moment
> of corruption and used instead. The question is, how can I determine this
> moment? Is it the moment a message about the corrupt primary is logged in
> the
> SQL Server error log? Can this moment be determined at all? How would you
> deal with the situation I described?
> Respectfully,
> Oskar
>|||Apparently the problem with this is that the error message may be logged only
some time after the corruption. How long does it take to log the message
after the corruption has taken place?
"Tom Moreau" wrote:
> The error log is your best bet - if SQL Server detected the corruption.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:16E74120-24C8-4C6E-B937-61A32EB727CA@.microsoft.com...
> Thanks Tom. Though by the word "corruption" here I meant the corruption of
> SQL Server's internal storage structures (for lack of a better name),
> detected by the CHECKDB command, not the application or user negligence.
> The question still remains, how can I determine the moment, just before
> corruption was loaded into the main standby database, after which all the
> remaining transaction log backups are to be discarded?
>
> "Tom Moreau" wrote:
> > Very often, the cause is data corruption is the user or administration.
> > For
> > example, someone *thought* they were in test but they were in prod and
> > deleted or modified data. It's very important to know when the data was
> > corrupted because that is the point at which you want to stop restoring
> > your
> > log.
> >
> > If you copy the log over to your standby server right after it is backed
> > up,
> > that's a good thing. Putting a delay of the restoration of the log gives
> > you a bit of time to investigate. At the very least, you can restore WITH
> > STANDBY to have a look at what the data looked like and then copy that
> > data
> > back to the primary.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> > SQL Server MVP
> > Toronto, ON Canada
> > https://mvp.support.microsoft.com/profile/Tom.Moreau
> >
> >
> > "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> > news:B6562D0D-99C4-4291-81DB-A5C67D5DE334@.microsoft.com...
> > Hi,
> > I've set up two log shipping pairs between three Microsoft SQL Server 2000
> > SP3 databases: a primary, a main standby, and an auxiliary standby
> > database.
> > For the main standby database jobs that create, copy, and restore
> > transaction
> > log backups run one after another without any delay. Now if the primary
> > database fails it may well be because it has become corrupt. According to
> > documentation on the Microsoft website and considering my set-up (i.e. no
> > delay for loading transaction logs), corrupt data may be carried over into
> > the main standby databaseby means of log shipping (could you please
> > elaborate
> > on how/ in which cases this can happen?) . So in order to deal with a
> > situation, when it's not possible to switch over to the main standby
> > database, because corrupt data has been carried over into it, I maintain a
> > lagged auxiliary standby database, which could be restored up to the
> > moment
> > of corruption and used instead. The question is, how can I determine this
> > moment? Is it the moment a message about the corrupt primary is logged in
> > the
> > SQL Server error log? Can this moment be determined at all? How would you
> > deal with the situation I described?
> >
> > Respectfully,
> > Oskar
> >
> >
>|||That depends. If SQL Server notices it, it goes into the log right away.
However, I have seen situations where it does not notice it and you find out
the hard way.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:F4DDD609-5D12-4E92-A6A9-6A437EE5C746@.microsoft.com...
Apparently the problem with this is that the error message may be logged
only
some time after the corruption. How long does it take to log the message
after the corruption has taken place?
"Tom Moreau" wrote:
> The error log is your best bet - if SQL Server detected the corruption.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:16E74120-24C8-4C6E-B937-61A32EB727CA@.microsoft.com...
> Thanks Tom. Though by the word "corruption" here I meant the corruption of
> SQL Server's internal storage structures (for lack of a better name),
> detected by the CHECKDB command, not the application or user negligence.
> The question still remains, how can I determine the moment, just before
> corruption was loaded into the main standby database, after which all the
> remaining transaction log backups are to be discarded?
>
> "Tom Moreau" wrote:
> > Very often, the cause is data corruption is the user or administration.
> > For
> > example, someone *thought* they were in test but they were in prod and
> > deleted or modified data. It's very important to know when the data was
> > corrupted because that is the point at which you want to stop restoring
> > your
> > log.
> >
> > If you copy the log over to your standby server right after it is backed
> > up,
> > that's a good thing. Putting a delay of the restoration of the log
> > gives
> > you a bit of time to investigate. At the very least, you can restore
> > WITH
> > STANDBY to have a look at what the data looked like and then copy that
> > data
> > back to the primary.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> > SQL Server MVP
> > Toronto, ON Canada
> > https://mvp.support.microsoft.com/profile/Tom.Moreau
> >
> >
> > "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> > news:B6562D0D-99C4-4291-81DB-A5C67D5DE334@.microsoft.com...
> > Hi,
> > I've set up two log shipping pairs between three Microsoft SQL Server
> > 2000
> > SP3 databases: a primary, a main standby, and an auxiliary standby
> > database.
> > For the main standby database jobs that create, copy, and restore
> > transaction
> > log backups run one after another without any delay. Now if the primary
> > database fails it may well be because it has become corrupt. According
> > to
> > documentation on the Microsoft website and considering my set-up (i.e.
> > no
> > delay for loading transaction logs), corrupt data may be carried over
> > into
> > the main standby databaseby means of log shipping (could you please
> > elaborate
> > on how/ in which cases this can happen?) . So in order to deal with a
> > situation, when it's not possible to switch over to the main standby
> > database, because corrupt data has been carried over into it, I maintain
> > a
> > lagged auxiliary standby database, which could be restored up to the
> > moment
> > of corruption and used instead. The question is, how can I determine
> > this
> > moment? Is it the moment a message about the corrupt primary is logged
> > in
> > the
> > SQL Server error log? Can this moment be determined at all? How would
> > you
> > deal with the situation I described?
> >
> > Respectfully,
> > Oskar
> >
> >
>

Friday, February 24, 2012

Log Shipping and loading service packs

Log Shipping and loading service packs

Log Shipping and loading service packs

Log shipping and index rebuilds

Log shipping and index rebuilds

Log shipping and index rebuilds

log shipping and growing of trans log on backup server

log shipping and Full text indexing

log shipping and Full text indexing

log shipping and Full text indexing

Log shipping and Full Text

Log shipping and error 22029

Log shipping and DTS packages update

Log shipping and DB reorgs

Log shipping and DB reorgs

Log shipping and DB corruption

Log shipping and DB corruption

Log shipping alerts

Blog Archive

About Me