DML (DROP TABLE) not showing up in transaction logs?
-
Hello,
Yesterday we 'lost' a number of production and testing application tables. I thought this was a great time to convince my customer to purchase Lumigent's Log Explorer so I managed to obtain a temporary full license for the product. Once I loaded up the entire days worth of transaction log backup files and ran the tool I expected to find all of the DROP TABLE commands...but I wasn't able to find a single one.
My questions are,
1. Are there ANY types of transactions that don't flow through the transaction logs?
2. Short of deleting rows from the system tables, is there any way to drop a table without using DML (i.e. delete from sysobjects)?
3. We have recently started doing merge replication with the servers that lost data also serving as our distribution server. Has anyone seen tables disappear when doing replication?
Thanks!
Art Decker
-
Of course I know this is DDL not DML !
-
Are you in SQL2000 with FULL recovery model?
-
I've just come off a replicatoin project where we were losing foreign keys in the DB. Also could not track the DROP statements. We did not look at the log, but we did run a trace , but did not find anything in the trace log.
Regards,
Morpheuz
"See how deep the rabbit hole goes..." -
About 4 years ago, we set up merge replication for our agents with SQL7.
We tested it for about 3 months with 4 subscribers and everything worked yust fine. Then we went live with 25 subscribers.
Everything went wrong from that moment; lost data, lost foreign keys, lost tables. In short, all databases went completely corrupt.
After this I never touched replication again!
-
I have never seen tables disappear with replication except once when I cancelled replicaiton in the middle of a rebuild becuase when you sleect initialize the data and schema at the subscriber it drops existing tables and recreates them.
DDL statements are NOT Logged only data changes are logged. That is why you did not see the drops in the transaction log. The only way to "rollback" those changes is through a FULL database restore.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Actually, DDL statements are logged. We just ran a test and monitored through log explorer. The creates, drops, everything was captured. Check out this header in BOL:
'Transaction Log Logical Architecture'
-
1. Yes, we are running in full recovery mode.
2. Running some tests, Log Explorer does track create/drop table commands.
Actually opening up the a backup log file that was created after we lost the data in Log Explorer shows that there are no rows to display.
Recovering the database to a separate server results in the following message when we attempt to restore this particular backup log.
"The log in this backup set begins at LSN 2307xxxxxxx, which is too late to apply to the database. An earlier log backup that includes LSN 2306xxxxx can be restored. Restore LOG is terminating abnormally."
Restoration of the backup log prior to the one referenced succeeds.
Is it possible that our log backup is corrupt?
-
Sounds like your missing an earlier tran log or diff backup?
-
This is why I like this site, I learn something new every day.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Just out of curiousity, what would happen to a backup transaction log if the database was shut down while the job was still running?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply