May 25, 2004 at 9:43 am
I'm curious if anyone knows how a table somehow became, I guess, corrupt. A program could no longer insert into it until we dropped and recreated the table. Of course, we didn't know to do this rigt off because the program that inserts into caused a memory problem, which led us all over the place trying to troubleshoot.
Specifically, this program inserts records every 2 minutes into a table. All of the sudden it began blowing up. The program's process would grow in memory from 6MB to about 900MB. The server has only 1GB of RAM; it would return a memory indicating out of memory. I tried trracing and troubleshooting memory-related settings. The trace showed that the program would try to insert into the table, could not, and then would keep trying in an infinite loop. The trace showed the error message was "No data found" even though an INSERT was being executed. Doesn't make sense.
To fix the problem (as a guess), we dropped and recreated the table. The error mesage was not helpful, Seems like this happened in the past at one time with another totally different database and program.
Anyone know how you can detect such a problem with a table/index to proactively fix it? Has anyone every experience this?
Thanks!
smv929
May 25, 2004 at 9:50 am
my guess would be to set a resonable query timeout at both ends. you can do this in EM by right clicking on your server and going to Connections and make sure the query timeout is not 0.
also in your application get the developers to put in some code to trap queries that are repeated if they fail.
come to think of it if the queries are being repeated in an endless loop the fault would more than likely exist in the software. do you know by what process the queries are sent ? do they use a transaction manager (DTC?)
cheers
dbgeezer
May 26, 2004 at 7:51 am
Thanks for the good suggestions. I agree the programmers should trap code if the query is repeated, but that will be hard to implement here. I'm not sure how this particular program manages or if it uses transactions. I would feel better if I knew why the table became in this state and how to detect it.
smv929
May 26, 2004 at 10:06 am
...possibly an alert in PerfMon...?
May 26, 2004 at 10:07 am
I would recommend that you run weekly maintenance on your databases and run DBCC's to check allocations. If this was a corrupted table you probably would have seen it by running a 'DBCC CheckDB' command against the database. You can also have checkdb attempt to fix the problems, but you want to make sure that you run this off hours or tell your users that the db or table will be offline during the maintenance.
Take a look at the various DBCC commands in BOL for more information.
steve
May 26, 2004 at 1:25 pm
Check the table fragmentation with DBCC showcontig. Based on the indexes and the order of inserts, over a period of time the table could get severely fragmented slowing down inserts. You might want to check for any locks or deadlocks.
May 26, 2004 at 2:04 pm
have you tried running the particular query the application is falling over on directly within query analyser (against the target database) to see if you replicate the issue or get the desired results?
lloyd
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply