March 19, 2008 at 3:36 am
Hi
I have a system running on SQL2003. It happens at more that one site that a table is suddenly empty. There seems to be no corruption - the data is just deleted. There are no deletion scrips or applications. I know that the servers at these sites are running at 100% capacity. How can i track what happened?
March 19, 2008 at 3:44 am
In terms of finding out what happened there are some third party tools that will allow you to look at the transactions that went through the log file. This assumes that you have backups of your log or you haven't truncated your log (and you don't have it in simple recovery mode).
If you don't have log backups and your log is truncated then there's not much you can do.
In terms of handling the problem in future you could run a trace and filter it on operations that occur on that table.
March 19, 2008 at 3:51 am
Txs Karl - I will run a trace and then have to wait and see
March 19, 2008 at 3:59 am
Hi
The issue is that i do not know with which table is is going to happen next - i seems to be any of the table carrying financial transaction data. Will the trace not impact performance as i have to put a trace on several tables
Txs
March 19, 2008 at 4:12 am
imgflow (3/19/2008)
HiThe issue is that i do not know with which table is is going to happen next - i seems to be any of the table carrying financial transaction data. Will the trace not impact performance as i have to put a trace on several tables
Txs
Ah, I thought that there was only one table and there was a specific time that it seemed to be happening.
Running a trace will impact performance somewhat but it's hard to say by how much - I run a trace every day on our live production servers for a short period (30-45 minutes) to get an idea of performance. In our situation it hardly impacts performance but every production environment is different. If you filter for the most likely tables, and also filter for delete or truncate statements in the TextData column you might be OK. You'd probably need to trace for SP:StmtStarting and SQL:StmtStarting as it's possible the command is coming through a stored procedure.
Failing that, if you don't want to go down the tracing route, you could start backing up your transaction logs and when the problem next occurs use one of the third party tools to track what happened. There are various tools available - SQL Log Rescue is one them.
March 19, 2008 at 12:32 pm
I have seen something like this a couple of times before. Both times it was related to the syntax of the [font="Courier New"]DELETE[/font] statement.
Here is how you typically delete a single row:
[font="Courier New"]DELETE FROM {tableName} WHERE PrimaryKey={value}[/font]
Simple enough, however, if something in the client code, or something between the client and the server cuts the command string off between the tableName and the [font="Courier New"]WHERE [/font]keyword, then BOOOM! no more data.
In one case, it was a logic bug in the client code that would forget to append the [font="Comic Sans MS"]WHERE[/font] clause when constructing the statement string. In the other case something in the rickety network was resulting in a [font="System"]nul [/font]character (ascii 0) between the tablename and the [font="Courier New"]WHERE[/font]. SQL Server back then used C-based nul-terminated strings (it may still) and that nul was seen as the end-of-string and thus the end of the command.
Now I use stored procedures.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply