do I have a torn page?

Ive been getting alerts like the following all morning:
DESCRIPTION: Error: 823, Severity: 24, State: 2
I/O error (bad page ID) detected during read at offset 0x0000111c264000 in
file 'D:\MSSQL\MSSQL\Data\production_Data.mdf'.
COMMENT: (None)
JOB RUN: (None)
What do I need to do here?
TIA, ChrisRHi
Look like corruption.
Run DBCC CHECKDB on the database and get last nights backup out, you might
need to restore it.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"ChrisR" <noemail@.bla.com> wrote in message
news:Ow4eB1ZTFHA.2680@.tk2msftngp13.phx.gbl...
> Ive been getting alerts like the following all morning:
> DESCRIPTION: Error: 823, Severity: 24, State: 2
> I/O error (bad page ID) detected during read at offset 0x0000111c264000 in
> file 'D:\MSSQL\MSSQL\Data\production_Data.mdf'.
> COMMENT: (None)
> JOB RUN: (None)
>
> What do I need to do here?
> TIA, ChrisR
>|||Here's some additional info about those situations:
http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <noemail@.bla.com> wrote in message news:Ow4eB1ZTFHA.2680@.tk2msftngp13.phx.gbl...
> Ive been getting alerts like the following all morning:
> DESCRIPTION: Error: 823, Severity: 24, State: 2
> I/O error (bad page ID) detected during read at offset 0x0000111c264000 in file
> 'D:\MSSQL\MSSQL\Data\production_Data.mdf'.
> COMMENT: (None)
> JOB RUN: (None)
>
> What do I need to do here?
> TIA, ChrisR
>

do I have a torn page?

Ive been getting alerts like the following all morning:
DESCRIPTION: Error: 823, Severity: 24, State: 2
I/O error (bad page ID) detected during read at offset 0x0000111c264000 in
file 'D:\MSSQL\MSSQL\Data\production_Data.mdf'.
COMMENT: (None)
JOB RUN: (None)
What do I need to do here?
TIA, ChrisRHi
Look like corruption.
Run DBCC CHECKDB on the database and get last nights backup out, you might
need to restore it.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"ChrisR" <noemail@.bla.com> wrote in message
news:Ow4eB1ZTFHA.2680@.tk2msftngp13.phx.gbl...
> Ive been getting alerts like the following all morning:
> DESCRIPTION: Error: 823, Severity: 24, State: 2
> I/O error (bad page ID) detected during read at offset 0x0000111c264000 in
> file 'D:\MSSQL\MSSQL\Data\production_Data.mdf'.
> COMMENT: (None)
> JOB RUN: (None)
>
> What do I need to do here?
> TIA, ChrisR
>|||Here's some additional info about those situations:
http://www.karaszi.com/SQLServer/in..._suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <noemail@.bla.com> wrote in message news:Ow4eB1ZTFHA.2680@.tk2msftngp13.phx.gbl...[vb
col=seagreen]
> Ive been getting alerts like the following all morning:
> DESCRIPTION: Error: 823, Severity: 24, State: 2
> I/O error (bad page ID) detected during read at offset 0x0000111c264000 in
file
> 'D:\MSSQL\MSSQL\Data\production_Data.mdf'.
> COMMENT: (None)
> JOB RUN: (None)
>
> What do I need to do here?
> TIA, ChrisR
>[/vbcol]

do I have a torn page?

Ive been getting alerts like the following all morning:
DESCRIPTION: Error: 823, Severity: 24, State: 2
I/O error (bad page ID) detected during read at offset 0x0000111c264000 in
file 'D:\MSSQL\MSSQL\Data\production_Data.mdf'.
COMMENT: (None)
JOB RUN: (None)
What do I need to do here?
TIA, ChrisR
Hi
Look like corruption.
Run DBCC CHECKDB on the database and get last nights backup out, you might
need to restore it.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"ChrisR" <noemail@.bla.com> wrote in message
news:Ow4eB1ZTFHA.2680@.tk2msftngp13.phx.gbl...
> Ive been getting alerts like the following all morning:
> DESCRIPTION: Error: 823, Severity: 24, State: 2
> I/O error (bad page ID) detected during read at offset 0x0000111c264000 in
> file 'D:\MSSQL\MSSQL\Data\production_Data.mdf'.
> COMMENT: (None)
> JOB RUN: (None)
>
> What do I need to do here?
> TIA, ChrisR
>
|||Here's some additional info about those situations:
http://www.karaszi.com/SQLServer/inf...suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <noemail@.bla.com> wrote in message news:Ow4eB1ZTFHA.2680@.tk2msftngp13.phx.gbl...
> Ive been getting alerts like the following all morning:
> DESCRIPTION: Error: 823, Severity: 24, State: 2
> I/O error (bad page ID) detected during read at offset 0x0000111c264000 in file
> 'D:\MSSQL\MSSQL\Data\production_Data.mdf'.
> COMMENT: (None)
> JOB RUN: (None)
>
> What do I need to do here?
> TIA, ChrisR
>
sql

