1
Ok, I'm looking for expert advice on this one.
I have a database which keeps going into read_only mode. Sometimes it goes into read_only / single user mode. Once it was taken offline completely. This seemingly happens randomly. Out of all of the database I have worked with, this has happened on 3 of them - several times randomly to each. All three of the databases that have exhibited this behaviour have been databases I have written for the company to which I am currently employed by. Once with SQL server 2000, another with sql server 2005, and another with SQL server 2008. Two of the database this happened to were/are using an asp.net front end - the other one was over 2 years ago and was using an ASP classic front end.
To be blunt, I believe a colleague who works for the same company is doing this in order to discredit me. I am almost certain about it, since it has happened at extremely odd times - like the day before a project review meeting where I really don't need to be spending hours trying to get my database out of read only mode - or today.. which was the worst occurrence yet - while in a project review meeting, he attempted to throw the database design under the bus directly to the boss man - to which the end result was us agreeing we would stress test the database to see whether in fact there is a problem with the design of the database or just any point of the database itself. I created a stored procedure to slam a parent table with 1,000,000 records, and then insert 40 records into a child table for each parent record. 41,000,000 records in total. (and yes I realize that's not an accurate test) If that was successful, I was going to perform other tests, this was just a good enough place to start. All was going good for 45 minutes, around 9,000,000 rows inserted and counting with minimal effort on the database server's part considering the feat it was performing. I left my desk to walk out of the building at 4:57ish and left the script to continue executing, give or take (while my colleage was still upstairs I might add). I got home later on, and the query had bombed. I checked the logs and the database went into read_only mode at 5:02.
The "dispute" between my colleague and I is over database design concept. He wrote the original one for the company - one that includes more than a few tables with 350+ columns. Rampant misuse of data types, no stored procedures. Horribly written views, etc. The database is written based on column based input, instead of row based relational data.
The database which I have structured - and which all three databases exhibiting this behaviour are/were structured like - uses relational n-tier structures with stored procedures, triggers, etc. EG: Table A has parent record, Table B is a "child" table of Table A and has 20 subsidiary records which relate to the parent record in Table A.
So.. I have several questions: Most of them simply because I'm second guessing myself now given the recent events, and I need to verify I'm not nuts.
1) Is this normal behaviour given my database design
2) Have I made serious error in the design of the database?
3) Is my colleague screwing with me?
4) If my colleague is screwing with me, can you think of any way I can set a trap for him and/or track by which method and/or user this issue is being executed - is there any way I can obtain proof that he is screwing with me.
5) Would you recommend any tweaks to my database design, or is it pretty standard?
6) The existing database, built by my colleague years ago has tables in it with 350 columns. Is 350+ columns in a single database table ludicrous or common place? It's ludicrous from my experiences, but I want to gather other people's thoughts on the matter.
7) If I'm not being screwed with, can you think of anything that could cause such a problem to occur?
Thanks, and I look forward to your responses on the matter :)
