I ran a update quey by mistake

With out any BEGIN Transaction statement , i ran a update query.

I had to update just one record using the "Where field='abc'"

But i happened to miss that. Is there any chance of recovery? can i use the transaction logs to bring back the records before the update?

A quick answer is appreciated.

Sahel

Hi Sahel.

No way to get it back directly using native Sql tools/operations without performing some restore type operations.

Best way may be, if you have a standard backup structure in place, take a log backup immediately, then restore a copy of your database side-by-side with the existing db (using a different db name), and restore logs up to the point in time right before you ran the update statement (see the STOPAT option of the RESTORE LOG statement), then update the existing table with the values that exist in the table(s) from the restored database.

There are some 3rd party applications that you could consider researching and using, although these aren't a supported solution generally speaking, so be sure to clarify/understand that before using them (Lumigent Log Explorer is one for example).

HTH

I need your general opinion on "Lock: Timeout"

Hi
In the process of investigating a poor performing application, I've ran
number of traces on our SQL server. In these traces, I can see that
there're a high number of EventClass 27 - Lock: Timout.
I'd assume that in theory I shouldn't have any lock timeouts, but is
that also the case in the real world?
The trace has run for an hour, and if I look in the data afterwards, I
have 11583 records of eventclass 27 out of a total of approx 1,6 million
records in the trace file.
I know that these figures may not tell a lot since it depends on many
factors, but is it "normal" to see such a high number of timouts?
What are your experience from other situations?
Regards
Steen"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:ex8CB4x0FHA.1564@.tk2msftngp13.phx.gbl...
> Hi
> In the process of investigating a poor performing application, I've ran
> number of traces on our SQL server. In these traces, I can see that
> there're a high number of EventClass 27 - Lock: Timout.
> I'd assume that in theory I shouldn't have any lock timeouts, but is that
> also the case in the real world?
> The trace has run for an hour, and if I look in the data afterwards, I
> have 11583 records of eventclass 27 out of a total of approx 1,6 million
> records in the trace file.
> I know that these figures may not tell a lot since it depends on many
> factors, but is it "normal" to see such a high number of timouts?
> What are your experience from other situations?
>
Unless the application has set a finite lock timeout, locks won't time out
at all. That event should tell you what object the lock is on. But more
importantly look at the Lock: Acquired event for events with a long duration
(it's in milliseconds). This is the surest sign of locking problems.
David|||David Browne wrote:
> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
> news:ex8CB4x0FHA.1564@.tk2msftngp13.phx.gbl...
>> Hi
>> In the process of investigating a poor performing application, I've ran
>> number of traces on our SQL server. In these traces, I can see that
>> there're a high number of EventClass 27 - Lock: Timout.
>> I'd assume that in theory I shouldn't have any lock timeouts, but is that
>> also the case in the real world?
>> The trace has run for an hour, and if I look in the data afterwards, I
>> have 11583 records of eventclass 27 out of a total of approx 1,6 million
>> records in the trace file.
>> I know that these figures may not tell a lot since it depends on many
>> factors, but is it "normal" to see such a high number of timouts?
>> What are your experience from other situations?
> Unless the application has set a finite lock timeout, locks won't time out
> at all. That event should tell you what object the lock is on. But more
> importantly look at the Lock: Acquired event for events with a long duration
> (it's in milliseconds). This is the surest sign of locking problems.
> David
>
I can see that I haven't included the "Lock Acquired" event in my trace,
so I'll have to run a new one with this included.
I've looked further in my current trace data, but there're no Object on
the "Lock: Timeout" event, so that doesn't tell me much.
I'll check it further when I have the new trace data and see if I can
get something out of the lock acquired event.
Regards
Steen|||Steen Persson (DK) wrote:
> I can see that I haven't included the "Lock Acquired" event in my
> trace, so I'll have to run a new one with this included.
> I've looked further in my current trace data, but there're no Object
> on the "Lock: Timeout" event, so that doesn't tell me much.
> I'll check it further when I have the new trace data and see if I can
> get something out of the lock acquired event.
> Regards
> Steen
Lock:Timeout will track internal type timeouts that are not really a
problem. If memory serves, you can use Lock:Timeout, but only worry
about those events that are raised with a Duration > 0. Also look for
Attention events, SQL:BatchCompleted/RPC:Completed events for a high
level analysis of SQL performance. Include SQL:StmtCompleted and
SP:StmtCompleted for more detail.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com