|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
self repair?Someone mentioned to me that they heard about a self-repair feature that can
be set up in MS SQL 2k. I did some hunting in BOL, on the web and in these NGs and didn't find anything. This person had a couple of power outages in the middle of the work day and was concerned about possible problems with their SQL database. So far it seems fine but I just wanted to find out about this repair thing. Thanks, Keith Self repair hmmmm. SQL Server with artificial intelligence I guess<g>...
Seriously though there is no such thing. Your best bet for corruption has been and always will be solid backups. There is an option in the maintenance wizard in that when you run Integrity Checks (DBCC CHECKDB) it will repair minor problems. I would never recommend that anyone just automatically "fix" any integrity issues without first understanding what the issue was and what caused it. Then after determining what data may be lost if any, executing the proper recovery plan. Some issues if minor such as a corrupted index can be rebuilt without data loss. But corruption in the data itself is likely to result in data loss unless you can restore from good backups. -- Show quoteAndrew J. Kelly SQL MVP "Keith G Hicks" <k**@comcast.net> wrote in message news:%23Up0ZXYpFHA.3244@TK2MSFTNGP09.phx.gbl... > Someone mentioned to me that they heard about a self-repair feature that > can > be set up in MS SQL 2k. I did some hunting in BOL, on the web and in these > NGs and didn't find anything. This person had a couple of power outages in > the middle of the work day and was concerned about possible problems with > their SQL database. So far it seems fine but I just wanted to find out > about > this repair thing. > > Thanks, > > Keith > > The person may have been talking about a maintenance plan that checks and
optionally fixes database errors. Maintenance plans normally run once a day or once a week and typically at times when database usage is minimal or preferably zero. From SQL Enterprise Manager, go your server \ Management \ Database Maintenance Plans and create a new maintenance plan. Most people don't have the plans automatically fix problems but instead just alert someone that there is a problem so that it can be looked at by a human before doing any fix (you can lose data with certain fixes). See the DBCC commands for checking and fixing database problems, this is what the maintenance plans do behind the scenes anyway. Its good to have maintainence plans because they do backups, checks, rebuild indexes, free disk space, etc. But its a good idea to have at least some level of understanding of what the pieces are and what they are doing before putting them in place. Also, they require planning because as I said they should be done at times of little or zero db usage and they also take up disk space (for reports and backups). Mike Show quote "Keith G Hicks" <k**@comcast.net> wrote in message news:%23Up0ZXYpFHA.3244@TK2MSFTNGP09.phx.gbl... > Someone mentioned to me that they heard about a self-repair feature that > can > be set up in MS SQL 2k. I did some hunting in BOL, on the web and in these > NGs and didn't find anything. This person had a couple of power outages in > the middle of the work day and was concerned about possible problems with > their SQL database. So far it seems fine but I just wanted to find out > about > this repair thing. > > Thanks, > > Keith > > SQL Server will automatically recover from a power outage. A power outage
will never corrupt SQL Server databases because SQL Server uses a write-ahead transaction log. You can read all about it if you search for "Transaction Recovery SQL Server Architecture" in BOL. Show quote "Keith G Hicks" <k**@comcast.net> wrote in message news:#Up0ZXYpFHA.3244@TK2MSFTNGP09.phx.gbl... > Someone mentioned to me that they heard about a self-repair feature that can > be set up in MS SQL 2k. I did some hunting in BOL, on the web and in these > NGs and didn't find anything. This person had a couple of power outages in > the middle of the work day and was concerned about possible problems with > their SQL database. So far it seems fine but I just wanted to find out about > this repair thing. > > Thanks, > > Keith > > > A power outage Just to expand a little bit on that:> will never corrupt SQL Server databases because SQL Server uses a > write-ahead transaction log. A power outage (or, we should say a lost write operation) can corrupt data in below circumstances: a. Power outage in the middle of writing a page. Disk subsystems typically guarantees atomic writes of sectors. A sector is typically 512 bytes. A page is 8192 byes. A torn page is a page which part of the page was written and part was not written. Having db option torn page detection mean that you will most likely detect this during startup (automatic recover will touch that page) and you can act. If you have write caching which is battery backuped, then this shouldn't happen (assuming you don't loose the stuff in cache). b. Hw write cache without battery backup or badly implemented battery backup. More information at http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "Brian Selzer" <br***@selzer-software.com> wrote in message news:%23cvW1kapFHA.736@tk2msftngp13.phx.gbl... > SQL Server will automatically recover from a power outage. A power outage > will never corrupt SQL Server databases because SQL Server uses a > write-ahead transaction log. You can read all about it if you search for > "Transaction Recovery SQL Server Architecture" in BOL. > > > "Keith G Hicks" <k**@comcast.net> wrote in message > news:#Up0ZXYpFHA.3244@TK2MSFTNGP09.phx.gbl... >> Someone mentioned to me that they heard about a self-repair feature that > can >> be set up in MS SQL 2k. I did some hunting in BOL, on the web and in these >> NGs and didn't find anything. This person had a couple of power outages in >> the middle of the work day and was concerned about possible problems with >> their SQL database. So far it seems fine but I just wanted to find out > about >> this repair thing. >> >> Thanks, >> >> Keith >> >> > > I stand corrected. If you don't use NTFS, or if you use NTFS but don't use
RAID and the cluster size is less than 8K, or if you do use RAID and the stripe size is less than 8K, or you've enabled write-back caching on your disk or disk controller without a battery backup, then a power outage can indeed damage the database. Of course, if you set up a production server like that, then you should probably be fired or sued, or both. "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in in below circumstances:message news:#OZiQybpFHA.2504@tk2msftngp13.phx.gbl... > > A power outage > > will never corrupt SQL Server databases because SQL Server uses a > > write-ahead transaction log. > > Just to expand a little bit on that: > > A power outage (or, we should say a lost write operation) can corrupt data > guarantees atomic writes> a. Power outage in the middle of writing a page. Disk subsystems typically > of sectors. A sector is typically 512 bytes. A page is 8192 byes. A torn page is a page which part> of the page was written and part was not written. Having db option torn page detection mean that you> will most likely detect this during startup (automatic recover will touch that page) and you can> act. If you have write caching which is battery backuped, then this shouldn't happen (assuming you> don't loose the stuff in cache). http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx.> > b. Hw write cache without battery backup or badly implemented battery backup. > > More information at Show quote > > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > Blog: http://solidqualitylearning.com/blogs/tibor/ > > > "Brian Selzer" <br***@selzer-software.com> wrote in message > news:%23cvW1kapFHA.736@tk2msftngp13.phx.gbl... > > SQL Server will automatically recover from a power outage. A power outage > > will never corrupt SQL Server databases because SQL Server uses a > > write-ahead transaction log. You can read all about it if you search for > > "Transaction Recovery SQL Server Architecture" in BOL. > > > > > > "Keith G Hicks" <k**@comcast.net> wrote in message > > news:#Up0ZXYpFHA.3244@TK2MSFTNGP09.phx.gbl... > >> Someone mentioned to me that they heard about a self-repair feature that > > can > >> be set up in MS SQL 2k. I did some hunting in BOL, on the web and in these > >> NGs and didn't find anything. This person had a couple of power outages in > >> the middle of the work day and was concerned about possible problems with > >> their SQL database. So far it seems fine but I just wanted to find out > > about > >> this repair thing. > >> > >> Thanks, > >> > >> Keith > >> > >> > > > > > Sued? Wow, if I could sue every person who ever set up a server poorly and
I could just have a 10% cut I would be rich beyond my wildest dreams. Of course don't forget that building UPS and the server UPS (well, at least the server UPS.) Then a power outage would require multiple failures before the generators kicked in :) -- Show quote---------------------------------------------------------------------------- Louis Davidson - http://spaces.msn.com/members/drsql/ SQL Server MVP "Arguments are to be avoided: they are always vulgar and often convincing." (Oscar Wilde) "Brian Selzer" <br***@selzer-software.com> wrote in message news:O%23DdbdcpFHA.3996@TK2MSFTNGP12.phx.gbl... >I stand corrected. If you don't use NTFS, or if you use NTFS but don't use > RAID and the cluster size is less than 8K, or if you do use RAID and the > stripe size is less than 8K, or you've enabled write-back caching on your > disk or disk controller without a battery backup, then a power outage can > indeed damage the database. Of course, if you set up a production server > like that, then you should probably be fired or sued, or both. > > > "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote > in > message news:#OZiQybpFHA.2504@tk2msftngp13.phx.gbl... >> > A power outage >> > will never corrupt SQL Server databases because SQL Server uses a >> > write-ahead transaction log. >> >> Just to expand a little bit on that: >> >> A power outage (or, we should say a lost write operation) can corrupt >> data > in below circumstances: >> >> a. Power outage in the middle of writing a page. Disk subsystems >> typically > guarantees atomic writes >> of sectors. A sector is typically 512 bytes. A page is 8192 byes. A torn > page is a page which part >> of the page was written and part was not written. Having db option torn > page detection mean that you >> will most likely detect this during startup (automatic recover will touch > that page) and you can >> act. If you have write caching which is battery backuped, then this > shouldn't happen (assuming you >> don't loose the stuff in cache). >> >> b. Hw write cache without battery backup or badly implemented battery > backup. >> >> More information at > http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx. >> >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://www.solidqualitylearning.com/ >> Blog: http://solidqualitylearning.com/blogs/tibor/ >> >> >> "Brian Selzer" <br***@selzer-software.com> wrote in message >> news:%23cvW1kapFHA.736@tk2msftngp13.phx.gbl... >> > SQL Server will automatically recover from a power outage. A power > outage >> > will never corrupt SQL Server databases because SQL Server uses a >> > write-ahead transaction log. You can read all about it if you search > for >> > "Transaction Recovery SQL Server Architecture" in BOL. >> > >> > >> > "Keith G Hicks" <k**@comcast.net> wrote in message >> > news:#Up0ZXYpFHA.3244@TK2MSFTNGP09.phx.gbl... >> >> Someone mentioned to me that they heard about a self-repair feature > that >> > can >> >> be set up in MS SQL 2k. I did some hunting in BOL, on the web and in > these >> >> NGs and didn't find anything. This person had a couple of power >> >> outages > in >> >> the middle of the work day and was concerned about possible problems > with >> >> their SQL database. So far it seems fine but I just wanted to find out >> > about >> >> this repair thing. >> >> >> >> Thanks, >> >> >> >> Keith >> >> >> >> >> > >> > >> > > You would have to prove damages, so it's really not practical to sue unless
there has already been data corruption. You can, however, fire the incompetent employee(s) or consulting firm before it happens and hire someone competent to fix it. Show quote "Louis Davidson" <dr_dontspamme_sql@hotmail.com> wrote in message http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx.news:#fno18dpFHA.1048@tk2msftngp13.phx.gbl... > Sued? Wow, if I could sue every person who ever set up a server poorly and > I could just have a 10% cut I would be rich beyond my wildest dreams. Of > course don't forget that building UPS and the server UPS (well, at least the > server UPS.) Then a power outage would require multiple failures before the > generators kicked in :) > > -- > -------------------------------------------------------------------------- -- > Louis Davidson - http://spaces.msn.com/members/drsql/ > SQL Server MVP > "Arguments are to be avoided: they are always vulgar and often convincing." > (Oscar Wilde) > > > "Brian Selzer" <br***@selzer-software.com> wrote in message > news:O%23DdbdcpFHA.3996@TK2MSFTNGP12.phx.gbl... > >I stand corrected. If you don't use NTFS, or if you use NTFS but don't use > > RAID and the cluster size is less than 8K, or if you do use RAID and the > > stripe size is less than 8K, or you've enabled write-back caching on your > > disk or disk controller without a battery backup, then a power outage can > > indeed damage the database. Of course, if you set up a production server > > like that, then you should probably be fired or sued, or both. > > > > > > "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote > > in > > message news:#OZiQybpFHA.2504@tk2msftngp13.phx.gbl... > >> > A power outage > >> > will never corrupt SQL Server databases because SQL Server uses a > >> > write-ahead transaction log. > >> > >> Just to expand a little bit on that: > >> > >> A power outage (or, we should say a lost write operation) can corrupt > >> data > > in below circumstances: > >> > >> a. Power outage in the middle of writing a page. Disk subsystems > >> typically > > guarantees atomic writes > >> of sectors. A sector is typically 512 bytes. A page is 8192 byes. A torn > > page is a page which part > >> of the page was written and part was not written. Having db option torn > > page detection mean that you > >> will most likely detect this during startup (automatic recover will touch > > that page) and you can > >> act. If you have write caching which is battery backuped, then this > > shouldn't happen (assuming you > >> don't loose the stuff in cache). > >> > >> b. Hw write cache without battery backup or badly implemented battery > > backup. > >> > >> More information at > > Show quote > >> > >> > >> -- > >> Tibor Karaszi, SQL Server MVP > >> http://www.karaszi.com/sqlserver/default.asp > >> http://www.solidqualitylearning.com/ > >> Blog: http://solidqualitylearning.com/blogs/tibor/ > >> > >> > >> "Brian Selzer" <br***@selzer-software.com> wrote in message > >> news:%23cvW1kapFHA.736@tk2msftngp13.phx.gbl... > >> > SQL Server will automatically recover from a power outage. A power > > outage > >> > will never corrupt SQL Server databases because SQL Server uses a > >> > write-ahead transaction log. You can read all about it if you search > > for > >> > "Transaction Recovery SQL Server Architecture" in BOL. > >> > > >> > > >> > "Keith G Hicks" <k**@comcast.net> wrote in message > >> > news:#Up0ZXYpFHA.3244@TK2MSFTNGP09.phx.gbl... > >> >> Someone mentioned to me that they heard about a self-repair feature > > that > >> > can > >> >> be set up in MS SQL 2k. I did some hunting in BOL, on the web and in > > these > >> >> NGs and didn't find anything. This person had a couple of power > >> >> outages > > in > >> >> the middle of the work day and was concerned about possible problems > > with > >> >> their SQL database. So far it seems fine but I just wanted to find out > >> > about > >> >> this repair thing. > >> >> > >> >> Thanks, > >> >> > >> >> Keith > >> >> > >> >> > >> > > >> > > >> > > > > > > I have been through that and it is not easy either way. Usually I find if
someone has achieved a level of power that they could cause enough damage without having the intelligence to back it up that they have something far more powerful than intelligence. Charisma. In fact that would actually be the worlds most powerful superhero: CharismaMan. "Mayor, I think Batman should be locked up. I mean, a bat suit? What good could a guy in a bat suit do?" Even worse, CharismaMan would not be overtly evil, just dumber than a post but with a smooth taking demeanor. Not that everyone with great charisma is bad, or everyone with intelligence particularly good. But I digress :) -- Show quote---------------------------------------------------------------------------- Louis Davidson - http://spaces.msn.com/members/drsql/ SQL Server MVP "Arguments are to be avoided: they are always vulgar and often convincing." (Oscar Wilde) "Brian Selzer" <br***@selzer-software.com> wrote in message news:e1LsHxepFHA.708@TK2MSFTNGP09.phx.gbl... > You would have to prove damages, so it's really not practical to sue > unless > there has already been data corruption. You can, however, fire the > incompetent employee(s) or consulting firm before it happens and hire > someone competent to fix it. > > > "Louis Davidson" <dr_dontspamme_sql@hotmail.com> wrote in message > news:#fno18dpFHA.1048@tk2msftngp13.phx.gbl... >> Sued? Wow, if I could sue every person who ever set up a server poorly > and >> I could just have a 10% cut I would be rich beyond my wildest dreams. Of >> course don't forget that building UPS and the server UPS (well, at least > the >> server UPS.) Then a power outage would require multiple failures before > the >> generators kicked in :) >> >> -- >> -------------------------------------------------------------------------- > -- >> Louis Davidson - http://spaces.msn.com/members/drsql/ >> SQL Server MVP >> "Arguments are to be avoided: they are always vulgar and often > convincing." >> (Oscar Wilde) >> >> >> "Brian Selzer" <br***@selzer-software.com> wrote in message >> news:O%23DdbdcpFHA.3996@TK2MSFTNGP12.phx.gbl... >> >I stand corrected. If you don't use NTFS, or if you use NTFS but don't > use >> > RAID and the cluster size is less than 8K, or if you do use RAID and >> > the >> > stripe size is less than 8K, or you've enabled write-back caching on > your >> > disk or disk controller without a battery backup, then a power outage > can >> > indeed damage the database. Of course, if you set up a production > server >> > like that, then you should probably be fired or sued, or both. >> > >> > >> > "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> >> > wrote >> > in >> > message news:#OZiQybpFHA.2504@tk2msftngp13.phx.gbl... >> >> > A power outage >> >> > will never corrupt SQL Server databases because SQL Server uses a >> >> > write-ahead transaction log. >> >> >> >> Just to expand a little bit on that: >> >> >> >> A power outage (or, we should say a lost write operation) can corrupt >> >> data >> > in below circumstances: >> >> >> >> a. Power outage in the middle of writing a page. Disk subsystems >> >> typically >> > guarantees atomic writes >> >> of sectors. A sector is typically 512 bytes. A page is 8192 byes. A > torn >> > page is a page which part >> >> of the page was written and part was not written. Having db option >> >> torn >> > page detection mean that you >> >> will most likely detect this during startup (automatic recover will > touch >> > that page) and you can >> >> act. If you have write caching which is battery backuped, then this >> > shouldn't happen (assuming you >> >> don't loose the stuff in cache). >> >> >> >> b. Hw write cache without battery backup or badly implemented battery >> > backup. >> >> >> >> More information at >> > > http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx. >> >> >> >> >> >> -- >> >> Tibor Karaszi, SQL Server MVP >> >> http://www.karaszi.com/sqlserver/default.asp >> >> http://www.solidqualitylearning.com/ >> >> Blog: http://solidqualitylearning.com/blogs/tibor/ >> >> >> >> >> >> "Brian Selzer" <br***@selzer-software.com> wrote in message >> >> news:%23cvW1kapFHA.736@tk2msftngp13.phx.gbl... >> >> > SQL Server will automatically recover from a power outage. A power >> > outage >> >> > will never corrupt SQL Server databases because SQL Server uses a >> >> > write-ahead transaction log. You can read all about it if you >> >> > search >> > for >> >> > "Transaction Recovery SQL Server Architecture" in BOL. >> >> > >> >> > >> >> > "Keith G Hicks" <k**@comcast.net> wrote in message >> >> > news:#Up0ZXYpFHA.3244@TK2MSFTNGP09.phx.gbl... >> >> >> Someone mentioned to me that they heard about a self-repair feature >> > that >> >> > can >> >> >> be set up in MS SQL 2k. I did some hunting in BOL, on the web and >> >> >> in >> > these >> >> >> NGs and didn't find anything. This person had a couple of power >> >> >> outages >> > in >> >> >> the middle of the work day and was concerned about possible >> >> >> problems >> > with >> >> >> their SQL database. So far it seems fine but I just wanted to find > out >> >> > about >> >> >> this repair thing. >> >> >> >> >> >> Thanks, >> >> >> >> >> >> Keith >> >> >> >> >> >> >> >> > >> >> > >> >> >> > >> > >> >> > > I have a feeling that the critical part is the sector, not the cluster. The disk drive does atomic
writes at the sector level. Specifying another cluster size in the file system will not change this. Unfortunately, http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx does not say this explicitly, but reading the section "Torn I/O Prevention (Split I/Os)" (cluster) and the ones describing sector, for instance "Torn I/O" sort of implies this. I fully agree about battery backed up HW cache and UPS, of course. :-) -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "Brian Selzer" <br***@selzer-software.com> wrote in message news:O%23DdbdcpFHA.3996@TK2MSFTNGP12.phx.gbl... >I stand corrected. If you don't use NTFS, or if you use NTFS but don't use > RAID and the cluster size is less than 8K, or if you do use RAID and the > stripe size is less than 8K, or you've enabled write-back caching on your > disk or disk controller without a battery backup, then a power outage can > indeed damage the database. Of course, if you set up a production server > like that, then you should probably be fired or sued, or both. > > > "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in > message news:#OZiQybpFHA.2504@tk2msftngp13.phx.gbl... >> > A power outage >> > will never corrupt SQL Server databases because SQL Server uses a >> > write-ahead transaction log. >> >> Just to expand a little bit on that: >> >> A power outage (or, we should say a lost write operation) can corrupt data > in below circumstances: >> >> a. Power outage in the middle of writing a page. Disk subsystems typically > guarantees atomic writes >> of sectors. A sector is typically 512 bytes. A page is 8192 byes. A torn > page is a page which part >> of the page was written and part was not written. Having db option torn > page detection mean that you >> will most likely detect this during startup (automatic recover will touch > that page) and you can >> act. If you have write caching which is battery backuped, then this > shouldn't happen (assuming you >> don't loose the stuff in cache). >> >> b. Hw write cache without battery backup or badly implemented battery > backup. >> >> More information at > http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx. >> >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://www.solidqualitylearning.com/ >> Blog: http://solidqualitylearning.com/blogs/tibor/ >> >> >> "Brian Selzer" <br***@selzer-software.com> wrote in message >> news:%23cvW1kapFHA.736@tk2msftngp13.phx.gbl... >> > SQL Server will automatically recover from a power outage. A power > outage >> > will never corrupt SQL Server databases because SQL Server uses a >> > write-ahead transaction log. You can read all about it if you search > for >> > "Transaction Recovery SQL Server Architecture" in BOL. >> > >> > >> > "Keith G Hicks" <k**@comcast.net> wrote in message >> > news:#Up0ZXYpFHA.3244@TK2MSFTNGP09.phx.gbl... >> >> Someone mentioned to me that they heard about a self-repair feature > that >> > can >> >> be set up in MS SQL 2k. I did some hunting in BOL, on the web and in > these >> >> NGs and didn't find anything. This person had a couple of power outages > in >> >> the middle of the work day and was concerned about possible problems > with >> >> their SQL database. So far it seems fine but I just wanted to find out >> > about >> >> this repair thing. >> >> >> >> Thanks, >> >> >> >> Keith >> >> >> >> >> > >> > >> > > I've never seen a torn page. Of course most of the systems I've worked with
have had a hardware RAID controller. I was under the impression that if you use NTFS, that the system detects whether or not a write was successful during recovery and fixes it. After further investigation, it appears that you're right. NTFS guarantees the consistency of a volume's metadata, not the consistency of the volume's data, so it looks like RAID with a stripe size of at least 8K is needed to prevent torn pages. A RAID subsystem writes a stripe at a time and ensures that each write is successful; therefore, when the system starts up the RAID subsystem will know whether a write was complete and will essentially back out any incomplete write by either copying the data from its mirror (RAID-1, 10), or by computing it from the rest of the disks (RAID-5). Of course, if your RAID controller has a battery-backup for the cache, then incomplete writes are completed when the system comes back up. By the way, RAID-0 is an oxymoron, because it isn't a REDUNDANT Array of Inexpensive Disks, so by RAID I mean at least RAID-1 :) "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in The disk drive does atomicmessage news:Osy#ovupFHA.3256@TK2MSFTNGP12.phx.gbl... > I have a feeling that the critical part is the sector, not the cluster. > writes at the sector level. Specifying another cluster size in the file system will not change this.> Unfortunately, http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspxdoes > not say this explicitly, but reading the section "Torn I/O Prevention (Split I/Os)" (cluster) andShow quote > the ones describing sector, for instance "Torn I/O" sort of implies this. http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx.> > I fully agree about battery backed up HW cache and UPS, of course. :-) > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > Blog: http://solidqualitylearning.com/blogs/tibor/ > > > "Brian Selzer" <br***@selzer-software.com> wrote in message > news:O%23DdbdcpFHA.3996@TK2MSFTNGP12.phx.gbl... > >I stand corrected. If you don't use NTFS, or if you use NTFS but don't use > > RAID and the cluster size is less than 8K, or if you do use RAID and the > > stripe size is less than 8K, or you've enabled write-back caching on your > > disk or disk controller without a battery backup, then a power outage can > > indeed damage the database. Of course, if you set up a production server > > like that, then you should probably be fired or sued, or both. > > > > > > "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in > > message news:#OZiQybpFHA.2504@tk2msftngp13.phx.gbl... > >> > A power outage > >> > will never corrupt SQL Server databases because SQL Server uses a > >> > write-ahead transaction log. > >> > >> Just to expand a little bit on that: > >> > >> A power outage (or, we should say a lost write operation) can corrupt data > > in below circumstances: > >> > >> a. Power outage in the middle of writing a page. Disk subsystems typically > > guarantees atomic writes > >> of sectors. A sector is typically 512 bytes. A page is 8192 byes. A torn > > page is a page which part > >> of the page was written and part was not written. Having db option torn > > page detection mean that you > >> will most likely detect this during startup (automatic recover will touch > > that page) and you can > >> act. If you have write caching which is battery backuped, then this > > shouldn't happen (assuming you > >> don't loose the stuff in cache). > >> > >> b. Hw write cache without battery backup or badly implemented battery > > backup. > >> > >> More information at > > Show quote > >> > >> > >> -- > >> Tibor Karaszi, SQL Server MVP > >> http://www.karaszi.com/sqlserver/default.asp > >> http://www.solidqualitylearning.com/ > >> Blog: http://solidqualitylearning.com/blogs/tibor/ > >> > >> > >> "Brian Selzer" <br***@selzer-software.com> wrote in message > >> news:%23cvW1kapFHA.736@tk2msftngp13.phx.gbl... > >> > SQL Server will automatically recover from a power outage. A power > > outage > >> > will never corrupt SQL Server databases because SQL Server uses a > >> > write-ahead transaction log. You can read all about it if you search > > for > >> > "Transaction Recovery SQL Server Architecture" in BOL. > >> > > >> > > >> > "Keith G Hicks" <k**@comcast.net> wrote in message > >> > news:#Up0ZXYpFHA.3244@TK2MSFTNGP09.phx.gbl... > >> >> Someone mentioned to me that they heard about a self-repair feature > > that > >> > can > >> >> be set up in MS SQL 2k. I did some hunting in BOL, on the web and in > > these > >> >> NGs and didn't find anything. This person had a couple of power outages > > in > >> >> the middle of the work day and was concerned about possible problems > > with > >> >> their SQL database. So far it seems fine but I just wanted to find out > >> > about > >> >> this repair thing. > >> >> > >> >> Thanks, > >> >> > >> >> Keith > >> >> > >> >> > >> > > >> > > >> > > > > > Thanks for all the input on this. It's just as I thought. He was
misinformed. I knew about maintenance plans, check db and such but had never heard of this self-repair idea either. They're well protected. There's a backup that runs nightly and is moved off site every day. They also have a pretty beefy UPS connected to the server. Thanks again for the input. -keith  
Other interesting topics
|
|||||||||||||||||||||||
