April 11, 2007 at 10:28 am
Hi,
I've experienced a weird issue last week. Some tables in a database were lost during a transaction. Here is the scenario (SQL 2000) :
In a software X, save a quotation (into a table dbo.Quot), and then print it. In the background, some temp tables (like [dbo].[#TMP_Quot_0]) are created, relevant data are copied from the Quot table into the temp table. Then the software prints, and after, it drops the temp tables.
However, during a print, the software hung up on one station. The user left everything on the station, and got to another one. He did the same thing (save a quotation, print it) without problem. He came back on the hung station, and killed the software (using task manager).
When the application was killed, the main table Quot was somehow lost into the process, along many others tables related to the printing process. I suspect that SQL rollbacked the transaction and dropped the tables internaly. All the main tables used to copy data into the temp tables were lost.
The user used to connect to SQL is SA (OK I know that it is not recommended, but if any other SQL user could have also "lost" the tables).
The server was also rebooted later, suspecting that there was some OS / Memory / cache issues.
SQL Logs files does not show disk errors or anything suspect. Sounds like it is a normal behavior of SQL server.
Databases log files didn't reported drop transaction (Or I may not know how to get that information properly) The Recovery Model is at full. We tried Apex SQL Log, Lumigent Log Explorer, mdf recovery programs, dbcc check db...
So my questions are:
Is there things we must not do that may remove precious information from the database log files?
Is there a special way to backup the transaction logs files to be able to consult them with the log tools?
Is it a normal behavior of SQL to "Lost" tables when a rollback is done with temp tables?
Any clues or guides to recover tables?
The customer is back again on their tracks, but we dont want to have that problem again...
Patrick Veilleux
April 11, 2007 at 11:54 pm
Hi Patrick,
Do you mean to say that the Tables were DROPped by SQL server anyhow? I never heard of such kind of problem. In not case the Table can be dropped by a rolled back Transaction.
In any case if this is the case, that must have been done by Faulty Stored proc. or Software itself.
April 12, 2007 at 5:53 am
Strange problem. Sorry if I ask, but are you sure that the tables are lost ? Is the Db in simple of full recovery mode ?
When you boot your server the temp DB is cleared.
To recover table you need a backup tool like LiteSpeed.
April 12, 2007 at 7:46 am
Hi,
I've checked in the sysobjects table if I could find out at least the QUOT table name, and it was not there. Some other SQL guys here could confirm that the tables are not there anymore.
The DB is in full recovery mode. The server was rebooted before any db backup was taken.
I will have a look at LiteSpeed.
Thank you,
Patrick
April 12, 2007 at 8:07 am
Can u post the results to this query?
select * from sysobjects
where name='quot'
April 12, 2007 at 9:06 am
Hi,
No rows are returned.
Patrick
April 12, 2007 at 9:11 am
But you said, sysobjects did have some trace of 'quot' table.
April 12, 2007 at 9:51 am
"I've checked in the sysobjects table if I could find out at least the QUOT table name, and it was not there"
Patrick
April 12, 2007 at 11:58 pm
Try a select * from sysobjects and check if other tables are lost.
Grab your last backup and restore it with another name and check for your table. Try to find out if a procedure will delete the table. You may try to reproduce the error and monitor the transactions with profiler
April 13, 2007 at 8:27 am
Hi,
Yes, there is also other tables that was lost in the same transaction: I didn't mentionned them because they were all used in the transasction and were all lost in the same way:
Quot, QuotLines, CieInfo, QuotTaxes, DocumentNumering, Partners
During the transaction, temporary tables are created (#TMP_Quot_0, #TMP_QuotLines_0, #TMP_CieInfo_0, #TMP_QuotTaxes_0, #TMP_DocumentNumbering_0, #TMP_Partners_0), and only relevant data from the main tables is inserted into the temp tables.
The printing occurs and then the temp tables are dropped.
Somehow in that transaction, those main tables were lost... I don't think the Quotation software could do it, because I suppose it was killed before the temp tables are dropped... or even if it could drop the temp tables.. the main tables should not be affected...
I already traced the operation with the SQL profiler, and this is how i've discovered the use of temp tables and many "exec sp_prepexec" statements.
I'll continue to try to reproduce it along with tracing the transactions... I should be able to reproduce it one day or another...
Thank you for your ideas.
Patrick V.
April 16, 2007 at 2:29 am
Run DBCC CheckDB to see if you have any data corruption.
Since you say the users connect as SA, are you sure someone didn't accidentally drop the tables? That's the most likely explaination.
If you still have your log backups you may be able to use the log reading tool mentioned above to see if there's a drop table there. I'm not too familiar with the log reader tools.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 23, 2007 at 2:02 pm
Hi Gang,
I think that the software issued the wrong drop table command...
I've found out the resulting transactions on the system tables in Apex SQL Log explorer:
DELETE FROM [dbo].[syscolumns] WHERE [id] = 930870433 AND [colid] = 1 AND [number] = 0
DELETE FROM [dbo].[syscolumns] WHERE [id] = 930870433 AND [colid] = 2 AND [number] = 0
....
DELETE FROM [dbo].[syscolumns] WHERE [id] = 930870433 AND [colid] = 18 AND [number] = 0
DELETE FROM [dbo].[sysindexes] WHERE [id] = 930870433 AND [indid] = 1
...
DELETE FROM [dbo].[sysobjects] WHERE [id] = 930870433 <<<<this is the table itself
DELETE FROM [dbo].[sysobjects] WHERE [id] = 946870490 <<<<this is the default for a field
...
... <<< Primary keys deleted... etc...
all logued under the same SPID as a the user that worked into the system and doing quotes.
I've tried to "undo" the transactions with Apex SQL log... but the resulting script directly writes to the system tables. This left me with a correct "table" structure, I'm able to read some fields contents (the data is partially present in the pages), but some fields are corrupted. (Page (1:94360), slot 0 for text, ntext, or image node does not exist. Connection Broken)
So, it doesn't seems we can successfully restore back a table from only the transaction logs.
What is your best procedures to restore a table? Restore a recent backup on a temp database, restore transaction logs, and then copy the tables to the live db?
Thank you for all your clues and guidelines.
Patrick V.
April 24, 2007 at 12:17 am
Restore a backup, restore the tran logs and stop just before the 'drop'
Then you can find whoever wrote that code and give then a good wack upside the head for directly updating the system tables. That's a big, big, big no-no!
I'd look at altering the security, reducing the rights that the users have. iirc only sysadmin can update the system tables and even they they need to switch the option on.
Go to your server and run the following.
exec sp_configure 'allow updates'
If the config or run values are 1, then run the following to switch the allow updates off
exec sp_configure 'allow updates', 0
That will ensure that the deletes from the system tables won't happen again. Unfortunatly the app may throw errors. Personally I'd prefer errors to system tables being manipulated.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply