Log Shipping Transaction Logs.

Hi,

We currently have a couple a large Databases running on SQL 2000 SP3 Clustered Windows 2000 SP3 environment.

Log Shipping is enabled for both databases shipping to a Standalone SQL 2000 SP3 Windows 2000 SP3 box.

Log Shipping occurs every 15 mins with the Transaction Files on average being no more than 500KB in size. However, every now and then a Transaction Log comes through and it can be as big as 3.52GB.

Not sure why this is happening. Anyone got any ideas?

Regards

Paul TowlerIs this after substantial activity or reindexing?|||i think auto shrink or some other job is enabled
if iam right, the auto shrink or job gets activated and the step goes thru
which will obviously create transaction file with huge size.|||Thanks for your replies.

There is a Job to Optimise the Database which does re-index the database. I presume all the indexing changes count as changes and therefore shipped as one big Transacton file.

Hopefully there is a way to prevent these hugh Transaction Files without turning off the indexing job.

Regards
Paul Towlersql

Log shipping sync error 14421

Hi
SQLServer 2000 sp3 enterprise running logshipping smoothly, today its
broken, out of sync error for all databases on standby server error
14421. I have manually restored all the log backups and updated manually
log_shipping_secondaries columns set last_copied_last_updated &
last_loaded_last_updated with 15 min difference which i have configured.
But the log backups are not copied &last_copied_filename is not getting
updated. how can i fix this issues and continue log shipping with out
reconfiguring?
thanks for looking
kris
Did you look at:
Description of error message 14420 and error message 14421 that occur when
you use log shipping in SQL Server
http://support.microsoft.com/kb/329133
?
"krishna" wrote:

> Hi
> SQLServer 2000 sp3 enterprise running logshipping smoothly, today its
> broken, out of sync error for all databases on standby server error
> 14421. I have manually restored all the log backups and updated manually
> log_shipping_secondaries columns set last_copied_last_updated &
> last_loaded_last_updated with 15 min difference which i have configured.
> But the log backups are not copied &last_copied_filename is not getting
> updated. how can i fix this issues and continue log shipping with out
> reconfiguring?
> thanks for looking
> kris
>
|||I looked into that kb article, i have tried accessing the folder on the
standby server from source machine. it is fine. i dont know why the logs
are not copied to standby server. I just want to fix the issue without
reconfiguring. Is there any way to copy & restore the remaining logs and
updating the logshipping tables and continue the process?
Edgardo Valdez wrote:[vbcol=seagreen]
> Did you look at:
> Description of error message 14420 and error message 14421 that occur when
> you use log shipping in SQL Server
> http://support.microsoft.com/kb/329133
> ?
> "krishna" wrote:
|||I looked into that kb article, i have tried accessing the folder on the
standby server from source machine. it is fine. i dont know why the logs
are not copied to standby server. I just want to fix the issue without
reconfiguring. Is there any way to copy & restore the remaining logs and
updating the logshipping tables and continue the process?
krishna wrote:
> Hi
> SQLServer 2000 sp3 enterprise running logshipping smoothly, today its
> broken, out of sync error for all databases on standby server error
> 14421. I have manually restored all the log backups and updated manually
> log_shipping_secondaries columns set last_copied_last_updated &
> last_loaded_last_updated with 15 min difference which i have configured.
> But the log backups are not copied &last_copied_filename is not getting
> updated. how can i fix this issues and continue log shipping with out
> reconfiguring?
> thanks for looking
> kris

Log shipping sync error 14421

Hi
SQLServer 2000 sp3 enterprise running logshipping smoothly, today its
broken, out of sync error for all databases on standby server error
14421. I have manually restored all the log backups and updated manually
log_shipping_secondaries columns set last_copied_last_updated &
last_loaded_last_updated with 15 min difference which i have configured.
But the log backups are not copied &last_copied_filename is not getting
updated. how can i fix this issues and continue log shipping with out
reconfiguring?
thanks for looking
krisDid you look at:
Description of error message 14420 and error message 14421 that occur when
you use log shipping in SQL Server
http://support.microsoft.com/kb/329133
?
"krishna" wrote:

