|
|
Posted by Erland Sommarskog on 09/10/05 14:47
Nick Stansbury (nick.stansbury@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, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|