pre-execute failure

I just started getting this error.

[DTS.Pipeline] Error: component "User Type" (377) failed the pre-execute phase and returned error code 0x8007000E.

It wasn't happening before. Does anyone know what it means?

Hi Jim,
Without more information it is probably impossible to say.

What type of component is it?
What are you using it for?
How have you configured it?
When do you get the error - when the package starts or when the data-flow starts?
What inputs does it take?

etc...etc...

Regards
Jamie|||

Excelent questions.
This s a dataflow component. All of it's input is from a table. It seems like just rearranging the dataflow components in the work flow eliminates the problem. Since it isn't happening any longer, I can't do a better job answering your questions.

I don't understand it.

|||Incidentally, that error is Out Of Memory, so there could have been some transient cause.... Please let us know if you do come across a repro.sql

Pre execute Failure on Look up task

Hi,

I have an OLE DB Source. The output of OLEDB source is connected to a look up task. The output of the lookup task goes into OLE DB Destination. When executed..

I am getting this error:

DTS.Pipeline: component "Lookup" (2834) failed the pre-execute phase and returned error code 0x8007000E.

Any idea why pre-execute phase failure occurs?

TIN,
Anand

0x8007000E is out of memory error. How big is the reference table? Is Lookup running in Full Cache mode? Can you try running it in Partial Cache mode?|||Hi,

You are probably right. I have a data flow task before this specific task which contains 15 Look Up Tasks. I am presuming that memory is not released when needed.

The reference table is very small. It contains around 220 records.

Also, I have 2 GB memory available on this machine.

-Anandsql

Power failure->Sharepoint DB with torn page

