logshipping setup with two standby db servers.

Hi,
Currently we have logshipping setup with one production db server
sending the logs to one standby database server.

we would like to have setup where there will be two standby db
servers. Second server will be located in different country.
Kindly let me know if we can have the setup where
there is one primary server and two standby db servers.

When I tried to configure the same I am not able to complete the
maintenance plan.
It gives error that logshipping monitor already exists.
Please help me. tks in advance.
Regards
Kamalkarora@.melstar.com (Kamal) wrote in message news:<a4d6a1fb.0408230107.abdaf08@.posting.google.com>...
> Hi,
> Currently we have logshipping setup with one production db server
> sending the logs to one standby database server.
> we would like to have setup where there will be two standby db
> servers. Second server will be located in different country.
> Kindly let me know if we can have the setup where
> there is one primary server and two standby db servers.
> When I tried to configure the same I am not able to complete the
> maintenance plan.
> It gives error that logshipping monitor already exists.
> Please help me. tks in advance.
> Regards
> Kamal

Yes, this is possible:

http://support.microsoft.com/defaul...inal.asp#modify
http://www.microsoft.com/resources/...art4/c1361.mspx

You might also want to check this article, which describes one
situation where you might get that error:

http://support.microsoft.com/defaul...q298743&sd=tech

Also check out "Modifying Log Shipping" in Books Online.

Simon

Logshipping ERROR(urgent)

Hi Guys,
I am facing following problem with MSSQL Server 2000.
I am using Microsoft sql server Log Shipping feature to have a standby
server for disaster recovery purposes.
in the logshipping Destination server while restoring the transaction log
the restore process is failling with following error.
"[Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 3456: [Microsoft][ODBC SQL
Server Driver][SQL Server]Could not redo log record (8130:35542:2), for
transaction ID (0:91843227), on page (1:519336), database 'REVENUES' (5).
Page: LSN = (8130:29950:3), type = 1. Log: OpCode = 3, context 2,
PrevPageLSN: (8122:7374:3)."
Both servers, source and destination were running with SQL SERVER
ENTERPRISE EDITION SP3 .
after this failure i rebuilded the logshipping process but same error
happened again so after one week updated the destination server with sql sp4
and rebuilded the logshipping process . but the same error happened again.
During this error SQL Server is creating a dump file and text file
pls adviceAre you sure that your databases are consistent - run checkdb...
Bojidar Alexandrov
"bijupg@.hotmail.com" <bijupghotmailcom@.discussions.microsoft.com> wrote in
message news:F6B405F2-0038-418C-8703-BB77E2C8435D@.microsoft.com...
> Hi Guys,
> I am facing following problem with MSSQL Server 2000.
> I am using Microsoft sql server Log Shipping feature to have a standby
> server for disaster recovery purposes.
> in the logshipping Destination server while restoring the transaction log
> the restore process is failling with following error.
> "[Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 3456: [Microsoft][ODBC
SQL
> Server Driver][SQL Server]Could not redo log record (8130:35542:2), for
> transaction ID (0:91843227), on page (1:519336), database 'REVENUES' (5).
> Page: LSN = (8130:29950:3), type = 1. Log: OpCode = 3, context 2,
> PrevPageLSN: (8122:7374:3)."
> Both servers, source and destination were running with SQL SERVER
> ENTERPRISE EDITION SP3 .
> after this failure i rebuilded the logshipping process but same error
> happened again so after one week updated the destination server with sql
sp4
> and rebuilded the logshipping process . but the same error happened again.
> During this error SQL Server is creating a dump file and text file
> pls advice|||Hi Bojidar Alexandrov,
in source or destination?
source is consistent as i am doing dbcc every day and no errors found.
"Bojidar Alexandrov" wrote:
> Are you sure that your databases are consistent - run checkdb...
> Bojidar Alexandrov
> "bijupg@.hotmail.com" <bijupghotmailcom@.discussions.microsoft.com> wrote in
> message news:F6B405F2-0038-418C-8703-BB77E2C8435D@.microsoft.com...
> > Hi Guys,
> > I am facing following problem with MSSQL Server 2000.
> > I am using Microsoft sql server Log Shipping feature to have a standby
> > server for disaster recovery purposes.
> > in the logshipping Destination server while restoring the transaction log
> > the restore process is failling with following error.
> >
> > "[Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 3456: [Microsoft][ODBC
> SQL
> > Server Driver][SQL Server]Could not redo log record (8130:35542:2), for
> > transaction ID (0:91843227), on page (1:519336), database 'REVENUES' (5).
> > Page: LSN = (8130:29950:3), type = 1. Log: OpCode = 3, context 2,
> > PrevPageLSN: (8122:7374:3)."
> >
> > Both servers, source and destination were running with SQL SERVER
> > ENTERPRISE EDITION SP3 .
> > after this failure i rebuilded the logshipping process but same error
> > happened again so after one week updated the destination server with sql
> sp4
> > and rebuilded the logshipping process . but the same error happened again.
> > During this error SQL Server is creating a dump file and text file
> >
> > pls advice
>|||"bijupg@.hotmail.com" schrieb:
> Hi Guys,
> I am facing following problem with MSSQL Server 2000.
> I am using Microsoft sql server Log Shipping feature to have a standby
> server for disaster recovery purposes.
> in the logshipping Destination server while restoring the transaction log
> the restore process is failling with following error.
> "[Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 3456: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Could not redo log record (8130:35542:2), for
> transaction ID (0:91843227), on page (1:519336), database 'REVENUES' (5).
> Page: LSN = (8130:29950:3), type = 1. Log: OpCode = 3, context 2,
> PrevPageLSN: (8122:7374:3)."
> Both servers, source and destination were running with SQL SERVER
> ENTERPRISE EDITION SP3 .
> after this failure i rebuilded the logshipping process but same error
> happened again so after one week updated the destination server with sql sp4
> and rebuilded the logshipping process . but the same error happened again.
> During this error SQL Server is creating a dump file and text file
> pls advice
Did you do transaction log backups in between? Don't forget that they empty
the TA-Log and produce a new sequence number - then the logshipping (being
fully incremental) are missing some data and cannot work any more ...|||No i am not doing any transaction log backup in between
"Christian Donner" wrote:
> "bijupg@.hotmail.com" schrieb:
> > Hi Guys,
> > I am facing following problem with MSSQL Server 2000.
> > I am using Microsoft sql server Log Shipping feature to have a standby
> > server for disaster recovery purposes.
> > in the logshipping Destination server while restoring the transaction log
> > the restore process is failling with following error.
> >
> > "[Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 3456: [Microsoft][ODBC SQL
> > Server Driver][SQL Server]Could not redo log record (8130:35542:2), for
> > transaction ID (0:91843227), on page (1:519336), database 'REVENUES' (5).
> > Page: LSN = (8130:29950:3), type = 1. Log: OpCode = 3, context 2,
> > PrevPageLSN: (8122:7374:3)."
> >
> > Both servers, source and destination were running with SQL SERVER
> > ENTERPRISE EDITION SP3 .
> > after this failure i rebuilded the logshipping process but same error
> > happened again so after one week updated the destination server with sql sp4
> > and rebuilded the logshipping process . but the same error happened again.
> > During this error SQL Server is creating a dump file and text file
> >
> > pls advice
> Did you do transaction log backups in between? Don't forget that they empty
> the TA-Log and produce a new sequence number - then the logshipping (being
> fully incremental) are missing some data and cannot work any more ...|||The type of error you are seeing is most likely being caused by corruption
in your transaction logs. Review system event logs on both servers for ANY
hardware-related warnings and errors. Update all your firmware and drivers.
Adrian
"bijupg@.hotmail.com" <bijupghotmailcom@.discussions.microsoft.com> wrote in
message news:F6B405F2-0038-418C-8703-BB77E2C8435D@.microsoft.com...
> Hi Guys,
> I am facing following problem with MSSQL Server 2000.
> I am using Microsoft sql server Log Shipping feature to have a standby
> server for disaster recovery purposes.
> in the logshipping Destination server while restoring the transaction log
> the restore process is failling with following error.
> "[Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 3456: [Microsoft][ODBC
> SQL
> Server Driver][SQL Server]Could not redo log record (8130:35542:2), for
> transaction ID (0:91843227), on page (1:519336), database 'REVENUES' (5).
> Page: LSN = (8130:29950:3), type = 1. Log: OpCode = 3, context 2,
> PrevPageLSN: (8122:7374:3)."
> Both servers, source and destination were running with SQL SERVER
> ENTERPRISE EDITION SP3 .
> after this failure i rebuilded the logshipping process but same error
> happened again so after one week updated the destination server with sql
> sp4
> and rebuilded the logshipping process . but the same error happened again.
> During this error SQL Server is creating a dump file and text file
> pls advice|||Biju.
You have been having trouble with this DB many times in the past. You had
corruption, it showed as fixed, and now it is back. You do not have torn
page detection on. Turn ton page detect ion on.
If your corruption continues, you will soon have a database that all you can
do it delete.
It may be worth your while to open a case with Microsoft PSS.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Adrian Zajkeskovic" <azajkeskovic@.hotmail.com> wrote in message
news:tsednVPPk5VQVjPfRVn-3Q@.rogers.com...
> The type of error you are seeing is most likely being caused by corruption
> in your transaction logs. Review system event logs on both servers for ANY
> hardware-related warnings and errors. Update all your firmware and
> drivers.
> Adrian
>
> "bijupg@.hotmail.com" <bijupghotmailcom@.discussions.microsoft.com> wrote in
> message news:F6B405F2-0038-418C-8703-BB77E2C8435D@.microsoft.com...
>> Hi Guys,
>> I am facing following problem with MSSQL Server 2000.
>> I am using Microsoft sql server Log Shipping feature to have a standby
>> server for disaster recovery purposes.
>> in the logshipping Destination server while restoring the transaction log
>> the restore process is failling with following error.
>> "[Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 3456: [Microsoft][ODBC
>> SQL
>> Server Driver][SQL Server]Could not redo log record (8130:35542:2), for
>> transaction ID (0:91843227), on page (1:519336), database 'REVENUES' (5).
>> Page: LSN = (8130:29950:3), type = 1. Log: OpCode = 3, context 2,
>> PrevPageLSN: (8122:7374:3)."
>> Both servers, source and destination were running with SQL SERVER
>> ENTERPRISE EDITION SP3 .
>> after this failure i rebuilded the logshipping process but same error
>> happened again so after one week updated the destination server with sql
>> sp4
>> and rebuilded the logshipping process . but the same error happened
>> again.
>> During this error SQL Server is creating a dump file and text file
>> pls advice
>|||Hi Mike,
can you pls advice how to open case with Microsoft PSS.
"Mike Epprecht (SQL MVP)" wrote:
> Biju.
> You have been having trouble with this DB many times in the past. You had
> corruption, it showed as fixed, and now it is back. You do not have torn
> page detection on. Turn ton page detect ion on.
> If your corruption continues, you will soon have a database that all you can
> do it delete.
> It may be worth your while to open a case with Microsoft PSS.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Adrian Zajkeskovic" <azajkeskovic@.hotmail.com> wrote in message
> news:tsednVPPk5VQVjPfRVn-3Q@.rogers.com...
> > The type of error you are seeing is most likely being caused by corruption
> > in your transaction logs. Review system event logs on both servers for ANY
> > hardware-related warnings and errors. Update all your firmware and
> > drivers.
> >
> > Adrian
> >
> >
> > "bijupg@.hotmail.com" <bijupghotmailcom@.discussions.microsoft.com> wrote in
> > message news:F6B405F2-0038-418C-8703-BB77E2C8435D@.microsoft.com...
> >> Hi Guys,
> >> I am facing following problem with MSSQL Server 2000.
> >> I am using Microsoft sql server Log Shipping feature to have a standby
> >> server for disaster recovery purposes.
> >> in the logshipping Destination server while restoring the transaction log
> >> the restore process is failling with following error.
> >>
> >> "[Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 3456: [Microsoft][ODBC
> >> SQL
> >> Server Driver][SQL Server]Could not redo log record (8130:35542:2), for
> >> transaction ID (0:91843227), on page (1:519336), database 'REVENUES' (5).
> >> Page: LSN = (8130:29950:3), type = 1. Log: OpCode = 3, context 2,
> >> PrevPageLSN: (8122:7374:3)."
> >>
> >> Both servers, source and destination were running with SQL SERVER
> >> ENTERPRISE EDITION SP3 .
> >> after this failure i rebuilded the logshipping process but same error
> >> happened again so after one week updated the destination server with sql
> >> sp4
> >> and rebuilded the logshipping process . but the same error happened
> >> again.
> >> During this error SQL Server is creating a dump file and text file
> >>
> >> pls advice
> >
> >
>
>|||Hi guys,
DBCC CHECKDB is not giving any errors or warnings and database is
functioning normally.
there are no hardware related errors in event viewer.
and we are using compaq cluster with compaq smart array controller so is
there any chance to have a torn page?this type of diska should have mechanism
to ensure that data is completely written and also there are no power failure
at all.
i am using transactional replication for reporting and it is working fine if
there was any problem with source database then it should also fail like
logshipping.
what should be the ideal temperature in datacentres/server romms currently
we are keeping 20-22 degree celcius is it ok?
Pls advice.
"Mike Epprecht (SQL MVP)" wrote:
> Biju.
> You have been having trouble with this DB many times in the past. You had
> corruption, it showed as fixed, and now it is back. You do not have torn
> page detection on. Turn ton page detect ion on.
> If your corruption continues, you will soon have a database that all you can
> do it delete.
> It may be worth your while to open a case with Microsoft PSS.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Adrian Zajkeskovic" <azajkeskovic@.hotmail.com> wrote in message
> news:tsednVPPk5VQVjPfRVn-3Q@.rogers.com...
> > The type of error you are seeing is most likely being caused by corruption
> > in your transaction logs. Review system event logs on both servers for ANY
> > hardware-related warnings and errors. Update all your firmware and
> > drivers.
> >
> > Adrian
> >
> >
> > "bijupg@.hotmail.com" <bijupghotmailcom@.discussions.microsoft.com> wrote in
> > message news:F6B405F2-0038-418C-8703-BB77E2C8435D@.microsoft.com...
> >> Hi Guys,
> >> I am facing following problem with MSSQL Server 2000.
> >> I am using Microsoft sql server Log Shipping feature to have a standby
> >> server for disaster recovery purposes.
> >> in the logshipping Destination server while restoring the transaction log
> >> the restore process is failling with following error.
> >>
> >> "[Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 3456: [Microsoft][ODBC
> >> SQL
> >> Server Driver][SQL Server]Could not redo log record (8130:35542:2), for
> >> transaction ID (0:91843227), on page (1:519336), database 'REVENUES' (5).
> >> Page: LSN = (8130:29950:3), type = 1. Log: OpCode = 3, context 2,
> >> PrevPageLSN: (8122:7374:3)."
> >>
> >> Both servers, source and destination were running with SQL SERVER
> >> ENTERPRISE EDITION SP3 .
> >> after this failure i rebuilded the logshipping process but same error
> >> happened again so after one week updated the destination server with sql
> >> sp4
> >> and rebuilded the logshipping process . but the same error happened
> >> again.
> >> During this error SQL Server is creating a dump file and text file
> >>
> >> pls advice
> >
> >
>
>sql