Divide by zero error! Help!

I don't understand why I get the error "Server: Msg 8134, Level 16,
State 1, Line 1
Divide by zero error encountered."
I check for 0, actually if I change the statement after ELSE to 2, it
will run with no issue and get 1 since the when statement is 0 in this
case. Please help.
SELECT
CASE WHEN SUM(CONVERT(INT, ICFPM_USER_12)* CONVERT(INT,
ICFPM_USER_14))= 0 THEN 1
ELSE SUM(REV_ORDER_QTY/(CONVERT(INT, ICFPM_USER_12)*CONVERT(INT,
ICFPM_USER_14)))
END
FROM SOFOD, ICFPM
WHERE SOFOD.PART_ID = ICFPM.PART_ID AND SOFOD.SO_ID = '105706'Hi
Don't assume that the execution order of an ELSE will follow the order you
coded. It may evaluate both CASEs in parallel and then use the output later.
During query execution, your query may be run differently to what you think.
This can be influenced by number of processors, RAM available, indexes and
statistics.
You need to make sure that your query does not have the possibility of
failing, no matter the code execution path.
Regards
--
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
<zod91@.yahoo.com> wrote in message
news:1149560228.320495.202850@.j55g2000cwa.googlegroups.com...
>I don't understand why I get the error "Server: Msg 8134, Level 16,
> State 1, Line 1
> Divide by zero error encountered."
> I check for 0, actually if I change the statement after ELSE to 2, it
> will run with no issue and get 1 since the when statement is 0 in this
> case. Please help.
>
> SELECT
> CASE WHEN SUM(CONVERT(INT, ICFPM_USER_12)* CONVERT(INT,
> ICFPM_USER_14))= 0 THEN 1
> ELSE SUM(REV_ORDER_QTY/(CONVERT(INT, ICFPM_USER_12)*CONVERT(INT,
> ICFPM_USER_14)))
> END
> FROM SOFOD, ICFPM
> WHERE SOFOD.PART_ID = ICFPM.PART_ID AND SOFOD.SO_ID = '105706'
>

Divide by zero error! Help!

I don't understand why I get the error "Server: Msg 8134, Level 16,
State 1, Line 1
Divide by zero error encountered."
I check for 0, actually if I change the statement after ELSE to 2, it
will run with no issue and get 1 since the when statement is 0 in this
case. Please help.
SELECT
CASE WHEN SUM(CONVERT(INT, ICFPM_USER_12)* CONVERT(INT,
ICFPM_USER_14))= 0 THEN 1
ELSE SUM(REV_ORDER_QTY/(CONVERT(INT, ICFPM_USER_12)*CONVERT(INT,
ICFPM_USER_14)))
END
FROM SOFOD, ICFPM
WHERE SOFOD.PART_ID = ICFPM.PART_ID AND SOFOD.SO_ID = '105706'Hi
Don't assume that the execution order of an ELSE will follow the order you
coded. It may evaluate both CASEs in parallel and then use the output later.
During query execution, your query may be run differently to what you think.
This can be influenced by number of processors, RAM available, indexes and
statistics.
You need to make sure that your query does not have the possibility of
failing, no matter the code execution path.
Regards
--
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
<zod91@.yahoo.com> wrote in message
news:1149560228.320495.202850@.j55g2000cwa.googlegroups.com...
>I don't understand why I get the error "Server: Msg 8134, Level 16,
> State 1, Line 1
> Divide by zero error encountered."
> I check for 0, actually if I change the statement after ELSE to 2, it
> will run with no issue and get 1 since the when statement is 0 in this
> case. Please help.
>
> SELECT
> CASE WHEN SUM(CONVERT(INT, ICFPM_USER_12)* CONVERT(INT,
> ICFPM_USER_14))= 0 THEN 1
> ELSE SUM(REV_ORDER_QTY/(CONVERT(INT, ICFPM_USER_12)*CONVERT(INT,
> ICFPM_USER_14)))
> END
> FROM SOFOD, ICFPM
> WHERE SOFOD.PART_ID = ICFPM.PART_ID AND SOFOD.SO_ID = '105706'
>

Divide by zero error! Help!

I don't understand why I get the error "Server: Msg 8134, Level 16,
State 1, Line 1
Divide by zero error encountered."

I check for 0, actually if I change the statement after ELSE to 2, it
will run with no issue and get 1 since the when statement is 0 in this
case. Please help.

SELECT
CASE WHEN SUM(CONVERT(INT, ICFPM_USER_12)* CONVERT(INT,
ICFPM_USER_14))= 0 THEN 1
ELSE SUM(REV_ORDER_QTY/(CONVERT(INT, ICFPM_USER_12)*CONVERT(INT,
ICFPM_USER_14)))

