|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
drop table not showing up in the transaction log
transaction log? Sometime this morning, some one or some thing dropped a bunch of tables from one of my databases. I've been using ::fn_dblog (NULL, NULL) to poke around in the transaction log and have not been able to find anything. I have search for any [Transaction Name] that would match a 'DROPOBJ' command, I have filtered on time (we know approximately when this happened), I have filtered on the table names that were dropped with only INSERTS and UPDATES coming up. I even went so far as to create a test table '_______TEST_______' in the database and then delete it using a right click on the table. That came up in the transaction log. It is true, isn't it, that the only way to remove a table from a database is via the 'DROP TABLE' command? Either directly from the dba typing the 'DROP TABLE' command into SSMS or indirectly via a right click on the table name or via a 3rd party application (Adept SQLDiff). Does anybody have any idea how this might have happened? Thank you. Wayne E. Pfeffer Sr. Database Developer Home Buyer's Marketing II, Inc. A drop is the only way that I know of but is it possible the Drop happened
in the previous log files? Once the tran has been committed and the log has been backed up the space in the log is marked for reuse. -- Show quoteHide quoteAndrew J. Kelly SQL MVP Solid Quality Mentors "wpfeffer" <wpfef***@discussions.microsoft.com> wrote in message news:5F4B323D-7200-481C-8E00-D224573327E8@microsoft.com... > Is there a reason why a drop table command wouldn't show up in the > transaction log? > > Sometime this morning, some one or some thing dropped a bunch of tables > from > one of my databases. I've been using ::fn_dblog (NULL, NULL) to poke > around > in the transaction log and have not been able to find anything. I have > search > for any [Transaction Name] that would match a 'DROPOBJ' command, I have > filtered on time (we know approximately when this happened), I have > filtered > on the table names that were dropped with only INSERTS and UPDATES coming > up. > I even went so far as to create a test table '_______TEST_______' in the > database and then delete it using a right click on the table. That came up > in > the transaction log. > > It is true, isn't it, that the only way to remove a table from a database > is > via the 'DROP TABLE' command? Either directly from the dba typing the > 'DROP > TABLE' command into SSMS or indirectly via a right click on the table name > or > via a 3rd party application (Adept SQLDiff). > > Does anybody have any idea how this might have happened? > > Thank you. > > Wayne E. Pfeffer > Sr. Database Developer > Home Buyer's Marketing II, Inc. What is your recovery model? Since the drop, have you performed log backups
in full, or checkpoints/truncates in simple? This information does not stay in the log forever, sorry... On 7/6/09 3:29 PM, in article 5F4B323D-7200-481C-8E00-D22457332***@microsoft.com, "wpfeffer" <wpfef***@discussions.microsoft.com> wrote: Show quoteHide quote > Is there a reason why a drop table command wouldn't show up in the > transaction log? > > Sometime this morning, some one or some thing dropped a bunch of tables from > one of my databases. I've been using ::fn_dblog (NULL, NULL) to poke around > in the transaction log and have not been able to find anything. I have search > for any [Transaction Name] that would match a 'DROPOBJ' command, I have > filtered on time (we know approximately when this happened), I have filtered > on the table names that were dropped with only INSERTS and UPDATES coming up. > I even went so far as to create a test table '_______TEST_______' in the > database and then delete it using a right click on the table. That came up in > the transaction log. > > It is true, isn't it, that the only way to remove a table from a database is > via the 'DROP TABLE' command? Either directly from the dba typing the 'DROP > TABLE' command into SSMS or indirectly via a right click on the table name or > via a 3rd party application (Adept SQLDiff). > > Does anybody have any idea how this might have happened? > > Thank you. > > Wayne E. Pfeffer > Sr. Database Developer > Home Buyer's Marketing II, Inc. Obviously I cannot have any idea how this may have happened, but ApexSQL Log
may be able to recover the lost data if your database is in full recovery mode. -- Show quoteHide quoteKevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net "wpfeffer" <wpfef***@discussions.microsoft.com> wrote in message news:5F4B323D-7200-481C-8E00-D224573327E8@microsoft.com... > > Is there a reason why a drop table command wouldn't show up in the > transaction log? > > Sometime this morning, some one or some thing dropped a bunch of tables > from > one of my databases. I've been using ::fn_dblog (NULL, NULL) to poke > around > in the transaction log and have not been able to find anything. I have > search > for any [Transaction Name] that would match a 'DROPOBJ' command, I have > filtered on time (we know approximately when this happened), I have > filtered > on the table names that were dropped with only INSERTS and UPDATES coming > up. > I even went so far as to create a test table '_______TEST_______' in the > database and then delete it using a right click on the table. That came up > in > the transaction log. > > It is true, isn't it, that the only way to remove a table from a database > is > via the 'DROP TABLE' command? Either directly from the dba typing the > 'DROP > TABLE' command into SSMS or indirectly via a right click on the table name > or > via a 3rd party application (Adept SQLDiff). > > Does anybody have any idea how this might have happened? > > Thank you. > > Wayne E. Pfeffer > Sr. Database Developer > Home Buyer's Marketing II, Inc. You can also check the default trace to identify when the drop occurred.
Jeff Show quoteHide quote "TheSQLGuru" <kgbo***@earthlink.net> wrote in message news:hZadnWCnR-65F8_XnZ2dnUVZ_v2dnZ2d@earthlink.com... > Obviously I cannot have any idea how this may have happened, but ApexSQL > Log may be able to recover the lost data if your database is in full > recovery mode. > > -- > Kevin G. Boles > Indicium Resources, Inc. > SQL Server MVP > kgboles a earthlink dt net > > > "wpfeffer" <wpfef***@discussions.microsoft.com> wrote in message > news:5F4B323D-7200-481C-8E00-D224573327E8@microsoft.com... >> >> Is there a reason why a drop table command wouldn't show up in the >> transaction log? >> >> Sometime this morning, some one or some thing dropped a bunch of tables >> from >> one of my databases. I've been using ::fn_dblog (NULL, NULL) to poke >> around >> in the transaction log and have not been able to find anything. I have >> search >> for any [Transaction Name] that would match a 'DROPOBJ' command, I have >> filtered on time (we know approximately when this happened), I have >> filtered >> on the table names that were dropped with only INSERTS and UPDATES coming >> up. >> I even went so far as to create a test table '_______TEST_______' in the >> database and then delete it using a right click on the table. That came >> up in >> the transaction log. >> >> It is true, isn't it, that the only way to remove a table from a database >> is >> via the 'DROP TABLE' command? Either directly from the dba typing the >> 'DROP >> TABLE' command into SSMS or indirectly via a right click on the table >> name or >> via a 3rd party application (Adept SQLDiff). >> >> Does anybody have any idea how this might have happened? >> >> Thank you. >> >> Wayne E. Pfeffer >> Sr. Database Developer >> Home Buyer's Marketing II, Inc. > > In answer to a few of your questions:
We have the backup model set for 'Full'. The missing tables appeared to have been removed at about 10:40 am. We perform full transaction log backups every morning at 3:00 am and then truncate the logs. No log truncation or back up occurred just before, during or just after the event. We were able to recover the lost tables and data from our nightlys, where we are now with this is wondering how it happened and trying to figure it out so that it doesn't happen again. I have put a DDL trigger in place on the database now since I apparently can't trust the transaction log to actually log every transaction. Thanks again for any help. Wayne Show quoteHide quote "wpfeffer" wrote: > Is there a reason why a drop table command wouldn't show up in the > transaction log? > > Sometime this morning, some one or some thing dropped a bunch of tables from > one of my databases. I've been using ::fn_dblog (NULL, NULL) to poke around > in the transaction log and have not been able to find anything. I have search > for any [Transaction Name] that would match a 'DROPOBJ' command, I have > filtered on time (we know approximately when this happened), I have filtered > on the table names that were dropped with only INSERTS and UPDATES coming up. > I even went so far as to create a test table '_______TEST_______' in the > database and then delete it using a right click on the table. That came up in > the transaction log. > > It is true, isn't it, that the only way to remove a table from a database is > via the 'DROP TABLE' command? Either directly from the dba typing the 'DROP > TABLE' command into SSMS or indirectly via a right click on the table name or > via a 3rd party application (Adept SQLDiff). > > Does anybody have any idea how this might have happened? > > Thank you. > > Wayne E. Pfeffer > Sr. Database Developer > Home Buyer's Marketing II, Inc. If you are truncating the logs you can't be assured anything is in them.
Manually Truncating the logs is something that should only be done in an emergency and under specific conditions. Once you truncate the logs you have to perform a FULL backup again in order for them to be useful. And backing them up once a night is almost useless as well. The main purpose of log backups is to reduce the chance of data loss. The longer you go in between log backups the more chance there is for data loss. Most people backup the logs every 15 minutes or so and never explicitly truncate them. -- Show quoteHide quoteAndrew J. Kelly SQL MVP Solid Quality Mentors "wpfeffer" <wpfef***@discussions.microsoft.com> wrote in message news:A2D896AB-1D68-44FF-96C4-0E622FD0D32C@microsoft.com... > In answer to a few of your questions: > > We have the backup model set for 'Full'. The missing tables appeared to > have > been removed at about 10:40 am. We perform full transaction log backups > every > morning at 3:00 am and then truncate the logs. No log truncation or back > up > occurred just before, during or just after the event. We were able to > recover > the lost tables and data from our nightlys, where we are now with this is > wondering how it happened and trying to figure it out so that it doesn't > happen again. I have put a DDL trigger in place on the database now since > I > apparently can't trust the transaction log to actually log every > transaction. > > Thanks again for any help. > > Wayne > > "wpfeffer" wrote: > >> Is there a reason why a drop table command wouldn't show up in the >> transaction log? >> >> Sometime this morning, some one or some thing dropped a bunch of tables >> from >> one of my databases. I've been using ::fn_dblog (NULL, NULL) to poke >> around >> in the transaction log and have not been able to find anything. I have >> search >> for any [Transaction Name] that would match a 'DROPOBJ' command, I have >> filtered on time (we know approximately when this happened), I have >> filtered >> on the table names that were dropped with only INSERTS and UPDATES coming >> up. >> I even went so far as to create a test table '_______TEST_______' in the >> database and then delete it using a right click on the table. That came >> up in >> the transaction log. >> >> It is true, isn't it, that the only way to remove a table from a database >> is >> via the 'DROP TABLE' command? Either directly from the dba typing the >> 'DROP >> TABLE' command into SSMS or indirectly via a right click on the table >> name or >> via a 3rd party application (Adept SQLDiff). >> >> Does anybody have any idea how this might have happened? >> >> Thank you. >> >> Wayne E. Pfeffer >> Sr. Database Developer >> Home Buyer's Marketing II, Inc. Hi
It is too late probabyl , but have you run SELECT [Transaction Id], [Begin Time], [UID], [SPID] FROM ::fn_dblog (NULL, NULL) WHERE [Transaction Name] = 'DROPOBJ' GO Show quoteHide quote "wpfeffer" <wpfef***@discussions.microsoft.com> wrote in message news:5F4B323D-7200-481C-8E00-D224573327E8@microsoft.com... > Is there a reason why a drop table command wouldn't show up in the > transaction log? > > Sometime this morning, some one or some thing dropped a bunch of tables > from > one of my databases. I've been using ::fn_dblog (NULL, NULL) to poke > around > in the transaction log and have not been able to find anything. I have > search > for any [Transaction Name] that would match a 'DROPOBJ' command, I have > filtered on time (we know approximately when this happened), I have > filtered > on the table names that were dropped with only INSERTS and UPDATES coming > up. > I even went so far as to create a test table '_______TEST_______' in the > database and then delete it using a right click on the table. That came up > in > the transaction log. > > It is true, isn't it, that the only way to remove a table from a database > is > via the 'DROP TABLE' command? Either directly from the dba typing the > 'DROP > TABLE' command into SSMS or indirectly via a right click on the table name > or > via a 3rd party application (Adept SQLDiff). > > Does anybody have any idea how this might have happened? > > Thank you. > > Wayne E. Pfeffer > Sr. Database Developer > Home Buyer's Marketing II, Inc. I've already tried that and came up empty. According to the transaction log
these tables existed, were never dropped and yet they were gone. Thanks Andrew for bringing up the backup / truncate thing, I and our sys admin are looking into why things are being done that way. Show quoteHide quote "Uri Dimant" wrote: > Hi > It is too late probabyl , but have you run > SELECT [Transaction Id], [Begin Time], [UID], [SPID] > FROM ::fn_dblog (NULL, NULL) > WHERE [Transaction Name] = 'DROPOBJ' > GO > > > "wpfeffer" <wpfef***@discussions.microsoft.com> wrote in message > news:5F4B323D-7200-481C-8E00-D224573327E8@microsoft.com... > > Is there a reason why a drop table command wouldn't show up in the > > transaction log? > > > > Sometime this morning, some one or some thing dropped a bunch of tables > > from > > one of my databases. I've been using ::fn_dblog (NULL, NULL) to poke > > around > > in the transaction log and have not been able to find anything. I have > > search > > for any [Transaction Name] that would match a 'DROPOBJ' command, I have > > filtered on time (we know approximately when this happened), I have > > filtered > > on the table names that were dropped with only INSERTS and UPDATES coming > > up. > > I even went so far as to create a test table '_______TEST_______' in the > > database and then delete it using a right click on the table. That came up > > in > > the transaction log. > > > > It is true, isn't it, that the only way to remove a table from a database > > is > > via the 'DROP TABLE' command? Either directly from the dba typing the > > 'DROP > > TABLE' command into SSMS or indirectly via a right click on the table name > > or > > via a 3rd party application (Adept SQLDiff). > > > > Does anybody have any idea how this might have happened? > > > > Thank you. > > > > Wayne E. Pfeffer > > Sr. Database Developer > > Home Buyer's Marketing II, Inc. > > >  
Other interesting topics
|
|||||||||||||||||||||||
