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

Groups > comp.databases.ms-sqlserver > #1739 > unrolled thread

power loss DB corruption

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  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

#1739 — power loss DB corruption

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?


Hendrik vdH

[toc] | [next] | [standalone]

#1740

Hendrik van der Heijden (hvdh@gmx.de) writes:
> 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.

If you pull the power cord, there are some risk for incomplete writes, 
isn't there?

What is important if you want to minimize data loss is to use the full 
recovery model and frequent transaction log backups.

Note that if the data file is damaged in case of a power outage, but 
the log file is not, you can recover the database by taking a tail-of-
the-log backup. If the log file is damaged, the prospects are bleaker,
even if the database is not.


-- 
Erland Sommarskog, Stockholm, esquel@sommarskog.se

[toc] | [prev] | [next] | [standalone]

#1741

El viernes, 25 de abril de 2014 12:24:40 UTC+2, Erland Sommarskog  escribió:
> Hendrik van der Heijden (hvdh@gmx.de) writes:
> 
> > 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.
> 
> 
> 
> If you pull the power cord, there are some risk for incomplete writes, 
> 
> isn't there?

Should not happen if Sql Server is full ACID compilant and it claims to be since SQL Server 2010.

http://technet.microsoft.com/en-us/library/aa213068(v=sql.80).aspx

> 
> 
> What is important if you want to minimize data loss is to use the full 
> 
> recovery model and frequent transaction log backups.
> 
> 
> Note that if the data file is damaged in case of a power outage, but 
> 
> the log file is not, you can recover the database by taking a tail-of-
> 
> the-log backup. If the log file is damaged, the prospects are bleaker,
> 
> even if the database is not.
> 

Good point, don't trust totally in ACID compilance. A good backup policy as the above described should always happen.

Please, note that I am *not* saying that Hendrik is not doing proper backups. ;-)

[toc] | [prev] | [next] | [standalone]

#1744

bradbury9 (ray.bradbury9@gmail.com) writes:
> Should not happen if Sql Server is full ACID compilant and it claims to
> be since SQL Server 2010. 

SQL Server 2010?

Anyway, if you pull the powercord in the middle of a big filecopy operation,
and you run CHKDISK after the operation, are you surprised if errors are 
reported?



-- 
Erland Sommarskog, Stockholm, esquel@sommarskog.se

[toc] | [prev] | [next] | [standalone]

#1746

El viernes, 25 de abril de 2014 18:16:09 UTC+2, Erland Sommarskog  escribió:
> bradbury9 (ray.bradbury9@gmail.com) writes:
> 
> > Should not happen if Sql Server is full ACID compilant and it claims to
> 
> > be since SQL Server 2010. 
> 
> 
> 
> SQL Server 2010?
> 
> 
> 
> Anyway, if you pull the powercord in the middle of a big filecopy operation,
> 
> and you run CHKDISK after the operation, are you surprised if errors are 
> 
> reported?
> 
> 
> 
> 
> 
> 
> 
> -- 
> 
> Erland Sommarskog, Stockholm, esquel@sommarskog.se

If fact transaction should be commited after the write in done. In that case the chkdisk should raise no problems which seems to not be the case

[toc] | [prev] | [next] | [standalone]

#1743

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.

[toc] | [prev] | [next] | [standalone]

#1781

On 2014-04-25 08:43, Hendrik van der Heijden wrote:
> 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.


The company I worked for in South Africa has done extensive tests on
power loss and OS & DB corruptions. South Africa is very prone to
blackouts and brownouts.

We have found that at file system level, the JFS (originally developed
by IBM) was the best file system under a Linux environment. At the time
I didn't know about ZFS - which I think would now be the better choice
(features like copy-on-write, CRC checks on reading and writing etc).

As for database servers. We tried many, and the Firebird DB Server
recovered without a single error in all our tests. Firebird is very well
supported on Windows, Linux, UNIX, FreeBSD, AIX and OSX. All our company
products now run with the Firebird DB. Oh, and Firebird is free and open
source too - another massive benefit.

Regards,
  Graeme

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web