> Hi
> SQLServer 2000 sp3 enterprise running logshipping smoothly, today its
> broken, out of sync error for all databases on standby server error
> 14421. I have manually restored all the log backups and updated manually
> log_shipping_secondaries columns set last_copied_last_updated &
> last_loaded_last_updated with 15 min difference which i have configured.
> But the log backups are not copied &last_copied_filename is not getting
> updated. how can i fix this issues and continue log shipping with out
> reconfiguring?
> thanks for looking
> kris
>|||I looked into that kb article, i have tried accessing the folder on the
standby server from source machine. it is fine. i dont know why the logs
are not copied to standby server. I just want to fix the issue without
reconfiguring. Is there any way to copy & restore the remaining logs and
updating the logshipping tables and continue the process?
Edgardo Valdez wrote:[vbcol=seagreen]
> Did you look at:
> Description of error message 14420 and error message 14421 that occur when
> you use log shipping in SQL Server
> http://support.microsoft.com/kb/329133
> ?
> "krishna" wrote:
>|||I looked into that kb article, i have tried accessing the folder on the
standby server from source machine. it is fine. i dont know why the logs
are not copied to standby server. I just want to fix the issue without
reconfiguring. Is there any way to copy & restore the remaining logs and
updating the logshipping tables and continue the process?
krishna wrote:
> Hi
> SQLServer 2000 sp3 enterprise running logshipping smoothly, today its
> broken, out of sync error for all databases on standby server error
> 14421. I have manually restored all the log backups and updated manually
> log_shipping_secondaries columns set last_copied_last_updated &
> last_loaded_last_updated with 15 min difference which i have configured.
> But the log backups are not copied &last_copied_filename is not getting
> updated. how can i fix this issues and continue log shipping with out
> reconfiguring?
> thanks for looking
> kris

Log shipping sync error 14421

Hi
SQLServer 2000 sp3 enterprise running logshipping smoothly, today its
broken, out of sync error for all databases on standby server error
14421. I have manually restored all the log backups and updated manually
log_shipping_secondaries columns set last_copied_last_updated &
last_loaded_last_updated with 15 min difference which i have configured.
But the log backups are not copied &last_copied_filename is not getting
updated. how can i fix this issues and continue log shipping with out
reconfiguring?
thanks for looking
krisDid you look at:
Description of error message 14420 and error message 14421 that occur when
you use log shipping in SQL Server
http://support.microsoft.com/kb/329133
?
"krishna" wrote:
> Hi
> SQLServer 2000 sp3 enterprise running logshipping smoothly, today its
> broken, out of sync error for all databases on standby server error
> 14421. I have manually restored all the log backups and updated manually
> log_shipping_secondaries columns set last_copied_last_updated &
> last_loaded_last_updated with 15 min difference which i have configured.
> But the log backups are not copied &last_copied_filename is not getting
> updated. how can i fix this issues and continue log shipping with out
> reconfiguring?
> thanks for looking
> kris
>|||I looked into that kb article, i have tried accessing the folder on the
standby server from source machine. it is fine. i dont know why the logs
are not copied to standby server. I just want to fix the issue without
reconfiguring. Is there any way to copy & restore the remaining logs and
updating the logshipping tables and continue the process?
Edgardo Valdez wrote:
> Did you look at:
> Description of error message 14420 and error message 14421 that occur when
> you use log shipping in SQL Server
> http://support.microsoft.com/kb/329133
> ?
> "krishna" wrote:
>> Hi
>> SQLServer 2000 sp3 enterprise running logshipping smoothly, today its
>> broken, out of sync error for all databases on standby server error
>> 14421. I have manually restored all the log backups and updated manually
>> log_shipping_secondaries columns set last_copied_last_updated &
>> last_loaded_last_updated with 15 min difference which i have configured.
>> But the log backups are not copied &last_copied_filename is not getting
>> updated. how can i fix this issues and continue log shipping with out
>> reconfiguring?
>> thanks for looking
>> kris|||I looked into that kb article, i have tried accessing the folder on the
standby server from source machine. it is fine. i dont know why the logs
are not copied to standby server. I just want to fix the issue without
reconfiguring. Is there any way to copy & restore the remaining logs and
updating the logshipping tables and continue the process?
krishna wrote:
> Hi
> SQLServer 2000 sp3 enterprise running logshipping smoothly, today its
> broken, out of sync error for all databases on standby server error
> 14421. I have manually restored all the log backups and updated manually
> log_shipping_secondaries columns set last_copied_last_updated &
> last_loaded_last_updated with 15 min difference which i have configured.
> But the log backups are not copied &last_copied_filename is not getting
> updated. how can i fix this issues and continue log shipping with out
> reconfiguring?
> thanks for looking
> kris

Log Shipping Role Reversal

