Very strange behavior of SS2K...
-
Hi all,
I face a problem as follows: We have an application runnig on SS2K.We log every delete of documents(from Archive table) in another table.Now it seems some of the rows have deleted strangely without any delete log by our application.We assumed there is somebody who has direct access to database and delete them manually(obviousely our app does not generate any log in this situation)But there is no people.We check that with admins many times.
Does SQL Server itself deletes rows for any reason? How can I know what is happening?Do you think our app flaws somewhere?
Thanks a lot for your attention.
-
No, SQL Server doesn't delete rows for any reason. Whenever I want to set up an audit trail like you mentioned, I use triggers rather than relying on the application to do it.
You can start a trace using Profiler and define a filter to grab DELETES and I would also include UPDATES against that table. One note is that rather than trying to use the Database Name and Object Name, use the Database ID and Object ID to define your filters.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
DC makes a good point and it's preferable to use triggers to update an audit table over having your applicaiton handle this. This way you know who altered the data and when regardless of wheter it was through the app or not. But this is after the fact and will only prevent future problems.
If no one else has access to your database, have you also run DBCC_CheckDB or DBCC_CheckTable to make sure that you don't have a data corruption issue?
My hovercraft is full of eels.
-
If this is a big issue for you and you do not want to rely on triggers you can take a look at a tool that interrogates the active and backed up transaction logs. One is LogPI (http://www.logpi.com). This tool will tell you who deleted/dropped rows/tables and give you methods for undo-ing the changes.
-
maybe someone ran "truncate table tablename". This would be a non-logged delete.
Also, triggers can be disabled; which would do the same.
cl
Signature is NULL
-
Does SQL Server itself deletes rows for any reason? How can I know what is happening?Do you think our app flaws somewhere?
As said above, no, of course SQL Server does not delete rows itself. Either you have data corruption as sswords hinted, though I think you would have noticed that in other ways as well. You application might of course be flawed, but only you can know that. I think it is quite possible that someone ran a DELETE statement manually though.
It could of course be an intruder, but I always have a suspect feeling when someone says something like "But there is no people.We check that with admins many times." First of all, what do they mean with that? Of course there are people who have access to the server, at least your application does. And then there is the quite possible reason that someone made a mistake, and ran a DELETE statement with incorrect filtering or forgot to start a transaction.
This has happened to me (and a project I was in), where a network admin who was learning SQL while he was on call during nights mistakenly ran an update statement replacing all texts in a table with the word 'Hello'. This is so common that there is even a law for it:
"Never attribute to malice that which is adequately explained by stupidity" - Hanlon's Razor
-
maybe someone ran "truncate table tablename". This would be a non-logged delete.
I'm not sure exactly in which context this is, but just to make sure noone misunderstands it when reading. TRUNCATE TABLE is not really nonlogged, it is minimally logged. The individual row deletes are not logged, as they are for a DELETE statement, but the operation is still logged and can be rolled back. From BOL:
TRUNCATE TABLE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log.
It is true however that triggers are not triggered by a TRUNCATE statement.
-
If someone is in your database with the ability to run TRUNCATE TABLE or ALTER TABLE you have a HUGE security hole, and a few missing rows in a table are the least of your worries.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Not to mention that truncate won't delete only some of the rows anyways... now that would really be a big concern
. -
Hi thanks all for replies,
When I said it is not logged,I mean the log by our application,which means some inserts into a table called LogTable.
I did not mean log entries in transaction logs because I don't know a way to read these T-Logs and I think truncate does not(or at least a does a little) log into T-Logs(As in Oracle).
BTW, Our applications logging method is using a stored procedure call after pushing Delete button in GUI.This stored procedure does some inserts into that LogTable.
We have similar thing implemented using Triggers in Oracle version and have no problem,I think some sort of exception or rollback cause not calling stored procedure correctly.Is there any idea?
-Your help is so appreciated.
-
As was said above, you can use triggers in SQL Server as well. Or, if you just want to see now afterhand what happened, you can use a tool such as Log PI to look in the transaction logs and see info about the delete statement that removed the rows.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply