can I get the Parent task in a custom data flow task?

I'm building a custom data flow task to handle errors in a generic way in C#. I want to output the Task name (the name of the Data flow) as well as the Task name that is inputting data into my custom task.

Is there a way to get both the task name and the previous data flow task name that's inputting the data?

THanks in advance,
-Chris

PS, the VariableContainer didn't seem to contain any of the system variables- ex. the below statement returned false.

this.VariableDispenser.Contains("System::SourceName")

Are you talking about a control flow task or a data flow component? For a dataflow component you can not get any information about any other component other than yourself.

As for the variable dispenser, unless you have added variables to it via the lock methods then it contains no variables. Additionally, there is no such system variable as SourceName.

Thanks,
Matt|||I am talking about a dataflow component.

Do I have to add the system variables to the VariableDispenser?

Thanks for your help Matt,
-Chris|||Yes, you have to add all variables to the variable dispenser in the form of the lock methods. Otherwise the variable dispenser is empty.

Matt|||

I added the System::SourceName variable to the custom data flow component by using LockOneForWrite, but gave an error that that variable doesn't exist.

Is there a place where I can find a listing of the system variables available at the data flow component level?

|||As I mentioned in my original post, there is no such system variable. You can look in the designer to see all the system variable that are available.

Matt|||

Matt David wrote:

As I mentioned in my original post, there is no such system variable. You can look in the designer to see all the system variable that are available.

Matt

Just to clarify Matt's point...SSIS actually does have a System::SourceName variable but it is only available in the eventhandler container. You can't reference it in your control-flow.

-Jamie|||I believe you can get hold of the component next to it in the flow, by looking at one of the input collections. However you can't find out the component that added a column to a flow.

can I get the Parent task in a custom data flow task?

I'm building a custom data flow task to handle errors in a generic way in C#. I want to output the Task name (the name of the Data flow) as well as the Task name that is inputting data into my custom task.

Is there a way to get both the task name and the previous data flow task name that's inputting the data?

THanks in advance,
-Chris

PS, the VariableContainer didn't seem to contain any of the system variables- ex. the below statement returned false.

this.VariableDispenser.Contains("System::SourceName")

Are you talking about a control flow task or a data flow component? For a dataflow component you can not get any information about any other component other than yourself.

As for the variable dispenser, unless you have added variables to it via the lock methods then it contains no variables. Additionally, there is no such system variable as SourceName.

Thanks,
Matt|||I am talking about a dataflow component.

Do I have to add the system variables to the VariableDispenser?

Thanks for your help Matt,
-Chris|||Yes, you have to add all variables to the variable dispenser in the form of the lock methods. Otherwise the variable dispenser is empty.

Matt|||

I added the System::SourceName variable to the custom data flow component by using LockOneForWrite, but gave an error that that variable doesn't exist.

Is there a place where I can find a listing of the system variables available at the data flow component level?

|||As I mentioned in my original post, there is no such system variable. You can look in the designer to see all the system variable that are available.

Matt|||

Matt David wrote:

As I mentioned in my original post, there is no such system variable. You can look in the designer to see all the system variable that are available.

Matt

Just to clarify Matt's point...SSIS actually does have a System::SourceName variable but it is only available in the eventhandler container. You can't reference it in your control-flow.

-Jamie|||I believe you can get hold of the component next to it in the flow, by looking at one of the input collections. However you can't find out the component that added a column to a flow.

Can errors occur while committing a tx?

Can an error occur /during/ the process of committing a transaction?
BeginTraction();
try {
UpdateTable1();
UpdateTable2();
CommitTransaction(); <-- error here?
} catch(Exception e){
RollbackTransaction();
}
Also, what are the implications in such a situation where both the
Updates pass without any errors, but committing the transaction fails?
Is this scenario even possible at all?
If so, what are the suggested best-practices for recovering from such
types of errors?
TIA,
Abdullah"Abdullah Kauchali" <none@.none.com> wrote in message
news:eT1t1UAzFHA.1264@.tk2msftngp13.phx.gbl...
> Can an error occur /during/ the process of committing a transaction?
> BeginTraction();
> try {
> UpdateTable1();
> UpdateTable2();
> CommitTransaction(); <-- error here?
> } catch(Exception e){
> RollbackTransaction();
throw;
> }
>
Remember to rethrow the exception!
You know, that's a really good question.
Yes errors are possible, but pretty darn unlikely (at least in SQL Server).
When you go to commit the transaction all of the changes have allready been
made to the tables, and written to the memory cache of the log file. So
almost everything that could go wrong already would have. There could
possibly be some error flushing the log to disk, or you could loose your
connection to the database server, or the server could just fail. If the
commit fails on the server, the transaction will be rolled back (or at worst
it won't be there when the database recovers). But from a client there's
probably some possiblility that the commit succeeds, but a network problem
prevents you from learning about it.

> Also, what are the implications in such a situation where both the
> Updates pass without any errors, but committing the transaction fails?
The transaction will be rolled back.

> Is this scenario even possible at all?
> If so, what are the suggested best-practices for recovering from such
> types of errors?
>
Treat it like a server or network failure.
It's so unlikely to happen in the first place, and you are so unlikely to be
able to recover if it does, that I would just pretend like it's impossible.
Just pretend like it can't happen. Your responsibility is only to keep the
database in a logically consistent state. You've done that by coding your
transaction. You are not responsible for making sure the transaction
suceeds. That responsibility belongs to a higher context (ie a user or an
automated agent).
For most programs any kind of transaction retry is not worth the coding.
The code complexity and residual risk are just too great. Just propagate
the error out to the user or calling code and let them deal with it. You
just don't have the right context to deal with a server or network failures
in a meaningful way.
If you feel like you must deal with it, then propagate the exception out of
this method, and catch it at the level which knows how to retry the entire
transaction. Wait around for the instance to fail over to another server,
reconnect and issue the transaction again. If you know you are running
against a cluster with such high availability requirements that it the DBA's
must fail the instance over to perform routine maintenance then you can
expect this to happen, and you have no choice but to code around it. But in
any case the retry code does not belong in that method, but in an outer
controlling context.
David

Can errors occur while committing a tx?

Can an error occur /during/ the process of committing a transaction?
BeginTraction();
try {
UpdateTable1();
UpdateTable2();
CommitTransaction(); <-- error here?
} catch(Exception e){
RollbackTransaction();
}
Also, what are the implications in such a situation where both the
Updates pass without any errors, but committing the transaction fails?
Is this scenario even possible at all?
If so, what are the suggested best-practices for recovering from such
types of errors?
TIA,
Abdullah"Abdullah Kauchali" <none@.none.com> wrote in message
news:eT1t1UAzFHA.1264@.tk2msftngp13.phx.gbl...
> Can an error occur /during/ the process of committing a transaction?
> BeginTraction();
> try {
> UpdateTable1();
> UpdateTable2();
> CommitTransaction(); <-- error here?
> } catch(Exception e){
> RollbackTransaction();
throw;
> }
>
Remember to rethrow the exception!
You know, that's a really good question.
Yes errors are possible, but pretty darn unlikely (at least in SQL Server).
When you go to commit the transaction all of the changes have allready been
made to the tables, and written to the memory cache of the log file. So
almost everything that could go wrong already would have. There could
possibly be some error flushing the log to disk, or you could loose your
connection to the database server, or the server could just fail. If the
commit fails on the server, the transaction will be rolled back (or at worst
it won't be there when the database recovers). But from a client there's
probably some possiblility that the commit succeeds, but a network problem
prevents you from learning about it.
> Also, what are the implications in such a situation where both the
> Updates pass without any errors, but committing the transaction fails?
The transaction will be rolled back.
> Is this scenario even possible at all?
> If so, what are the suggested best-practices for recovering from such
> types of errors?
>
Treat it like a server or network failure.
It's so unlikely to happen in the first place, and you are so unlikely to be
able to recover if it does, that I would just pretend like it's impossible.
Just pretend like it can't happen. Your responsibility is only to keep the
database in a logically consistent state. You've done that by coding your
transaction. You are not responsible for making sure the transaction
suceeds. That responsibility belongs to a higher context (ie a user or an
automated agent).
For most programs any kind of transaction retry is not worth the coding.
The code complexity and residual risk are just too great. Just propagate
the error out to the user or calling code and let them deal with it. You
just don't have the right context to deal with a server or network failures
in a meaningful way.
If you feel like you must deal with it, then propagate the exception out of
this method, and catch it at the level which knows how to retry the entire
transaction. Wait around for the instance to fail over to another server,
reconnect and issue the transaction again. If you know you are running
against a cluster with such high availability requirements that it the DBA's
must fail the instance over to perform routine maintenance then you can
expect this to happen, and you have no choice but to code around it. But in
any case the retry code does not belong in that method, but in an outer
controlling context.
David

Can errors occur while committing a tx?

Can an error occur /during/ the process of committing a transaction?
BeginTraction();
try {
UpdateTable1();
UpdateTable2();
CommitTransaction(); <-- error here?
} catch(Exception e){
RollbackTransaction();
}
Also, what are the implications in such a situation where both the
Updates pass without any errors, but committing the transaction fails?
Is this scenario even possible at all?
If so, what are the suggested best-practices for recovering from such
types of errors?
TIA,
Abdullah
"Abdullah Kauchali" <none@.none.com> wrote in message
news:eT1t1UAzFHA.1264@.tk2msftngp13.phx.gbl...
> Can an error occur /during/ the process of committing a transaction?
> BeginTraction();
> try {
> UpdateTable1();
> UpdateTable2();
> CommitTransaction(); <-- error here?
> } catch(Exception e){
> RollbackTransaction();
throw;
> }
>
Remember to rethrow the exception!
You know, that's a really good question.
Yes errors are possible, but pretty darn unlikely (at least in SQL Server).
When you go to commit the transaction all of the changes have allready been
made to the tables, and written to the memory cache of the log file. So
almost everything that could go wrong already would have. There could
possibly be some error flushing the log to disk, or you could loose your
connection to the database server, or the server could just fail. If the
commit fails on the server, the transaction will be rolled back (or at worst
it won't be there when the database recovers). But from a client there's
probably some possiblility that the commit succeeds, but a network problem
prevents you from learning about it.

> Also, what are the implications in such a situation where both the
> Updates pass without any errors, but committing the transaction fails?
The transaction will be rolled back.

> Is this scenario even possible at all?
> If so, what are the suggested best-practices for recovering from such
> types of errors?
>
Treat it like a server or network failure.
It's so unlikely to happen in the first place, and you are so unlikely to be
able to recover if it does, that I would just pretend like it's impossible.
Just pretend like it can't happen. Your responsibility is only to keep the
database in a logically consistent state. You've done that by coding your
transaction. You are not responsible for making sure the transaction
suceeds. That responsibility belongs to a higher context (ie a user or an
automated agent).
For most programs any kind of transaction retry is not worth the coding.
The code complexity and residual risk are just too great. Just propagate
the error out to the user or calling code and let them deal with it. You
just don't have the right context to deal with a server or network failures
in a meaningful way.
If you feel like you must deal with it, then propagate the exception out of
this method, and catch it at the level which knows how to retry the entire
transaction. Wait around for the instance to fail over to another server,
reconnect and issue the transaction again. If you know you are running
against a cluster with such high availability requirements that it the DBA's
must fail the instance over to perform routine maintenance then you can
expect this to happen, and you have no choice but to code around it. But in
any case the retry code does not belong in that method, but in an outer
controlling context.
David

Can anyone help solve this puzzle?

I have one SQL instance that is giving me problems: everytime I restore a
database to it, the database has many consistency errors.
I have 3 servers I can test with: Server A, Server B, and Server C. Server
C is the one that's giving me problems.
I run a DBCC CheckDB on a database on Server A, then back it up. If I then
try to restore that backup on Server C, the backup works but DBCC CheckDB
reveals numerous errors. However, I can take that same backup file and
restore it on Server B, and everything is fine.
I can spend lots of time with my corrupt database on Server C and using DBCC
CheckDB (repair_rebuild or repair_allow_data_loss) and dbreindex to finally
get the database to report that it has no consitency errors. HOWEVER, if I
then try to back up that database and restore it on either Server A or
Server B, the restore works ok, but the database again is full of
consistency errors. Restore/backup works with no problems whatsoever
between Server A and Server B.
All of these problems apply not only to backup/restore, but also if I try to
just detach from Server A (or B) and attach to Server C.
I have sees several errors in the SQL log, on Server C, 604 (and some
others). I wish I had the complete list but I have re-installed SQL and
lost the logs.
I thought maybe it was an problem with the install, but as I just mentioned,
I completely re-installed on Server C, but the problems continue. To this
point in the current logs, there are no errors.
At this point I am thinking hardware, but the network guys insist that it
has passed all tests. Next I am going to run SQLIOStress (
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q231/6/19.asp&NoWebContent=1 )
and see if that turns anything up.
Oh, info on the servers:
Server A: SQL: 8.00.194 RTM Enterprise Edition OS: Windows 2000
(Build 2195 SP3)
Server B: SQL: 8.00.760 SP3 Developer Edition OS: Windows Server 2003
Standard Edition (Build 3790.srv03_rtm.030324-2048)
Server C: SQL: 8.00.760 SP3 Enterprise Edition OS: Windows 2000
(Build 2195 SP4) NOTE: the new install has no SQL service packs applied, no
difference in behavior
Any help/suggestions/links would be greatly appreciated. Please post back
here so that maybe any other unlucky soul that has this problem can benefit
from the smart folks out there.
thanks,
Greg
gjleclair AT hotmail DOT comGreg,
Step 1: install SQL Server service pack 3a and Win2K service pack 4 on
server A.
Step 2: see if you are still having trouble.
I wouldn't be so sure it's server C with the problem. Server A, which
has no SQL Server service packs installed, created the backup.
You mentioned briefly a problem detaching from B and attaching to C. If
that is a problem that is reproducible from scratch without any contact
with Server A, post the details of what's going on with that.
There are hardware issues that can cause this kind of problem that might
not be called hardware problems, such as the one described in
http://support.microsoft.com:80/support/kb/articles/q268/4/81.asp, but I
doubt you'll get much help asking why things aren't working on an
non-updated server.
SK
Greg wrote:
>I have one SQL instance that is giving me problems: everytime I restore a
>database to it, the database has many consistency errors.
>I have 3 servers I can test with: Server A, Server B, and Server C. Server
>C is the one that's giving me problems.
>I run a DBCC CheckDB on a database on Server A, then back it up. If I then
>try to restore that backup on Server C, the backup works but DBCC CheckDB
>reveals numerous errors. However, I can take that same backup file and
>restore it on Server B, and everything is fine.
>I can spend lots of time with my corrupt database on Server C and using DBCC
>CheckDB (repair_rebuild or repair_allow_data_loss) and dbreindex to finally
>get the database to report that it has no consitency errors. HOWEVER, if I
>then try to back up that database and restore it on either Server A or
>Server B, the restore works ok, but the database again is full of
>consistency errors. Restore/backup works with no problems whatsoever
>between Server A and Server B.
>All of these problems apply not only to backup/restore, but also if I try to
>just detach from Server A (or B) and attach to Server C.
>I have sees several errors in the SQL log, on Server C, 604 (and some
>others). I wish I had the complete list but I have re-installed SQL and
>lost the logs.
>I thought maybe it was an problem with the install, but as I just mentioned,
>I completely re-installed on Server C, but the problems continue. To this
>point in the current logs, there are no errors.
>At this point I am thinking hardware, but the network guys insist that it
>has passed all tests. Next I am going to run SQLIOStress (
>http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q231/6/19.asp&NoWebContent=1 )
>and see if that turns anything up.
>
>Oh, info on the servers:
>Server A: SQL: 8.00.194 RTM Enterprise Edition OS: Windows 2000
>(Build 2195 SP3)
>Server B: SQL: 8.00.760 SP3 Developer Edition OS: Windows Server 2003
>Standard Edition (Build 3790.srv03_rtm.030324-2048)
>Server C: SQL: 8.00.760 SP3 Enterprise Edition OS: Windows 2000
>(Build 2195 SP4) NOTE: the new install has no SQL service packs applied, no
>difference in behavior
>
>Any help/suggestions/links would be greatly appreciated. Please post back
>here so that maybe any other unlucky soul that has this problem can benefit
>from the smart folks out there.
>
>thanks,
>Greg
>gjleclair AT hotmail DOT com
>
>|||Thanks for the reply, Steve.
I understand that applying the service packs would make the most sense as a
starting point...but the powers that be do not want me to do that since
"that's only one that's working".
The reason I didn't think it was server A is that all interaction between
Server A and Server B (backup/restore, attach/detach) work fine. Any
interaction between Server B and Server C result in the same consistency
problems.
As an update the SQLIOstress failed out with a series of these errors:
>>>>*** ERROR: LSN for page 277534 is out of sequence. Expected:
605885 and found 601789 in sector 15. Probably a torn page.
>>>> Byte 513 in pattern is [>] and from read is [.]
Followed by this:
>>>>----
--
>>>>Expected pattern [C] in file for page 277534.
>>>>Bytes read = 8192
>>>>Potential torn write, lost write or stale read may have been
encountered
>>>>----
--
I'm guessing that we have a hardware issue. Any thoughts? It seems like it
is up to me to "prove" that the hardware is having issues but I'm not really
sure what those errors indicate.
Once again, any help is appreciated.
-Greg
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23bYZld26DHA.2556@.TK2MSFTNGP09.phx.gbl...
> Greg,
> Step 1: install SQL Server service pack 3a and Win2K service pack 4 on
> server A.
> Step 2: see if you are still having trouble.
> I wouldn't be so sure it's server C with the problem. Server A, which
> has no SQL Server service packs installed, created the backup.
> You mentioned briefly a problem detaching from B and attaching to C. If
> that is a problem that is reproducible from scratch without any contact
> with Server A, post the details of what's going on with that.
> There are hardware issues that can cause this kind of problem that might
> not be called hardware problems, such as the one described in
> http://support.microsoft.com:80/support/kb/articles/q268/4/81.asp, but I
> doubt you'll get much help asking why things aren't working on an
> non-updated server.
> SK
> Greg wrote:
> >I have one SQL instance that is giving me problems: everytime I restore
a
> >database to it, the database has many consistency errors.
> >I have 3 servers I can test with: Server A, Server B, and Server C.
Server
> >C is the one that's giving me problems.
> >
> >I run a DBCC CheckDB on a database on Server A, then back it up. If I
then
> >try to restore that backup on Server C, the backup works but DBCC CheckDB
> >reveals numerous errors. However, I can take that same backup file and
> >restore it on Server B, and everything is fine.
> >
> >I can spend lots of time with my corrupt database on Server C and using
DBCC
> >CheckDB (repair_rebuild or repair_allow_data_loss) and dbreindex to
finally
> >get the database to report that it has no consitency errors. HOWEVER, if
I
> >then try to back up that database and restore it on either Server A or
> >Server B, the restore works ok, but the database again is full of
> >consistency errors. Restore/backup works with no problems whatsoever
> >between Server A and Server B.
> >
> >All of these problems apply not only to backup/restore, but also if I try
to
> >just detach from Server A (or B) and attach to Server C.
> >
> >I have sees several errors in the SQL log, on Server C, 604 (and some
> >others). I wish I had the complete list but I have re-installed SQL and
> >lost the logs.
> >
> >I thought maybe it was an problem with the install, but as I just
mentioned,
> >I completely re-installed on Server C, but the problems continue. To
this
> >point in the current logs, there are no errors.
> >
> >At this point I am thinking hardware, but the network guys insist that it
> >has passed all tests. Next I am going to run SQLIOStress (
>http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com
:80/support/kb/articles/q231/6/19.asp&NoWebContent=1 )
> >and see if that turns anything up.
> >
> >
> >Oh, info on the servers:
> >
> >Server A: SQL: 8.00.194 RTM Enterprise Edition OS: Windows 2000
> >(Build 2195 SP3)
> >Server B: SQL: 8.00.760 SP3 Developer Edition OS: Windows Server
2003
> >Standard Edition (Build 3790.srv03_rtm.030324-2048)
> >Server C: SQL: 8.00.760 SP3 Enterprise Edition OS: Windows 2000
> >(Build 2195 SP4) NOTE: the new install has no SQL service packs applied,
no
> >difference in behavior
> >
> >
> >Any help/suggestions/links would be greatly appreciated. Please post
back
> >here so that maybe any other unlucky soul that has this problem can
benefit
> >from the smart folks out there.
> >
> >
> >thanks,
> >Greg
> >
> >gjleclair AT hotmail DOT com
> >
> >
> >
> >
>|||Greg,
It sure sounds like a hardware issue, but I don't have any great
suggestions. If you haven't already looked, see if these Knowledge Base
articles help at all:
http://support.microsoft.com:80/support/kb/articles/q231/6/19.asp
(and the articles it references at the bottom)
http://support.microsoft.com/default.aspx?scid=kb;en-us;268481
It is sounding more like it's server C and not the service pack, but
you never know... It does seem a little strange that restore fails so
easily, yet you have to work to get SQLIOstress to error out.
SK
Greg wrote:
>Thanks for the reply, Steve.
>I understand that applying the service packs would make the most sense as a
>starting point...but the powers that be do not want me to do that since
>"that's only one that's working".
>The reason I didn't think it was server A is that all interaction between
>Server A and Server B (backup/restore, attach/detach) work fine. Any
>interaction between Server B and Server C result in the same consistency
>problems.
>As an update the SQLIOstress failed out with a series of these errors:
>
>
>>>>*** ERROR: LSN for page 277534 is out of sequence. Expected:
>>>>
>>>>
>605885 and found 601789 in sector 15. Probably a torn page.
>
>>>>Byte 513 in pattern is [>] and from read is [.]
>>>>
>>>>
>Followed by this:
>
>>>>----
>>>>
>>>>
>--
>
>>>>Expected pattern [C] in file for page 277534.
>>>>Bytes read = 8192
>>>>Potential torn write, lost write or stale read may have been
>>>>
>>>>
>encountered
>
>>>>----
>>>>
>>>>
>--
>I'm guessing that we have a hardware issue. Any thoughts? It seems like it
>is up to me to "prove" that the hardware is having issues but I'm not really
>sure what those errors indicate.
>Once again, any help is appreciated.
>-Greg
>
>
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:%23bYZld26DHA.2556@.TK2MSFTNGP09.phx.gbl...
>
>>Greg,
>>Step 1: install SQL Server service pack 3a and Win2K service pack 4 on
>>server A.
>>Step 2: see if you are still having trouble.
>>I wouldn't be so sure it's server C with the problem. Server A, which
>>has no SQL Server service packs installed, created the backup.
>>You mentioned briefly a problem detaching from B and attaching to C. If
>>that is a problem that is reproducible from scratch without any contact
>>with Server A, post the details of what's going on with that.
>>There are hardware issues that can cause this kind of problem that might
>>not be called hardware problems, such as the one described in
>>http://support.microsoft.com:80/support/kb/articles/q268/4/81.asp, but I
>>doubt you'll get much help asking why things aren't working on an
>>non-updated server.
>>SK
>>Greg wrote:
>>
>>I have one SQL instance that is giving me problems: everytime I restore
>>
>a
>
>>database to it, the database has many consistency errors.
>>I have 3 servers I can test with: Server A, Server B, and Server C.
>>
>Server
>
>>C is the one that's giving me problems.
>>I run a DBCC CheckDB on a database on Server A, then back it up. If I
>>
>then
>
>>try to restore that backup on Server C, the backup works but DBCC CheckDB
>>reveals numerous errors. However, I can take that same backup file and
>>restore it on Server B, and everything is fine.
>>I can spend lots of time with my corrupt database on Server C and using
>>
>DBCC
>
>>CheckDB (repair_rebuild or repair_allow_data_loss) and dbreindex to
>>
>finally
>
>>get the database to report that it has no consitency errors. HOWEVER, if
>>
>I
>
>>then try to back up that database and restore it on either Server A or
>>Server B, the restore works ok, but the database again is full of
>>consistency errors. Restore/backup works with no problems whatsoever
>>between Server A and Server B.
>>All of these problems apply not only to backup/restore, but also if I try
>>
>to
>
>>just detach from Server A (or B) and attach to Server C.
>>I have sees several errors in the SQL log, on Server C, 604 (and some
>>others). I wish I had the complete list but I have re-installed SQL and
>>lost the logs.
>>I thought maybe it was an problem with the install, but as I just
>>
>mentioned,
>
>>I completely re-installed on Server C, but the problems continue. To
>>
>this
>
>>point in the current logs, there are no errors.
>>At this point I am thinking hardware, but the network guys insist that it
>>has passed all tests. Next I am going to run SQLIOStress (
>>
>>http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com
>>
>:80/support/kb/articles/q231/6/19.asp&NoWebContent=1 )
>
>>and see if that turns anything up.
>>
>>Oh, info on the servers:
>>Server A: SQL: 8.00.194 RTM Enterprise Edition OS: Windows 2000
>>(Build 2195 SP3)
>>Server B: SQL: 8.00.760 SP3 Developer Edition OS: Windows Server
>>
>2003
>
>>Standard Edition (Build 3790.srv03_rtm.030324-2048)
>>Server C: SQL: 8.00.760 SP3 Enterprise Edition OS: Windows 2000
>>(Build 2195 SP4) NOTE: the new install has no SQL service packs applied,
>>
>no
>
>>difference in behavior
>>
>>Any help/suggestions/links would be greatly appreciated. Please post
>>
>back
>
>>here so that maybe any other unlucky soul that has this problem can
>>
>benefit
>
>>from the smart folks out there.
>>
>>thanks,
>>Greg
>>gjleclair AT hotmail DOT com
>>
>>
>>
>
>

Can anyone help solve this puzzle?

I have one SQL instance that is giving me problems: everytime I restore a
database to it, the database has many consistency errors.
I have 3 servers I can test with: Server A, Server B, and Server C. Server
C is the one that's giving me problems.
I run a DBCC CheckDB on a database on Server A, then back it up. If I then
try to restore that backup on Server C, the backup works but DBCC CheckDB
reveals numerous errors. However, I can take that same backup file and
restore it on Server B, and everything is fine.
I can spend lots of time with my corrupt database on Server C and using DBCC
CheckDB (repair_rebuild or repair_allow_data_loss) and dbreindex to finally
get the database to report that it has no consitency errors. HOWEVER, if I
then try to back up that database and restore it on either Server A or
Server B, the restore works ok, but the database again is full of
consistency errors. Restore/backup works with no problems whatsoever
between Server A and Server B.
All of these problems apply not only to backup/restore, but also if I try to
just detach from Server A (or B) and attach to Server C.
I have sees several errors in the SQL log, on Server C, 604 (and some
others). I wish I had the complete list but I have re-installed SQL and
lost the logs.
I thought maybe it was an problem with the install, but as I just mentioned,
I completely re-installed on Server C, but the problems continue. To this
point in the current logs, there are no errors.
At this point I am thinking hardware, but the network guys insist that it
has passed all tests. Next I am going to run SQLIOStress (
http://support.microsoft.com/defaul...&NoWebContent=1 )
and see if that turns anything up.
Oh, info on the servers:
Server A: SQL: 8.00.194 RTM Enterprise Edition OS: Windows 2000
(Build 2195 SP3)
Server B: SQL: 8.00.760 SP3 Developer Edition OS: Windows Server 2003
Standard Edition (Build 3790.srv03_rtm.030324-2048)
Server C: SQL: 8.00.760 SP3 Enterprise Edition OS: Windows 2000
(Build 2195 SP4) NOTE: the new install has no SQL service packs applied, no
difference in behavior
Any help/suggestions/links would be greatly appreciated. Please post back
here so that maybe any other unlucky soul that has this problem can benefit
from the smart folks out there.
thanks,
Greg
gjleclair AT hotmail DOT comGreg,
Step 1: install SQL Server service pack 3a and Win2K service pack 4 on
server A.
Step 2: see if you are still having trouble.
I wouldn't be so sure it's server C with the problem. Server A, which
has no SQL Server service packs installed, created the backup.
You mentioned briefly a problem detaching from B and attaching to C. If
that is a problem that is reproducible from scratch without any contact
with Server A, post the details of what's going on with that.
There are hardware issues that can cause this kind of problem that might
not be called hardware problems, such as the one described in
http://support.microsoft.com:80/sup.../q268/4/81.asp, but I
doubt you'll get much help asking why things aren't working on an
non-updated server.
SK
Greg wrote:

quote:

---

>I have one SQL instance that is giving me problems: everytime I restore a
>database to it, the database has many consistency errors.
>I have 3 servers I can test with: Server A, Server B, and Server C. Server
>C is the one that's giving me problems.
>I run a DBCC CheckDB on a database on Server A, then back it up. If I then
>try to restore that backup on Server C, the backup works but DBCC CheckDB
>reveals numerous errors. However, I can take that same backup file and
>restore it on Server B, and everything is fine.
>I can spend lots of time with my corrupt database on Server C and using DBC
C
>CheckDB (repair_rebuild or repair_allow_data_loss) and dbreindex to finally
>get the database to report that it has no consitency errors. HOWEVER, if I
>then try to back up that database and restore it on either Server A or
>Server B, the restore works ok, but the database again is full of
>consistency errors. Restore/backup works with no problems whatsoever
>between Server A and Server B.
>All of these problems apply not only to backup/restore, but also if I try t
o
>just detach from Server A (or B) and attach to Server C.
>I have sees several errors in the SQL log, on Server C, 604 (and some
>others). I wish I had the complete list but I have re-installed SQL and
>lost the logs.
>I thought maybe it was an problem with the install, but as I just mentioned
,
>I completely re-installed on Server C, but the problems continue. To this
>point in the current logs, there are no errors.
>At this point I am thinking hardware, but the network guys insist that it
>has passed all tests. Next I am going to run SQLIOStress (
>http://support.microsoft.com/defaul...&NoWebContent=1 )
>and see if that turns anything up.
>
>Oh, info on the servers:
>Server A: SQL: 8.00.194 RTM Enterprise Edition OS: Windows 2000
>(Build 2195 SP3)
>Server B: SQL: 8.00.760 SP3 Developer Edition OS: Windows Server 200
3
>Standard Edition (Build 3790.srv03_rtm.030324-2048)
>Server C: SQL: 8.00.760 SP3 Enterprise Edition OS: Windows 2000
>(Build 2195 SP4) NOTE: the new install has no SQL service packs applied, n
o
>difference in behavior
>
>Any help/suggestions/links would be greatly appreciated. Please post back
>here so that maybe any other unlucky soul that has this problem can benefit
>from the smart folks out there.
>
>thanks,
>Greg
>gjleclair AT hotmail DOT com
>
>

---

|||Thanks for the reply, Steve.
I understand that applying the service packs would make the most sense as a
starting point...but the powers that be do not want me to do that since
"that's only one that's working".
The reason I didn't think it was server A is that all interaction between
Server A and Server B (backup/restore, attach/detach) work fine. Any
interaction between Server B and Server C result in the same consistency
problems.
As an update the SQLIOstress failed out with a series of these errors:

quote:

---

605885 and found 601789 in sector 15. Probably a torn page.[QUOTE]
Followed by this:
[QUOTE]
--[QUOTE]
encountered[QUOTE]
--
I'm guessing that we have a hardware issue. Any thoughts? It seems like it
is up to me to "prove" that the hardware is having issues but I'm not really
sure what those errors indicate.
Once again, any help is appreciated.
-Greg
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23bYZld26DHA.2556@.TK2MSFTNGP09.phx.gbl...[QUOTE]
> Greg,
> Step 1: install SQL Server service pack 3a and Win2K service pack 4 on
> server A.
> Step 2: see if you are still having trouble.
> I wouldn't be so sure it's server C with the problem. Server A, which
> has no SQL Server service packs installed, created the backup.
> You mentioned briefly a problem detaching from B and attaching to C. If
> that is a problem that is reproducible from scratch without any contact
> with Server A, post the details of what's going on with that.
> There are hardware issues that can cause this kind of problem that might
> not be called hardware problems, such as the one described in
> http://support.microsoft.com:80/sup.../q268/4/81.asp, but I
> doubt you'll get much help asking why things aren't working on an
> non-updated server.
> SK
> Greg wrote:
>
a[QUOTE]
Server[QUOTE]
then[QUOTE]
DBCC[QUOTE]
finally[QUOTE]
I[QUOTE]
to[QUOTE]
mentioned,[QUOTE]
this[QUOTE]
>http://support.microsoft.com/defaul...t.microsoft.com

---

:80/support/kb/articles/q231/6/19.asp&NoWebContent=1 )

quote:

---

2003[QUOTE]
no[QUOTE]
back[QUOTE]
benefit[QUOTE]
>

---

|||Greg,
It sure sounds like a hardware issue, but I don't have any great
suggestions. If you haven't already looked, see if these Knowledge Base
articles help at all:
http://support.microsoft.com:80/sup...s/q231/6/19.asp
(and the articles it references at the bottom)
http://support.microsoft.com/defaul...kb;en-us;268481
It is sounding more like it's server C and not the service pack, but
you never know... It does seem a little strange that restore fails so
easily, yet you have to work to get SQLIOstress to error out.
SK
Greg wrote:

quote:

---

>Thanks for the reply, Steve.
>I understand that applying the service packs would make the most sense as a
>starting point...but the powers that be do not want me to do that since
>"that's only one that's working".
>The reason I didn't think it was server A is that all interaction between
>Server A and Server B (backup/restore, attach/detach) work fine. Any
>interaction between Server B and Server C result in the same consistency
>problems.
>As an update the SQLIOstress failed out with a series of these errors:
>
>
>605885 and found 601789 in sector 15. Probably a torn page.
>
>Followed by this:
>
>
>--
>
>encountered
>
>--
>I'm guessing that we have a hardware issue. Any thoughts? It seems like i
t
>is up to me to "prove" that the hardware is having issues but I'm not reall
y
>sure what those errors indicate.
>Once again, any help is appreciated.
>-Greg
>
>
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:%23bYZld26DHA.2556@.TK2MSFTNGP09.phx.gbl...
>
>a
>
>Server
>
>then
>
>DBCC
>
>finally
>
>I
>
>to
>
>mentioned,
>
>this
>
>:80/support/kb/articles/q231/6/19.asp&NoWebContent=1 )
>
>2003
>
>no
>
>back
>
>benefit
>
>
>

---