LogShipping Copy and Load Question

Hi,
I have established a LogShipping plan with Secondary
Server DB set as Standby, 15 minutes interval of Backup
Log, Load and Restore. Questions:
(1) Inside Log Shipping History, it seems that the Copy
will always copy NEWER Log (in my case, 15 minutes later,
eg. 1815), and Load will always Load OLDER Log (in my
case, 15 minuutes earlier eg. 1800). How can I resolve
this?
(2) Most importantly, The Logshipping will have errors
after a while and it reaches a stage where NO file(s) will
be processed and "Last File" in Log Shipping History
always remains the same file (Eg. ...200407090615.TRN). I
suspect it is all related to the frequency that I set.
I just find it hard to maintain a good Log Shipping plan
which does not require close monitoring.
I would appreciate any help for me here. Thanks a lot in
advance.
Tom
Tom,
making the load frequency every 5 mins should solve the issues you are
experiencing.
I always have a more frequent load than copy to prevent backlogs of the log
files.
HTH,
Paul Ibison
sql

Logs/Restore Logs

Whats contained the the Backup Log Files ? What goes on when a restore of
the log takes place on say a standby server ?
If I do some inserts/updates and deletes against DB A ... And when I backup
the log after executing those statements, will the log contain those DML
statements or will it contain all the data/index pages that were being
affected by those DML ?
And if it does contain the DML , does that mean that when a restore log
takes place, the DML gets fired on the standby ?
And if the backup logs do not contain DML statements, what would it contain
and how does it do the restore ?
Please let me know the technical details...Using SQL 2000.
Also curious to know what happens when some DBCC commands are run such as
dbreindex or indexdefrag ...Hassan,
The log files contain changes made to data in the database. If the DML is
changed it updates (for example) sysobjects, syscolumns, syscomments, etc.
depending on the DML. If you ALTER TABLE and add a column with a default,
the log will contain (1) changes to the system objects, (2) every row in the
table with its new default value, (3) any indexes that are updated in the
process, (4) and so forth.
The commands that you issued are not recorded in the log, the results that
you obtained are recorded there.
Ditto on DBCC, any changes go into the logs.
Russell Fields
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#UUxvUx9DHA.1672@.TK2MSFTNGP12.phx.gbl...
> Whats contained the the Backup Log Files ? What goes on when a restore of
> the log takes place on say a standby server ?
> If I do some inserts/updates and deletes against DB A ... And when I
backup
> the log after executing those statements, will the log contain those DML
> statements or will it contain all the data/index pages that were being
> affected by those DML ?
> And if it does contain the DML , does that mean that when a restore log
> takes place, the DML gets fired on the standby ?
> And if the backup logs do not contain DML statements, what would it
contain
> and how does it do the restore ?
> Please let me know the technical details...Using SQL 2000.
> Also curious to know what happens when some DBCC commands are run such as
> dbreindex or indexdefrag ...
>|||Ok.. thats informational.. How does the restore take place then ? So ive got
all the changes which im assuming that the data and index pages are captured
in the log.. Does it just about and replacing the data and index pages with
the new ones on the standby server.. Is that how the restore logs work
Thanks
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:e1F77cy9DHA.2308@.TK2MSFTNGP09.phx.gbl...
> Hassan,
> The log files contain changes made to data in the database. If the DML is
> changed it updates (for example) sysobjects, syscolumns, syscomments, etc.
> depending on the DML. If you ALTER TABLE and add a column with a default,
> the log will contain (1) changes to the system objects, (2) every row in
the
> table with its new default value, (3) any indexes that are updated in the
> process, (4) and so forth.
> The commands that you issued are not recorded in the log, the results that
> you obtained are recorded there.
> Ditto on DBCC, any changes go into the logs.
> Russell Fields
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:#UUxvUx9DHA.1672@.TK2MSFTNGP12.phx.gbl...
of
> backup
> contain
as
>|||Row changes are in the log as well as page allocations, splits, etc.
So, no it does not just replace the pages since that would greatly increase
the log size. (For example, in that case if I updated 10 200-byte rows on
10 pages I would log about 160,000 bytes instead of about 4000 bytes.
Before and after images.)
The transactions are rolled forward one at a time.
Russell Fields
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#3UnFiy9DHA.3176@.TK2MSFTNGP11.phx.gbl...
> Ok.. thats informational.. How does the restore take place then ? So ive
got
> all the changes which im assuming that the data and index pages are
captured
> in the log.. Does it just about and replacing the data and index pages
with
> the new ones on the standby server.. Is that how the restore logs work
> Thanks
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:e1F77cy9DHA.2308@.TK2MSFTNGP09.phx.gbl...
is
etc.
default,
> the
the
that
> of
DML
log
> as
>|||Hassan,
If you really want to know how most products implement transaction logging,
I recommend the books "Transaction Processing Concepts and Techniques" by
Reuter and Gray. Not light reading, though. But it goes through what type of
information that need to be logged in order for a product to use the log for
roll forward and roll back. Of course, vendors are probably tweaking these
algorithms a bit, but my guess is that most products uses these base
techniques in one form or another.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%233UnFiy9DHA.3176@.TK2MSFTNGP11.phx.gbl...
> Ok.. thats informational.. How does the restore take place then ? So ive
got
> all the changes which im assuming that the data and index pages are
captured
> in the log.. Does it just about and replacing the data and index pages
with
> the new ones on the standby server.. Is that how the restore logs work
> Thanks
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:e1F77cy9DHA.2308@.TK2MSFTNGP09.phx.gbl...
is
etc.
default,
> the
the
that
> of
DML
log
> as
>sql

