Home > Archive > SQL Server Programming > August 2005 > self repair?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| Keith G Hicks 2005-08-20, 7:58 am |
| 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
| |
| Mike Jansen 2005-08-20, 7:01 pm |
| 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 w 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
"Keith G Hicks" <krh@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
>
>
| |
| Andrew J. Kelly 2005-08-20, 7:01 pm |
| 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.
--
Andrew J. Kelly SQL MVP
"Keith G Hicks" <krh@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
>
>
| |
| Brian Selzer 2005-08-20, 7:01 pm |
| 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" <krh@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
>
>
| |
| Tibor Karaszi 2005-08-20, 7:01 pm |
| > 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/pr...lIObasics.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" <brian@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" <krh@comcast.net> wrote in message
> news:#Up0ZXYpFHA.3244@TK2MSFTNGP09.phx.gbl...
> can
> about
>
>
| |
| Brian Selzer 2005-08-20, 7:01 pm |
| 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_karaszi@hotmail.nomail.com> wrote in
message news:#OZiQybpFHA.2504@tk2msftngp13.phx.gbl...
>
> 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/pr...lIObasics.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" <brian@selzer-software.com> wrote in message
> news:%23cvW1kapFHA.736@tk2msftngp13.phx.gbl...
outage[color=darkred]
for[color=darkred]
that[color=darkred]
these[color=darkred]
in[color=darkred]
with[color=darkred]
>
| |
| Keith G Hicks 2005-08-20, 7:01 pm |
| 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
| |
| Louis Davidson 2005-08-20, 7:01 pm |
| 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" <brian@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_karaszi@hotmail.nomail.com> wrote
> in
> message news:#OZiQybpFHA.2504@tk2msftngp13.phx.gbl...
> in below circumstances:
> guarantees atomic writes
> page is a page which part
> page detection mean that you
> that page) and you can
> shouldn't happen (assuming you
> backup.
> http://www.microsoft.com/technet/pr...lIObasics.mspx.
> outage
> for
> that
> these
> in
> with
>
>
| |
| Brian Selzer 2005-08-20, 9:57 pm |
| 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" <brian@selzer-software.com> wrote in message
> news:O%23DdbdcpFHA.3996@TK2MSFTNGP12.phx.gbl...
use[color=darkred]
your[color=darkred]
can[color=darkred]
server[color=darkred]
torn[color=darkred]
touch[color=darkred]
http://www.microsoft.com/technet/pr...lIObasics.mspx.[color=darkred]
out[color=darkred]
>
>
| |
| Louis Davidson 2005-08-20, 9:57 pm |
| 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 :)
--
----------------------------------------------------------------------------
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" <brian@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...
> and
> the
> the
> --
> convincing."
> use
> your
> can
> server
> torn
> touch
> http://www.microsoft.com/technet/pr...lIObasics.mspx.
> out
>
>
| |
| Tibor Karaszi 2005-08-22, 3:59 am |
| 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/pr...qlIObasics.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. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Brian Selzer" <brian@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_karaszi@hotmail.nomail.com> wrote in
> message news:#OZiQybpFHA.2504@tk2msftngp13.phx.gbl...
> in below circumstances:
> guarantees atomic writes
> page is a page which part
> page detection mean that you
> that page) and you can
> shouldn't happen (assuming you
> backup.
> http://www.microsoft.com/technet/pr...lIObasics.mspx.
> outage
> for
> that
> these
> in
> with
>
>
| |
| Brian Selzer 2005-08-22, 7:05 pm |
| 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_karaszi@hotmail.nomail.com> wrote in
message news:Osy#ovupFHA.3256@TK2MSFTNGP12.phx.gbl...
> 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/pr...qlIObasics.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. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
>
> "Brian Selzer" <brian@selzer-software.com> wrote in message
> news:O%23DdbdcpFHA.3996@TK2MSFTNGP12.phx.gbl...
use[color=darkred]
your[color=darkred]
can[color=darkred]
server[color=darkred]
in[color=darkred]
data[color=darkred]
typically[color=darkred]
torn[color=darkred]
touch[color=darkred]
http://www.microsoft.com/technet/pr...lIObasics.mspx.[color=darkred]
outages[color=darkred]
out[color=darkred]
>
|
|
|
|
|