Can SQL Lose Records?

We are running SQL 7 with a front end that links to the tables through ODBC.
In our main table, the user has no way to delete a record through the
interface, though it is possible to delete it by opening the ODBC link.
Users would have no reason to delete a record, but one of our records turned
up missing.

Now, it's possible that a user may have accidentally deleted the record.
But, since users don't have any reason to delete records, and since they
don't access the ODBC links, it seems unlikely (though possible).

I was wondering if anyone had every heard of SQL Server ever "losing" a
record that had previously been saved. I checked the nightly backup from the
night after it was added, and the record was there. So either a user deleted
it, or somehow it got lost in SQL Server. I have no code that deletes
records in this table in any way, shape or form, so it couldn't have been
malfunctioning code.

So, while I have a hard time believing that SQL Server would just "lose" a
record, I also know that anything's possible, so I thought I'd ask if anyone
had ever heard of such a thing.

Thanks!

Neil

Re: Can SQL Lose Records?

On Jun 20, 2:53 pm, "Neil" wrote:
> We are running SQL 7 with a front end that links to the tables through ODBC.
> In our main table, the user has no way to delete a record through the
> interface, though it is possible to delete it by opening the ODBC link.
> Users would have no reason to delete a record, but one of our records turned
> up missing.
>
> Now, it's possible that a user may have accidentally deleted the record.
> But, since users don't have any reason to delete records, and since they
> don't access the ODBC links, it seems unlikely (though possible).
>
> I was wondering if anyone had every heard of SQL Server ever "losing" a
> record that had previously been saved. I checked the nightly backup from the
> night after it was added, and the record was there. So either a user deleted
> it, or somehow it got lost in SQL Server. I have no code that deletes
> records in this table in any way, shape or form, so it couldn't have been
> malfunctioning code.
>
> So, while I have a hard time believing that SQL Server would just "lose" a
> record, I also know that anything's possible, so I thought I'd ask if anyone
> had ever heard of such a thing.
>
> Thanks!
>
> Neil

Never heard of it. Revoke delete permissions from all your users. Add
a trigger prohibiting any deletes.

Re: Can SQL Lose Records?

I've never seen or heard of a row going missing either, and I spent
plenty of time using 7.0.

Along with what Alex suggested I would suggest doing a complete set of
DBCC integrity checks on the database.

Roy Harvey
Beacon Falls, CT

On Wed, 20 Jun 2007 19:53:32 GMT, "Neil" wrote:

>We are running SQL 7 with a front end that links to the tables through ODBC.
>In our main table, the user has no way to delete a record through the
>interface, though it is possible to delete it by opening the ODBC link.
>Users would have no reason to delete a record, but one of our records turned
>up missing.
>
>Now, it's possible that a user may have accidentally deleted the record.
>But, since users don't have any reason to delete records, and since they
>don't access the ODBC links, it seems unlikely (though possible).
>
>I was wondering if anyone had every heard of SQL Server ever "losing" a
>record that had previously been saved. I checked the nightly backup from the
>night after it was added, and the record was there. So either a user deleted
>it, or somehow it got lost in SQL Server. I have no code that deletes
>records in this table in any way, shape or form, so it couldn't have been
>malfunctioning code.
>
>So, while I have a hard time believing that SQL Server would just "lose" a
>record, I also know that anything's possible, so I thought I'd ask if anyone
>had ever heard of such a thing.
>
>Thanks!
>
>Neil

Re: Can SQL Lose Records?

Neil (nospam@nospam.net) writes:
> So, while I have a hard time believing that SQL Server would just "lose"
> a record, I also know that anything's possible, so I thought I'd ask if
> anyone had ever heard of such a thing.

Well, I have lost rows, but that was a on a system where no one was looking
at the event log or the DBCC logs, and finally the database broke down,
with several levels of corruption.

As Roy said, run DBCC. If it comes up with corruption, then that may be
the answer.

But I'm prepared to place my bets that there was a human involved.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Can SQL Lose Records?

> Never heard of it. Revoke delete permissions from all your users. Add
> a trigger prohibiting any deletes.
>

If I add a trigger prohibiting any deletes, then it wouldn't be possible for
me to go in and delete a record if I ever needed to, right? Or is there a
way to set up a trigger so that it can allow the delete in some cases?

Thanks.

Re: Can SQL Lose Records?

I'm not familiar with DBCC. Can you point me in the right direction?

Thanks.

"Erland Sommarskog" wrote in message
news:Xns9955ED0DF77BAYazorman@127.0.0.1...
> Neil (nospam@nospam.net) writes:
>> So, while I have a hard time believing that SQL Server would just "lose"
>> a record, I also know that anything's possible, so I thought I'd ask if
>> anyone had ever heard of such a thing.
>
> Well, I have lost rows, but that was a on a system where no one was
> looking
> at the event log or the DBCC logs, and finally the database broke down,
> with several levels of corruption.
>
> As Roy said, run DBCC. If it comes up with corruption, then that may be
> the answer.
>
> But I'm prepared to place my bets that there was a human involved.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Can SQL Lose Records?

On Jun 20, 5:01 pm, "Neil" wrote:
> > Never heard of it. Revoke delete permissions from all your users. Add
> > a trigger prohibiting any deletes.
>
> If I add a trigger prohibiting any deletes, then it wouldn't be possible for
> me to go in and delete a record if I ever needed to, right? Or is there a
> way to set up a trigger so that it can allow the delete in some cases?
>
> Thanks.

You can disable the trigger for the duration of your delete.
Alternatively you can have you trigger allow you to do whatever you
want, based on user_id() or suser_id(). Trigger can be bypassed using
nested triggers and or recursive trigger setting. There are other ways
best described in T-SQL Programming by Itzik Ben-Gan.

http://sqlserver-tips.blogspot.com/

Re: Can SQL Lose Records?

Neil (nospam@nospam.net) writes:
> I'm not familiar with DBCC. Can you point me in the right direction?

There are several DBCC commands, but the one of interest here is DBCC
CHECKDB which checks the database for consistency errors. If the database is
of any size, run it off-hours.

You should regularly run DBCC on your database, for instance as part of a
maintenance job, and make sure that you get alerted if it finds any errors.

If memory serves, you just say "DBCC CHECKDB" in the database you want to
examine. But check Books Online for details.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Can SQL Lose Records?

Hello,

You can think of SQL Profiler as well if still you doubt on the SQL
Activities for some particular time.

Thanks
Ajay