Data changes made late at night "dissapear"

[Nick Stansbury]

Hi,
Sorry for the obscure title but I'm afraid I can't think of a better
way to describe what happened to one of my clerks last night. The guy was
working late, made a series of changes (accross a number of tables with a
dependant relationship structure - i.e. a customer linked to an order linked
to an invoice linked to a payments etc.) Now when he came back this morning
none of the work he did last night was still there. I'm by no means a
sql-pro - but I've managed to make do so far. Here's what I know:

1) All of our records in all of our tables have date/time stamped. Once when
they go in and once when they're changed. So with a little work I can build
up a good picture of what usage our database gets at what times. I've pulled
up a query and there is a big fat hole between 8pm-10pm - stuff that
happened before that is still there, stuff that happened after that is still
there - but all the changes that were put in between then just aren't there
any more. So it's not just insertions but updates as well.

2) There is no perceivable break in our identity columns. So despite the
fact that I know he entered 7 new customers, I can go back through the
customers table and look at where those customers should sit visa vie entry
times - and it's just as though the customers he entered never existed -
sql server just keeps incrementing sequentially and there's no break - so
the last customer entered at about 6:50 pm was something like number 11912
and the customers entered this morning have numbers that carry on from
11913.

3) I'm running in a shared environment - the server is hosted by a
webhosting company (who shall remain nameless unless I can prove it's their
fault!) based in the US - the server has 100's of other Users databases
running on it.

I just don't know where to begin diagnosing this sort of problem. But it has
me really running scared. It's not the first time something like this has
happened to me (i've noticed it once before when I had to get a connection
killed by the people who manage our server because of a long-running query
that seemed to have just got bunged up and was locking a key table) - but
that was just a few records changed by the user who's connection got killed.
Nothing like this... but it's pretty scary - I've got no way of knowing that
I'm losing changes like this on a continuous basis. It's my worst
nightmare - like a pipe leaking underneath a floor board - and you only find
out when the water starts running down the stairs!

Has anyone got any ideas? Any starting places? Anyone else had anything like
this happen to them before?

Thanks

Nick

[Greg D. Moore \(Strider\)]

"Nick Stansbury" <ni************@sage-removepartners.com> wrote in message
news:df**********@pop-news.nl.colt.net...

Has anyone got any ideas? Any starting places? Anyone else had anything

like this happen to them before?

Yes. Sounds like the database was restored overnight to its 8:00 PM state.

Thanks

Nick

[Erland Sommarskog]

Nick Stansbury (ni************@sage-removepartners.com) writes:

Sorry for the obscure title but I'm afraid I can't think of a > better way to describe what happened to one of my clerks last night. The guy was working late, made a series of changes (accross a number of
tables with a dependant relationship structure - i.e. a customer linked
to an order linked to an invoice linked to a payments etc.) Now when he
came back this morning none of the work he did last night was still
there. I'm by no means a sql-pro - but I've managed to make do so far.
Here's what I know:
So from this information there are two possibilities:

1) A backup was restored. (Or some variation thereof)
2) Due to application error, the transaction was never committed, and
when the user disconnected, it was rolled back.

However, here is an important piece of information:
2) There is no perceivable break in our identity columns. So despite the
fact that I know he entered 7 new customers, I can go back through the
customers table and look at where those customers should sit visa vie
entry times - and it's just as though the customers he entered never
existed - sql server just keeps incrementing sequentially and there's no
break - so the last customer entered at about 6:50 pm was something like
number 11912 and the customers entered this morning have numbers that
carry on from 11913.
Assuming that this columns really have the IDENTITY property, then this
rules out the second possibility. IDENTITY numbers are not affected by
transaction scope. Had the transaction never been committed, you would
have seen a gap.
3) I'm running in a shared environment - the server is hosted by a
webhosting company (who shall remain nameless unless I can prove it's
their fault!)

They are indeed highly suspect. It appears that they restored the database
to the state of 18:50 or whereabouts. If you have access to msdb, you
can check the table restorehistory. (There is a second possibility that
will not show up in this table. Say that they detached the database,
copied it to another server, reattached it, and not until later repointed
the server, causing your clerk to work in a dead end.)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Nick Stansbury]

Dear Erland,

Thanks for your help. Setting aside the issue over identity column
incrementation (although I'm going to beat them up to try and get them to
admit to doing something funny - a restore / detached database) - is there
anyway this could be caused by implicit transactions being turned on?

I don't know anything at all about implicit transactions - but it seems
logical that they could mean that if a connection crashed then none of the
data entered in that connection would be entered? Is there anyway to check
this possibility (i.e. prove that it had nothing to do with transactions?)

