DB2 and Service Pack 2

Hi

We have recently upgraded to SP2, and some of the packages that used to run under SP1, simply don't work anymore. The thing is everytime we try to access DB2 using a SQL Task with at least 1 parameter, the task fails with the following error.

CLI0109E String data right truncation. SQLSTATE=22001 Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

The SQL Task works as long as I don't use parameters, but if I try something like this

SELECT CHAR(current timestamp) FROM sysibm.sysdummy1 where 'SAMPLE' = ?

and I try to map a String variable, that contais the value SAMPLE, to its only parameter,

User::Var1 INPUT VARCHAR 0 -1

the task fails.

Has anyone faced the same problem using IBM's OLE DB provider for DB2 after installing SP2?
I would really appreciatte any suggestions/comments on this matter.

Thanks.

While I don't know what might be causing the issue, a workaround would be to use a package variable to contain the SQL command and update the variable with a Script task before the Execute SQL task runs. In the Script task, you could update the variable and concatenate in the value of your parameter, producing a complete string that represents the parameterized SQL you want to run. This would eliminate the need for a parameterized query.

HTH,

Dave Fackler

|||Hello Dave

Yes, I suppose I could use a Script Task to build the query, and if I only had 1 sql task I would probably do it, but the truth is I've got several packages, each one having several SQL Tasks accesing DB2. Besides, in the near future, more people from my department will be using SSIS to access DB2, so sooner or later we will have to address this problem.

Due to the nature of the error, my guess is the new 'Parameter Size' field has something to do with it, but I cannot be certain.

Thanks|||Just a follow up

I've just noticed that some of my SSISs actually do work. The difference is that in these packages the SQL Tasks use the SUBSTR function to process the parameter. As far as I know SUBSTR processes a character expression and returns a VARCHAR, so I guess I'm experiencing a data-type conversion problem.

I would really appreciatte it if someone could tell me if they are able to use a SQL Task with parameters to access DB2 using IBM's OLE DB Provider for DB2 and SQL Server 2005 SP2.

Thanks in advance.|||

A number of changes were made to the SQL Task in SP2 to improve our handling of variable length parameters (mainly string types). I didn't think they affected input parameters, but if you're only seeing the problems after installing SP2, they could be related.

The last field on the parameter mapping page sets the parameter size. Since your error message is relating to string truncation, could you try setting this value to something large enough to hold your string? (1024, for example). The default value (-1) tell SSIS to guess at the correct size to use, and we may be having trouble retrieving the size from the DB2 provider.

|||Hello everybody

I had the same problem with data truncation. Then I changed the connection from OLE DB to ODBC. Now it works fine.

However you to adapt the parameter names according to the yellow table in this entry: http://www.msdner.com/dev-archive/5/19-80-57637.shtm

For string values I took the datatype "SQL_WVARCHAR".

Hope this helps...

Cheers

sql

DB taken offline by getspinlock pre-Sleep() error

We are using SQL 2000 v8.00.194 on Server 2003 SP1 with 2GB RAM
At 00:30 at random day intervals, databases on two different servers are
taken offline which causes jobs to be terminated and sessions left open. We
have obtained a trace log using DebugView which contains:
0057110112:30:40 AM[516] 2006-09-21 00:30:40.15 getspinlock pre-Sleep():
spid 8, 10000 yields on lock type "LOGFLUSHQ" (adr 0x1a406f7c)
0057110512:30:47 AM[516] 2006-09-21 00:30:47.05 getspinlock pre-Sleep():
spid 0, 10000 yields on lock type "SRVPROC" (adr 0x1a5fe3f8)
We have no SQL jobs running at 00:30 so this looks like an internal SQL
maintenance job, possibly performance related?
This problem was caused by Veritas Backup Exec trying to backup the database
files (which were obviously in use!). The scripts used to include the
SQLBackup folder but had been changed to include SQLData as well, so removing
SQLData from the script resolved the problem.
"David Grant" wrote:

> We are using SQL 2000 v8.00.194 on Server 2003 SP1 with 2GB RAM
> At 00:30 at random day intervals, databases on two different servers are
> taken offline which causes jobs to be terminated and sessions left open. We
> have obtained a trace log using DebugView which contains:
> 0057110112:30:40 AM[516] 2006-09-21 00:30:40.15 getspinlock pre-Sleep():
> spid 8, 10000 yields on lock type "LOGFLUSHQ" (adr 0x1a406f7c)
>
> 0057110512:30:47 AM[516] 2006-09-21 00:30:47.05 getspinlock pre-Sleep():
> spid 0, 10000 yields on lock type "SRVPROC" (adr 0x1a5fe3f8)
> We have no SQL jobs running at 00:30 so this looks like an internal SQL
> maintenance job, possibly performance related?
>
|||David Grant wrote:
> This problem was caused by Veritas Backup Exec trying to backup the database
> files (which were obviously in use!). The scripts used to include the
> SQLBackup folder but had been changed to include SQLData as well, so removing
> SQLData from the script resolved the problem.
>
Veritas shouldn't have been able to take the databases offline like
this, the data files should have been "in use" by SQL at the time. The
only way I can picture this happening is if you have "Auto-Close"
enabled on your databases. If SQL "closed" the database, Veritas comes
along and starts backing up the data file, and SQL tries to "open" the
database again, it's going to fail. You shouldn't use the AutoClose or
AutoShrink options on a production database.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||AutoClose is not enabled on any of our live or test systems. AutoShrink is
only enabled on our test systems.
"Tracy McKibben" wrote:

> David Grant wrote:
> Veritas shouldn't have been able to take the databases offline like
> this, the data files should have been "in use" by SQL at the time. The
> only way I can picture this happening is if you have "Auto-Close"
> enabled on your databases. If SQL "closed" the database, Veritas comes
> along and starts backing up the data file, and SQL tries to "open" the
> database again, it's going to fail. You shouldn't use the AutoClose or
> AutoShrink options on a production database.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
|||I've seen similar things when the network admins were using
Veritas and VSS. I think it was along the lines that if you
have some activity or high activity in SQL Server, it can
prevent or interfere with the freeze I/O and then you can
get different errors. You may want to check the event logs
to see if you can find more information in there - look for
issues with VSS. The solution is to exclude the SQL Server
data directories. There might be some other configurations
in Veritas that impact this as well - don't know for sure.
-Sue
On Tue, 3 Oct 2006 06:11:02 -0700, David Grant
<DavidGrant@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>AutoClose is not enabled on any of our live or test systems. AutoShrink is
>only enabled on our test systems.
>"Tracy McKibben" wrote:

DB taken offline by getspinlock pre-Sleep() error

We are using SQL 2000 v8.00.194 on Server 2003 SP1 with 2GB RAM
At 00:30 at random day intervals, databases on two different servers are
taken offline which causes jobs to be terminated and sessions left open. We
have obtained a trace log using DebugView which contains:
00571101 12:30:40 AM [516] 2006-09-21 00:30:40.15 getspinlock pre-Sleep(
):
spid 8, 10000 yields on lock type "LOGFLUSHQ" (adr 0x1a406f7c)
00571105 12:30:47 AM [516] 2006-09-21 00:30:47.05 getspinlock pre-Sleep(
):
spid 0, 10000 yields on lock type "SRVPROC" (adr 0x1a5fe3f8)
We have no SQL jobs running at 00:30 so this looks like an internal SQL
maintenance job, possibly performance related?This problem was caused by Veritas Backup Exec trying to backup the database
files (which were obviously in use!). The scripts used to include the
SQLBackup folder but had been changed to include SQLData as well, so removin
g
SQLData from the script resolved the problem.
"David Grant" wrote:

> We are using SQL 2000 v8.00.194 on Server 2003 SP1 with 2GB RAM
> At 00:30 at random day intervals, databases on two different servers are
> taken offline which causes jobs to be terminated and sessions left open. W
e
> have obtained a trace log using DebugView which contains:
> 00571101 12:30:40 AM [516] 2006-09-21 00:30:40.15 getspinlock pre-Slee
p():
> spid 8, 10000 yields on lock type "LOGFLUSHQ" (adr 0x1a406f7c)
>
> 00571105 12:30:47 AM [516] 2006-09-21 00:30:47.05 getspinlock pre-Slee
p():
> spid 0, 10000 yields on lock type "SRVPROC" (adr 0x1a5fe3f8)
> We have no SQL jobs running at 00:30 so this looks like an internal SQL
> maintenance job, possibly performance related?
>|||David Grant wrote:
> This problem was caused by Veritas Backup Exec trying to backup the databa
se
> files (which were obviously in use!). The scripts used to include the
> SQLBackup folder but had been changed to include SQLData as well, so remov
ing
> SQLData from the script resolved the problem.
>
Veritas shouldn't have been able to take the databases offline like
this, the data files should have been "in use" by SQL at the time. The
only way I can picture this happening is if you have "Auto-Close"
enabled on your databases. If SQL "closed" the database, Veritas comes
along and starts backing up the data file, and SQL tries to "open" the
database again, it's going to fail. You shouldn't use the AutoClose or
AutoShrink options on a production database.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||AutoClose is not enabled on any of our live or test systems. AutoShrink is
only enabled on our test systems.
"Tracy McKibben" wrote:

> David Grant wrote:
> Veritas shouldn't have been able to take the databases offline like
> this, the data files should have been "in use" by SQL at the time. The
> only way I can picture this happening is if you have "Auto-Close"
> enabled on your databases. If SQL "closed" the database, Veritas comes
> along and starts backing up the data file, and SQL tries to "open" the
> database again, it's going to fail. You shouldn't use the AutoClose or
> AutoShrink options on a production database.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||I've seen similar things when the network admins were using
Veritas and VSS. I think it was along the lines that if you
have some activity or high activity in SQL Server, it can
prevent or interfere with the freeze I/O and then you can
get different errors. You may want to check the event logs
to see if you can find more information in there - look for
issues with VSS. The solution is to exclude the SQL Server
data directories. There might be some other configurations
in Veritas that impact this as well - don't know for sure.
-Sue
On Tue, 3 Oct 2006 06:11:02 -0700, David Grant
<DavidGrant@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>AutoClose is not enabled on any of our live or test systems. AutoShrink is
>only enabled on our test systems.
>"Tracy McKibben" wrote:
>

DB taken offline by getspinlock pre-Sleep() error

We are using SQL 2000 v8.00.194 on Server 2003 SP1 with 2GB RAM
At 00:30 at random day intervals, databases on two different servers are
taken offline which causes jobs to be terminated and sessions left open. We
have obtained a trace log using DebugView which contains:
00571101 12:30:40 AM [516] 2006-09-21 00:30:40.15 getspinlock pre-Sleep():
spid 8, 10000 yields on lock type "LOGFLUSHQ" (adr 0x1a406f7c)
00571105 12:30:47 AM [516] 2006-09-21 00:30:47.05 getspinlock pre-Sleep():
spid 0, 10000 yields on lock type "SRVPROC" (adr 0x1a5fe3f8)
We have no SQL jobs running at 00:30 so this looks like an internal SQL
maintenance job, possibly performance related?This problem was caused by Veritas Backup Exec trying to backup the database
files (which were obviously in use!). The scripts used to include the
SQLBackup folder but had been changed to include SQLData as well, so removing
SQLData from the script resolved the problem.
"David Grant" wrote:
> We are using SQL 2000 v8.00.194 on Server 2003 SP1 with 2GB RAM
> At 00:30 at random day intervals, databases on two different servers are
> taken offline which causes jobs to be terminated and sessions left open. We
> have obtained a trace log using DebugView which contains:
> 00571101 12:30:40 AM [516] 2006-09-21 00:30:40.15 getspinlock pre-Sleep():
> spid 8, 10000 yields on lock type "LOGFLUSHQ" (adr 0x1a406f7c)
>
> 00571105 12:30:47 AM [516] 2006-09-21 00:30:47.05 getspinlock pre-Sleep():
> spid 0, 10000 yields on lock type "SRVPROC" (adr 0x1a5fe3f8)
> We have no SQL jobs running at 00:30 so this looks like an internal SQL
> maintenance job, possibly performance related?
>|||David Grant wrote:
> This problem was caused by Veritas Backup Exec trying to backup the database
> files (which were obviously in use!). The scripts used to include the
> SQLBackup folder but had been changed to include SQLData as well, so removing
> SQLData from the script resolved the problem.
>
Veritas shouldn't have been able to take the databases offline like
this, the data files should have been "in use" by SQL at the time. The
only way I can picture this happening is if you have "Auto-Close"
enabled on your databases. If SQL "closed" the database, Veritas comes
along and starts backing up the data file, and SQL tries to "open" the
database again, it's going to fail. You shouldn't use the AutoClose or
AutoShrink options on a production database.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||AutoClose is not enabled on any of our live or test systems. AutoShrink is
only enabled on our test systems.
"Tracy McKibben" wrote:
> David Grant wrote:
> > This problem was caused by Veritas Backup Exec trying to backup the database
> > files (which were obviously in use!). The scripts used to include the
> > SQLBackup folder but had been changed to include SQLData as well, so removing
> > SQLData from the script resolved the problem.
> >
> Veritas shouldn't have been able to take the databases offline like
> this, the data files should have been "in use" by SQL at the time. The
> only way I can picture this happening is if you have "Auto-Close"
> enabled on your databases. If SQL "closed" the database, Veritas comes
> along and starts backing up the data file, and SQL tries to "open" the
> database again, it's going to fail. You shouldn't use the AutoClose or
> AutoShrink options on a production database.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||I've seen similar things when the network admins were using
Veritas and VSS. I think it was along the lines that if you
have some activity or high activity in SQL Server, it can
prevent or interfere with the freeze I/O and then you can
get different errors. You may want to check the event logs
to see if you can find more information in there - look for
issues with VSS. The solution is to exclude the SQL Server
data directories. There might be some other configurations
in Veritas that impact this as well - don't know for sure.
-Sue
On Tue, 3 Oct 2006 06:11:02 -0700, David Grant
<DavidGrant@.discussions.microsoft.com> wrote:
>AutoClose is not enabled on any of our live or test systems. AutoShrink is
>only enabled on our test systems.
>"Tracy McKibben" wrote:
>> David Grant wrote:
>> > This problem was caused by Veritas Backup Exec trying to backup the database
>> > files (which were obviously in use!). The scripts used to include the
>> > SQLBackup folder but had been changed to include SQLData as well, so removing
>> > SQLData from the script resolved the problem.
>> >
>> Veritas shouldn't have been able to take the databases offline like
>> this, the data files should have been "in use" by SQL at the time. The
>> only way I can picture this happening is if you have "Auto-Close"
>> enabled on your databases. If SQL "closed" the database, Veritas comes
>> along and starts backing up the data file, and SQL tries to "open" the
>> database again, it's going to fail. You shouldn't use the AutoClose or
>> AutoShrink options on a production database.
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com

DB restore error to new server

Hi,
We have just built a new Windows 2003 Std Ed, SP1 server with SQL
Server 2005 no SP.
We are attempting to restore the databases from an old server to this
new one, however after the restore process gets to 100% we recive the
following error:
SQL Server detected a logical consistency-based I/O error: incorrect
checksum (expected: 0x65f3e4dc; actual: 0x65f3e4de). It occurred during
a read of page (1:32625) in database ID 7 at offset 0x0000000fee2000 in
file 'D:\sqldata\filename.mdf'. Additional messages in the SQL Server
error log or system event log may provide more detail. This is a severe
error condition that threatens database integrity and must be corrected
immediately. Complete a full database consistency check (DBCC CHECKDB)
Does anybody know what might be the problem?
Regards
Paul RobertsBob wrote:
> Hi,
> We have just built a new Windows 2003 Std Ed, SP1 server with SQL
> Server 2005 no SP.
> We are attempting to restore the databases from an old server to this
> new one, however after the restore process gets to 100% we recive the
> following error:
> SQL Server detected a logical consistency-based I/O error: incorrect
> checksum (expected: 0x65f3e4dc; actual: 0x65f3e4de). It occurred during
> a read of page (1:32625) in database ID 7 at offset 0x0000000fee2000 in
> file 'D:\sqldata\filename.mdf'. Additional messages in the SQL Server
> error log or system event log may provide more detail. This is a severe
> error condition that threatens database integrity and must be corrected
> immediately. Complete a full database consistency check (DBCC CHECKDB)
> Does anybody know what might be the problem?
> Regards
> Paul Roberts
>
Sounds like a corrupt backup. That error is occurring as SQL tries to
"recover" the new database to bring it online. It's telling you that
database has an internal problem of some kind. I would run a new backup
of the original database to restore from. Barring that, use DBCC
CHECKDB to attempt a repair of the restored database.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi,
We tried that too with no luck.
This database is from a replicated database being restored to a
non-replication system.|||Bob wrote:
> Hi,
> We tried that too with no luck.
> This database is from a replicated database being restored to a
> non-replication system.
>
Tried the new backup, or tried running DBCC? If the new backup gave the
same error, you might want to run DBCC CHECKDB against the original
database.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The RESTORE command has a CONTINUE_AFTER_ERROR option. That should at lest g
et the database there,
so you can do something with it (like last resort emergency mode). But you s
hould really CHECKDB the
source database. If that db is clean, you shouldn't get corrupted backups.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bob" <pmroberts@.talk21.com> wrote in message
news:1152811634.990074.129800@.p79g2000cwp.googlegroups.com...
> Hi,
> We tried that too with no luck.
> This database is from a replicated database being restored to a
> non-replication system.
>|||2 questions -
1. How are you copying over the .bak file between the 2 machines?
2. Is the file sie the same?
"Bob" wrote:

> Hi,
> We have just built a new Windows 2003 Std Ed, SP1 server with SQL
> Server 2005 no SP.
> We are attempting to restore the databases from an old server to this
> new one, however after the restore process gets to 100% we recive the
> following error:
> SQL Server detected a logical consistency-based I/O error: incorrect
> checksum (expected: 0x65f3e4dc; actual: 0x65f3e4de). It occurred during
> a read of page (1:32625) in database ID 7 at offset 0x0000000fee2000 in
> file 'D:\sqldata\filename.mdf'. Additional messages in the SQL Server
> error log or system event log may provide more detail. This is a severe
> error condition that threatens database integrity and must be corrected
> immediately. Complete a full database consistency check (DBCC CHECKDB)
> Does anybody know what might be the problem?
> Regards
> Paul Roberts
>|||Hi,
The files are being copied using windows drag and drop.
The file sizes are the same.
I am using SQL server management studio, I cannot see the RESTORE
command has a CONTINUE_AFTER_ERROR, I tried this option under the
backup option but with no luck.
I also tried new backups.
Is there a limit to the size of the backups?|||How do I use DBCC CHECKDB?|||Bob wrote:
> Hi,
> The files are being copied using windows drag and drop.
> The file sizes are the same.
> I am using SQL server management studio, I cannot see the RESTORE
> command has a CONTINUE_AFTER_ERROR, I tried this option under the
> backup option but with no luck.
> I also tried new backups.
> Is there a limit to the size of the backups?
>
You might need to do the restore with the sql command in order to
specify the CONTINUE AFTER ERROR option.
Try to look up RESTORE Database in Books On Line.
Also, have you tried to restore the files to a different drive? It might
not be the case, but you could have a bad spot on the disk and that's
why it fails (..I know it's a wild guess but it should be fairly easy to
try...).
Regards
Steen Schlter Persson
Databaseadministrator / Systemadministrator|||Hi,
Problem solved.
The issue is due to the fact that they are replication databases.
Since I am restoring to a different server, without replication, the
SQL server is supposed to realise this and remove replication status,
however it does not and you have to remove this manually with the
command: sp_removedbreplication dbname
You can then bring the database online.
See http://msdn2.microsoft.com/en-us/library/ms151782(d=ide).aspx
for more information.

DB restore error to new server

Hi,
We have just built a new Windows 2003 Std Ed, SP1 server with SQL
Server 2005 no SP.
We are attempting to restore the databases from an old server to this
new one, however after the restore process gets to 100% we recive the
following error:
SQL Server detected a logical consistency-based I/O error: incorrect
checksum (expected: 0x65f3e4dc; actual: 0x65f3e4de). It occurred during
a read of page (1:32625) in database ID 7 at offset 0x0000000fee2000 in
file 'D:\sqldata\filename.mdf'. Additional messages in the SQL Server
error log or system event log may provide more detail. This is a severe
error condition that threatens database integrity and must be corrected
immediately. Complete a full database consistency check (DBCC CHECKDB)
Does anybody know what might be the problem?
Regards
Paul RobertsBob wrote:
> Hi,
> We have just built a new Windows 2003 Std Ed, SP1 server with SQL
> Server 2005 no SP.
> We are attempting to restore the databases from an old server to this
> new one, however after the restore process gets to 100% we recive the
> following error:
> SQL Server detected a logical consistency-based I/O error: incorrect
> checksum (expected: 0x65f3e4dc; actual: 0x65f3e4de). It occurred during
> a read of page (1:32625) in database ID 7 at offset 0x0000000fee2000 in
> file 'D:\sqldata\filename.mdf'. Additional messages in the SQL Server
> error log or system event log may provide more detail. This is a severe
> error condition that threatens database integrity and must be corrected
> immediately. Complete a full database consistency check (DBCC CHECKDB)
> Does anybody know what might be the problem?
> Regards
> Paul Roberts
>
Sounds like a corrupt backup. That error is occurring as SQL tries to
"recover" the new database to bring it online. It's telling you that
database has an internal problem of some kind. I would run a new backup
of the original database to restore from. Barring that, use DBCC
CHECKDB to attempt a repair of the restored database.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi,
We tried that too with no luck.
This database is from a replicated database being restored to a
non-replication system.|||Bob wrote:
> Hi,
> We tried that too with no luck.
> This database is from a replicated database being restored to a
> non-replication system.
>
Tried the new backup, or tried running DBCC? If the new backup gave the
same error, you might want to run DBCC CHECKDB against the original
database.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The RESTORE command has a CONTINUE_AFTER_ERROR option. That should at lest get the database there,
so you can do something with it (like last resort emergency mode). But you should really CHECKDB the
source database. If that db is clean, you shouldn't get corrupted backups.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bob" <pmroberts@.talk21.com> wrote in message
news:1152811634.990074.129800@.p79g2000cwp.googlegroups.com...
> Hi,
> We tried that too with no luck.
> This database is from a replicated database being restored to a
> non-replication system.
>|||2 questions -
1. How are you copying over the .bak file between the 2 machines?
2. Is the file sie the same?
"Bob" wrote:
> Hi,
> We have just built a new Windows 2003 Std Ed, SP1 server with SQL
> Server 2005 no SP.
> We are attempting to restore the databases from an old server to this
> new one, however after the restore process gets to 100% we recive the
> following error:
> SQL Server detected a logical consistency-based I/O error: incorrect
> checksum (expected: 0x65f3e4dc; actual: 0x65f3e4de). It occurred during
> a read of page (1:32625) in database ID 7 at offset 0x0000000fee2000 in
> file 'D:\sqldata\filename.mdf'. Additional messages in the SQL Server
> error log or system event log may provide more detail. This is a severe
> error condition that threatens database integrity and must be corrected
> immediately. Complete a full database consistency check (DBCC CHECKDB)
> Does anybody know what might be the problem?
> Regards
> Paul Roberts
>|||Hi,
The files are being copied using windows drag and drop.
The file sizes are the same.
I am using SQL server management studio, I cannot see the RESTORE
command has a CONTINUE_AFTER_ERROR, I tried this option under the
backup option but with no luck.
I also tried new backups.
Is there a limit to the size of the backups?|||How do I use DBCC CHECKDB?|||This is a multi-part message in MIME format.
--090202070601080406010907
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
Bob wrote:
> Hi,
> The files are being copied using windows drag and drop.
> The file sizes are the same.
> I am using SQL server management studio, I cannot see the RESTORE
> command has a CONTINUE_AFTER_ERROR, I tried this option under the
> backup option but with no luck.
> I also tried new backups.
> Is there a limit to the size of the backups?
>
You might need to do the restore with the sql command in order to
specify the CONTINUE AFTER ERROR option.
Try to look up RESTORE Database in Books On Line.
Also, have you tried to restore the files to a different drive? It might
not be the case, but you could have a bad spot on the disk and that's
why it fails (..I know it's a wild guess but it should be fairly easy to
try...).
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator
--090202070601080406010907
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Bob wrote:
<blockquote
cite="mid1152874083.247364.97190@.35g2000cwc.googlegroups.com"
type="cite">
<pre wrap="">Hi,
The files are being copied using windows drag and drop.
The file sizes are the same.
I am using SQL server management studio, I cannot see the RESTORE
command has a CONTINUE_AFTER_ERROR, I tried this option under the
backup option but with no luck.
I also tried new backups.
Is there a limit to the size of the backups?
</pre>
</blockquote>
<font size="-1"><font face="Arial">You might need to do the restore
with the sql command in order to specify the CONTINUE AFTER ERROR
option.<br>
Try to look up RESTORE Database in Books On Line.<br>
<br>
Also, have you tried to restore the files to a different drive? It
might not be the case, but you could have a bad spot on the disk and
that's why it fails (..I know it's a wild guess but it should be fairly
easy to try...).<br>
<br>
<br>
-- <br>
Regards<br>
Steen Schlüter Persson<br>
Databaseadministrator / Systemadministrator<br>
</font></font>
</body>
</html>
--090202070601080406010907--|||Hi,
Problem solved.
The issue is due to the fact that they are replication databases.
Since I am restoring to a different server, without replication, the
SQL server is supposed to realise this and remove replication status,
however it does not and you have to remove this manually with the
command: sp_removedbreplication dbname
You can then bring the database online.
See http://msdn2.microsoft.com/en-us/library/ms151782(d=ide).aspx
for more information.

DB Mirror

Hello,
SQL Server 2005 SP1 SE on WINDOWS 2003 sp1 both principal and mirror
SQL Serverver Express SP1 on WINDOWS 2003 sp1 Witness
In mirror monitoring witness indicates disconected from mirror.
All else is green.
Can ping witness from mirror and connect via MSSMS.
No errors in Witness SQL server errorlog.
What can I do to debug this? I have drilled into the status and I do not
see any errors.Hi Joe
I think that this may be correct see http://tinyurl.com/2zbgu8
John
"Joe" wrote:

> Hello,
> SQL Server 2005 SP1 SE on WINDOWS 2003 sp1 both principal and mirror
> SQL Serverver Express SP1 on WINDOWS 2003 sp1 Witness
> In mirror monitoring witness indicates disconected from mirror.
> All else is green.
> Can ping witness from mirror and connect via MSSMS.
> No errors in Witness SQL server errorlog.
> What can I do to debug this? I have drilled into the status and I do not
> see any errors.|||Hi John.
Thanks for the reply.
I kinda assumed I had a connectivity problem. Any ideas on how to
debug/resolve it?
Joe
"John Bell" wrote:
[vbcol=seagreen]
> Hi Joe
> I think that this may be correct see http://tinyurl.com/2zbgu8
> John
> "Joe" wrote:
>|||Hi Joe
I am not a replication/mirroring export. You may want to try the replication
news group!
John
"Joe" wrote:
[vbcol=seagreen]
> Hi John.
> Thanks for the reply.
> I kinda assumed I had a connectivity problem. Any ideas on how to
> debug/resolve it?
> Joe
> "John Bell" wrote:
>

DB Mirror

Hello,
SQL Server 2005 SP1 SE on WINDOWS 2003 sp1 both principal and mirror
SQL Serverver Express SP1 on WINDOWS 2003 sp1 Witness
In mirror monitoring witness indicates disconected from mirror.
All else is green.
Can ping witness from mirror and connect via MSSMS.
No errors in Witness SQL server errorlog.
What can I do to debug this? I have drilled into the status and I do not
see any errors.Hi Joe
I think that this may be correct see http://tinyurl.com/2zbgu8
John
"Joe" wrote:
> Hello,
> SQL Server 2005 SP1 SE on WINDOWS 2003 sp1 both principal and mirror
> SQL Serverver Express SP1 on WINDOWS 2003 sp1 Witness
> In mirror monitoring witness indicates disconected from mirror.
> All else is green.
> Can ping witness from mirror and connect via MSSMS.
> No errors in Witness SQL server errorlog.
> What can I do to debug this? I have drilled into the status and I do not
> see any errors.|||Hi John.
Thanks for the reply.
I kinda assumed I had a connectivity problem. Any ideas on how to
debug/resolve it?
Joe
"John Bell" wrote:
> Hi Joe
> I think that this may be correct see http://tinyurl.com/2zbgu8
> John
> "Joe" wrote:
> > Hello,
> > SQL Server 2005 SP1 SE on WINDOWS 2003 sp1 both principal and mirror
> > SQL Serverver Express SP1 on WINDOWS 2003 sp1 Witness
> > In mirror monitoring witness indicates disconected from mirror.
> > All else is green.
> > Can ping witness from mirror and connect via MSSMS.
> > No errors in Witness SQL server errorlog.
> > What can I do to debug this? I have drilled into the status and I do not
> > see any errors.|||Hi Joe
I am not a replication/mirroring export. You may want to try the replication
news group!
John
"Joe" wrote:
> Hi John.
> Thanks for the reply.
> I kinda assumed I had a connectivity problem. Any ideas on how to
> debug/resolve it?
> Joe
> "John Bell" wrote:
> > Hi Joe
> >
> > I think that this may be correct see http://tinyurl.com/2zbgu8
> >
> > John
> >
> > "Joe" wrote:
> >
> > > Hello,
> > > SQL Server 2005 SP1 SE on WINDOWS 2003 sp1 both principal and mirror
> > > SQL Serverver Express SP1 on WINDOWS 2003 sp1 Witness
> > > In mirror monitoring witness indicates disconected from mirror.
> > > All else is green.
> > > Can ping witness from mirror and connect via MSSMS.
> > > No errors in Witness SQL server errorlog.
> > > What can I do to debug this? I have drilled into the status and I do not
> > > see any errors.

DB Mirror

Hello,
SQL Server 2005 SP1 SE on WINDOWS 2003 sp1 both principal and mirror
SQL Serverver Express SP1 on WINDOWS 2003 sp1 Witness
In mirror monitoring witness indicates disconected from mirror.
All else is green.
Can ping witness from mirror and connect via MSSMS.
No errors in Witness SQL server errorlog.
What can I do to debug this? I have drilled into the status and I do not
see any errors.
Hi Joe
I think that this may be correct see http://tinyurl.com/2zbgu8
John
"Joe" wrote:

> Hello,
> SQL Server 2005 SP1 SE on WINDOWS 2003 sp1 both principal and mirror
> SQL Serverver Express SP1 on WINDOWS 2003 sp1 Witness
> In mirror monitoring witness indicates disconected from mirror.
> All else is green.
> Can ping witness from mirror and connect via MSSMS.
> No errors in Witness SQL server errorlog.
> What can I do to debug this? I have drilled into the status and I do not
> see any errors.
|||Hi John.
Thanks for the reply.
I kinda assumed I had a connectivity problem. Any ideas on how to
debug/resolve it?
Joe
"John Bell" wrote:
[vbcol=seagreen]
> Hi Joe
> I think that this may be correct see http://tinyurl.com/2zbgu8
> John
> "Joe" wrote:
|||Hi Joe
I am not a replication/mirroring export. You may want to try the replication
news group!
John
"Joe" wrote:
[vbcol=seagreen]
> Hi John.
> Thanks for the reply.
> I kinda assumed I had a connectivity problem. Any ideas on how to
> debug/resolve it?
> Joe
> "John Bell" wrote:

DB marked Suspect

I have SQL Express SP1 installed on serveral machines, each has one instance and each instance has more than one database.

Recently, I found out some dbs are marked Suspect. MOM tool reported something like:

The database "your-DB" in the instance "Your-Instance" is in a critical state. The state is: Suspect.

I tried to run "DBCC CHECKDB", but it failed.

Can someone tell me what could cause the problem, and how to fix it?

Thanks,

Peter

This generally occurs when there is a bad restore from a backup. If this is your situation then first attempt to do the restore again. It's telling that the databases MDF file has been corrupted.

If redoing the restore does not work then run this:
DBCC CHECKDB with REPAIR_REBUILD

if that fails then this

DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS

|||

Thanks for the reply.

In my case, after a few tries, I found out that simply restarting the service of the instance would fix the problem. I did not really had time to debug the problem because customer was waiting. Next time if the problem appears again. I will look into it a little bit deepper.

It seems that the problem occurred after the additional databases were created for the same instance. BTW, we have SQL2005 running on the same machine too, and the SQL browser service running under different user than the user for SQL Express instance.

DB marked Suspect

I have SQL Express SP1 installed on serveral machines, each has one instance and each instance has more than one database.

Recently, I found out some dbs are marked Suspect. MOM tool reported something like:

The database "your-DB" in the instance "Your-Instance" is in a critical state. The state is: Suspect.

I tried to run "DBCC CHECKDB", but it failed.

Can someone tell me what could cause the problem, and how to fix it?

Thanks,

Peter

This generally occurs when there is a bad restore from a backup. If this is your situation then first attempt to do the restore again. It's telling that the databases MDF file has been corrupted.

If redoing the restore does not work then run this:
DBCC CHECKDB with REPAIR_REBUILD

if that fails then this

DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS|||

Thanks for the reply.

In my case, after a few tries, I found out that simply restarting the service of the instance would fix the problem. I did not really had time to debug the problem because customer was waiting. Next time if the problem appears again. I will look into it a little bit deepper.

It seems that the problem occurred after the additional databases were created for the same instance. BTW, we have SQL2005 running on the same machine too, and the SQL browser service running under different user than the user for SQL Express instance.