Database files and power failures

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi.
XPE installed on CF and SQL Express (or MSDE) installed. How minimize
database files corruption due to a power failure?
 
Davide said:
Hi.
XPE installed on CF and SQL Express (or MSDE) installed. How minimize
database files corruption due to a power failure?
Click to expand...

I couldn't do it. I ended up going to storing my data in several
different files. Backups are written 30 seconds later. Buffers are
flushed. Even with this, very occasionally both primary and backup will
be corrupted. My software has to cope with this automatically so worst
case is recent data is lost.
 
I couldn't do it. I ended up going to storing my data in several
different files. Backups are written 30 seconds later. Buffers are
flushed. Even with this, very occasionally both primary and backup will
be corrupted. My software has to cope with this automatically so worst
case is recent data is lost.
Click to expand...

Flushing buffers may not be enough: by default the buffers are flushed to
the OS, but the OS may still cache them for later writing.


There are three ways to ensure that flushing really goes all the way to
disk:
- using the _commit() low-level IO function
- adding the 'c' flag to the mode parameter in fopen()
- link the program with "commode.obj", which makes 'c' flag the default.

This is easily overlooked by people who "already know" C/C++ because
neither possibility exists in the ISO standard variety.


When you're using low level IO, _commit is supposed to force immediate
flushing of a file to disk.
http://msdn2.microsoft.com/en-us/library/17618685.aspx


Standard C fflush() or flushall() will flush a stream's data to the OS, but
don't guarantee by default that the physical write has occurred before the
call returns.

http://msdn2.microsoft.com/en-us/library/c565h7xx.aspx near the end of the
page:

"Use fflush or _flushall to ensure that the buffer associated with a
specified file or all open buffers are flushed to the operating system,
which can cache data before writing it to disk. The commit-to-disk
feature ensures that the flushed buffer contents are not lost in the event
of a system failure."

The "commit-to-disk" feature is activated by the 'c' flag in fopen (for
example "w+c" instead of "w+"), or linking the program with "commode.obj".
 
Lucvdv said:
Flushing buffers may not be enough: by default the buffers are
flushed to the OS, but the OS may still cache them for later writing.
Click to expand...

Thanks for the information.

I thought _commit() just called the API FlushFileBuffers(). Is this not
the case? I admit my C/C++ knowledge is not very good. :-)
 
Hi.

I think that your example (interesting) does not solve the original problem.
If I open a new file I can flush the content to the disk with your API
calls, but the question is slightly different.
Infact your example (and Microsoft example) are about the fopen, fwrite and
flush.

I think that the database itself manages the .mdf and .ldf files writing on
the disk.

Suppose (as from Mike suggestion) that you can make periodic database
backups (for example every 30 minutes).
There is a power failure and after the system restarts.
How can I programmatically decide if I have to use the original mdf or call
a restore to retrieve the backup file? And how?

Thanks in advance for your reply.
 
Davide said:
Hi.

I think that your example (interesting) does not solve the original
problem. If I open a new file I can flush the content to the disk
with your API calls, but the question is slightly different.
Infact your example (and Microsoft example) are about the fopen,
fwrite and flush.

I think that the database itself manages the .mdf and .ldf files
writing on the disk.

Suppose (as from Mike suggestion) that you can make periodic database
backups (for example every 30 minutes).
There is a power failure and after the system restarts.
How can I programmatically decide if I have to use the original mdf
or call a restore to retrieve the backup file? And how?

Thanks in advance for your reply.
Click to expand...


In my recovery program I would get corrupted files occasionally and the
recovery would have to be executed again. I have not had the problem
since adding the following after copying the files.

This is Delphi but you should get the idea.

hDrive := CreateFile(PAnsiChar('\\.\X:'),
GENERIC_READ or GENERIC_WRITE,
FILE_SHARE_READ or FILE_SHARE_WRITE,
nil, OPEN_EXISTING, FILE_ATTRIBUTE_NORMAL, 0);

SYSFlushed := FlushFileBuffers(hDrive);

CloseHandle(hDrive);

This opens the volume directly and flushes it.
 
Hi, Mike.

I cannot understand the procedure you suggested, because it seems that you
can apply the method only on the backup file.

1. You have a SQL server running, so you have xxx.mdf and xxx.ldf on the
disk LOCKED.
2. The system makes a periodically backup of the database, so there is
another file on the disk (for example using agent I make a backup)
2a. I flush the drive so the backup file is written on the CF
3. There is a power failure
4. When the system restarts, the database service starts and try to open my
MDF file.

How can I decide programmatically to use the complete mdf file or to restore
backup one?

Thanks
 
I thought _commit() just called the API FlushFileBuffers(). Is this not
the case? I admit my C/C++ knowledge is not very good. :-)
Click to expand...

