poweroff & index

power-off happened, and server [w2k, sql2k] stopped during working day.
quick analyze showed there is no evident data loss [last changed values are
still there], so they restarted the server and continued to work.
later, some mallfunctions [broken relations] assured us that indices [at
least some of them] are damaged.
questionn is: how are primary key [and other uniques] indices handled?
is it possible, if index not repaired, doubled [not-unique] key being
inserted?
what is the most efficient way to examine if this happened.
after rebuilding primary key index [if broken], what happens with duplicated
keys?
some comments or experience about this?
thnx.
SQL Server should in many cases handle a hard power off just fine. There are two cases where this
can go bad, though:
1. Hardware caching without battery backup. SQL Server depends on what has been written is actually
on the disk. It directs the OS to not cache write operations, but if you have HW caching, then all
bets are off.
2. Torn page. SQL Server directs the OS to do write in page sizes as smallest size. So the OS will
direct the disk subsystem to do the write, but typically smallest size for write operation at disk
level is sector (typically 512 bytes) so a page can be partially written.
The best place to start if you want to read more about this are below two articles:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2005/iobasics.mspx
Also, I suggest you visit for lots of good stuff related to this topic.
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sali" <sali@.euroherc.hr> wrote in message news:eEYuqK4wHHA.4300@.TK2MSFTNGP04.phx.gbl...
> power-off happened, and server [w2k, sql2k] stopped during working day.
> quick analyze showed there is no evident data loss [last changed values are still there], so they
> restarted the server and continued to work.
> later, some mallfunctions [broken relations] assured us that indices [at least some of them] are
> damaged.
> questionn is: how are primary key [and other uniques] indices handled?
> is it possible, if index not repaired, doubled [not-unique] key being inserted?
> what is the most efficient way to examine if this happened.
> after rebuilding primary key index [if broken], what happens with duplicated keys?
> some comments or experience about this?
> thnx.
>
|||I don't no if you have the resources but.
Why don't you make a online full-backup.
Restore it....do the rebuilld index or repairs and see what happens.
Offcourse you can also query the database to check for duplicate keys.
I don't think this is happening.
But don't take my word for it.
Make sure you have a recent a log back-up and get to work!
Good luck!
"sali" wrote:

> power-off happened, and server [w2k, sql2k] stopped during working day.
> quick analyze showed there is no evident data loss [last changed values are
> still there], so they restarted the server and continued to work.
> later, some mallfunctions [broken relations] assured us that indices [at
> least some of them] are damaged.
> questionn is: how are primary key [and other uniques] indices handled?
> is it possible, if index not repaired, doubled [not-unique] key being
> inserted?
> what is the most efficient way to examine if this happened.
> after rebuilding primary key index [if broken], what happens with duplicated
> keys?
> some comments or experience about this?
> thnx.
>
>
|||thnx
right now server is working, but i want to be sure there is no hidden
problem with corrupted primary & unique keys.
i am affraid that simple :
select count(*), prim_key
group by prim_key
wouldn't show me there is a prim_keys with count()>1
or could i may select with prime_key index switched_off [simple flat scan]?
it all may depends of internal architecture of database engine, i've got a
link about it and going to examine it.
"Hate_orphaned_users" <Hateorphanedusers@.discussions.microsoft.com> je
napisao u poruci interesnoj
grupi:A7465133-50EA-43AB-92BB-2040C3844328@.microsoft.com...[vbcol=seagreen]
> Offcourse you can also query the database to check for duplicate keys.
> Good luck!
>
> "sali" wrote:
|||You might want to check out the DBCC CHECKCONSTRAINTS command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sali" <sali@.euroherc.hr> wrote in message news:%23EyhH44wHHA.4640@.TK2MSFTNGP03.phx.gbl...
> thnx
> right now server is working, but i want to be sure there is no hidden
> problem with corrupted primary & unique keys.
> i am affraid that simple :
> select count(*), prim_key
> group by prim_key
> wouldn't show me there is a prim_keys with count()>1
> or could i may select with prime_key index switched_off [simple flat scan]?
> it all may depends of internal architecture of database engine, i've got a
> link about it and going to examine it.
>
> "Hate_orphaned_users" <Hateorphanedusers@.discussions.microsoft.com> je
> napisao u poruci interesnoj
> grupi:A7465133-50EA-43AB-92BB-2040C3844328@.microsoft.com...
>