END
FROM SOFOD, ICFPM
WHERE SOFOD.PART_ID = ICFPM.PART_ID AND SOFOD.SO_ID = '105706'zod91@.yahoo.com (zod91@.yahoo.com) writes:
> I don't understand why I get the error "Server: Msg 8134, Level 16,
> State 1, Line 1
> Divide by zero error encountered."
> I check for 0, actually if I change the statement after ELSE to 2, it
> will run with no issue and get 1 since the when statement is 0 in this
> case. Please help.
> SELECT
> CASE WHEN SUM(CONVERT(INT, ICFPM_USER_12)* CONVERT(INT,
> ICFPM_USER_14))= 0 THEN 1
> ELSE SUM(REV_ORDER_QTY/(CONVERT(INT, ICFPM_USER_12)*CONVERT(INT,
> ICFPM_USER_14)))
> END
> FROM SOFOD, ICFPM
> WHERE SOFOD.PART_ID = ICFPM.PART_ID AND SOFOD.SO_ID = '105706'

I guess that you get the error, because SQL Server computes both SUMs
when traversing the rows. If it were to do the statement literaly,
it would first have to traverse the rows to compute the first sum,
and if that sum is not 0, traverse once more. So you would need:

SELECT CASE WHEN SUM(CONVERT(INT, ICFPM_USER_12) *
CONVERT(INT, ICFPM_USER_14)) = 0 THEN 1
ELSE SUM(REV_ORDER_QTY /
CASE WHEN CONVERT(INT, ICFPM_USER_12) *
CONVERT(INT, ICFPM_USER_14) <> 0
THEN CONVERT(INT, ICFPM_USER_12) *
CONVERT(INT, ICFPM_USER_14)
ELSE 1 -- or whatever
END
FROM SOFOD, ICFPM
WHERE SOFOD.PART_ID = ICFPM.PART_ID AND SOFOD.SO_ID = '105706'

This still looks funny to me. As soon as any of the columns is 0 for a
row, the outcome is always 1. Then again, I don't anything about the
business, so maybe this is OK.

--
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

Distribution database in suspect state

I have sqlserver2000 and I have set up replication. Accidentally I
have deleted distribution data and distribution database went to
suspect state, Is there any way I can recover from this state?
Put the database into emergency mode and bcp the data out. Drop it, recreate
it and then bcp it back in.
Drop it using
sp_dropdistpublisher with no_checks and ignore_distributor,
sp_dropdistributiondb and sp_dropdistributor.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ST" <sitatetali@.gmail.com> wrote in message
news:1173548966.133736.226600@.v33g2000cwv.googlegr oups.com...
>I have sqlserver2000 and I have set up replication. Accidentally I
> have deleted distribution data and distribution database went to
> suspect state, Is there any way I can recover from this state?
>

Distributed Transaction Persistent Error

When I try to make a distributed transaction, I always get this error:

Server: Msg 7391, Level 16, State 1, Line 14
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].

That is although, I am sure that both servers have active DTC.

Have you verified from Control Panel->Component Services->Computers->My Computer->Properties->Security Configuration that Network DTC access is allowed and other settings on the same tab?

|||

No, I didn't verify that.

Now I checked the Network DTC Access check box in the place you mentioned, but still the same error persistent.

What are the other settings in the same tab that I should check?

Thanks

|||

You may want to check the following to trouble shoot the Distributed transaction errors:

1. Verify there are no MS DTC firewall issues. Please see link: http://support.microsoft.com/default.aspx?scid=kb;en-us;Q306843 on how to trouble shoot them.
2. In case the two computers are running in domains or workgroups that do not trust each other, the MSDTC may fail to mutually authenticate. Please see link: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cossdk/html/dc45d41e-58f7-412f-834d-fec72e00fc01.asp for more details.

Thanks

Suroor

|||

The matter is very sophisticated.

Isn't there an easier way than that whole sophistication?

What are the settings to allow under MSDTC "Allow Network Access"?

Thanks

|||

http://support.microsoft.com/kb/827805#kb3

http://support.microsoft.com/kb/329332/

HTH

distributed transaction failure

Im attempting to insert the result set from a remote query into a local table and Im getting the following error:

Msg 8501, Level 16, State 1, Line 1
MSDTC on server 'REMOTESVR' is unavailable.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d01c].
Msg 7391, Level 16, State 1, Line 1

The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.

MSDTC is running on both servers. Has anyone ever seen this or have any insight into the cause?


The code that Im trying to run is:

create table msver
(
[Index] int,
[Name] varchar(30),
Internal_Value varchar(20),
Character_Value varchar(512)
)

insert into msver
exec ('exec [REMOTESVR].master.dbo.xp_msver')

Note that the remote query works fine.

Thanks!
EricMake sure MSDTC is started on both the servers.|||I might be wrong, but I don't think you're going to get xp_msver to work this way. Run it as:

insert msver
exec [REMOTESVR].sp_executesql 'exec master.dbo.xp_msver'

See if that works. I don't have my laptop hooked up, or I would test it really quick at the lab. :)|||Check on the 'remoteserver' SQL server error log for #
"... server Attempting to initialize Distributed Transaction Coordinator..."