Serverwide Rollback

[Wangkhar]

Hi

Having a little issue... is it possible to have multiple connections
all get rolled back to the same point? I have a small database that
seems to have been busy - but magically lost 5 hours of data (several
100k records...)

This is across multiple databases. In one of them I do have a large
identity gap - in the others the 'identity' type columns are self
rolled incrementers.

In a rollback I would normally expect to see _some_ data missing - but
not _everything_ from 20 odd connections all binned for 5 hours!

The only real clue I have is that one monitoring process that counts
the rows in a table (usually 200 - 400) running from SQLAgent took 5
hours to complete! Other than that NOTHING in any server log, nothing
in the sql logs.

Please - any serious suggestions - including any useful monitoring
ideas for future reference happily received!

[John Bell]

Hi

You don't say if this is a single database or you have multiple databases
that have this issue. It is highly
unlikely that multiple databases would have a gap. The only thing I could
think of would be the time/timezone being changed but I thought this was
logged in the event log.

John

<Wa******@yahoo.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

Hi

Having a little issue... is it possible to have multiple connections
all get rolled back to the same point? I have a small database that
seems to have been busy - but magically lost 5 hours of data (several
100k records...)

This is across multiple databases. In one of them I do have a large
identity gap - in the others the 'identity' type columns are self
rolled incrementers.

In a rollback I would normally expect to see _some_ data missing - but
not _everything_ from 20 odd connections all binned for 5 hours!

The only real clue I have is that one monitoring process that counts
the rows in a table (usually 200 - 400) running from SQLAgent took 5
hours to complete! Other than that NOTHING in any server log, nothing
in the sql logs.

Please - any serious suggestions - including any useful monitoring
ideas for future reference happily received!

[Greg D. Moore \(Strider\)]

"John Bell" <jb************@hotmail.com> wrote in message
news:42**********************@news.zen.co.uk...

Hi

You don't say if this is a single database or you have multiple databases
that have this issue. It is highly
unlikely that multiple databases would have a gap. The only thing I could
think of would be the time/timezone being changed but I thought this was
logged in the event log.
Actually was my other thought was if say the server lost its network
connectivity completely, and treated all its connections as "broken" and as
a result rolled back all of them.


John

<Wa******@yahoo.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

Hi

Having a little issue... is it possible to have multiple connections
all get rolled back to the same point? I have a small database that
seems to have been busy - but magically lost 5 hours of data (several
100k records...)

This is across multiple databases. In one of them I do have a large
identity gap - in the others the 'identity' type columns are self
rolled incrementers.

In a rollback I would normally expect to see _some_ data missing - but
not _everything_ from 20 odd connections all binned for 5 hours!

The only real clue I have is that one monitoring process that counts
the rows in a table (usually 200 - 400) running from SQLAgent took 5
hours to complete! Other than that NOTHING in any server log, nothing
in the sql logs.

Please - any serious suggestions - including any useful monitoring
ideas for future reference happily received!

[John Bell]

I would hope the transacations would be short than that!!! But may be there
is no error checking (or even worse errors being deliberately ignored) in
the client?

John

"Greg D. Moore (Strider)" <mo****************@greenms.com> wrote in message
news:ff*******************@twister.nyroc.rr.com...

"John Bell" <jb************@hotmail.com> wrote in message
news:42**********************@news.zen.co.uk...

Hi

You don't say if this is a single database or you have multiple databases
that have this issue. It is highly
unlikely that multiple databases would have a gap. The only thing I could
think of would be the time/timezone being changed but I thought this was
logged in the event log.

Actually was my other thought was if say the server lost its network
connectivity completely, and treated all its connections as "broken" and
as
a result rolled back all of them.

John

<Wa******@yahoo.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

> Hi
>
> Having a little issue... is it possible to have multiple connections
> all get rolled back to the same point? I have a small database that
> seems to have been busy - but magically lost 5 hours of data (several
> 100k records...)
>
> This is across multiple databases. In one of them I do have a large
> identity gap - in the others the 'identity' type columns are self
> rolled incrementers.
>
> In a rollback I would normally expect to see _some_ data missing - but
> not _everything_ from 20 odd connections all binned for 5 hours!
>
> The only real clue I have is that one monitoring process that counts
> the rows in a table (usually 200 - 400) running from SQLAgent took 5
> hours to complete! Other than that NOTHING in any server log, nothing
> in the sql logs.
>
> Please - any serious suggestions - including any useful monitoring
> ideas for future reference happily received!
>

[Erland Sommarskog]

(Wa******@yahoo.com) writes:

Having a little issue... is it possible to have multiple connections
all get rolled back to the same point? I have a small database that
seems to have been busy - but magically lost 5 hours of data (several
100k records...)
....
In a rollback I would normally expect to see _some_ data missing - but
not _everything_ from 20 odd connections all binned for 5 hours!

The only real clue I have is that one monitoring process that counts
the rows in a table (usually 200 - 400) running from SQLAgent took 5
hours to complete! Other than that NOTHING in any server log, nothing
in the sql logs.

If the connections never commit, and you then pull the plug - there
will indeed be a huge rollback. And the fact that your SELECT COUNT(*)
was stalled for five hours indicates that there was some serious
blocking.

This could have happened:

There was some initial blocking situation, which cause connections
to timeout. The timeout was not properly handled - that is transactions
started were not rolled back. The applications then continued and
were indeed able to insert both this and that. However, all were in
an open transaction, and then when something was closed down after
five hours, there was a rollback all over town.


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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

[Wangkhar]

Thanks all for the feedback, its a rather sticky problem ...
Yes, the error handling is chaotic (I have been encouraging better
handling but people can't be bothered (or as you say have the last line
of a proc returning 0 explicitly!!) - and I don't have the power to
enforce it! - just pick up the pieces)

This sounds pretty much the closest explanation I to what seems to have
happened, guess I shall have to lean of the dev and management to get
it bulletproofed.

Cheers all.

Erland Sommarskog wrote:

(Wa******@yahoo.com) writes:

Having a little issue... is it possible to have multiple connections all get rolled back to the same point? I have a small database that seems to have been busy - but magically lost 5 hours of data (several 100k records...)
....
In a rollback I would normally expect to see _some_ data missing - but not _everything_ from 20 odd connections all binned for 5 hours!

The only real clue I have is that one monitoring process that counts the rows in a table (usually 200 - 400) running from SQLAgent took 5 hours to complete! Other than that NOTHING in any server log, nothing in the sql logs.
If the connections never commit, and you then pull the plug - there
will indeed be a huge rollback. And the fact that your SELECT

COUNT(*) was stalled for five hours indicates that there was some serious
blocking.

This could have happened:

There was some initial blocking situation, which cause connections
to timeout. The timeout was not properly handled - that is transactions started were not rolled back. The applications then continued and
were indeed able to insert both this and that. However, all were in
an open transaction, and then when something was closed down after
five hours, there was a rollback all over town.


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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

[Wangkhar]

Ok gents, thanks for the help here.
It happenend again this morning, but fortunatly they didnt kill it
before I got there.

Looks like the way we serialise our transactions caused all the other
connections to block up against one (leaving about 40 connections
blocked!) which had 3 open transactions (for about 5 hours!). By
altering a procedure to have a commit loop and calling this from the
still active website (which was routing all traffic to this functional
connection) we committed all the open transactions and recovered with
minimal data loss - rather than last time where I gather they restarted
the sql service resulting in the rollback of 5 hours worth! This of
course unblocked everything else. Now they are taking the error
handling seriously, looks like I'll be busy today rewriting other
peoples code!

Ta.