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

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

Re: power loss DB corruption

Show all headers | View raw

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. ;-)

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