I think you're right, and neither is mine ;)
 
2a. I flush the drive so the backup file is written on the CF
Click to expand...

You already mentioned "XPE installed on CF" in your original question.

Does this mean you boot XPe from CF without using EWF?
A solution that might prove expensive in maintenance cost...


When I did my first tests with XPe, I ran FBA on CF and never included EWF
because it was easier, and at the same time it would give me an idea about
how long a CF will survive when windows sees it as a real harddisk.

The latter turned out to be about 2 to 3 weeks.
 
You may want to disable write caching on your volumes. We also use MSDE on
our products and they are never being shut down properly "without having
problems with file corruptions". You can turn it off by opening a volume's
property page, go to the Hardware tab, open the hard drive(s)'s properties,
go to the policies tab and disable the write cache. Preferrably, you make a
script that enumerates all hard drives and turns the caching off, which you
schedule to run after cloning.

When you make a backup of the database, you will still need to flush the
volume...

Best regards,
Gert Leunen
Software Engineer
R&D International NV
 
You may want to disable write caching on your volumes. We also use MSDE on
our products and they are never being shut down properly "without having
problems with file corruptions". You can turn it off by opening a volume's
property page, go to the Hardware tab, open the hard drive(s)'s properties,
go to the policies tab and disable the write cache. Preferrably, you make a
script that enumerates all hard drives and turns the caching off, which you
schedule to run after cloning.
Click to expand...

It may sound strange, but that option only disables a harddisk's own
internal cache, not windows's write cache.

http://support.microsoft.com/?kbid=233541

Disabling it allows journaling file systems like NTFS to keep tight control
of the exact order sectors are written in, to enhance their built-in crash
resistance at a performance cost.


It doesn't really make that much difference. Powering down without
experiencing corrupt files is not at all unusual, if you use NTFS-formatted
disks. NTFS was designed from the ground up to better resist such
treatment.

Doing the same with FAT[32] is close to asking for disaster, unless the
disks are read-only (or protected by EWF).

A problem both still have in common is that bad sectors can be created when
the power drops when the drive is half way through writing a sector.
Whether it can continue to the end of the sector on stored energy in the
capacitors on the drive's control board or not, is out of the OS's control.
 
It may sound strange, but that option only disables a harddisk's own
internal cache, not windows's write cache.
Click to expand...

That's correct, that's why you still need to flush the volume after writing.
It doesn't really make that much difference. Powering down without
experiencing corrupt files is not at all unusual, if you use
NTFS-formatted
disks. NTFS was designed from the ground up to better resist such
treatment.
Click to expand...

True, but before we started disabling the caching, we received reports
(although rare) that systems were unable to start (blue screen during boot)
after a power down. We haven't heared of such reports since we turned it
off.
A problem both still have in common is that bad sectors can be created
when
the power drops when the drive is half way through writing a sector.
Whether it can continue to the end of the sector on stored energy in the
capacitors on the drive's control board or not, is out of the OS's
control.
Click to expand...

That's true, but considering that this option re-orders write operations
(file-agnostic) to reduce head movements, many more files may be involved at
any single time, putting them all at risk (so with this option on, you
typically create many more bad files). This is the core of the resiliency
versus performance trade-off.

Best regards

Gert Leunen
 
OK. I understand the mechanisms of writeback and so.
I understand the advantages of EWF, but on the partition where I have the
MDF file I cannot enable EWF because all the database changes will be lost
after restart.

I appreciate the suggestion to use NTFS file system on this volume and also
the periodically backup of the database itself with a commit (using API) on
that volume. The only open question is: how can I decide (programmatically) ,
at the system restart, if the databse MDF file is corrupted? If it is
corrupted the database server cannot access it and I must make a restore of
the latest backup.

Davide
 
Davide said:
OK. I understand the mechanisms of writeback and so.
I understand the advantages of EWF, but on the partition where I have the
MDF file I cannot enable EWF because all the database changes will be lost
after restart.

I appreciate the suggestion to use NTFS file system on this volume and
also
the periodically backup of the database itself with a commit (using API)
on
that volume. The only open question is: how can I decide
(programmatically) ,
at the system restart, if the databse MDF file is corrupted? If it is
corrupted the database server cannot access it and I must make a restore
of
the latest backup.
Click to expand...

Try the TSQL command:

DBCC CHECKDB ('dbname') WITH PHYSICAL_ONLY
 
OK. I understand the mechanisms of writeback and so.
I understand the advantages of EWF, but on the partition where I have the
MDF file I cannot enable EWF because all the database changes will be lost
after restart.
Click to expand...

IIRC, you said in your initial message that you're using CF - CompactFlash?

I already said so in an earlier message, but you didn't react: you do NOT
want to use CompactFlash without EWF, believe me :)
 
Back
Top