SQL logs

[autumn]

g day all

I seem to have a few problems at the moment, of course it always happens that way.

I have a SQL db that is losing information. I'm not SQL admin however I am.

The software company which db it is, isn't any help as they don't have SQL admin's and they aren't hosting the db so don't care (well as software companies do).

the db is for student and course info. basically the student gets enrolled in a course and the company can put notes attached to their course or to the student. Now it's not all users having the issue that we know of and the users which are having issues log in to a Remote Desktop server and then access another server which the sql on it. So what happens is they'll put a note in then later on and then (could be an hour, day, week) they go back in and the note isn't there. It's not a single user and sometime they go in and check and it's there and then go in again later and it's gone. The program has an report which shows deleted notes and they aren't shown up in the report.

is there logs that will show connections activity/losses and more so from one computer (the Remote Desktop Server) and/or errors in the db.

Thanks

Tim

 

[BlackLabTechs]

Do you have access to the sql db itself? If so, look at the table for the notes and see if the records are there and possibly a field for deleted. It could be a case of those records that are "missing" are really in the db but the deleted flag has been set to 1 or true. A good db design will rarely ever truly delete a record. It just gets flagged deleted so history is still searchable.

If this is the case, I would try and track down any audit logs within the db or look at any sql agent jobs marking records over 'x' days deleted.

While you are in there (if you can), there should be an auto increment field named noteid or something close. if there are any gaps in the numbering sequence, then records are truly getting deleted.

 

[YeOldeStonecat]

The software company which db it is, isn't any help as they don't have SQL admin's and they aren't hosting the db so don't care (well as software companies do).

In my experience, the software company should be assisting you with this...their software plugs into SQL...it should be their job to work with you to fix it. I've always had them work with me to resolve issues.

 

[autumn]

Yep agree YeOldeStonecat, so far I have only talked to first level support and they have been reluctant to send to second level without SQL logs, however they haven't been able to help get them. They haven't made a support ticket either.

 

[brandonkick]

I don't think this is a SQL level issue, especially since only some records are having issues.

I would look at more of a glitch in the software. Often times applications are poorly coded to
not have sql queries wrapped in a transaction. If the connection is interrupted for any reason,
and the query is poorly executed, you can wind up with orphaned data. How a user connects
to use this application should have little difference to how the application itself talks to the DB.

You can use SQL profiler to take a look at queries as they come into the DB. Have someone execute
some of these inserts through their RDP window and then capture the query. Then have someone
go in and enter a note under a normal circumstance and capture that query. They should be identical
and if they are then it's not an issue of where the person is when they are doing this action. It's an issue
with the application sending and executing queries on the DB.

If you had DB access, I'd grab some ID's or GUID's for those records that have "missing notes" and do
a bit of detective work. Check to see if the records really are there, or if they just aren't being returned
due to something like a delete flag. As stated, it's bad practice to actually delete anything in most cases.
However, if the delete flag is in effect, and is set.... they still won't see the data. It will see be "deleted" as
far as the customer is concerned.

The one thing that sounds a bit strange is that the report that shows "deleted" records apparently agrees that
these items really are gone in that it doesn't show them.

 

[glennd]

Unless there's a regular purge going on, it seems like that data never makes it to the db. I think you need to be firmer with the software company to get them looking at this 'bug' in their software.