BZ,
Quote:> Experts, correct me if I am wrong but, transactions won't help if the
> power goes out?
One of the most fundamental tenets of enterprise level RDBMS's is that they
must conform to the ACID properties (defined in section "Transactions" of
the SQL Server Books Online. The ACID properties essentially define the
characteristics of a transaction.
If the server crashes for any reason, all enterprise level RDBMS's will
rollback uncommitted transactions when the database is restarted.
I think you might be referring more to the following (from "Inside Microsoft
SQL Server 2000" by Kalen Delaney, and quoted by permission of the author):
"Your disk controller must guarantee that any write operation reported to
the operating system as successful will actually be completed. You should
never use a write-back caching controller that can "lie" (report a write as
completed without guaranteeing that it will actually perform the write)
because your databases can become corrupted. In a write-back cache scheme,
performance is increased because the bits are simply written to the cache
(memory) and the I/O completion is immediately acknowledged. The controller
writes the bits to the actual media a moment later. This introduces a timing
window that makes the system vulnerable unless the controller designer has
protected the cache with a battery backup and has provided correct recovery
logic.
Let's say, for example, that a power failure occurs immediately after the
controller reports that a write operation has completed but before the cache
is actually written to disk. With write-ahead logging, SQL Server assumes
that any change is physically written to its transaction log before it
acknowledges the commit to the client. If the controller has just cached the
write and then fails and never completes it, the system's integrity is
broken. If a controller provides write-back caching as a feature, it must
also guarantee that the writes will be completed and that they will be
properly sequenced if they are reported to the operating system as
successful. To be reliable, the caching controller also must guarantee that
once it reports that an I/O has completed, the I/O will actually be carried
out, no matter what. Such a controller typically employs a built-in battery
backup and has solutions that guarantee that any write reported as completed
will in fact be completed. If the system fails before a write operation has
been completed, the operation is maintained in the controller's memory and
is performed immediately after the system restarts, before any other I/O
operations occur.
If a controller cannot guarantee that the write will ultimately be
completed, you should disable the write-back caching feature of the
controller or use a different controller. Disabling write-back caching in an
I/O-intensive environment (more than 250 I/Os per second) might result in a
SQL Server performance penalty of less than 5 percent. (After all, those
writes must be performed eventually. The caching reduces the latency, but
ultimately just as much I/O must be carried out.) In a less I/O-intensive
test, the effect would likely be negligible. Realistically, the amount of
the penalty doesn't matter much if the write isn't guaranteed to complete.
Write-back caching must be disabled, or you run the risk of corrupting your
data."
-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.
> Matt,
> > I am currently trying to avoid damage to my db during failure conditions
> If your failure conditions include power outages, look into
> battery-packed disk cache to maintain your data consistent.
> Experts, correct me if I am wrong but, transactions won't help if the
> power goes out?
> Hope that helps,
> BZ
> > Hi
> > I am currently trying to avoid damage to my db during failure conditions
> > I have a a simple update caluse as follows
> > update jongel
> > where the variables are supplied by the GUI frontend to a SP, now how
can I
> > avoid table corruption if there is a power outage during the update?
> > I tried with begin tran and commit tran but what if there is a series of
> > updates and if any one of them fail I want the others undone, how can I
> > handle that, like follows
> > /* start of a series of updates */
> > update jongel
> > update jongel2
> > /* end of the series of updates */
> > is it possible that if the second update fails to undo all updates ??
by