Nick

1) A backup was restored. (Or some variation thereof)
2) Due to application error, the transaction was never committed, and
when the user disconnected, it was rolled back.

However, here is an important piece of information:

2) There is no perceivable break in our identity columns. So despite the
fact that I know he entered 7 new customers, I can go back through the
customers table and look at where those customers should sit visa vie
entry times - and it's just as though the customers he entered never
existed - sql server just keeps incrementing sequentially and there's no
break - so the last customer entered at about 6:50 pm was something like
number 11912 and the customers entered this morning have numbers that
carry on from 11913.

Assuming that this columns really have the IDENTITY property, then this
rules out the second possibility. IDENTITY numbers are not affected by
transaction scope. Had the transaction never been committed, you would
have seen a gap.

3) I'm running in a shared environment - the server is hosted by a
webhosting company (who shall remain nameless unless I can prove it's
their fault!)

They are indeed highly suspect. It appears that they restored the database
to the state of 18:50 or whereabouts. If you have access to msdb, you
can check the table restorehistory. (There is a second possibility that
will not show up in this table. Say that they detached the database,
copied it to another server, reattached it, and not until later repointed
the server, causing your clerk to work in a dead end.)

[Erland Sommarskog]

Nick Stansbury (ni************@sage-removepartners.com) writes:

Thanks for your help. Setting aside the issue over identity column
incrementation (although I'm going to beat them up to try and get them to
admit to doing something funny - a restore / detached database) - is there
anyway this could be caused by implicit transactions being turned on?

First I like to stress, that since you can see that the IDENTITY column
does not have a gap, we can rule out this possibility.

But we assume that we did not have this fact, if someone had enabled
IMPLICIT_TRANSACTIONS, and the application does cater for this, you
would get this effect. Most of the ANSI settings has a database setting,
but IMPLICIT TRANSACTIONS has not - at least not one that is documented.
It is possible configure this on server level, though.

There is a more common scenario that can have the same effect. The
application calls a stored procedure that starts a transaction and
then gets blocked. The application times out after 30 seconds - the
default timeout in several client APIs. The timeout does not roll
back the transaction automatically, and the application code ignores
to do it. The effect is that the user continues to work, and everything
looks fine. But when the user disconnects, all data entry is lost.

But all that is all theoretic. If you have a real IDENTITY column in
that table, this did not happen, because then we would have seen gaps,
as the IDENTITY numbers taken are not rolled back.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[John Bell]

Hi Nick

You may have information in the Web logs that shows that your colleague
carried out this work so make sure they are preserved.

If you can view the SQL Agent scheduled jobs then you might be able to see
what is carried out on your database.

John
"Nick Stansbury" <ni************@sage-removepartners.com> wrote in message
news:df**********@pop-news.nl.colt.net...

Dear Erland,

Thanks for your help. Setting aside the issue over identity column
incrementation (although I'm going to beat them up to try and get them to
admit to doing something funny - a restore / detached database) - is there
anyway this could be caused by implicit transactions being turned on?

I don't know anything at all about implicit transactions - but it seems
logical that they could mean that if a connection crashed then none of the
data entered in that connection would be entered? Is there anyway to check
this possibility (i.e. prove that it had nothing to do with transactions?)

Nick

1) A backup was restored. (Or some variation thereof)
2) Due to application error, the transaction was never committed, and
when the user disconnected, it was rolled back.

However, here is an important piece of information:

> 2) There is no perceivable break in our identity columns. So despite
> the
> fact that I know he entered 7 new customers, I can go back through the
> customers table and look at where those customers should sit visa vie
> entry times - and it's just as though the customers he entered never
> existed - sql server just keeps incrementing sequentially and there's
> no
> break - so the last customer entered at about 6:50 pm was something
> like
> number 11912 and the customers entered this morning have numbers that
> carry on from 11913.

Assuming that this columns really have the IDENTITY property, then this
rules out the second possibility. IDENTITY numbers are not affected by
transaction scope. Had the transaction never been committed, you would
have seen a gap.

> 3) I'm running in a shared environment - the server is hosted by a
> webhosting company (who shall remain nameless unless I can prove it's
> their fault!)

They are indeed highly suspect. It appears that they restored the
database
to the state of 18:50 or whereabouts. If you have access to msdb, you
can check the table restorehistory. (There is a second possibility that
will not show up in this table. Say that they detached the database,
copied it to another server, reattached it, and not until later repointed
the server, causing your clerk to work in a dead end.)