SQL Server Database loses half of its records and indexes

Post #57,664 by orion 10/18/02 6:58:17 PM Reply	SQL Server Database loses half of its records and indexes Either I am going mad, or SQL Server 2000 is more buggier than I thought. In the past week, twice we have lost half of our data and half of our indexes. The log files do not show any record deletions that I can find. All that had been done recently was replication, backup, and exporting of the database. None of that should be deleting stuff, to the best of my knowledge. But if I am wrong, what conditions would make it so that over half the stuff we have goes missing? Windows 2000 SP3 SQL Server 2000 SP2 I had MSDE 2000 on my laptop, it did not have the same problems as its big brother. doing the same stuff. I will be setting up SQL Server 2000 at home and doing some testing to see if it does the same thing at home, same OS, same service packs, etc. Maybe a different database, but I will try to create the same conditions. Any ideas? Apparently we cannot pay for support, any third party support must be free. No third party tools, either. My hands are seriously tied here. *[link\|http://games.speakeasy.net/data/files/khan.jpg\|"Khan!!!" -Kirk]*
Post #57,730 by gdaustin 10/18/02 10:09:13 PM Reply	SQL Database loses half of its records and indexes Indexes can be rebuilt, so don't worry about those. Maybe some of your DB "containers" have gone missing from the DB configuration. DB2 won't let the containers go missing (the DB won't start), but I don't know if SQL Server would have a problem if a disk drive didn't start up, or if a DB container was on a network drive that wasn't attached? I would think a good relational DB wouldn't allow that kind of crap to happen. But, we're talking about SQL Server here... What about a cascade delete from a foreign key? I wonder if the export in SQL Server also deletes the records as an option? I'm pretty sure DB2 doesn't have that kind of option on export, but SQL Server does have a lot more "features". SQL Server should have some kind of trace tools to capture statements as they execute in the DB. I would recommend turning on these trace tools, then trying to recreate the conditions where the data was lost. If the problem is software (not missing DB containers), then the trace should capture that statements that caused the data to disappear. Some more things. Make sure the DB security is tight. The password to the sa account and other admin accounts should not be widely known. If the passwords ARE known, then it is entirely possible you have an intruder. Finally, I think you said you work in a hospital, so this database should probably NOT be out on an Internet connection. Make sure of this because if the DB ports are open to the Internet, you could be hacked (and someone could be deleting your data for fun and harassment).
Post #59,585 by tablizer 10/25/02 11:19:48 PM Reply	Recipe for doom Finally, I think you said you work in a hospital, so this database should probably NOT be out on an Internet connection. Make sure of this because if the DB ports are open to the Internet, you could be hacked (and someone could be deleting your data for fun and harassment). Microsoft + Internet + Hospital_Data = Holy_Sh8t! I personally would recommend against MS-SQL for important or sensative data, if you have a say there. I have seen it do frightening things. Maybe Oracle or DB2 is a safer choice. ________________ oop.ismad.com
Post #59,629 by jlalexander 10/26/02 3:38:31 PM Reply	Re: Recipe for doom I'd totally disagree. I worked for BlueCross BlueShield for a few years where we had multi-gigs worth of Medicare data in SQL Server, and never had a hitch. And, that was on SQL Server 6.5. If you have problems, you have a bad DBA. I never had any database issues until I worked for JPMorgan Chase Bank where they forced us to convert our application from SQL Server to Oracle. We had huge slowdowns, and continuous data corruption. And, Oracle alone killed our development budget. So, it's all subjective. You've all had your bad experiences; so have I. ---- My pid is Inigo Montoya. You "killed -9" my parent process. Prepare to vi.
Post #59,646 by mmoffitt 10/26/02 6:18:53 PM Reply	I'd disagree as well, but not so strongly. Because Sql Server (especially 7.0 & Up) is so easy to configure, too many times inexperienced DBA's are charged with setting up the database. This can lead to really big problems. (For instance, if they're experienced w/one of the Xbases, they're unfamiliar w/true databases, so the set up Public w/select, insert, update, delete permissions on the tables. No joke, I actually saw this for a not-so-small HMO once.) I'm pretty much in agreement w/greg. If you've got an experienced dba, he'll get the job done w/Sybase, MS, DB2 or Oracle. bcnu, Mikem
Post #59,815 by tablizer 10/28/02 2:22:42 AM Reply	DBA's fault? So what you are saying that if the DBA is no good, then queries may return the wrong answer? Crash or slow or bad security I could see, but it should never return the wrong answer just because the DBA is a dultz. That strikes me as odd. ________________ oop.ismad.com
Post #59,840 by ChrisR 10/28/02 9:37:21 AM Reply	Have an example handy? There was a problem with SQLServer 7.0 when both assigning a temp variable and using it as a constraint - it would cause a problem when the query was interrupted with a context switch. My opinion on that matter was that it was bad query design in the first place - though MS should have fixed the problem. Other than that one, can you give some examples of wrong results being returned?
Post #60,104 by mmoffitt 10/29/02 6:58:07 PM Reply	Actually there was a bug in Sql 7.0. Before SP1, if you had a query that had a where clause that looked something like: where (a.Foo = 1 or a.Foo = 2 or a.Foo = 3 or ... a.Foo = 40) and (a.Foo1 = 100 or a.Foo2 = 101 or a.Foo2 = 102 or ... a.Foo2 = 201) and a.Foo3 = 200 ... When the sql was compiled, only the first big OR was included and executed. AFAIK, that was the only one where a legit (although messy) query might have returned the wrong result. And it was fixed in SP1 for Sql 7. (Note: I think this is an example of the problem, I'm stretching my memory here, but there was a bug in how large OR's were handled). bcnu, Mikem
Post #60,199 by gdaustin 10/30/02 11:04:42 AM 10/30/02 11:06:21 AM Reply	Seen lots of SQL generate "incorrect" results... Not so much because the SQL parser was bad (although that does occur occasionally), but because the person didn't know SQL in general or that specific dialect of SQL very well. I actually had a bug in some of my DB2 SQL code some time ago, where I had a multiple line SQL statment with line continuation characters (so the parser saw the code as a single line ), and I put a -- comment at the end of one of my lines before the \\. The query ran fine, but I didn't know that the -- caused the parser to ignore everything else in the SQL statement. Then I started getting results I wasn't expecting, rows were included, that were supposed to be excluded. I started investigating. By the time I figured it out, I was cursing DB2 SQL big time. Now, I know better. It took me about 2-3 hours to figure it out. In SQL, -- comments cause everthing to be ignored to the end of the line. If you use line continuation characters, then everything on subsequent lines is ignored, too! These kinds of bugs are the ones that separate the people who have been in a CS class in college (where parser principles are taught), from everyone else. I've seen people who didn't understand parsing spend WEEKS trying to figure out these kinds of problems. They end up reading the manual or asking someone before they "get it". Edited by gdaustin Oct. 30, 2002, 11:06:21 AM EST Expand All History
Post #60,201 by wharris2 10/30/02 11:10:57 AM Reply	Those dang outer joins The rules for outer joins are pretty well defined but often far from intuitive. I've seen SQL using outer joins produce seemingly weird but (after meditating on them for a while) completely logical results.
Post #60,205 by CRConrad 10/30/02 11:15:37 AM Reply	Why does it care about line-ends in the first place?!?
Post #60,213 by gdaustin 10/30/02 11:50:03 AM 10/30/02 11:51:17 AM Reply	Re: Why does it care about line-ends in the first place?!? It may have just been the command line tool I was using. We write a lot of DB scripts and then "pipe them" into the DB2 command line tool. That way they can be automated into shell scripts quickly. We even archive our scripts in our source code database, something I've never seen done before, but now I'm a fan. I'm now a "zealot" about this, because I can't lose key DDL or queries, and I have back versions. All the commands we need to create tables, indexes, permissions, views, etc. are in our source code control system. In fact, I've even created a script which pretty much creates all the tables/indexes/permissions in the DB after the DB is created. Now that I've been to SysAdmin class for DB2 (and I'm certified), I could actually automate the whole thing ( DB creation, tablespace creation, tuning comands ) into a script or series of scripts. But maybe that's going too far. Then again, we're about to run a test on some hardware we may be buying, and that level of automation could make my life really sweet. Just walk in, run the the scripts, and we're all installed! But, alas, I'm also SysAdmin, system architect, and Java developer, too. Edited by gdaustin Oct. 30, 2002, 11:51:17 AM EST Expand All History
Post #60,214 by admin 10/30/02 11:52:45 AM Reply	We do the same thing here. All DDL, packages, even the client config data, is in the source code control system. You can run one command and set up the entire system, including databases, web servers, and process servers. Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."
Post #60,393 by static 10/31/02 6:30:42 AM Reply	We've done that, too. Well, for the SQL, anyway. It was an illuminating experience trying to use it to create a new install. We discovered a number of tables with incorrect definitions. :-) Wade. "Ah. One of the difficult questions."
Post #60,693 by Meerkat 11/2/02 12:35:34 AM Reply	Speaking of incorrect results... At two places I've worked they've used a database that can't even count properly. RedBrick database, now owned by Informix now owned by IBM. Stores count results in some kind of pre-calc table thing, and doesn't update it anywhere near enough. Select count(*) from fred where 1=1 forced it to actually look at the table in question and give you an accurate result. In a word: shocking. John. Busy lad.
Post #59,893 by wharris2 10/28/02 1:15:04 PM Reply	Possibly For instance, if the DBA gives update, insert, and read access to all tables... it's fairly easy for a clutz to update data in a table inappropriately.
Post #58,162 by mmoffitt 10/21/02 10:26:27 PM Reply	What kind of replication are you doing? In Sql Server you can delete rows w/no transactions. Not possible that "merge" replication is the culprit is it? (i.e. data deleted from the subscriber then merged w/the publisher?) You probably already know this, but NOBODY should have base table access (except sa of course). SP's for insert/update/delete and SP's or views for selects ;-)
Post #58,171 by folkert 10/21/02 10:48:50 PM Reply	Expect no response from Norm... `[link\|mailto:curley95@attbi.com\|greg] - Grand-Master Artist in IT [link\|http://www.iwethey.org/ed_curry/\|REMEMBER ED CURRY!!!] Your friendly Homeland Security Officer reminds: Hold Thumbprint to Screen for 5 seconds, we'll take the imprint, or Just continue to type on your keyboard, and we'll just sample your DNA.`
Post #58,379 by mmoffitt 10/22/02 12:51:47 PM Reply	Why's that? Is he no longer active here?
Post #58,385 by CRConrad 10/22/02 1:03:05 PM Reply	Claimed so on the mailing list- but, "Promises, promises..."
Post #60,964 by orion 11/4/02 4:23:36 AM Reply	Get over it I am back, somewhat, but not as active as before. I tried to quit, but I cannot quit cold turkey. So I came back because you guys kept on messing with me. Why not listen to Scott, your Admin here, and just leave me alone, treat me with silence? *[link\|http://games.speakeasy.net/data/files/khan.jpg\|"Khan!!!" -Kirk]*
Post #60,965 by orion 11/4/02 4:29:26 AM Reply	Update on Replication After having half the data on the database going missing twice, our fearless leader decided that Microsoft SQL Server Replication will not be used. So now I will attempt to re-write the Replication part using just Visual BASIC, ADO, and whatever I can figure out to copy from one database to another and update the data. This is a trick that even Microsoft, IBM, and other companies have not figured out how to do. Not Replication, but more like Synchronization like a PalmOS device does. Hospital Server has data, our Server has data. We make a change on ours, it gets sent to theirs. They make a change on theirs, it gets sent to ours. We won't always have a connection to their LAN. Some of them have a dial-up, others an encrypted connection and the key changes every 15 seconds to connect to it, and yet others have a VPN that we have to manually connect in order to connect to their network. So SQL Server Replication will not work here, I will have to make a custom VB program to do the Data Copy for them, So simple that they just make the connection, set up the UDL files to point to the Hospital database on our server and the Hospital database on their server, and then they just press a button and start the Data Copy. Brain Surgery, Rocket Science, Quantum Physics, all child's play next to this. Wish me luck. *[link\|http://games.speakeasy.net/data/files/khan.jpg\|"Khan!!!" -Kirk]*
Post #61,028 by mmoffitt 11/4/02 2:40:38 PM Reply	Try MSMQ. Might work for you.
Post #61,084 by boxley 11/4/02 8:06:19 PM Reply	how will you resolve last write issues on the same record? hospital db server db temp work db both hospital and server put changes to temp db which has a copy of server db at a specific point in time like 00:05 am during the day as things are update replace the hospital db with tempdb using a copy function. at midnight replace server with tempdb rinse and repeat. Might want to ask tablizer to verify but it should be able to be done in xbase in a matter of hours. Trouble is your not using xbase. thanx, bill will work for cash and other incentives [link\|http://home.tampabay.rr.com/boxley/resume/Resume.html\|skill set] "Money for jobs? No first you get the job, then you get the money" Raimondo
Post #61,122 by CRConrad 11/5/02 2:29:23 AM Reply	I've told him how to do it already.
Post #64,710 by orion 11/24/02 4:30:46 AM Reply	Thanks, but I lost that job. I told them the way you wanted me to do it, and the boss said "No". It may have led to my firing, suggesting Interbase and other Borland products. After he read the email I sent him on it, he slammed his fists down on the desk, packed up his laptop after shutting it down, and then ran out the door muttering "Damn programmers...." or something like that. *[link\|http://games.speakeasy.net/data/files/khan.jpg\|"Khan!!!" -Kirk]*
Post #64,729 by CRConrad 11/24/02 7:30:37 AM Reply	Yeah, sure - that must be why I did it. (new thread) Created as new thread #64728 titled [link\|/forums/render/content/show?contentid=64728\|Yeah, sure - that must be why I did it.] Christian R. Conrad Mechanisation As our souls are slowly stolen The wheels of progress keep steamrolling Mechanisation melts our minds To drive the furnace that drives us blind. -- [link\|http://www.vergenet.net/~conrad/poetry/mechanisation.html\|© Conrad Parker, 1993]
Post #61,505 by Arkadiy 11/6/02 8:37:22 PM Reply	Since you're down to synchronization, not replication now - should you export the data to some simple portable format, and then send it to hospital and/or to the home base? This way you don't need any sophisticated tools over connection, and if you chnage the central data server, you can still support old versions. -- We have only 2 things to worry about: That things will never get back to normal, and that they already have.

Welcome to IWETHEY!