I have log shipping configured on a pair of test SQL 2000 servers, both are
at SP3. The log shipping is working fine, but the problem occurs when I try
to reverse roles. I run this SQL statement on the primary server:
USE master
GO
EXEC msdb.dbo.sp_change_primary_role
@.db_name = 'ModelCopy',
@.backup_log = 1,
@.terminate = 1,
@.final_state = 3,
@.access_level = 1
This places the primary database into read-only mode. I then run this SQL
statement on the secondary server.
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
At which time I get this error:
Server: Msg 927, Level 14, State 2, Line 1
Database 'ModelCopy' cannot be opened. It is in the middle of a restore.
Server: Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Server: Msg 14440, Level 16, State 1, Procedure sp_change_secondary_role,
Line 49
Could not set single user mode.
In each case, I am runnig the statements in Query Analyzer with the
Enterprise Manager window closed. I understand that these statements cause
the primary to make a final copy and the secondary to do a final restore, but
the database I'm using is very small (a copy of the Model databae) and
shouldn't take but a few seconds, which is all the time it took to create and
initialize the database on the secondary to begin with. Is this process hung,
or am I just being impatient? It's been over 20 minutes.
TIA,
Ken
Ken,
See the KB article 294397 BUG: sp_change_secondary_role Fails with Error
3101 if There Are Outstanding Transaction Log Backups
http://support.microsoft.com/default...&Product=sql2k
This is similar, though not identical, to the error you are getting. The
upshot is that you need to make sure that all the transaction logs have been
restored if you set the @.terminate parameter to 1.
Hope this helps,
Ron
Ron Talmage
SQL Server MVP
"kmkrause2" <kmkrause2@.discussions.microsoft.com> wrote in message
news:733456B1-BC99-43B2-B6BD-4030A08DB6EC@.microsoft.com...
> I have log shipping configured on a pair of test SQL 2000 servers, both
are
> at SP3. The log shipping is working fine, but the problem occurs when I
try
> to reverse roles. I run this SQL statement on the primary server:
> USE master
> GO
> EXEC msdb.dbo.sp_change_primary_role
> @.db_name = 'ModelCopy',
> @.backup_log = 1,
> @.terminate = 1,
> @.final_state = 3,
> @.access_level = 1
> This places the primary database into read-only mode. I then run this SQL
> statement on the secondary server.
> 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
> At which time I get this error:
> Server: Msg 927, Level 14, State 2, Line 1
> Database 'ModelCopy' cannot be opened. It is in the middle of a restore.
> Server: Msg 5069, Level 16, State 1, Line 1
> ALTER DATABASE statement failed.
> Server: Msg 14440, Level 16, State 1, Procedure sp_change_secondary_role,
> Line 49
> Could not set single user mode.
> In each case, I am runnig the statements in Query Analyzer with the
> Enterprise Manager window closed. I understand that these statements cause
> the primary to make a final copy and the secondary to do a final restore,
but
> the database I'm using is very small (a copy of the Model databae) and
> shouldn't take but a few seconds, which is all the time it took to create
and
> initialize the database on the secondary to begin with. Is this process
hung,
> or am I just being impatient? It's been over 20 minutes.
> TIA,
> Ken
|||Thanks for the info Ron. It pointed me in the right direction. I still needed
to to a detach/attach sequesnce on the database before running the secondary
stored procedure, but at least it is working in a predictable manner now.
"Ron Talmage" wrote:

> Ken,
> See the KB article 294397 BUG: sp_change_secondary_role Fails with Error
> 3101 if There Are Outstanding Transaction Log Backups
> http://support.microsoft.com/default...&Product=sql2k
> This is similar, though not identical, to the error you are getting. The
> upshot is that you need to make sure that all the transaction logs have been
> restored if you set the @.terminate parameter to 1.
> Hope this helps,
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "kmkrause2" <kmkrause2@.discussions.microsoft.com> wrote in message
> news:733456B1-BC99-43B2-B6BD-4030A08DB6EC@.microsoft.com...
> are
> try
> but
> and
> hung,
>
>
|||OK, I spoke too soon. Occasionally, the sp_change_secondary_role procedure
will end with a "sqlmaint.exe failed. [SQLSTATE 42000][Error 22029]. The step
failed." message. I haven't been able to find any helpful information on this
message yet. Can you shed some more light on what is happening during this
procedure? Same scripts as posted previously in this thread, and include the
manual running of the restore job and a database detach/attach sequence in
Enterprise Manager immediately prior to running the secondary role change
stpred procedure.
Thanks Again,
Ken
"kmkrause2" wrote:
[vbcol=seagreen]
> Thanks for the info Ron. It pointed me in the right direction. I still needed
> to to a detach/attach sequesnce on the database before running the secondary
> stored procedure, but at least it is working in a predictable manner now.
> "Ron Talmage" wrote:

Log Shipping functionality questions

SQL2K sp3
I need to have a basic understanding of LS in order to
answer some Disaster Recovery questions today. I
understand that it takes TLog backups from BoxA(Production
Server in use) and restores them to BoxB(secondary server
that those backups are being sent too) using NoRecovery. I
also understand that if BoxA dies BoxB can perform a Role
Change and become the Production Box. This part Im clear
on.
But what Im not clear on once BoxA is back online what
takes place? Im pretty sure you can do another Role Change
make BoxA the Production Box again. But what actually
takes place here?
Does BoxA go through a Restore process of all its old
backups using NoRecovery, have the latest TLog backups
shipped from BoxB, restore those with Recovery, and then
become Production again? This is what Im guessing would
need to happen in order to accomplish a Role Change from
BoxB to BoxA. But Im not sure and could use some insight.
Of course I JUST found out I need to know this stuff
IMMEDIATELY from the higher ups or else I would just try
testing this out on my own. So sorry for the lazy
questions here.
TIA, ChrisRfull backup of B applied to A. Then apply Trnxn Logs From B To A and A is
"HOT" again.
not sure if that answers your question.
are you planning on using the maintenance plan stuff...aka "Log shipping
Wizard" ?
Greg Jackson
PDX, Oregon|||>full backup of B applied to A. Then apply Trnxn Logs From
B To A and A is
>"HOT" again.
So I actually have to do a full transfer over the line
from B to A? Is this correct? How do companies with large
db's use this as an Enterprise solution? Im not trying to
sound ungrateful for your help, just trying to get a clear
understanding.
>are you planning on using the maintenance plan
stuff...aka "Log shipping
>Wizard" ?
Yes.
>--Original Message--
>full backup of B applied to A. Then apply Trnxn Logs From
B To A and A is
>"HOT" again.
>
>not sure if that answers your question.
>are you planning on using the maintenance plan
stuff...aka "Log shipping
>Wizard" ?
>
>
>Greg Jackson
>PDX, Oregon
>
>.
>|||it aint easy.
moving data of the wire SUX. Especially for large Dbs.
this is really a "Poor Man's Replication" so larger companies probably just
use replication.
I have done tons of this in the past and am doing a huge log shipping
implementation right now.
I recommend NOT using the Wizard Thingy.
Roll your own.
if you need scripts, have questions, etc let me know.
GAJ|||Thanks for the info Jaxon.
>--Original Message--
>it aint easy.
>moving data of the wire SUX. Especially for large Dbs.
>this is really a "Poor Man's Replication" so larger
companies probably just
>use replication.
>I have done tons of this in the past and am doing a huge
log shipping
>implementation right now.
>I recommend NOT using the Wizard Thingy.
>Roll your own.
>if you need scripts, have questions, etc let me know.
>
>GAJ
>
>.
>

Log shipping from SQL2000EE SP3 to SP4

Hi,
Source ==> SQL Server 2000 EE SP3
Target ==> SQL Server 2000 EE SP4
Does automated log shipping work for this configuration? Or log
shipping requires same service pack levels?
Thanks,
zrb
That config should work (although it is preferable to have the same
service pack level on both servers so that if you have to point your
client code at the secondary server there are no post-SP3 bug surprises).
*mike hodgson*
blog: http://sqlnerd.blogspot.com
zrajbun@.hotmail.com wrote:

>Hi,
>Source ==> SQL Server 2000 EE SP3
>Target ==> SQL Server 2000 EE SP4
>Does automated log shipping work for this configuration? Or log
>shipping requires same service pack levels?
>Thanks,
>zrb
>
>
|||Thank you Mike. I think I'll go with the same service pack levels,
keeping SP3.
b4n
zrb

Log shipping from SQL2000EE SP3 to SP4

Hi,
Source ==> SQL Server 2000 EE SP3
Target ==> SQL Server 2000 EE SP4
Does automated log shipping work for this configuration? Or log
shipping requires same service pack levels?
Thanks,
zrbThat config should work (although it is preferable to have the same
service pack level on both servers so that if you have to point your
client code at the secondary server there are no post-SP3 bug surprises).
*mike hodgson*
blog: http://sqlnerd.blogspot.com
zrajbun@.hotmail.com wrote:

>Hi,
>Source ==> SQL Server 2000 EE SP3
>Target ==> SQL Server 2000 EE SP4
>Does automated log shipping work for this configuration? Or log
>shipping requires same service pack levels?
>Thanks,
>zrb
>
>|||Thank you Mike. I think I'll go with the same service pack levels,
keeping SP3.
b4n
zrb

Log shipping from SQL2000EE SP3 to SP4

Hi,
Source ==> SQL Server 2000 EE SP3
Target ==> SQL Server 2000 EE SP4
Does automated log shipping work for this configuration? Or log
shipping requires same service pack levels?
Thanks,
zrbThis is a multi-part message in MIME format.
--090403030908040509010200
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
That config should work (although it is preferable to have the same
service pack level on both servers so that if you have to point your
client code at the secondary server there are no post-SP3 bug surprises).
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
zrajbun@.hotmail.com wrote:
>Hi,
>Source ==> SQL Server 2000 EE SP3
>Target ==> SQL Server 2000 EE SP4
>Does automated log shipping work for this configuration? Or log
>shipping requires same service pack levels?
>Thanks,
>zrb
>
>
--090403030908040509010200
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>That config should work (although it is preferable to have the same
service pack level on both servers so that if you have to point your
client code at the secondary server there are no post-SP3 bug
surprises).</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:zrajbun@.hotmail.com">zrajbun@.hotmail.com</a> wrote:
<blockquote
cite="mid1124169030.671658.100080@.g49g2000cwa.googlegroups.com"
type="cite">
<pre wrap="">Hi,
Source ==> SQL Server 2000 EE SP3
Target ==> SQL Server 2000 EE SP4
Does automated log shipping work for this configuration? Or log
shipping requires same service pack levels?
Thanks,
zrb
</pre>
</blockquote>
</body>
</html>
--090403030908040509010200--|||Thank you Mike. I think I'll go with the same service pack levels,
keeping SP3.
b4n
zrb

Log Shipping and Recovery

I have just created a pair of log shipping SQL Servers.
Both are running SQL Server 2000 sp3 on Win2k sp3.
I've read the documentation on how to change roles between
the servers, but it assumes that both servers are
available for a controlled change. What if the primary
server is destroyed? How can I make the secondary server
the primary in an emergency situation? I know I can't just
change the database on the secondary from Read-Only to
Normal. What has to happen in order for this secondary
server to become active?
TIA,
Ken> I know I can't just
> change the database on the secondary from Read-Only to
> Normal.
Why do you say that? All you should need to do is:
RESTORE DATABASE dbname WITH RECOVERY
Above assumes that EM's log shipping doesn't do anything strange.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Ken Krause" <kenkrause@.promaxautonews.com> wrote in message
news:005601c3aba4$e6b6c5f0$a401280a@.phx.gbl...
> I have just created a pair of log shipping SQL Servers.
> Both are running SQL Server 2000 sp3 on Win2k sp3.
> I've read the documentation on how to change roles between
> the servers, but it assumes that both servers are
> available for a controlled change. What if the primary
> server is destroyed? How can I make the secondary server
> the primary in an emergency situation? I know I can't just
> change the database on the secondary from Read-Only to
> Normal. What has to happen in order for this secondary
> server to become active?
> TIA,
> Ken|||I have documentation for this, send me an email
and I will reply with the information.
>--Original Message--
>I have just created a pair of log shipping SQL Servers.
>Both are running SQL Server 2000 sp3 on Win2k sp3.
>I've read the documentation on how to change roles
between
>the servers, but it assumes that both servers are
>available for a controlled change. What if the primary
>server is destroyed? How can I make the secondary server
>the primary in an emergency situation? I know I can't
just
>change the database on the secondary from Read-Only to
>Normal. What has to happen in order for this secondary
>server to become active?
>TIA,
>Ken
>.
>|||The book Microsoft SQL Server 2000 High Availability
covers this in the Log Shipping chapter. Making a role
change has nothing to do with whether the primary is
available ... you can make it, but you may possibly lose
the last transactions if you can't get the tail of the
log. You're only as good as your last tran log you have
access to.
If you have the DB in read-only mode, this is incorrect.
I assume it is in STANDBY, which shows the DB as read-only.
>--Original Message--
>I have just created a pair of log shipping SQL Servers.
>Both are running SQL Server 2000 sp3 on Win2k sp3.
>I've read the documentation on how to change roles
between
>the servers, but it assumes that both servers are
>available for a controlled change. What if the primary
>server is destroyed? How can I make the secondary server
>the primary in an emergency situation? I know I can't
just
>change the database on the secondary from Read-Only to
>Normal. What has to happen in order for this secondary
>server to become active?
>TIA,
>Ken
>.
>

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
> >
> >
>