Are things like 'create proc' commands logged to the transaction log?
What about things like DDL 'create table' and 'create index'?

I have looked through the manuals and am unable to find direct answers
to these questions.

TIA
Chris

chris_jack@msn.com (Chris Jack) writes:

>Are things like 'create proc' commands logged to the transaction log?
>What about things like DDL 'create table' and 'create index'?

AFAIK DDL is not written to the transaction log and cannot be rolled
back.

--
Ed Avis <ed@membled.com>

Ed Avis <ed@membled.com> wrote in message news:<l1he18t0sy.fsf@budvar.future-i.net>...
> chris_jack@msn.com (Chris Jack) writes:
>
> >Are things like 'create proc' commands logged to the transaction log?
> >What about things like DDL 'create table' and 'create index'?
>
> AFAIK DDL is not written to the transaction log and cannot be rolled
> back.

I can speak on how ASE does it but not ASA. Which are you asking
about?

The ASE log isn't a log of the commands issued, but rather, the
records in the log reflect the changes in various tables that resulted
from the command. There are many kinds of log records, but the most
basic ones would be begin transaction, insert, update, delete, and end
transaction. A create table command would generate a begin
transaction, an insert into sysobjects, one or more inserts into
sysindexes, one or more inserts into syscolumns, one or more inserts
into sysprotects, [etc.] and a final commit tran. There are many more
kinds of log records (such as index insert, index delete, index page
split, checkpoint, etc.) - this is really a gross simplification. All
the commands you mention would generate log records - pretty much
everything that changes the state of the database does generate log
records. SELECT, since it does not change the database state, does
not generate log records.

-bret

Ed Avis <ed@membled.com> wrote in message news:<l1he18t0sy.fsf@budvar.future-i.net>...
> chris_jack@msn.com (Chris Jack) writes:
>
> >Are things like 'create proc' commands logged to the transaction log?
> >What about things like DDL 'create table' and 'create index'?
>
> AFAIK DDL is not written to the transaction log and cannot be rolled
> back.
Hi,
If that would have been the case, then we would'nt have been able to
do any point in time restores in case of dropping tables by mistake.

Thanks

dba

bret@sybase.com (Bret Halford) writes:

>A create table command would generate a begin transaction, an insert
>into sysobjects, one or more inserts into sysindexes, one or more
>inserts into syscolumns, one or more inserts into sysprotects, [etc.]
>and a final commit tran.

Thanks for the correction. I assumed that 'create table' etc. could
not be rolled back so I didn't think about the necessary changes to
sysobjects etc., which of course will go into the database's
transaction log.

--
Ed Avis <ed@membled.com>