Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1743

Re: power loss DB corruption

Show all headers | View raw

On Friday, 25 April 2014 08:43:19 UTC+1, Hendrik van der Heijden  wrote:
> Hi everyone,
> 
> I get database corruption on hard shutdowns and wonder if there's
> some way to fix this. I googled a lot but couldn't find an answer
> whether this is supposed to work at all.
> 
> I have a standard consumer PC (single SATA drive on Intel Chipset
> Controller) running Windows 7 and MS SQL Server 2012 Express.
> 
> When I load the database with transactions and pull the PC power cord,
> due to ACID compliance, I expect to get no data loss on reportedly
> completed transactions. However, on SQL Server 2012 very often the
> database is corrupt afterwards and cannot be used anymore.
> I tried to disable HDD write caching, but it didn't improve things.
> On SQL Server 2008, the problem occurred less likely.
> 
> ** Can somepoint point me to documentation stating whether MSSQL
> ** supports ACID (especially the D) compliance on consumer hardware
> ** and what needs to be configured?
> 
> PostgreSQL, InnoDB and others support this as they can be configured
> to use fsync after each transaction. Does Microsoft also offer this?

Well - 
http://blogs.msdn.com/b/sqlexpress/archive/2008/02/22/sql-express-behaviors-idle-time-resources-usage-auto-close-and-user-instances.aspx

was written in 2008 and doesn't refer to the 
2012 edition, but states strongly that at /that/
time, SQL Server Express was the same program code,
but with - actually, several different behaviours,
about files and memory and so forth.

It should, however, be solid, I thought, as far 
as losing power and then recovering with the 
transaction log goes.  Your committed transactions
may be in RAM - a chill ran up my spine, reading 
that "When SQL Express is idle, it writes the 
cached data back to disk" - but they're also in 
the transaction log on disk, or they /should/ be.

And a database being unusable is /really/ bad.

Well - I think you're saying that you are not sure 
that you disabled write caching.  You do have to do 
that - otherwise, of course, SQL Server may think 
that the data is safely on disk, but it isn't.
But maybe it is still getting cached.  The HDD unit 
itself and your Windows settings for it may provide 
separate levels of caching.  Maybe the controller too.

A "consumer PC" may have other limitations, such as 
leaving files in a mess if the power is cut while 
the disk is running, and, having 8-bit, non-ECC 
and non-parity memory.  You could even have a 
corrupt memory chip.  This can be difficult to 
detect; I think my laptop's own diagnostics reported 
its memory was fine until I used the Linux 
"SystemRescueCD" and proved that it wasn't.

So, you could check that; you should be careful with
backups; you could buy an uninterruptible power 
supply (ideally one that tells your PC to shut down 
when its battery runs out); and possibly it would
be safer to put your database files on, for instance,
an external networked disk enclosure, i.e.
<http://en.wikipedia.org/wiki/Network-attached_storage>
- again with a UPS.

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

power loss DB corruption Hendrik van der Heijden <hvdh@gmx.de> - 2014-04-25 09:43 +0200
  Re: power loss DB corruption Erland Sommarskog <esquel@sommarskog.se> - 2014-04-25 12:24 +0200
    Re: power loss DB corruption bradbury9 <ray.bradbury9@gmail.com> - 2014-04-25 04:42 -0700
      Re: power loss DB corruption Erland Sommarskog <esquel@sommarskog.se> - 2014-04-25 18:16 +0200
        Re: power loss DB corruption bradbury9 <ray.bradbury9@gmail.com> - 2014-04-27 09:08 -0700
  Re: power loss DB corruption rja.carnegie@gmail.com - 2014-04-25 06:54 -0700
  Re: power loss DB corruption Graeme Geldenhuys <graemeg@example.net> - 2014-07-02 22:28 +0100

csiph-web