Disappearing Data in SQL Server

The problem i am facing here is disappearing data in sql server 2000. Inot sure what is the problem, but it is getting worse and worse. Anyonecan point me to a right direction whether this is a securityvulnerability which i need to download the patch, or is it my computeris infected with virus., or is it a bug in sql server?
I remember having read somewhere about this problem also, but not surein which website already. Anyone, please help!!! Because we are goingto deploy to production server soon, and this is a problem that need tobe solve ASAP..
Thanks in advance...

Hi,

There can be a number of reasons for data to disappear, not all of them are the result of a bug, or attack. If the referential integrity of the data is compromised, the normal activities of inserts, updates and deletes can cause data anomalies. SQL Server is a RDBMS and as such relies on the relational model. If the schema of the database is not setup correctly in the beginning, or it has been altered (denormalizing a DB to improve performance can often lead to loss of referential integrity), you loose the primary benefit of using an RDBMS. BRN..

|||Yes, there are just so many things that could be the cause. Atrigger gone haywire, a faulty DELETE statement, plus all of the thingsthat Brian mentioned.
You might consider runningSQL Profiler to help you identify where the problem lies.
|||Thanks brian and tmorton for your prompt replies.
As i am new to sql server, may i ask how do i go about to identify the problem?
Is there any website or walkthrough that you all can recommend?
Thanks a lot in advance...
|||

Hi,

Here's a link to download SQL Server books online. It may come in handy while you work through the problem. http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp (via tmorton).

If you, or whoever the person was that designed your database, doesn't have a working knowledge of the principles of Relational Database design, isolating a design problem will be tough. (And as we mentioned - it may not be a design problem you have). Even if it turns out that the problem you are having now is not related to Relational Database Architecture, you'll still want to make sure that any R-DB, the accuracy and reliability of which is important to you, is designed correctly. I'd try to spend a little time on getting familiar with a few terms at the core of R-DB design issues.

Relational Database Management Systems(RDBMS), like MS SQL Server, Oracle and others, are used to create and manage databases that comply with theRelational Model. This model is kind of a universal template for these types of databases. The more closely your application specificdata model (data modeling is the process of designing the structure for a database), or DBschemacomplieswith theRelational Model, the more mathematical certainties you'll have that your DB will not be prone to a limited class ofdata anomalies. Even though such anomalies are only one of the many things that can go wrong with a DB, they are good to exclude because they can crop up unpredictably and can be very hard to trace logically.

A big part of having a DB schema comply with the Relational Model is the process ofnormalization. This is mostly a process of making sure that all the data in your design is stored or referenced in one, and only one, logical location in the DB schema. In general, the higher the degree of normalization the closer the compliance with the RM. However, there can be performance costs when running certain types of queries against a highly normalized database. So, a schema might bedenormalized to improve the performance of such queries. In this case, the DB architect must compensate for the loss of the built in protections of data integrity provided by compliance, with protections of their own. Also keep in mind that not all types of databases need to be normalized. Most data warehouses use schemas that are not normalized, though their ultimate sources for data often are. That's because DWs are optimized for data reading (queries), not data writing (transactions). It's the big picture view that makes DWs useful, details (like accuracy of a single transaction), aren't important to their function. On the other hand, On Line Transaction Processing (OLTP) databases need to be accurate down to the single transaction (just ask a person that bought a new car ifthat single transaction is important to them).

If these terms and concepts are new to you, spending some time looking into them may not solve your current problem, but will help you understand the underling principles involved. BRN..

|||Hie Brian,
I do have the basic knowledge of RDBMS. My team have normalized thedatabase and denormalized it for performance purpose. The accuracy ofeach transaction is important and the performance of the systemis equally critical as this is a hospital information system we aredeveloping. I have tested again the database and the data seemed todissapear when my team all login to the database and performtransaction. To be frank, i am the one in charge of the SQL Server, yeti don't have the knowledge to test and fine tune the database.
I have looked into the SQL Profiler and tried to use it. But really, iam quite incompetent in this. And my team also don't have a workingknowledge in this. We are hoping anyone, anyone at all , to point us inthe right direction to test our data integrity or do a tracing or dosomething to isolate the problem.
And hopefully, a step by step tutorial if possible.
Thanks in advance.
|||I assume this is happening on only certain tables? Are theretriggers on those tables? Have you carefully studied them to makesure they're doing the right thing (if they're there)?
Is all data access being done via stored procedures, or areapplications running ad hoc SQL? If the former, you can start byfiguring out which stored procedures access the table(s) that you'reseeing data loss on:
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%YourTableName%'
Now start looking at those stored procedures. Which deletedata? Where and when are they being called? You can useProfiler to figure that out. Try the following article for an intro:
http://www.developer.com/db/article.php/3482216
If applications are passing in ad hoc SQL, you'll have a bit roughertime -- you'll have to be even more reliant on Profiler. Filteron the TextData column, LIKE '%YourTableName%' and you'll start seeingall of the batches that apply to the table in question.
Eventually you should be able to isolate and eliminate the culprit. Good luck!
|||

Data cannot just disappear in RDBMS you could be violating ANSI SQL Cascade delete no action and Cascade Update no action. That means nobody is allowed to update or delete from your tables including you.Try these sample create table statements.

The definition of the Patient table looks something like this

CREATE TABLE [dbo] . [Patient] (
[PatientID] [int] NOT NULL,
[FirstName] [nvachar] (20) NULL,
[MiddleInitial] [nvachar] (1) NULL,
[Lastname] [nvachar] (35) NULL,
[Birthdate] [DateTime] NULL,
[SSN] [nvachar] (11) NULL,
--Lots of other Patient information would appear here
[MedicaidNum] [nvachar] (14) NuLL,
[MedicareNum] [nvachar] (12) NULL,
[AdmitDate] [Datetime] NULL,
[AdmissionWeight] [int] NULL,
[FacilityNumber] [int],
[NursingStation] [nvachar] (8) NULL,
[Bed] [nvachar] (10) NULL,
[MedicalRecordNumber] [nvachar] (20) NULL,
)

The definition of the Facility table looks something like this

CREATE TABLE [dbo] . [Facility] (
[FacilityNumber] [int] NOT NULL,
[FacilityName] [nvachar] (50) NULL,
[AddressLine1] [nvachar] (30) NULL,
[AddressLine2] [nvachar] (30) NULL,
[City] [Nvachar] (50) NULL,
[State] [nvachar] (2) NULL,
[Zip] [nvachar] (10) NULL,
[Phone] [nvachar] (20) NULL,
[Fax] [nvachar] (20) NULL,
--Lots of other facility information would appear here
)

The PatientWieghtHistory table looks like this

CREATE TABLE [dbo] . [PatientWieghtHistory] (
[PatientID] [int] NOT NULL,
[MonthAndYear] [DateTime] NOT NULL,
[Week1Weight] [int] NULL,
[Week2Weight] [int] NULL,
[Week3Weight] [int] NULL,
[Week4weight] [int] NULL,
[Week5Weight] [int] NULL,
)

The books below are SQL Server Performance tuning book and a small but complex database design book with sample Catalogs you can use. The Microsoft link is for you to download the eighty plus tables database Microsoft Created from the second book. If you have many databases DBCC REINDEX if not look into DBCC ShowContig. Hope this helps.

Microsoft SQL Server 2000 Performance Optimization and Tuning Handbook Ken England

http://wings.buffalo.edu/mgmt/courses/mgtsand/data.html

http://www.microsoft.com/downloads/details.aspx?FamilyID=487C9C23-2356-436E-94A8-2BFB66F0ABDC&displaylang=en

|||

Hi,

Hope things start going better for your team soon. Sounds like you got elected to tackle a project without having all the tools at hand to feel confident about getting it done. I guess you know you're not alone in that! A number of things have come to mind, after reading your messages. I don't know if any will apply, but I hope some might be helpful.

First, more detail about what data is going missing, and how do you know it's missing? Is this real data, or only test data for development purposes? If it's real, there are ways you might be able to recover it. When you examine the tables in the DB with enterprise manager, are the tables empty? Are whole rows missing? Or, is it just some values in some rows? I think we're all assuming you mean that data is actually gone from the tables in the DB, not just that it doesn't show up in the user interface that you are using - like an ASP.NET application - am I correct in that? If it's only that the data doesn't show - then we are all off on the wrong tangent.