Hi guys!
I'd really appreciate some help and directions about a sql server issue: I
had failure on my server (the fan of a cpu's got broken so the system
ungracefully stopped due to the overheat protection of the CPU). It's running
SBS 2003 premium with sharepoint on SQL 2000.
I was able to get the system back online (had some trobules with the RAID
array but got it fixed) and I was able to have a snapshot of the files just
in case I should be in need to get back to the "original" situation.
Due to the RAID unalignment, my DBs are someway corrupt and I've got a 106MB
sharepoint DB that I'd love to recover.
I already tried to ask for help in the italian sql server newsgroup but no
one was able to help me. What's more, I can't ask for microsoft online
support as they only support english platforms (perhaps I could give it a
try, I mean, the problem would still be exactly the same).
Now the situation is that I've got the mdf and ldf files (I can't use
backups as I need some recent data that still wasn't backed up) and the DB
has a torn page that I'm unable (don't know how) to recover from. Here's what
I get in the event viewer:
Tipo evento: Errore
Origine evento: MSSQL$SHAREPOINT
Categoria evento: (2)
ID evento: 17055
Data: 11/06/2005
Ora: 12.46.51
Utente: TIBERTI\Administrator
Computer: TWINTIB
Descrizione:
18052 :
Errore: 823, gravità : 24, stato: 2.
Per ulteriori informazioni, consultare la Guida in linea e supporto tecnico
all'indirizzo http://go.microsoft.com/fwlink/events.asp.
Dati:
0000: 84 46 00 00 10 00 00 00 â'F.....
0008: 13 00 00 00 54 00 57 00 ...T.W.
0010: 49 00 4e 00 54 00 49 00 I.N.T.I.
0018: 42 00 5c 00 53 00 48 00 B.\.S.H.
0020: 41 00 52 00 45 00 50 00 A.R.E.P.
0028: 4f 00 49 00 4e 00 54 00 O.I.N.T.
0030: 00 00 0a 00 00 00 6f 00 .....o.
0038: 6c 00 64 00 6d 00 61 00 l.d.m.a.
0040: 73 00 74 00 65 00 72 00 s.t.e.r.
0048: 00 00 ..
Tipo evento: Errore
Origine evento: MSSQL$SHAREPOINT
Categoria evento: (2)
ID evento: 17052
Data: 11/06/2005
Ora: 12.46.51
Utente: TIBERTI\Administrator
Computer: TWINTIB
Descrizione:
Errore: 823, gravità : 24, stato: 2
I/O error (torn page) detected during read at offset 0x00000000318000 in
file 'F:\Programmi\Microsoft SQL
Server\MSSQL$SHAREPOINT\Data\oldmaster.mdf'.
Per ulteriori informazioni, consultare la Guida in linea e supporto tecnico
all'indirizzo http://go.microsoft.com/fwlink/events.asp.
Dati:
0000: 37 03 00 00 18 00 00 00 7......
0008: 13 00 00 00 54 00 57 00 ...T.W.
0010: 49 00 4e 00 54 00 49 00 I.N.T.I.
0018: 42 00 5c 00 53 00 48 00 B.\.S.H.
0020: 41 00 52 00 45 00 50 00 A.R.E.P.
0028: 4f 00 49 00 4e 00 54 00 O.I.N.T.
0030: 00 00 0a 00 00 00 6f 00 .....o.
0038: 6c 00 64 00 6d 00 61 00 l.d.m.a.
0040: 73 00 74 00 65 00 72 00 s.t.e.r.
0048: 00 00 ..
Tipo evento: Errore
Origine evento: MSSQL$SHAREPOINT
Categoria evento: (2)
ID evento: 17052
Data: 11/06/2005
Ora: 12.48.06
Utente: TIBERTI\Administrator
Computer: TWINTIB
Descrizione:
Errore: 823, gravità : 24, stato: 6
I/O error (torn page) detected during read at offset 0000000000000000 in
file 'F:\Programmi\Microsoft SQL
Server\MSSQL$SHAREPOINT\Data\STS_content.mdf'.
Per ulteriori informazioni, consultare la Guida in linea e supporto tecnico
all'indirizzo http://go.microsoft.com/fwlink/events.asp.
Dati:
0000: 37 03 00 00 18 00 00 00 7......
0008: 13 00 00 00 54 00 57 00 ...T.W.
0010: 49 00 4e 00 54 00 49 00 I.N.T.I.
0018: 42 00 5c 00 53 00 48 00 B.\.S.H.
0020: 41 00 52 00 45 00 50 00 A.R.E.P.
0028: 4f 00 49 00 4e 00 54 00 O.I.N.T.
0030: 00 00 07 00 00 00 6d 00 .....m.
0038: 61 00 73 00 74 00 65 00 a.s.t.e.
0040: 72 00 00 00 r...
I've already tried with tools to recover data from DBs (SQLRecovery) but it
obviusly wasn't able to recover the data saved from sharepoint...
What I'm looking for is: some directions (I am a system engineer even if I
don't work on databases but I can deal with that under correct guidance) on
how to fix this or how to obtain support from microsoft for an affordable
price (I'm a freelance consultant and MAPS subscriber and I already had to
spend much money for the RAID disks to get recovered and can't spend a
fortune on this).
thanks to you all!
AlessandroIt would be helfpul to know in more detail what you have done.
Is it really the master database which is corrupted or another
database?
Only the mdf file, not the ldf file right?
And you're unable to do any recovery of the file whatsoever?
Mark|||Hi Mark,
First of all thanks for answering. Coming to your questions to help me:
The master DB is not ok but that's not a problem. Once in the past I was
able to recover a sharepoint site from the DBs that conteined it just by
installing a new sql server with the same previous name, attaching the old
(good) content DB and make sharepoint point to it. And, I was able to
install a new instance of the SQL server, create a config and contents db
(those that are used by sharepoint), stop the sql server instance, replace
the "empty" files with the one I recovered and that's how I got into the
torn page error.
I have both the mdf and ldf files. I'm able to recover old copies of the DBs
(including the master) but in which the contents are not up to date.
If you should need further informations, please ask.
Greetings, and thank once again,
Alessandro
<markandersen@.evare.com> ha scritto nel messaggio
news:1120589924.726106.226510@.g44g2000cwa.googlegroups.com...
> It would be helfpul to know in more detail what you have done.
> Is it really the master database which is corrupted or another
> database?
> Only the mdf file, not the ldf file right?
> And you're unable to do any recovery of the file whatsoever?
> Mark
>|||What you should have done it to do regular transaction log backups. If you'd done that, you could
now just do a log backup, restore the most recent clean db backup and then all log backup. Zero data
loss.
In your situation, you could consider exporting all tables and objects to a new database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Alessandro Tiberti" <Alessandro Tiberti@.discussions.microsoft.com> wrote in message
news:A3A7DE8B-5FFE-4BAE-987C-9B634CF6E219@.microsoft.com...
> Hi guys!
> I'd really appreciate some help and directions about a sql server issue: I
> had failure on my server (the fan of a cpu's got broken so the system
> ungracefully stopped due to the overheat protection of the CPU). It's running
> SBS 2003 premium with sharepoint on SQL 2000.
> I was able to get the system back online (had some trobules with the RAID
> array but got it fixed) and I was able to have a snapshot of the files just
> in case I should be in need to get back to the "original" situation.
> Due to the RAID unalignment, my DBs are someway corrupt and I've got a 106MB
> sharepoint DB that I'd love to recover.
> I already tried to ask for help in the italian sql server newsgroup but no
> one was able to help me. What's more, I can't ask for microsoft online
> support as they only support english platforms (perhaps I could give it a
> try, I mean, the problem would still be exactly the same).
> Now the situation is that I've got the mdf and ldf files (I can't use
> backups as I need some recent data that still wasn't backed up) and the DB
> has a torn page that I'm unable (don't know how) to recover from. Here's what
> I get in the event viewer:
> Tipo evento: Errore
> Origine evento: MSSQL$SHAREPOINT
> Categoria evento: (2)
> ID evento: 17055
> Data: 11/06/2005
> Ora: 12.46.51
> Utente: TIBERTI\Administrator
> Computer: TWINTIB
> Descrizione:
> 18052 :
> Errore: 823, gravità : 24, stato: 2.
>
> Per ulteriori informazioni, consultare la Guida in linea e supporto tecnico
> all'indirizzo http://go.microsoft.com/fwlink/events.asp.
> Dati:
> 0000: 84 46 00 00 10 00 00 00 â'F.....
> 0008: 13 00 00 00 54 00 57 00 ...T.W.
> 0010: 49 00 4e 00 54 00 49 00 I.N.T.I.
> 0018: 42 00 5c 00 53 00 48 00 B.\.S.H.
> 0020: 41 00 52 00 45 00 50 00 A.R.E.P.
> 0028: 4f 00 49 00 4e 00 54 00 O.I.N.T.
> 0030: 00 00 0a 00 00 00 6f 00 .....o.
> 0038: 6c 00 64 00 6d 00 61 00 l.d.m.a.
> 0040: 73 00 74 00 65 00 72 00 s.t.e.r.
> 0048: 00 00 ..
> Tipo evento: Errore
> Origine evento: MSSQL$SHAREPOINT
> Categoria evento: (2)
> ID evento: 17052
> Data: 11/06/2005
> Ora: 12.46.51
> Utente: TIBERTI\Administrator
> Computer: TWINTIB
> Descrizione:
> Errore: 823, gravità : 24, stato: 2
> I/O error (torn page) detected during read at offset 0x00000000318000 in
> file 'F:\Programmi\Microsoft SQL
> Server\MSSQL$SHAREPOINT\Data\oldmaster.mdf'.
> Per ulteriori informazioni, consultare la Guida in linea e supporto tecnico
> all'indirizzo http://go.microsoft.com/fwlink/events.asp.
> Dati:
> 0000: 37 03 00 00 18 00 00 00 7......
> 0008: 13 00 00 00 54 00 57 00 ...T.W.
> 0010: 49 00 4e 00 54 00 49 00 I.N.T.I.
> 0018: 42 00 5c 00 53 00 48 00 B.\.S.H.
> 0020: 41 00 52 00 45 00 50 00 A.R.E.P.
> 0028: 4f 00 49 00 4e 00 54 00 O.I.N.T.
> 0030: 00 00 0a 00 00 00 6f 00 .....o.
> 0038: 6c 00 64 00 6d 00 61 00 l.d.m.a.
> 0040: 73 00 74 00 65 00 72 00 s.t.e.r.
> 0048: 00 00 ..
> Tipo evento: Errore
> Origine evento: MSSQL$SHAREPOINT
> Categoria evento: (2)
> ID evento: 17052
> Data: 11/06/2005
> Ora: 12.48.06
> Utente: TIBERTI\Administrator
> Computer: TWINTIB
> Descrizione:
> Errore: 823, gravità : 24, stato: 6
> I/O error (torn page) detected during read at offset 0000000000000000 in
> file 'F:\Programmi\Microsoft SQL
> Server\MSSQL$SHAREPOINT\Data\STS_content.mdf'.
> Per ulteriori informazioni, consultare la Guida in linea e supporto tecnico
> all'indirizzo http://go.microsoft.com/fwlink/events.asp.
> Dati:
> 0000: 37 03 00 00 18 00 00 00 7......
> 0008: 13 00 00 00 54 00 57 00 ...T.W.
> 0010: 49 00 4e 00 54 00 49 00 I.N.T.I.
> 0018: 42 00 5c 00 53 00 48 00 B.\.S.H.
> 0020: 41 00 52 00 45 00 50 00 A.R.E.P.
> 0028: 4f 00 49 00 4e 00 54 00 O.I.N.T.
> 0030: 00 00 07 00 00 00 6d 00 .....m.
> 0038: 61 00 73 00 74 00 65 00 a.s.t.e.
> 0040: 72 00 00 00 r...
> I've already tried with tools to recover data from DBs (SQLRecovery) but it
> obviusly wasn't able to recover the data saved from sharepoint...
> What I'm looking for is: some directions (I am a system engineer even if I
> don't work on databases but I can deal with that under correct guidance) on
> how to fix this or how to obtain support from microsoft for an affordable
> price (I'm a freelance consultant and MAPS subscriber and I already had to
> spend much money for the RAID disks to get recovered and can't spend a
> fortune on this).
> thanks to you all!
> Alessandro|||as stated in a previous post in microsoft.public.it.sq
(http://groups.google.it/group/microsoft.public.it.sql/browse_frm/thread/4a416e2d1cbfdb89/bd56532ea25dab83?q=tiberti+sql&rnum=4&hl=it#bd56532ea25dab83),
I creted a new instance, created a new db in it with the STS_content name
(the same as the previous one), stopped the instance, replaced the new empty
files (mdf and ldf) with the old full ones, brought back sql service online
and then tried to remove the torn page check following the procedure
explained at
http://www.google.com/url?sa=D&q=http://www.google.it/groups%3Fselm%3D356mcjF4klli...%40individual.net
but I was unsuccesful:
USE master
GO
ALTER DATABASE STS_content
SET TORN_PAGE_DETECTION OFF
USE master
GO
sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_resetstatus STS_content
sp_configure 'allow updates', 0
GO
RECONFIGURE WITH OVERRIDE
GO
but the DB still remains in emergency status
I then received other directions to follow:
http://www.google.com/url?sa=D&q=http://www.google.it/groups%3Fselm%3Dbbo917%24bvd1...%40ID-154627.news.dfncis.de
but when a try to rebuild the DB I get:
[Microsoft][ODBC SQL Server Driver][DBMSLPCN]ConnectionChe­ckForData
(CheckforData()).
Server: messaggio 11, livello 16, stato 1, riga 0
Errore di rete generico. Controllare la documentazione della rete.
Connessione interrotta.
if you need the english translations of the messages, let me know.
<markandersen@.evare.com> ha scritto nel messaggio
news:1120589924.726106.226510@.g44g2000cwa.googlegroups.com...
> It would be helfpul to know in more detail what you have done.
> Is it really the master database which is corrupted or another
> database?
> Only the mdf file, not the ldf file right?
> And you're unable to do any recovery of the file whatsoever?
> Mark
>|||Tibor (hope that's your first name),
the problem is: being unexperienced with SQL I don't know how to get out
tables/objects from there... the only thing I was able to do following some
directions was to start the DB in emergency mode so the DB was accessible...
I had some mail exchange with Mark that I'd love to share (after getting his
permission BTW) with others hoping that could be of any help. Greetings,
Alessandro
Alessandro,
Enterprise Manager has a section entitled
"Management/DatabaseMaintenancePlans". If you simply upgraded, then I would
look there to see about backups.
You could search your harddrive for files based on how recent they are as
another tool to locating backups. They don't have any own suffix, although
.TRN is a default suffix to look for.
I am unfamiliar with sqlrecovery. When I searched for it I found
www.sql-server-repair.com which might be of interest to you. also
www.sqlrecovery.net. What is your sqlrecovery tool?
Mark Andersen
--Original Message--
From: Alessandro Tiberti [mailto:alessandro@.tiberti.it]
Sent: Friday, July 08, 2005 10:33 AM
To: Andersen, Mark
Subject: R: Power failure->Sharepoint DB with torn page
Sorry for bring so ignorant about that subject. i was using the basic
sharepoint db installation (which uses msde) and then upgraded to SQL. I don't
know if there were transaction logs backups or not, how can I check it?
Enterprise manager? Looking for files with particular extension?
Greetings,
Alessandro
PS: the pdf you saw is the report about the data that the recovery service
could export from the corrupt db using a quite common and well known
software called sqlrecovery divided DB by DB and I sent it to you just to
make you see the difference between the data they are able to export and the
size of the DB (most probably 'cause sqlrecovery doesn't recognize files
stored in the SQL db as they are probably stored in a proprietary
format/data type):
----
Da: Andersen, Mark [mailto:MarkA@.evare.com]
Inviato: venerdì 8 luglio 2005 16.15
A: Alessandro Tiberti
Oggetto: RE: Power failure->Sharepoint DB with torn page
You can share these emails.
I could not tell from the attachment what all those files are.
Did you have log backups running regularly on yoru server? You should
have had a dbmaintenance plan running (or some other script) every X hours
backing up the transaction logs. This is usually done more frequently than
a full backup.
The restore procedure is to get the last good full backup (not the .mdf
file but the sql backup!) and then restore it, followed by all log backups.
You can restore at least to the point of the torn page, perhaps further.
If you do not have any full or log backups and all you have is an mdf
file, the best I can suggest is that you go back to the last .mdf file which
you backed up. Presumably you backup the .mdf and .ldf files from time to
time. However, I am unsure that such a backup will really work unless sql
server was offline when the .mdf and .ldf file were backed up as you would
have two files which are not synchronized.
***
Do you have a full database backup? Do you have a series of transaction
log backups/dumps?
Mark Andersen
781-270-5501 (work)
781-307-1944 (cell)
--Original Message--
From: Alessandro Tiberti [mailto:alessandro@.tiberti.it]
Sent: Friday, July 08, 2005 5:26 AM
To: Andersen, Mark
Subject: R: Power failure->Sharepoint DB with torn page
First of all thanks for helping.
What I was actually trying to do is to start the db in emergency mode
and try to remove the torn page detection and try to access it just to
recover the data from sharepoint. Probably something would be missing but
most of them should be ok. but as I am not a SQL specialist I had troubles
following pthe procedures to fix that (I was only able to make the db in
emergency mode). Another chance would be to bring up the db in emergency
mode and copy all the data/tables/don't know what's the appropriate word
into another fresh db and then bring it up in place of the corrupted one.
These should be the things that most probably would help me stating to
what other italian sql MVPs told me. I also tried to ask for data recovery
but I saw that the data that can be restored is probably unuseful looking at
it's dimension (see the attached pdf file) as many picture images and other
files where in the sharepoint repository (so in the DB itself). Anyway,
2/3MBs out of 106 are nearly nothing, I would probably get more from the feb
backup.
Greetings once more!
Alessandro
PS: I'd like to share all this with the NG, would you mind as these were
private emails?
----
Da: Andersen, Mark [mailto:MarkA@.evare.com]
Inviato: giovedì 7 luglio 2005 22.05
A: Alessandro Tiberti
Oggetto: RE: Power failure->Sharepoint DB with torn page
Got the file. Yes, I get a torn page error trying to restore from the
.mdf file.
If that occurs, I believe you have to restore from your last known
backup and transaction logs. I guess that is what i thought you were going
to provide. Have you tried to restore from that?
If you don't have transaction log backups then you cannot restore.
There might be companies which could edit your file and try to restore it
that way, but that's a fairly specialized service.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> ha
scritto nel messaggio news:%23AKtRvfgFHA.2896@.TK2MSFTNGP09.phx.gbl...
> What you should have done it to do regular transaction log backups. If
> you'd done that, you could now just do a log backup, restore the most
> recent clean db backup and then all log backup. Zero data loss.
> In your situation, you could consider exporting all tables and objects to
> a new database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/sql

Power failure->Sharepoint DB with torn page

Hi guys!
I'd really appreciate some help and directions about a sql server issue: I
had failure on my server (the fan of a cpu's got broken so the system
ungracefully stopped due to the overheat protection of the CPU). It's running
SBS 2003 premium with sharepoint on SQL 2000.
I was able to get the system back online (had some trobules with the RAID
array but got it fixed) and I was able to have a snapshot of the files just
in case I should be in need to get back to the "original" situation.
Due to the RAID unalignment, my DBs are someway corrupt and I've got a 106MB
sharepoint DB that I'd love to recover.
I already tried to ask for help in the italian sql server newsgroup but no
one was able to help me. What's more, I can't ask for microsoft online
support as they only support english platforms (perhaps I could give it a
try, I mean, the problem would still be exactly the same).
Now the situation is that I've got the mdf and ldf files (I can't use
backups as I need some recent data that still wasn't backed up) and the DB
has a torn page that I'm unable (don't know how) to recover from. Here's what
I get in the event viewer:
Tipo evento: Errore
Origine evento: MSSQL$SHAREPOINT
Categoria evento: (2)
ID evento: 17055
Data: 11/06/2005
Ora: 12.46.51
Utente: TIBERTI\Administrator
Computer: TWINTIB
Descrizione:
18052 :
Errore: 823, gravitX: 24, stato: 2.
Per ulteriori informazioni, consultare la Guida in linea e supporto tecnico
all'indirizzo http://go.microsoft.com/fwlink/events.asp.
Dati:
0000: 84 46 00 00 10 00 00 00 ?F.....
0008: 13 00 00 00 54 00 57 00 ...T.W.
0010: 49 00 4e 00 54 00 49 00 I.N.T.I.
0018: 42 00 5c 00 53 00 48 00 B.\.S.H.
0020: 41 00 52 00 45 00 50 00 A.R.E.P.
0028: 4f 00 49 00 4e 00 54 00 O.I.N.T.
0030: 00 00 0a 00 00 00 6f 00 .....o.
0038: 6c 00 64 00 6d 00 61 00 l.d.m.a.
0040: 73 00 74 00 65 00 72 00 s.t.e.r.
0048: 00 00 ..
Tipo evento: Errore
Origine evento: MSSQL$SHAREPOINT
Categoria evento: (2)
ID evento: 17052
Data: 11/06/2005
Ora: 12.46.51
Utente: TIBERTI\Administrator
Computer: TWINTIB
Descrizione:
Errore: 823, gravitX: 24, stato: 2
I/O error (torn page) detected during read at offset 0x00000000318000 in
file 'F:\Programmi\Microsoft SQL
Server\MSSQL$SHAREPOINT\Data\oldmaster.mdf'.
Per ulteriori informazioni, consultare la Guida in linea e supporto tecnico
all'indirizzo http://go.microsoft.com/fwlink/events.asp.
Dati:
0000: 37 03 00 00 18 00 00 00 7......
0008: 13 00 00 00 54 00 57 00 ...T.W.
0010: 49 00 4e 00 54 00 49 00 I.N.T.I.
0018: 42 00 5c 00 53 00 48 00 B.\.S.H.
0020: 41 00 52 00 45 00 50 00 A.R.E.P.
0028: 4f 00 49 00 4e 00 54 00 O.I.N.T.
0030: 00 00 0a 00 00 00 6f 00 .....o.
0038: 6c 00 64 00 6d 00 61 00 l.d.m.a.
0040: 73 00 74 00 65 00 72 00 s.t.e.r.
0048: 00 00 ..
Tipo evento: Errore
Origine evento: MSSQL$SHAREPOINT
Categoria evento: (2)
ID evento: 17052
Data: 11/06/2005
Ora: 12.48.06
Utente: TIBERTI\Administrator
Computer: TWINTIB
Descrizione:
Errore: 823, gravitX: 24, stato: 6
I/O error (torn page) detected during read at offset 0000000000000000 in
file 'F:\Programmi\Microsoft SQL
Server\MSSQL$SHAREPOINT\Data\STS_content.mdf'.
Per ulteriori informazioni, consultare la Guida in linea e supporto tecnico
all'indirizzo http://go.microsoft.com/fwlink/events.asp.
Dati:
0000: 37 03 00 00 18 00 00 00 7......
0008: 13 00 00 00 54 00 57 00 ...T.W.
0010: 49 00 4e 00 54 00 49 00 I.N.T.I.
0018: 42 00 5c 00 53 00 48 00 B.\.S.H.
0020: 41 00 52 00 45 00 50 00 A.R.E.P.
0028: 4f 00 49 00 4e 00 54 00 O.I.N.T.
0030: 00 00 07 00 00 00 6d 00 .....m.
0038: 61 00 73 00 74 00 65 00 a.s.t.e.
0040: 72 00 00 00 r...
I've already tried with tools to recover data from DBs (SQLRecovery) but it
obviusly wasn't able to recover the data saved from sharepoint...
What I'm looking for is: some directions (I am a system engineer even if I
don't work on databases but I can deal with that under correct guidance) on
how to fix this or how to obtain support from microsoft for an affordable
price (I'm a freelance consultant and MAPS subscriber and I already had to
spend much money for the RAID disks to get recovered and can't spend a
fortune on this).
thanks to you all!
Alessandro
It would be helfpul to know in more detail what you have done.
Is it really the master database which is corrupted or another
database?
Only the mdf file, not the ldf file right?
And you're unable to do any recovery of the file whatsoever?
Mark
|||Hi Mark,
First of all thanks for answering. Coming to your questions to help me:
The master DB is not ok but that's not a problem. Once in the past I was
able to recover a sharepoint site from the DBs that conteined it just by
installing a new sql server with the same previous name, attaching the old
(good) content DB and make sharepoint point to it. And, I was able to
install a new instance of the SQL server, create a config and contents db
(those that are used by sharepoint), stop the sql server instance, replace
the "empty" files with the one I recovered and that's how I got into the
torn page error.
I have both the mdf and ldf files. I'm able to recover old copies of the DBs
(including the master) but in which the contents are not up to date.
If you should need further informations, please ask.
Greetings, and thank once again,
Alessandro
<markandersen@.evare.com> ha scritto nel messaggio
news:1120589924.726106.226510@.g44g2000cwa.googlegr oups.com...
> It would be helfpul to know in more detail what you have done.
> Is it really the master database which is corrupted or another
> database?
> Only the mdf file, not the ldf file right?
> And you're unable to do any recovery of the file whatsoever?
> Mark
>
|||What you should have done it to do regular transaction log backups. If you'd done that, you could
now just do a log backup, restore the most recent clean db backup and then all log backup. Zero data
loss.
In your situation, you could consider exporting all tables and objects to a new database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Alessandro Tiberti" <Alessandro Tiberti@.discussions.microsoft.com> wrote in message
news:A3A7DE8B-5FFE-4BAE-987C-9B634CF6E219@.microsoft.com...
> Hi guys!
> I'd really appreciate some help and directions about a sql server issue: I
> had failure on my server (the fan of a cpu's got broken so the system
> ungracefully stopped due to the overheat protection of the CPU). It's running
> SBS 2003 premium with sharepoint on SQL 2000.
> I was able to get the system back online (had some trobules with the RAID
> array but got it fixed) and I was able to have a snapshot of the files just
> in case I should be in need to get back to the "original" situation.
> Due to the RAID unalignment, my DBs are someway corrupt and I've got a 106MB
> sharepoint DB that I'd love to recover.
> I already tried to ask for help in the italian sql server newsgroup but no
> one was able to help me. What's more, I can't ask for microsoft online
> support as they only support english platforms (perhaps I could give it a
> try, I mean, the problem would still be exactly the same).
> Now the situation is that I've got the mdf and ldf files (I can't use
> backups as I need some recent data that still wasn't backed up) and the DB
> has a torn page that I'm unable (don't know how) to recover from. Here's what
> I get in the event viewer:
> Tipo evento: Errore
> Origine evento: MSSQL$SHAREPOINT
> Categoria evento: (2)
> ID evento: 17055
> Data: 11/06/2005
> Ora: 12.46.51
> Utente: TIBERTI\Administrator
> Computer: TWINTIB
> Descrizione:
> 18052 :
> Errore: 823, gravitX: 24, stato: 2.
>
> Per ulteriori informazioni, consultare la Guida in linea e supporto tecnico
> all'indirizzo http://go.microsoft.com/fwlink/events.asp.
> Dati:
> 0000: 84 46 00 00 10 00 00 00 ?F.....
> 0008: 13 00 00 00 54 00 57 00 ...T.W.
> 0010: 49 00 4e 00 54 00 49 00 I.N.T.I.
> 0018: 42 00 5c 00 53 00 48 00 B.\.S.H.
> 0020: 41 00 52 00 45 00 50 00 A.R.E.P.
> 0028: 4f 00 49 00 4e 00 54 00 O.I.N.T.
> 0030: 00 00 0a 00 00 00 6f 00 .....o.
> 0038: 6c 00 64 00 6d 00 61 00 l.d.m.a.
> 0040: 73 00 74 00 65 00 72 00 s.t.e.r.
> 0048: 00 00 ..
> Tipo evento: Errore
> Origine evento: MSSQL$SHAREPOINT
> Categoria evento: (2)
> ID evento: 17052
> Data: 11/06/2005
> Ora: 12.46.51
> Utente: TIBERTI\Administrator
> Computer: TWINTIB
> Descrizione:
> Errore: 823, gravitX: 24, stato: 2
> I/O error (torn page) detected during read at offset 0x00000000318000 in
> file 'F:\Programmi\Microsoft SQL
> Server\MSSQL$SHAREPOINT\Data\oldmaster.mdf'.
> Per ulteriori informazioni, consultare la Guida in linea e supporto tecnico
> all'indirizzo http://go.microsoft.com/fwlink/events.asp.
> Dati:
> 0000: 37 03 00 00 18 00 00 00 7......
> 0008: 13 00 00 00 54 00 57 00 ...T.W.
> 0010: 49 00 4e 00 54 00 49 00 I.N.T.I.
> 0018: 42 00 5c 00 53 00 48 00 B.\.S.H.
> 0020: 41 00 52 00 45 00 50 00 A.R.E.P.
> 0028: 4f 00 49 00 4e 00 54 00 O.I.N.T.
> 0030: 00 00 0a 00 00 00 6f 00 .....o.
> 0038: 6c 00 64 00 6d 00 61 00 l.d.m.a.
> 0040: 73 00 74 00 65 00 72 00 s.t.e.r.
> 0048: 00 00 ..
> Tipo evento: Errore
> Origine evento: MSSQL$SHAREPOINT
> Categoria evento: (2)
> ID evento: 17052
> Data: 11/06/2005
> Ora: 12.48.06
> Utente: TIBERTI\Administrator
> Computer: TWINTIB
> Descrizione:
> Errore: 823, gravitX: 24, stato: 6
> I/O error (torn page) detected during read at offset 0000000000000000 in
> file 'F:\Programmi\Microsoft SQL
> Server\MSSQL$SHAREPOINT\Data\STS_content.mdf'.
> Per ulteriori informazioni, consultare la Guida in linea e supporto tecnico
> all'indirizzo http://go.microsoft.com/fwlink/events.asp.
> Dati:
> 0000: 37 03 00 00 18 00 00 00 7......
> 0008: 13 00 00 00 54 00 57 00 ...T.W.
> 0010: 49 00 4e 00 54 00 49 00 I.N.T.I.
> 0018: 42 00 5c 00 53 00 48 00 B.\.S.H.
> 0020: 41 00 52 00 45 00 50 00 A.R.E.P.
> 0028: 4f 00 49 00 4e 00 54 00 O.I.N.T.
> 0030: 00 00 07 00 00 00 6d 00 .....m.
> 0038: 61 00 73 00 74 00 65 00 a.s.t.e.
> 0040: 72 00 00 00 r...
> I've already tried with tools to recover data from DBs (SQLRecovery) but it
> obviusly wasn't able to recover the data saved from sharepoint...
> What I'm looking for is: some directions (I am a system engineer even if I
> don't work on databases but I can deal with that under correct guidance) on
> how to fix this or how to obtain support from microsoft for an affordable
> price (I'm a freelance consultant and MAPS subscriber and I already had to
> spend much money for the RAID disks to get recovered and can't spend a
> fortune on this).
> thanks to you all!
> Alessandro
|||as stated in a previous post in microsoft.public.it.sq
(http://groups.google.it/group/micros...53 2ea25dab83),
I creted a new instance, created a new db in it with the STS_content name
(the same as the previous one), stopped the instance, replaced the new empty
files (mdf and ldf) with the old full ones, brought back sql service online
and then tried to remove the torn page check following the procedure
explained at
http://www.google.com/url?sa=D&q=htt...individual.net
but I was unsuccesful:
USE master
GO
ALTER DATABASE STS_content
SET TORN_PAGE_DETECTION OFF
USE master
GO
sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_resetstatus STS_content
sp_configure 'allow updates', 0
GO
RECONFIGURE WITH OVERRIDE
GO
but the DB still remains in emergency status
I then received other directions to follow:
http://www.google.com/url?sa=D&q=htt...news.dfncis.de
but when a try to rebuild the DB I get:
[Microsoft][ODBC SQL Server Driver][DBMSLPCN]ConnectionCheXckForData
(CheckforData()).
Server: messaggio 11, livello 16, stato 1, riga 0
Errore di rete generico. Controllare la documentazione della rete.
Connessione interrotta.
if you need the english translations of the messages, let me know.
<markandersen@.evare.com> ha scritto nel messaggio
news:1120589924.726106.226510@.g44g2000cwa.googlegr oups.com...
> It would be helfpul to know in more detail what you have done.
> Is it really the master database which is corrupted or another
> database?
> Only the mdf file, not the ldf file right?
> And you're unable to do any recovery of the file whatsoever?
> Mark
>
|||Tibor (hope that's your first name),
the problem is: being unexperienced with SQL I don't know how to get out
tables/objects from there... the only thing I was able to do following some
directions was to start the DB in emergency mode so the DB was accessible...
I had some mail exchange with Mark that I'd love to share (after getting his
permission BTW) with others hoping that could be of any help. Greetings,
Alessandro
Alessandro,
Enterprise Manager has a section entitled
"Management/DatabaseMaintenancePlans". If you simply upgraded, then I would
look there to see about backups.
You could search your harddrive for files based on how recent they are as
another tool to locating backups. They don't have any own suffix, although
..TRN is a default suffix to look for.
I am unfamiliar with sqlrecovery. When I searched for it I found
www.sql-server-repair.com which might be of interest to you. also
www.sqlrecovery.net. What is your sqlrecovery tool?
Mark Andersen
--Original Message--
From: Alessandro Tiberti [mailto:alessandro@.tiberti.it]
Sent: Friday, July 08, 2005 10:33 AM
To: Andersen, Mark
Subject: R: Power failure->Sharepoint DB with torn page
Sorry for bring so ignorant about that subject. i was using the basic
sharepoint db installation (which uses msde) and then upgraded to SQL. I don't
know if there were transaction logs backups or not, how can I check it?
Enterprise manager? Looking for files with particular extension?
Greetings,
Alessandro
PS: the pdf you saw is the report about the data that the recovery service
could export from the corrupt db using a quite common and well known
software called sqlrecovery divided DB by DB and I sent it to you just to
make you see the difference between the data they are able to export and the
size of the DB (most probably 'cause sqlrecovery doesn't recognize files
stored in the SQL db as they are probably stored in a proprietary
format/data type):

Da: Andersen, Mark [mailto:MarkA@.evare.com]
Inviato: venerd 8 luglio 2005 16.15
A: Alessandro Tiberti
Oggetto: RE: Power failure->Sharepoint DB with torn page
You can share these emails.
I could not tell from the attachment what all those files are.
Did you have log backups running regularly on yoru server? You should
have had a dbmaintenance plan running (or some other script) every X hours
backing up the transaction logs. This is usually done more frequently than
a full backup.
The restore procedure is to get the last good full backup (not the .mdf
file but the sql backup!) and then restore it, followed by all log backups.
You can restore at least to the point of the torn page, perhaps further.
If you do not have any full or log backups and all you have is an mdf
file, the best I can suggest is that you go back to the last .mdf file which
you backed up. Presumably you backup the .mdf and .ldf files from time to
time. However, I am unsure that such a backup will really work unless sql
server was offline when the .mdf and .ldf file were backed up as you would
have two files which are not synchronized.
***
Do you have a full database backup? Do you have a series of transaction
log backups/dumps?
Mark Andersen
781-270-5501 (work)
781-307-1944 (cell)
--Original Message--
From: Alessandro Tiberti [mailto:alessandro@.tiberti.it]
Sent: Friday, July 08, 2005 5:26 AM
To: Andersen, Mark
Subject: R: Power failure->Sharepoint DB with torn page
First of all thanks for helping.
What I was actually trying to do is to start the db in emergency mode
and try to remove the torn page detection and try to access it just to
recover the data from sharepoint. Probably something would be missing but
most of them should be ok. but as I am not a SQL specialist I had troubles
following pthe procedures to fix that (I was only able to make the db in
emergency mode). Another chance would be to bring up the db in emergency
mode and copy all the data/tables/don't know what's the appropriate word
into another fresh db and then bring it up in place of the corrupted one.
These should be the things that most probably would help me stating to
what other italian sql MVPs told me. I also tried to ask for data recovery
but I saw that the data that can be restored is probably unuseful looking at
it's dimension (see the attached pdf file) as many picture images and other
files where in the sharepoint repository (so in the DB itself). Anyway,
2/3MBs out of 106 are nearly nothing, I would probably get more from the feb
backup.
Greetings once more!
Alessandro
PS: I'd like to share all this with the NG, would you mind as these were
private emails?

Da: Andersen, Mark [mailto:MarkA@.evare.com]
Inviato: gioved 7 luglio 2005 22.05
A: Alessandro Tiberti
Oggetto: RE: Power failure->Sharepoint DB with torn page
Got the file. Yes, I get a torn page error trying to restore from the
..mdf file.
If that occurs, I believe you have to restore from your last known
backup and transaction logs. I guess that is what i thought you were going
to provide. Have you tried to restore from that?
If you don't have transaction log backups then you cannot restore.
There might be companies which could edit your file and try to restore it
that way, but that's a fairly specialized service.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> ha
scritto nel messaggio news:%23AKtRvfgFHA.2896@.TK2MSFTNGP09.phx.gbl...
> What you should have done it to do regular transaction log backups. If
> you'd done that, you could now just do a log backup, restore the most
> recent clean db backup and then all log backup. Zero data loss.
> In your situation, you could consider exporting all tables and objects to
> a new database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/

Power failure->Sharepoint DB with torn page

Hi guys!
I'd really appreciate some help and directions about a sql server issue: I
had failure on my server (the fan of a cpu's got broken so the system
ungracefully stopped due to the overheat protection of the CPU). It's runnin
g
SBS 2003 premium with sharepoint on SQL 2000.
I was able to get the system back online (had some trobules with the RAID
array but got it fixed) and I was able to have a snapshot of the files just
in case I should be in need to get back to the "original" situation.
Due to the RAID unalignment, my DBs are someway corrupt and I've got a 106MB
sharepoint DB that I'd love to recover.
I already tried to ask for help in the italian sql server newsgroup but no
one was able to help me. What's more, I can't ask for microsoft online
support as they only support english platforms (perhaps I could give it a
try, I mean, the problem would still be exactly the same).
Now the situation is that I've got the mdf and ldf files (I can't use
backups as I need some recent data that still wasn't backed up) and the DB
has a torn page that I'm unable (don't know how) to recover from. Here's wha
t
I get in the event viewer:
Tipo evento: Errore
Origine evento: MSSQL$SHAREPOINT
Categoria evento: (2)
ID evento: 17055
Data: 11/06/2005
Ora: 12.46.51
Utente: TIBERTI\Administrator
Computer: TWINTIB
Descrizione:
18052 :
Errore: 823, gravit_: 24, stato: 2.
Per ulteriori informazioni, consultare la Guida in linea e supporto tecnico
all'indirizzo http://go.microsoft.com/fwlink/events.asp.
Dati:
0000: 84 46 00 00 10 00 00 00 ?F.....
0008: 13 00 00 00 54 00 57 00 ...T.W.
0010: 49 00 4e 00 54 00 49 00 I.N.T.I.
0018: 42 00 5c 00 53 00 48 00 B.\.S.H.
0020: 41 00 52 00 45 00 50 00 A.R.E.P.
0028: 4f 00 49 00 4e 00 54 00 O.I.N.T.
0030: 00 00 0a 00 00 00 6f 00 .....o.
0038: 6c 00 64 00 6d 00 61 00 l.d.m.a.
0040: 73 00 74 00 65 00 72 00 s.t.e.r.
0048: 00 00 ..
Tipo evento: Errore
Origine evento: MSSQL$SHAREPOINT
Categoria evento: (2)
ID evento: 17052
Data: 11/06/2005
Ora: 12.46.51
Utente: TIBERTI\Administrator
Computer: TWINTIB
Descrizione:
Errore: 823, gravit_: 24, stato: 2
I/O error (torn page) detected during read at offset 0x00000000318000 in
file 'F:\Programmi\Microsoft SQL
Server\MSSQL$SHAREPOINT\Data\oldmaster.mdf'.
Per ulteriori informazioni, consultare la Guida in linea e supporto tecnico
all'indirizzo http://go.microsoft.com/fwlink/events.asp.
Dati:
0000: 37 03 00 00 18 00 00 00 7......
0008: 13 00 00 00 54 00 57 00 ...T.W.
0010: 49 00 4e 00 54 00 49 00 I.N.T.I.
0018: 42 00 5c 00 53 00 48 00 B.\.S.H.
0020: 41 00 52 00 45 00 50 00 A.R.E.P.
0028: 4f 00 49 00 4e 00 54 00 O.I.N.T.
0030: 00 00 0a 00 00 00 6f 00 .....o.
0038: 6c 00 64 00 6d 00 61 00 l.d.m.a.
0040: 73 00 74 00 65 00 72 00 s.t.e.r.
0048: 00 00 ..
Tipo evento: Errore
Origine evento: MSSQL$SHAREPOINT
Categoria evento: (2)
ID evento: 17052
Data: 11/06/2005
Ora: 12.48.06
Utente: TIBERTI\Administrator
Computer: TWINTIB
Descrizione:
Errore: 823, gravit_: 24, stato: 6
I/O error (torn page) detected during read at offset 0000000000000000 in
file 'F:\Programmi\Microsoft SQL
Server\MSSQL$SHAREPOINT\Data\STS_content
.mdf'.
Per ulteriori informazioni, consultare la Guida in linea e supporto tecnico
all'indirizzo http://go.microsoft.com/fwlink/events.asp.
Dati:
0000: 37 03 00 00 18 00 00 00 7......
0008: 13 00 00 00 54 00 57 00 ...T.W.
0010: 49 00 4e 00 54 00 49 00 I.N.T.I.
0018: 42 00 5c 00 53 00 48 00 B.\.S.H.
0020: 41 00 52 00 45 00 50 00 A.R.E.P.
0028: 4f 00 49 00 4e 00 54 00 O.I.N.T.
0030: 00 00 07 00 00 00 6d 00 .....m.
0038: 61 00 73 00 74 00 65 00 a.s.t.e.
0040: 72 00 00 00 r...
I've already tried with tools to recover data from DBs (SQLRecovery) but it
obviusly wasn't able to recover the data saved from sharepoint...
What I'm looking for is: some directions (I am a system engineer even if I
don't work on databases but I can deal with that under correct guidance) on
how to fix this or how to obtain support from microsoft for an affordable
price (I'm a freelance consultant and MAPS subscriber and I already had to
spend much money for the RAID disks to get recovered and can't spend a
fortune on this).
thanks to you all!
AlessandroIt would be helfpul to know in more detail what you have done.
Is it really the master database which is corrupted or another
database?
Only the mdf file, not the ldf file right?
And you're unable to do any recovery of the file whatsoever?
Mark|||Hi Mark,
First of all thanks for answering. Coming to your questions to help me:
The master DB is not ok but that's not a problem. Once in the past I was
able to recover a sharepoint site from the DBs that conteined it just by
installing a new sql server with the same previous name, attaching the old
(good) content DB and make sharepoint point to it. And, I was able to
install a new instance of the SQL server, create a config and contents db
(those that are used by sharepoint), stop the sql server instance, replace
the "empty" files with the one I recovered and that's how I got into the
torn page error.
I have both the mdf and ldf files. I'm able to recover old copies of the DBs
(including the master) but in which the contents are not up to date.
If you should need further informations, please ask.
Greetings, and thank once again,
Alessandro
<markandersen@.evare.com> ha scritto nel messaggio
news:1120589924.726106.226510@.g44g2000cwa.googlegroups.com...
> It would be helfpul to know in more detail what you have done.
> Is it really the master database which is corrupted or another
> database?
> Only the mdf file, not the ldf file right?
> And you're unable to do any recovery of the file whatsoever?
> Mark
>|||What you should have done it to do regular transaction log backups. If you'd
done that, you could
now just do a log backup, restore the most recent clean db backup and then a
ll log backup. Zero data
loss.
In your situation, you could consider exporting all tables and objects to a
new database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Alessandro Tiberti" <Alessandro Tiberti@.discussions.microsoft.com> wrote in
message
news:A3A7DE8B-5FFE-4BAE-987C-9B634CF6E219@.microsoft.com...
> Hi guys!
> I'd really appreciate some help and directions about a sql server issue: I
> had failure on my server (the fan of a cpu's got broken so the system
> ungracefully stopped due to the overheat protection of the CPU). It's runn
ing
> SBS 2003 premium with sharepoint on SQL 2000.
> I was able to get the system back online (had some trobules with the RAID
> array but got it fixed) and I was able to have a snapshot of the files jus
t
> in case I should be in need to get back to the "original" situation.
> Due to the RAID unalignment, my DBs are someway corrupt and I've got a 106
MB
> sharepoint DB that I'd love to recover.
> I already tried to ask for help in the italian sql server newsgroup but no
> one was able to help me. What's more, I can't ask for microsoft online
> support as they only support english platforms (perhaps I could give it a
> try, I mean, the problem would still be exactly the same).
> Now the situation is that I've got the mdf and ldf files (I can't use
> backups as I need some recent data that still wasn't backed up) and the DB
> has a torn page that I'm unable (don't know how) to recover from. Here's w
hat
> I get in the event viewer:
> Tipo evento: Errore
> Origine evento: MSSQL$SHAREPOINT
> Categoria evento: (2)
> ID evento: 17055
> Data: 11/06/2005
> Ora: 12.46.51
> Utente: TIBERTI\Administrator
> Computer: TWINTIB
> Descrizione:
> 18052 :
> Errore: 823, gravit_: 24, stato: 2.
>
> Per ulteriori informazioni, consultare la Guida in linea e supporto tecnic
o
> all'indirizzo http://go.microsoft.com/fwlink/events.asp.
> Dati:
> 0000: 84 46 00 00 10 00 00 00 ?F.....
> 0008: 13 00 00 00 54 00 57 00 ...T.W.
> 0010: 49 00 4e 00 54 00 49 00 I.N.T.I.
> 0018: 42 00 5c 00 53 00 48 00 B.\.S.H.
> 0020: 41 00 52 00 45 00 50 00 A.R.E.P.
> 0028: 4f 00 49 00 4e 00 54 00 O.I.N.T.
> 0030: 00 00 0a 00 00 00 6f 00 .....o.
> 0038: 6c 00 64 00 6d 00 61 00 l.d.m.a.
> 0040: 73 00 74 00 65 00 72 00 s.t.e.r.
> 0048: 00 00 ..
> Tipo evento: Errore
> Origine evento: MSSQL$SHAREPOINT
> Categoria evento: (2)
> ID evento: 17052
> Data: 11/06/2005
> Ora: 12.46.51
> Utente: TIBERTI\Administrator
> Computer: TWINTIB
> Descrizione:
> Errore: 823, gravit_: 24, stato: 2
> I/O error (torn page) detected during read at offset 0x00000000318000 in
> file 'F:\Programmi\Microsoft SQL
> Server\MSSQL$SHAREPOINT\Data\oldmaster.mdf'.
> Per ulteriori informazioni, consultare la Guida in linea e supporto tecnic
o
> all'indirizzo http://go.microsoft.com/fwlink/events.asp.
> Dati:
> 0000: 37 03 00 00 18 00 00 00 7......
> 0008: 13 00 00 00 54 00 57 00 ...T.W.
> 0010: 49 00 4e 00 54 00 49 00 I.N.T.I.
> 0018: 42 00 5c 00 53 00 48 00 B.\.S.H.
> 0020: 41 00 52 00 45 00 50 00 A.R.E.P.
> 0028: 4f 00 49 00 4e 00 54 00 O.I.N.T.
> 0030: 00 00 0a 00 00 00 6f 00 .....o.
> 0038: 6c 00 64 00 6d 00 61 00 l.d.m.a.
> 0040: 73 00 74 00 65 00 72 00 s.t.e.r.
> 0048: 00 00 ..
> Tipo evento: Errore
> Origine evento: MSSQL$SHAREPOINT
> Categoria evento: (2)
> ID evento: 17052
> Data: 11/06/2005
> Ora: 12.48.06
> Utente: TIBERTI\Administrator
> Computer: TWINTIB
> Descrizione:
> Errore: 823, gravit_: 24, stato: 6
> I/O error (torn page) detected during read at offset 0000000000000000 in
> file 'F:\Programmi\Microsoft SQL
> Server\MSSQL$SHAREPOINT\Data\STS_content
.mdf'.
> Per ulteriori informazioni, consultare la Guida in linea e supporto tecnic
o
> all'indirizzo http://go.microsoft.com/fwlink/events.asp.
> Dati:
> 0000: 37 03 00 00 18 00 00 00 7......
> 0008: 13 00 00 00 54 00 57 00 ...T.W.
> 0010: 49 00 4e 00 54 00 49 00 I.N.T.I.
> 0018: 42 00 5c 00 53 00 48 00 B.\.S.H.
> 0020: 41 00 52 00 45 00 50 00 A.R.E.P.
> 0028: 4f 00 49 00 4e 00 54 00 O.I.N.T.
> 0030: 00 00 07 00 00 00 6d 00 .....m.
> 0038: 61 00 73 00 74 00 65 00 a.s.t.e.
> 0040: 72 00 00 00 r...
> I've already tried with tools to recover data from DBs (SQLRecovery) but i
t
> obviusly wasn't able to recover the data saved from sharepoint...
> What I'm looking for is: some directions (I am a system engineer even if I
> don't work on databases but I can deal with that under correct guidance) o
n
> how to fix this or how to obtain support from microsoft for an affordable
> price (I'm a freelance consultant and MAPS subscriber and I already had to
> spend much money for the RAID disks to get recovered and can't spend a
> fortune on this).
> thanks to you all!
> Alessandro|||as stated in a previous post in microsoft.public.it.sq
(http://groups.google.it/group/micro...d56532ea25dab83),
I creted a new instance, created a new db in it with the STS_content name
(the same as the previous one), stopped the instance, replaced the new empty
files (mdf and ldf) with the old full ones, brought back sql service online
and then tried to remove the torn page check following the procedure
explained at
http://www.google.com/url?sa=D&q=ht...0individual.net
but I was unsuccesful:
USE master
GO
ALTER DATABASE STS_content
SET TORN_PAGE_DETECTION OFF
USE master
GO
sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_resetstatus STS_content
sp_configure 'allow updates', 0
GO
RECONFIGURE WITH OVERRIDE
GO
but the DB still remains in emergency status
I then received other directions to follow:
http://www.google.com/url?sa=D&q=ht...news.dfncis.de
but when a try to rebuild the DB I get:
[Microsoft][ODBC SQL Server Driver][DBMSLPCN]ConnectionChe_ckFor
Data
(CheckforData()).
Server: messaggio 11, livello 16, stato 1, riga 0
Errore di rete generico. Controllare la documentazione della rete.
Connessione interrotta.
if you need the english translations of the messages, let me know.
<markandersen@.evare.com> ha scritto nel messaggio
news:1120589924.726106.226510@.g44g2000cwa.googlegroups.com...
> It would be helfpul to know in more detail what you have done.
> Is it really the master database which is corrupted or another
> database?
> Only the mdf file, not the ldf file right?
> And you're unable to do any recovery of the file whatsoever?
> Mark
>|||Tibor (hope that's your first name),
the problem is: being unexperienced with SQL I don't know how to get out
tables/objects from there... the only thing I was able to do following some
directions was to start the DB in emergency mode so the DB was accessible...
I had some mail exchange with Mark that I'd love to share (after getting his
permission BTW) with others hoping that could be of any help. Greetings,
Alessandro
Alessandro,
Enterprise Manager has a section entitled
"Management/DatabaseMaintenancePlans". If you simply upgraded, then I would
look there to see about backups.
You could search your harddrive for files based on how recent they are as
another tool to locating backups. They don't have any own suffix, although
.TRN is a default suffix to look for.
I am unfamiliar with sqlrecovery. When I searched for it I found
www.sql-server-repair.com which might be of interest to you. also
www.sqlrecovery.net. What is your sqlrecovery tool?
Mark Andersen
--Original Message--
From: Alessandro Tiberti [mailto:alessandro@.tiberti.it]
Sent: Friday, July 08, 2005 10:33 AM
To: Andersen, Mark
Subject: R: Power failure->Sharepoint DB with torn page
Sorry for bring so ignorant about that subject. i was using the basic
sharepoint db installation (which uses msde) and then upgraded to SQL. I don
't
know if there were transaction logs backups or not, how can I check it?
Enterprise manager? Looking for files with particular extension?
Greetings,
Alessandro
PS: the pdf you saw is the report about the data that the recovery service
could export from the corrupt db using a quite common and well known
software called sqlrecovery divided DB by DB and I sent it to you just to
make you see the difference between the data they are able to export and the
size of the DB (most probably 'cause sqlrecovery doesn't recognize files
stored in the SQL db as they are probably stored in a proprietary
format/data type):
----
--
Da: Andersen, Mark [mailto:MarkA@.evare.com]
Inviato: venerd 8 luglio 2005 16.15
A: Alessandro Tiberti
Oggetto: RE: Power failure->Sharepoint DB with torn page
You can share these emails.
I could not tell from the attachment what all those files are.
Did you have log backups running regularly on yoru server? You should
have had a dbmaintenance plan running (or some other script) every X hours
backing up the transaction logs. This is usually done more frequently than
a full backup.
The restore procedure is to get the last good full backup (not the .mdf
file but the sql backup!) and then restore it, followed by all log backups.
You can restore at least to the point of the torn page, perhaps further.
If you do not have any full or log backups and all you have is an mdf
file, the best I can suggest is that you go back to the last .mdf file which
you backed up. Presumably you backup the .mdf and .ldf files from time to
time. However, I am unsure that such a backup will really work unless sql
server was offline when the .mdf and .ldf file were backed up as you would
have two files which are not synchronized.
***
Do you have a full database backup? Do you have a series of transaction
log backups/dumps?
Mark Andersen
781-270-5501 (work)
781-307-1944 (cell)
--Original Message--
From: Alessandro Tiberti [mailto:alessandro@.tiberti.it]
Sent: Friday, July 08, 2005 5:26 AM
To: Andersen, Mark
Subject: R: Power failure->Sharepoint DB with torn page
First of all thanks for helping.
What I was actually trying to do is to start the db in emergency mode
and try to remove the torn page detection and try to access it just to
recover the data from sharepoint. Probably something would be missing but
most of them should be ok. but as I am not a SQL specialist I had troubles
following pthe procedures to fix that (I was only able to make the db in
emergency mode). Another chance would be to bring up the db in emergency
mode and copy all the data/tables/don't know what's the appropriate word
into another fresh db and then bring it up in place of the corrupted one.
These should be the things that most probably would help me stating to
what other italian sql MVPs told me. I also tried to ask for data recovery
but I saw that the data that can be restored is probably unuseful looking at
it's dimension (see the attached pdf file) as many picture images and other
files where in the sharepoint repository (so in the DB itself). Anyway,
2/3MBs out of 106 are nearly nothing, I would probably get more from the feb
backup.
Greetings once more!
Alessandro
PS: I'd like to share all this with the NG, would you mind as these were
private emails?
----
Da: Andersen, Mark [mailto:MarkA@.evare.com]
Inviato: gioved 7 luglio 2005 22.05
A: Alessandro Tiberti
Oggetto: RE: Power failure->Sharepoint DB with torn page
Got the file. Yes, I get a torn page error trying to restore from the
.mdf file.
If that occurs, I believe you have to restore from your last known
backup and transaction logs. I guess that is what i thought you were going
to provide. Have you tried to restore from that?
If you don't have transaction log backups then you cannot restore.
There might be companies which could edit your file and try to restore it
that way, but that's a fairly specialized service.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> ha
scritto nel messaggio news:%23AKtRvfgFHA.2896@.TK2MSFTNGP09.phx.gbl...
> What you should have done it to do regular transaction log backups. If
> you'd done that, you could now just do a log backup, restore the most
> recent clean db backup and then all log backup. Zero data loss.
> In your situation, you could consider exporting all tables and objects to
> a new database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/

Power failure using simple mode transaction Log

Any comments about what happens when we get a power failure using simple mode
transaction Log?
I had 3 Power failure and there has not been any problems with the data.
The auto recuperation informs that 5343 rows where commited and 0 rolled back.
Can I expect this behaviour in the future? (another power failure).
Note that I have a UPS, but I didn't have time to shutdown properly.
Run DBCC CHECKDB if you haven't already.
You should configure your UPS to shutdown the server automatically after N
minutes of power failure.
David Portas
SQL Server MVP
|||If you have hardware write cache, you need to make sure it is properly backed up, and follows a
certain set of rules. If it does, the database should not go corrupt because of a power failure. If
you don't have hardware write cache, then you probably don't have battery backup and you can see a
torn page. See http://www.microsoft.com/technet/pro...lIObasics.mspx for
full information.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"juan carlos via droptable.com" <forum@.droptable.com> wrote in message
news:51122050ED1E0@.droptable.com...
> Any comments about what happens when we get a power failure using simple mode
> transaction Log?
> I had 3 Power failure and there has not been any problems with the data.
> The auto recuperation informs that 5343 rows where commited and 0 rolled back.
>
> Can I expect this behaviour in the future? (another power failure).
> Note that I have a UPS, but I didn't have time to shutdown properly.

Power failure using simple mode transaction Log

Any comments about what happens when we get a power failure using simple mod
e
transaction Log?
I had 3 Power failure and there has not been any problems with the data.
The auto recuperation informs that 5343 rows where commited and 0 rolled bac
k.
Can I expect this behaviour in the future? (another power failure).
Note that I have a UPS, but I didn't have time to shutdown properly.Run DBCC CHECKDB if you haven't already.
You should configure your UPS to shutdown the server automatically after N
minutes of power failure.
David Portas
SQL Server MVP
--|||If you have hardware write cache, you need to make sure it is properly backe
d up, and follows a
certain set of rules. If it does, the database should not go corrupt because
of a power failure. If
you don't have hardware write cache, then you probably don't have battery ba
ckup and you can see a
torn page. See http://www.microsoft.com/technet/pr...>
Obasics.mspx for
full information.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"juan carlos via droptable.com" <forum@.droptable.com> wrote in message
news:51122050ED1E0@.droptable.com...
> Any comments about what happens when we get a power failure using simple m
ode
> transaction Log?
> I had 3 Power failure and there has not been any problems with the data.
> The auto recuperation informs that 5343 rows where commited and 0 rolled b
ack.
>
> Can I expect this behaviour in the future? (another power failure).
> Note that I have a UPS, but I didn't have time to shutdown properly.

Power failure using simple mode transaction Log

Any comments about what happens when we get a power failure using simple mode
transaction Log?
I had 3 Power failure and there has not been any problems with the data.
The auto recuperation informs that 5343 rows where commited and 0 rolled back.
Can I expect this behaviour in the future? (another power failure).
Note that I have a UPS, but I didn't have time to shutdown properly.Run DBCC CHECKDB if you haven't already.
You should configure your UPS to shutdown the server automatically after N
minutes of power failure.
--
David Portas
SQL Server MVP
--|||If you have hardware write cache, you need to make sure it is properly backed up, and follows a
certain set of rules. If it does, the database should not go corrupt because of a power failure. If
you don't have hardware write cache, then you probably don't have battery backup and you can see a
torn page. See http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx for
full information.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"juan carlos via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:51122050ED1E0@.SQLMonster.com...
> Any comments about what happens when we get a power failure using simple mode
> transaction Log?
> I had 3 Power failure and there has not been any problems with the data.
> The auto recuperation informs that 5343 rows where commited and 0 rolled back.
>
> Can I expect this behaviour in the future? (another power failure).
> Note that I have a UPS, but I didn't have time to shutdown properly.sql

Power Failure for SQL Server 2000

We encounter a power failure for about half an hour and
the SQL Server 2000 is back again.
For production databases, we use FULL Recovery Model with
Transaction Log backed up every half an hour. After power
is up, everything works properly.
I would like to know what happens to the SQL Server 2000
when the power fails and how the data is recovered when
power is up again.
ThanksRegardless of the recovery model, each database is automatically recovered
when the instance starts. Data are read from the transaction log since the
last checkpoint and applied to the database. Uncommitted transactions are
then rolled back. The end result is that the database is recovered to the
point of the failure, less uncommitted transactions.
Your FULL recovery model and log backups provide extra protection in the
event of media loss due to hardware failure or data corruption.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:01f401c5446e$a4183b70$a601280a@.phx.gbl...
> We encounter a power failure for about half an hour and
> the SQL Server 2000 is back again.
> For production databases, we use FULL Recovery Model with
> Transaction Log backed up every half an hour. After power
> is up, everything works properly.
> I would like to know what happens to the SQL Server 2000
> when the power fails and how the data is recovered when
> power is up again.
> Thanks|||Dear Dan,
Thank you for your advice.
However, for SIMPLE Recovery Model, there will be no
transaction log backup. To what state does the database
recovered to ?
Besides, would you mind to elaborate on the extra benefit
of using FULL Recovery Model ?
Thanks again.
>--Original Message--
>Regardless of the recovery model, each database is
automatically recovered
>when the instance starts. Data are read from the
transaction log since the
>last checkpoint and applied to the database. Uncommitted
transactions are
>then rolled back. The end result is that the database is
recovered to the
>point of the failure, less uncommitted transactions.
>Your FULL recovery model and log backups provide extra
protection in the
>event of media loss due to hardware failure or data
corruption.
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message
>news:01f401c5446e$a4183b70$a601280a@.phx.gbl...
>> We encounter a power failure for about half an hour and
>> the SQL Server 2000 is back again.
>> For production databases, we use FULL Recovery Model
with
>> Transaction Log backed up every half an hour. After
power
>> is up, everything works properly.
>> I would like to know what happens to the SQL Server 2000
>> when the power fails and how the data is recovered when
>> power is up again.
>> Thanks
>
>.
>|||Peter
<http://vyaskn.tripod.com/sql_server_administration_best_practices.htm#Step1
> --administaiting best practices
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:077101c54475$9661ee70$a501280a@.phx.gbl...
> Dear Dan,
> Thank you for your advice.
> However, for SIMPLE Recovery Model, there will be no
> transaction log backup. To what state does the database
> recovered to ?
> Besides, would you mind to elaborate on the extra benefit
> of using FULL Recovery Model ?
> Thanks again.
> >--Original Message--
> >Regardless of the recovery model, each database is
> automatically recovered
> >when the instance starts. Data are read from the
> transaction log since the
> >last checkpoint and applied to the database. Uncommitted
> transactions are
> >then rolled back. The end result is that the database is
> recovered to the
> >point of the failure, less uncommitted transactions.
> >
> >Your FULL recovery model and log backups provide extra
> protection in the
> >event of media loss due to hardware failure or data
> corruption.
> >
> >--
> >Hope this helps.
> >
> >Dan Guzman
> >SQL Server MVP
> >
> >"Peter" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:01f401c5446e$a4183b70$a601280a@.phx.gbl...
> >> We encounter a power failure for about half an hour and
> >> the SQL Server 2000 is back again.
> >>
> >> For production databases, we use FULL Recovery Model
> with
> >> Transaction Log backed up every half an hour. After
> power
> >> is up, everything works properly.
> >>
> >> I would like to know what happens to the SQL Server 2000
> >> when the power fails and how the data is recovered when
> >> power is up again.
> >>
> >> Thanks
> >
> >
> >.
> >|||Automatic recovery (what happens when you start SQL Server) doesn't have anything to do with
backups. SQL Server records all modifications in the transaction log, regardless of recovery model.
In simple, SQL Server removes log records from the transaction log when they aren't needed anymore
for this automatic recovery.
Full recovery model allow you to backup transaction log. This has a lot of advantages, like backup
log even of the database becomes corrupt, point in time restore etc.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:077101c54475$9661ee70$a501280a@.phx.gbl...
> Dear Dan,
> Thank you for your advice.
> However, for SIMPLE Recovery Model, there will be no
> transaction log backup. To what state does the database
> recovered to ?
> Besides, would you mind to elaborate on the extra benefit
> of using FULL Recovery Model ?
> Thanks again.
>>--Original Message--
>>Regardless of the recovery model, each database is
> automatically recovered
>>when the instance starts. Data are read from the
> transaction log since the
>>last checkpoint and applied to the database. Uncommitted
> transactions are
>>then rolled back. The end result is that the database is
> recovered to the
>>point of the failure, less uncommitted transactions.
>>Your FULL recovery model and log backups provide extra
> protection in the
>>event of media loss due to hardware failure or data
> corruption.
>>--
>>Hope this helps.
>>Dan Guzman
>>SQL Server MVP
>>"Peter" <anonymous@.discussions.microsoft.com> wrote in
> message
>>news:01f401c5446e$a4183b70$a601280a@.phx.gbl...
>> We encounter a power failure for about half an hour and
>> the SQL Server 2000 is back again.
>> For production databases, we use FULL Recovery Model
> with
>> Transaction Log backed up every half an hour. After
> power
>> is up, everything works properly.
>> I would like to know what happens to the SQL Server 2000
>> when the power fails and how the data is recovered when
>> power is up again.
>> Thanks
>>
>>.|||To add to the other responses, automatic recovery will recover databases to
the same consistent state regardless of the recovery model.
Separately, database and transaction log backups reduce your vulnerability
to potential data loss. For example, if your power outage caused a hardware
problem that corrupted your log file, you could still restore from your most
recent database backup and then apply your log backups. At most, you would
lose one half hour of work. If only data files were lost, you could backup
the current log with NO_TRUNCATE and then restore your database and log
backups. No data would be lost in this case.
In the SIMPLE recovery model, your only recourse after losing data or log
files is to restore from your most recent database backup. All data
modifications since the backup would be lost.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:077101c54475$9661ee70$a501280a@.phx.gbl...
> Dear Dan,
> Thank you for your advice.
> However, for SIMPLE Recovery Model, there will be no
> transaction log backup. To what state does the database
> recovered to ?
> Besides, would you mind to elaborate on the extra benefit
> of using FULL Recovery Model ?
> Thanks again.
>>--Original Message--
>>Regardless of the recovery model, each database is
> automatically recovered
>>when the instance starts. Data are read from the
> transaction log since the
>>last checkpoint and applied to the database. Uncommitted
> transactions are
>>then rolled back. The end result is that the database is
> recovered to the
>>point of the failure, less uncommitted transactions.
>>Your FULL recovery model and log backups provide extra
> protection in the
>>event of media loss due to hardware failure or data
> corruption.
>>--
>>Hope this helps.
>>Dan Guzman
>>SQL Server MVP
>>"Peter" <anonymous@.discussions.microsoft.com> wrote in
> message
>>news:01f401c5446e$a4183b70$a601280a@.phx.gbl...
>> We encounter a power failure for about half an hour and
>> the SQL Server 2000 is back again.
>> For production databases, we use FULL Recovery Model
> with
>> Transaction Log backed up every half an hour. After
> power
>> is up, everything works properly.
>> I would like to know what happens to the SQL Server 2000
>> when the power fails and how the data is recovered when
>> power is up again.
>> Thanks
>>
>>.

Power Failure for SQL Server 2000

We encounter a power failure for about half an hour and
the SQL Server 2000 is back again.
For production databases, we use FULL Recovery Model with
Transaction Log backed up every half an hour. After power
is up, everything works properly.
I would like to know what happens to the SQL Server 2000
when the power fails and how the data is recovered when
power is up again.
Thanks
Regardless of the recovery model, each database is automatically recovered
when the instance starts. Data are read from the transaction log since the
last checkpoint and applied to the database. Uncommitted transactions are
then rolled back. The end result is that the database is recovered to the
point of the failure, less uncommitted transactions.
Your FULL recovery model and log backups provide extra protection in the
event of media loss due to hardware failure or data corruption.
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:01f401c5446e$a4183b70$a601280a@.phx.gbl...
> We encounter a power failure for about half an hour and
> the SQL Server 2000 is back again.
> For production databases, we use FULL Recovery Model with
> Transaction Log backed up every half an hour. After power
> is up, everything works properly.
> I would like to know what happens to the SQL Server 2000
> when the power fails and how the data is recovered when
> power is up again.
> Thanks
|||Dear Dan,
Thank you for your advice.
However, for SIMPLE Recovery Model, there will be no
transaction log backup. To what state does the database
recovered to ?
Besides, would you mind to elaborate on the extra benefit
of using FULL Recovery Model ?
Thanks again.

>--Original Message--
>Regardless of the recovery model, each database is
automatically recovered
>when the instance starts. Data are read from the
transaction log since the
>last checkpoint and applied to the database. Uncommitted
transactions are
>then rolled back. The end result is that the database is
recovered to the
>point of the failure, less uncommitted transactions.
>Your FULL recovery model and log backups provide extra
protection in the
>event of media loss due to hardware failure or data
corruption.
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:01f401c5446e$a4183b70$a601280a@.phx.gbl...
with[vbcol=seagreen]
power
>
>.
>
|||Peter
<http://vyaskn.tripod.com/sql_server_...ices.htm#Step1
> --administaiting best practices
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:077101c54475$9661ee70$a501280a@.phx.gbl...[vbcol=seagreen]
> Dear Dan,
> Thank you for your advice.
> However, for SIMPLE Recovery Model, there will be no
> transaction log backup. To what state does the database
> recovered to ?
> Besides, would you mind to elaborate on the extra benefit
> of using FULL Recovery Model ?
> Thanks again.
> automatically recovered
> transaction log since the
> transactions are
> recovered to the
> protection in the
> corruption.
> message
> with
> power
|||Automatic recovery (what happens when you start SQL Server) doesn't have anything to do with
backups. SQL Server records all modifications in the transaction log, regardless of recovery model.
In simple, SQL Server removes log records from the transaction log when they aren't needed anymore
for this automatic recovery.
Full recovery model allow you to backup transaction log. This has a lot of advantages, like backup
log even of the database becomes corrupt, point in time restore etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:077101c54475$9661ee70$a501280a@.phx.gbl...[vbcol=seagreen]
> Dear Dan,
> Thank you for your advice.
> However, for SIMPLE Recovery Model, there will be no
> transaction log backup. To what state does the database
> recovered to ?
> Besides, would you mind to elaborate on the extra benefit
> of using FULL Recovery Model ?
> Thanks again.
> automatically recovered
> transaction log since the
> transactions are
> recovered to the
> protection in the
> corruption.
> message
> with
> power
|||To add to the other responses, automatic recovery will recover databases to
the same consistent state regardless of the recovery model.
Separately, database and transaction log backups reduce your vulnerability
to potential data loss. For example, if your power outage caused a hardware
problem that corrupted your log file, you could still restore from your most
recent database backup and then apply your log backups. At most, you would
lose one half hour of work. If only data files were lost, you could backup
the current log with NO_TRUNCATE and then restore your database and log
backups. No data would be lost in this case.
In the SIMPLE recovery model, your only recourse after losing data or log
files is to restore from your most recent database backup. All data
modifications since the backup would be lost.
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:077101c54475$9661ee70$a501280a@.phx.gbl...[vbcol=seagreen]
> Dear Dan,
> Thank you for your advice.
> However, for SIMPLE Recovery Model, there will be no
> transaction log backup. To what state does the database
> recovered to ?
> Besides, would you mind to elaborate on the extra benefit
> of using FULL Recovery Model ?
> Thanks again.
> automatically recovered
> transaction log since the
> transactions are
> recovered to the
> protection in the
> corruption.
> message
> with
> power

Power Failure for SQL Server 2000

We encounter a power failure for about half an hour and
the SQL Server 2000 is back again.
For production databases, we use FULL Recovery Model with
Transaction Log backed up every half an hour. After power
is up, everything works properly.
I would like to know what happens to the SQL Server 2000
when the power fails and how the data is recovered when
power is up again.
ThanksRegardless of the recovery model, each database is automatically recovered
when the instance starts. Data are read from the transaction log since the
last checkpoint and applied to the database. Uncommitted transactions are
then rolled back. The end result is that the database is recovered to the
point of the failure, less uncommitted transactions.
Your FULL recovery model and log backups provide extra protection in the
event of media loss due to hardware failure or data corruption.
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:01f401c5446e$a4183b70$a601280a@.phx.gbl...
> We encounter a power failure for about half an hour and
> the SQL Server 2000 is back again.
> For production databases, we use FULL Recovery Model with
> Transaction Log backed up every half an hour. After power
> is up, everything works properly.
> I would like to know what happens to the SQL Server 2000
> when the power fails and how the data is recovered when
> power is up again.
> Thanks|||Dear Dan,
Thank you for your advice.
However, for SIMPLE Recovery Model, there will be no
transaction log backup. To what state does the database
recovered to ?
Besides, would you mind to elaborate on the extra benefit
of using FULL Recovery Model ?
Thanks again.

>--Original Message--
>Regardless of the recovery model, each database is
automatically recovered
>when the instance starts. Data are read from the
transaction log since the
>last checkpoint and applied to the database. Uncommitted
transactions are
>then rolled back. The end result is that the database is
recovered to the
>point of the failure, less uncommitted transactions.
>Your FULL recovery model and log backups provide extra
protection in the
>event of media loss due to hardware failure or data
corruption.
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message
>news:01f401c5446e$a4183b70$a601280a@.phx.gbl...
with[vbcol=seagreen]
power[vbcol=seagreen]
>
>.
>|||Peter
<.htm#Step1" target="_blank">http://vyaskn.tripod.com/ sql_serve...r />
.htm#Step1
> --administaiting best practices
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:077101c54475$9661ee70$a501280a@.phx.gbl...[vbcol=seagreen]
> Dear Dan,
> Thank you for your advice.
> However, for SIMPLE Recovery Model, there will be no
> transaction log backup. To what state does the database
> recovered to ?
> Besides, would you mind to elaborate on the extra benefit
> of using FULL Recovery Model ?
> Thanks again.
>
> automatically recovered
> transaction log since the
> transactions are
> recovered to the
> protection in the
> corruption.
> message
> with
> power|||Automatic recovery (what happens when you start SQL Server) doesn't have any
thing to do with
backups. SQL Server records all modifications in the transaction log, regard
less of recovery model.
In simple, SQL Server removes log records from the transaction log when they
aren't needed anymore
for this automatic recovery.
Full recovery model allow you to backup transaction log. This has a lot of a
dvantages, like backup
log even of the database becomes corrupt, point in time restore etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:077101c54475$9661ee70$a501280a@.phx.gbl...[vbcol=seagreen]
> Dear Dan,
> Thank you for your advice.
> However, for SIMPLE Recovery Model, there will be no
> transaction log backup. To what state does the database
> recovered to ?
> Besides, would you mind to elaborate on the extra benefit
> of using FULL Recovery Model ?
> Thanks again.
>
> automatically recovered
> transaction log since the
> transactions are
> recovered to the
> protection in the
> corruption.
> message
> with
> power|||To add to the other responses, automatic recovery will recover databases to
the same consistent state regardless of the recovery model.
Separately, database and transaction log backups reduce your vulnerability
to potential data loss. For example, if your power outage caused a hardware
problem that corrupted your log file, you could still restore from your most
recent database backup and then apply your log backups. At most, you would
lose one half hour of work. If only data files were lost, you could backup
the current log with NO_TRUNCATE and then restore your database and log
backups. No data would be lost in this case.
In the SIMPLE recovery model, your only recourse after losing data or log
files is to restore from your most recent database backup. All data
modifications since the backup would be lost.
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:077101c54475$9661ee70$a501280a@.phx.gbl...[vbcol=seagreen]
> Dear Dan,
> Thank you for your advice.
> However, for SIMPLE Recovery Model, there will be no
> transaction log backup. To what state does the database
> recovered to ?
> Besides, would you mind to elaborate on the extra benefit
> of using FULL Recovery Model ?
> Thanks again.
>
> automatically recovered
> transaction log since the
> transactions are
> recovered to the
> protection in the
> corruption.
> message
> with
> power

Possible to get column number on a bcp_sendrow failure?

I've tried SQLGetDiagRec, which tells me that there was an invalid date
format on a column, but there's no indication of *which* column, and my
table has several date columns in it.
I then spotted some references to SQLGetDiagField with
SQL_DIAG_COLUMN_NUMBER, but I don't get any records back from that
call-- should I be able to get something, or is there some other way or
just *no* way to get the column number that failed?
SyncIt looks like no one knows the answer to this. I'm using VS 2005 & SQL
Server 2005, and after my bcp_sendrow error, have tried all the
SQL_DIAG fields with SQLGetDiagField, and have so far found that
SQL_DIAG_DYNAMIC_FUNCTION returns a null string on records 0 & 1,
SQL_DIAG_NUMBER returns 1, SQL_DIAG_RETURN_CODE returns -1,
SQL_DIAG_DYNAMIC_FUNCTION_CODE returns 0 and ignores record number, and
none of the rest return SQL_SUCCESS or SQL_SUCCESS_WITH_INFO. And
SQL_DIAG_SS_MSGSTATE and SQL_DIAG_SS_SEVERITY both return SQL_ERROR.
SQLGetDiagField appears completely useless here. And SQLGetDiagRec
doesn't include the column info.
On the other hand, bcp.exe used on the same data produces an error
message that includes column number. The question of the day is, how
does it do it? I turned on ODBC trace, and let bcp.exe do its thing.
In the trace log, I found a bunch of calls to SQLColAttributes, one for
each column, then a bunch of calls to SQLGetInfoW, then a call to
SQLSetConnectAttr with what appears to be a bogus attribute (-28236)
which then returns the first error-- it then calls SQLErrorW and gets
the message I'm able to get (invalid character for cast specification)
and then it does an SQLGetConnectionOption with 112 (packet_size) and
then disconnect and frees. I can't see where it's getting the
row/column info from that it writes to the error output.
Then, I tried turning ODBC trace on and running my program that uses
bcp_sendrow. It does a *single* call to SQLBindCol (though I'm
calling bcp_bind about 35 times), then a bunch of SQLGetInfoW's like
the bcp.exe version, then the same SQLSetConnectAttrW with the odd
attribute value (-28236) that returns the first error, and then my
SQLGetDiagRec/SQLGetDiagField tries that never get me the column
information.
One thing, is bcp_sendrow does not operate with a statement handle, but
a connection handle. bcp.exe is calling SQLColAttributes with a
statement handle, which it is using for all the SQLColAttributes calls,
after having issued a "select * from <table> where ?\ 0" which looks
like it could be a method for determining the column type of all the
columns, in which case the sanity checking of the data and perhaps even
the conversion may be done in bcp.exe itself, different from
bcp_sendrow which handles the conversion somewhere in the API. If
that's the case it may be that bcp_sendrow doesn't make column
information available on an error, which is really annoying. I'm
trying to produce a relatively generalized bulk import capability that
needs to flag what's wrong when a user tries to import bogus data. I'm
using bcp_sendrow because there is a lot of associated data tweaking on
the way in and I want to minimize the overhead-- eliminating writing an
intermediate form to disk and then calling bcp.exe to do the import. I
presume it is that kind of thing the bulk-copy API is for.
At least I know the row that is affected, as bcp_sendrow operates a row
at a time. I suppose I could use bcp_sendrow until I get an error,
then throw that row out to a file and run bcp.exe on it and let IT
detect the specific column information, but that's pretty darn kludgy.
SQLSetConnectAttrW with an attribute code of -28236 seems to be doing
something special-- perhaps even initiating the row import, as it
appears to be *that* call in both cases that is throwing the error I'm
trying to get the column number for. Haven't been able to find a
define for the value in the includes, and don't know how it would
appear anyway, possibly in hex as 0x91b4 or -0x6e4c or some kind of
ORing together multiple values...
Sync|||SUCCESS!
I'm talking to myself here but perhaps it will benefit others. I found
out how to get the row/column information on a bcp_sendrow error.
First, you have to specify an error file in the bcp_init call. I did
that and ended up getting a null error file, initially. Searched the
newsgroups and found several people had that problem. But then I
remembered that Windows is not like Unix in that file data sometimes
doesn't get written to disk if a close isn't done when the program
exits. I've seen that before. Open a file, fprint some stuff to it,
then exit. File exists, but is null. So, I figured I probably have to
call bcp_done to get the error file closed. Sure enough, now I'm
getting the error info.
One issue is, what if I don't want to "commit" the partial batch to the
database on an error? I figured by not doing a bcp_done I would
achieve a rollback. Haven't verified that though, and it looks like I
can't do it that way anyway because I need the errors. There may be
another way to do a rollback, which I'll look into and is a subject for
another day...
Sync|||If you don't have a blog up yet, maybe this is a good opportunity to start.
ML
http://milambda.blogspot.com/|||(kdd21@.hotmail.com) writes:
> I'm talking to myself here but perhaps it will benefit others. I found
> out how to get the row/column information on a bcp_sendrow error.
> First, you have to specify an error file in the bcp_init call. I did
> that and ended up getting a null error file, initially. Searched the
> newsgroups and found several people had that problem. But then I
> remembered that Windows is not like Unix in that file data sometimes
> doesn't get written to disk if a close isn't done when the program
> exits. I've seen that before. Open a file, fprint some stuff to it,
> then exit. File exists, but is null. So, I figured I probably have to
> call bcp_done to get the error file closed. Sure enough, now I'm
> getting the error info.
> One issue is, what if I don't want to "commit" the partial batch to the
> database on an error? I figured by not doing a bcp_done I would
> achieve a rollback. Haven't verified that though, and it looks like I
> can't do it that way anyway because I need the errors. There may be
> another way to do a rollback, which I'll look into and is a subject for
> another day...
Thanks for posting this! I don't relly have anything to add. But as I
have a module for Perl users that exposes bcp_sendrow et al, this is
useful information. My module uses DB-Library, so I need to port it to
ODBC one day...
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Your welcome. Thanks for the non-snide remarks. Also, WRT "commits,"
it appears that the most suggested way to handle imports is to import
everything into a "staging" table, then use SQL to move from the
staging table into the "live" table. Then, the "better" constraint
checking & transaction handling features are available. I found this a
pretty unsatisfactory solution, as the whole point of my project is to
reduce the overhead of large imports, as the app I'm working on is a
database analysis program that will be constantly doing large imports
of a good-sized schema's worth of tables exported from another server.
Seems to be mostly working now however, though I had to make use of
indicators on the bind variables so that I could pass in NULL flags
where necessary...
Sync