Logs/Restore Logs

Whats contained the the Backup Log Files ? What goes on when a restore of
the log takes place on say a standby server ?
If I do some inserts/updates and deletes against DB A ... And when I backup
the log after executing those statements, will the log contain those DML
statements or will it contain all the data/index pages that were being
affected by those DML ?
And if it does contain the DML , does that mean that when a restore log
takes place, the DML gets fired on the standby ?
And if the backup logs do not contain DML statements, what would it contain
and how does it do the restore ?
Please let me know the technical details...Using SQL 2000.
Also curious to know what happens when some DBCC commands are run such as
dbreindex or indexdefrag ...Hassan,
The log files contain changes made to data in the database. If the DML is
changed it updates (for example) sysobjects, syscolumns, syscomments, etc.
depending on the DML. If you ALTER TABLE and add a column with a default,
the log will contain (1) changes to the system objects, (2) every row in the
table with its new default value, (3) any indexes that are updated in the
process, (4) and so forth.
The commands that you issued are not recorded in the log, the results that
you obtained are recorded there.
Ditto on DBCC, any changes go into the logs.
Russell Fields
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#UUxvUx9DHA.1672@.TK2MSFTNGP12.phx.gbl...
> Whats contained the the Backup Log Files ? What goes on when a restore of
> the log takes place on say a standby server ?
> If I do some inserts/updates and deletes against DB A ... And when I
backup
> the log after executing those statements, will the log contain those DML
> statements or will it contain all the data/index pages that were being
> affected by those DML ?
> And if it does contain the DML , does that mean that when a restore log
> takes place, the DML gets fired on the standby ?
> And if the backup logs do not contain DML statements, what would it
contain
> and how does it do the restore ?
> Please let me know the technical details...Using SQL 2000.
> Also curious to know what happens when some DBCC commands are run such as
> dbreindex or indexdefrag ...
>|||Ok.. thats informational.. How does the restore take place then ? So ive got
all the changes which im assuming that the data and index pages are captured
in the log.. Does it just about and replacing the data and index pages with
the new ones on the standby server.. Is that how the restore logs work
Thanks
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:e1F77cy9DHA.2308@.TK2MSFTNGP09.phx.gbl...
> Hassan,
> The log files contain changes made to data in the database. If the DML is
> changed it updates (for example) sysobjects, syscolumns, syscomments, etc.
> depending on the DML. If you ALTER TABLE and add a column with a default,
> the log will contain (1) changes to the system objects, (2) every row in
the
> table with its new default value, (3) any indexes that are updated in the
> process, (4) and so forth.
> The commands that you issued are not recorded in the log, the results that
> you obtained are recorded there.
> Ditto on DBCC, any changes go into the logs.
> Russell Fields
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:#UUxvUx9DHA.1672@.TK2MSFTNGP12.phx.gbl...
> > Whats contained the the Backup Log Files ? What goes on when a restore
of
> > the log takes place on say a standby server ?
> >
> > If I do some inserts/updates and deletes against DB A ... And when I
> backup
> > the log after executing those statements, will the log contain those DML
> > statements or will it contain all the data/index pages that were being
> > affected by those DML ?
> >
> > And if it does contain the DML , does that mean that when a restore log
> > takes place, the DML gets fired on the standby ?
> > And if the backup logs do not contain DML statements, what would it
> contain
> > and how does it do the restore ?
> >
> > Please let me know the technical details...Using SQL 2000.
> >
> > Also curious to know what happens when some DBCC commands are run such
as
> > dbreindex or indexdefrag ...
> >
> >
>|||Row changes are in the log as well as page allocations, splits, etc.
So, no it does not just replace the pages since that would greatly increase
the log size. (For example, in that case if I updated 10 200-byte rows on
10 pages I would log about 160,000 bytes instead of about 4000 bytes.
Before and after images.)
The transactions are rolled forward one at a time.
Russell Fields
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#3UnFiy9DHA.3176@.TK2MSFTNGP11.phx.gbl...
> Ok.. thats informational.. How does the restore take place then ? So ive
got
> all the changes which im assuming that the data and index pages are
captured
> in the log.. Does it just about and replacing the data and index pages
with
> the new ones on the standby server.. Is that how the restore logs work
> Thanks
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:e1F77cy9DHA.2308@.TK2MSFTNGP09.phx.gbl...
> > Hassan,
> >
> > The log files contain changes made to data in the database. If the DML
is
> > changed it updates (for example) sysobjects, syscolumns, syscomments,
etc.
> > depending on the DML. If you ALTER TABLE and add a column with a
default,
> > the log will contain (1) changes to the system objects, (2) every row in
> the
> > table with its new default value, (3) any indexes that are updated in
the
> > process, (4) and so forth.
> >
> > The commands that you issued are not recorded in the log, the results
that
> > you obtained are recorded there.
> >
> > Ditto on DBCC, any changes go into the logs.
> >
> > Russell Fields
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:#UUxvUx9DHA.1672@.TK2MSFTNGP12.phx.gbl...
> > > Whats contained the the Backup Log Files ? What goes on when a restore
> of
> > > the log takes place on say a standby server ?
> > >
> > > If I do some inserts/updates and deletes against DB A ... And when I
> > backup
> > > the log after executing those statements, will the log contain those
DML
> > > statements or will it contain all the data/index pages that were being
> > > affected by those DML ?
> > >
> > > And if it does contain the DML , does that mean that when a restore
log
> > > takes place, the DML gets fired on the standby ?
> > > And if the backup logs do not contain DML statements, what would it
> > contain
> > > and how does it do the restore ?
> > >
> > > Please let me know the technical details...Using SQL 2000.
> > >
> > > Also curious to know what happens when some DBCC commands are run such
> as
> > > dbreindex or indexdefrag ...
> > >
> > >
> >
> >
>|||Hassan,
If you really want to know how most products implement transaction logging,
I recommend the books "Transaction Processing Concepts and Techniques" by
Reuter and Gray. Not light reading, though. But it goes through what type of
information that need to be logged in order for a product to use the log for
roll forward and roll back. Of course, vendors are probably tweaking these
algorithms a bit, but my guess is that most products uses these base
techniques in one form or another.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%233UnFiy9DHA.3176@.TK2MSFTNGP11.phx.gbl...
> Ok.. thats informational.. How does the restore take place then ? So ive
got
> all the changes which im assuming that the data and index pages are
captured
> in the log.. Does it just about and replacing the data and index pages
with
> the new ones on the standby server.. Is that how the restore logs work
> Thanks
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:e1F77cy9DHA.2308@.TK2MSFTNGP09.phx.gbl...
> > Hassan,
> >
> > The log files contain changes made to data in the database. If the DML
is
> > changed it updates (for example) sysobjects, syscolumns, syscomments,
etc.
> > depending on the DML. If you ALTER TABLE and add a column with a
default,
> > the log will contain (1) changes to the system objects, (2) every row in
> the
> > table with its new default value, (3) any indexes that are updated in
the
> > process, (4) and so forth.
> >
> > The commands that you issued are not recorded in the log, the results
that
> > you obtained are recorded there.
> >
> > Ditto on DBCC, any changes go into the logs.
> >
> > Russell Fields
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:#UUxvUx9DHA.1672@.TK2MSFTNGP12.phx.gbl...
> > > Whats contained the the Backup Log Files ? What goes on when a restore
> of
> > > the log takes place on say a standby server ?
> > >
> > > If I do some inserts/updates and deletes against DB A ... And when I
> > backup
> > > the log after executing those statements, will the log contain those
DML
> > > statements or will it contain all the data/index pages that were being
> > > affected by those DML ?
> > >
> > > And if it does contain the DML , does that mean that when a restore
log
> > > takes place, the DML gets fired on the standby ?
> > > And if the backup logs do not contain DML statements, what would it
> > contain
> > > and how does it do the restore ?
> > >
> > > Please let me know the technical details...Using SQL 2000.
> > >
> > > Also curious to know what happens when some DBCC commands are run such
> as
> > > dbreindex or indexdefrag ...
> > >
> > >
> >
> >
>