Did I, or anyone, ask if you had looked at the log file (dbname.ldf), for the database? The fact that MS SQL always creates a log file of each transaction, both explicit and implicit, can be very helpful. Is the log file intact? If the log file is in good order, you might be able to restore your data from that (if it's real data that matters to you). It also can be a very good way to see where and when the data was lost. BTW, just in case no one ever mentioned it, you take ahuge performance hit if the .ldf and .mdf files are on the same physical drive, and a lesser hit if they are on different drives but sharing the same controller, in some cases. So, having the two files on separate drives is very helpful.

I'm glad that you started with a normalized DB, and only then de-normalized it for performance. By going that root, you greatly reduced the likelihood of data loss anomalies. Still, I would only do any de-normalization when the performance increase is significant, you can't get to the performance level you need by other means (like query optimization), the performance iscritical and the data integrity issues are nailed down tight. -- I value data integrity that highly.

I don't know where in the development cycle you are, but if it's possible and practical in your case, look at maybe using two databases. One DB is for the transactions, and the second is for analysis. The first is optimized for writing in data (and preserving data integrity), and is known as a OLTP DB. The second is populated with data from the first, but not in real time. Also the schema is optimized for reading data (queries), and is not used to directly update data in the OLTP DB. By splitting the DB functions this way, you may get all the things you are after - high performance for your analysis queries, and high speed and data integrity for your transaction database.

I don't know where you are located, but the MS people I've dealt with in New England have always shown an interest in helping their SQL Server customers. Maybe there's a local rep that offer you some resources. There are SQL Server users groups (we used to have a very good one here in CT), and you can find those by searching msdn.microsoft.com, or microsoft.com.

Remember, if you get overwhelmed, you can a consultant to work with you. BRN..

|||

Hi,

Hope things start going better for your team soon. Sounds like you got elected to tackle a project without having all the tools at hand to feel confident about getting it done. I guess you know you're not alone in that! A number of things have come to mind, after reading your messages. I don't know if any will apply, but I hope some might be helpful.

First, more detail about what data is going missing, and how do you know it's missing? Is this real data, or only test data for development purposes? If it's real, there are ways you might be able to recover it. When you examine the tables in the DB with enterprise manager, are the tables empty? Are whole rows missing? Or, is it just some values in some rows? I think we're all assuming you mean that data is actually gone from the tables in the DB, not just that it doesn't show up in the user interface that you are using - like an ASP.NET application - am I correct in that? If it's only that the data doesn't show - then we are all off on the wrong tangent.

Did I, or anyone, ask if you had looked at the log file (dbname.ldf), for the database? The fact that MS SQL always creates a log file of each transaction, both explicit and implicit, can be very helpful. Is the log file intact? If the log file is in good order, you might be able to restore your data from that (if it's real data that matters to you). It also can be a very good way to see where and when the data was lost. BTW, just in case no one ever mentioned it, you take ahuge performance hit if the .ldf and .mdf files are on the same physical drive, and a lesser hit if they are on different drives but sharing the same controller, in some cases. So, having the two files on separate drives is very helpful.

I'm glad that you started with a normalized DB, and only then de-normalized it for performance. By going that root, you greatly reduced the likelihood of data loss anomalies. Still, I would only do any de-normalization when the performance increase is significant, you can't get to the performance level you need by other means (like query optimization), the performance iscritical and the data integrity issues are nailed down tight. -- I value data integrity that highly.

I don't know where in the development cycle you are, but if it's possible and practical in your case, look at maybe using two databases. One DB is for the transactions, and the second is for analysis. The first is optimized for writing in data (and preserving data integrity), and is known as a OLTP DB. The second is populated with data from the first, but not in real time. Also the schema is optimized for reading data (queries), and is not used to directly update data in the OLTP DB. By splitting the DB functions this way, you may get all the things you are after - high performance for your analysis queries, and high speed and data integrity for your transaction database.

I don't know where you are located, but the MS people I've dealt with in New England have always shown an interest in helping their SQL Server customers. Maybe there's a local rep that offer you some resources. There are SQL Server users groups (we used to have a very good one here in CT), and you can find those by searching msdn.microsoft.com, or microsoft.com.

Remember, if you get overwhelmed, you can a consultant to work with you. BRN..

|||Hie Adam, Caddre, & Brian,
Well, the data does dissapear, but it's only our test data. Mycompany plan to deploy to production server in June. So time is limitedto find out what happen. They do hire a so-called "consultant" whichdono nill about SQL. AND greatly support mySQL. Not that i amcomparing the 2 database provider, but my company already bought theSQL Server, wat's the point of discussing other options, right?
Back to the topic, I know we don't have trigger because i am the 1creating everything inside using Enterprise Manager. The initialdatabase structure is based on the Microsoft sample such as theAdventureWork, MS PetShop, Northwind, Pubs,etc. Every sample we canget, i try to get it all already.
Next, we also haven't venture into Stored Procedure, which we mightconsider, once the whole thing is deploy 1st, because time is toolimited for us to change. So everything is ad hoc SQL.
I found out the data disappearing because it's in the master indextable. Like in the Patient, Employee, etc. table. The 1st time wenotice because we can't login to system. The user's employee details isgone from the database. And i mean the whole row is deleted. But otheremployee details is still there. We are not sure whether transactiontable also face the same problem or not.
I have tried the SQL Profiler, and slowly getting a hang on it. Butjuggling between this and software programming is abit too much for mealready. (sori, enuff of my complaining) After running the ProfilerStandard Trace Template, found out the system is slowed down because iam backuping the database. I have created a maintanence plan to backupall database in middle of afternoon everyday. (which i think i shldn'tbe doing in real production server) This is to prepare us forbackuping our production database later on.
I not sure about MS representative here because we bought it from somevendor that is not very helpful. Thanks for the tip on putting the twofile in different drive. I was never aware of that.
Actually we haven't really gone into OLTP yet. All the select querieswe have are to assist in the process of the transaction. Like fordisplaying the customer info, displaying the customer's bill, etc.
Then there is the completion of transaction which we have to update afew tables then insert into another tables. I not sure where to openthe LDF file to check which transaction got problem. So its back tosearching again. I will check out the Profiler again, and try to get myteam to trial run it.
Thanks for the advice, I will keep trying...

|||

Hi,

As you can imagine, it's not easy to understand the nature of the problem you are having from a few messages. While there's no guarantee that it will help, if we had a clear picture of the project we might be able to see where things are going wrong.

If you choose to, and when you feel you have time, you could post details about your project (leaving out any sensitive, or security data). If your company has a website where you could post some files - that you can make public - maybe some of us can look at them and give you an opinion. What would be helpful, would be a lot more detail about your project. Unless we can see what the project is trying to do, and what processes are used to accomplish that goal, it's not likely that anyone can put a finger on one point and say "that's the problem." Give some thought to how best to do this and let us know.

From what you told us, my best guess is that the database is acting the way it's designed to act. If particular rows are being deleted, or changed to the point where they seem to disappear, I'd guess that it's a flaw in the schema or in the stored procedures or queries that you are using to insert, update or delete data. There is a chance that the security of your system has been compromised, but I would think that if that were the case, you'd be have lot's of other problems with your entire system.

For now, could you just pass along how many tables your database has, and how many columns are in each table. Also, approximately how many rows (or records), you estimate will be in the tables that will have the most (when the project goes into production). BRN..

|||After one year plus plus, I have found out the problem, which admittedly was found some time ago. The problem with the dissappearing data was because all the team member have enterprise manager, and all manipulate?the?data sometimes somewhere during the development phase.?As?it?happen,?someone?delete?a?whole?row?of?data?from?a?view?table,?causing?it?to?delete?all?relevent?row?for?referential?integrity. As it was near production, our testing got rougher, and data is being cleared quite often. Hence, the question of the problem getting worse and worse.

Thanks for all the helps and links. It really help to enhance my knowledge to the next level.|||

Hi,
I am glad to see everything is working for you, here is a new tool coming from Microsoft that you can use to run SQL compare before you make updates and insert. It is free for now so just test drive it. Hope this helps.

http://weblogs.asp.net/scottgu/archive/2006/10/18/Visual-Studio-for-Database-Professionals-and-other-Cool-Data-Management-Tools-for-.NET.aspx