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