sql reports: errors

Showing posts with label errors. Show all posts

Thursday, March 29, 2012

Can we handle ALL errors within a stored proceudre?

Hello, friends,
We call stored procedures from our app (asp.net), and use Try...Catch to
handle any possible DB error. But, can we handle all errors in a stored
proceudre? In another word, all DB errors should be caught within a stored
procedure, and return back to callers gracefully, as if nothing wrong.
We tried IF @.@.ERROR > 0 in sp, but errors such as Unique Constraint
Violation were still caught by our app, not sp.
Any ideas, reference papers, sample source code?
Thanks a lot.http://www.sommarskog.se/error-handling-II.html
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:59F7E8E6-1477-4A42-9B42-BB34D0136102@.microsoft.com...
> Hello, friends,
> We call stored procedures from our app (asp.net), and use Try...Catch to
> handle any possible DB error. But, can we handle all errors in a stored
> proceudre? In another word, all DB errors should be caught within a stored
> procedure, and return back to callers gracefully, as if nothing wrong.
> We tried IF @.@.ERROR > 0 in sp, but errors such as Unique Constraint
> Violation were still caught by our app, not sp.
> Any ideas, reference papers, sample source code?
> Thanks a lot.

Can we handle all errors within a stored proceudre.

Hello, friends,
We are calling stored procedures (sp) from our web app (asp.net). Since
using Try...Catch... in asp.net with C# or VB.net is very expensive, we are
considering to handle all database error in sp so that web app won't see any
DB error.
Do we have a way to catch all erros in sp and return back to callers
gracefully, as if nothing wrong in DB?
We tried to check IF @.@.ERROR > 0, but web app still get erros, such as
Unique contraint violation, etc.
Any ideas, reference papers, sample source code?
Thanks a lotexamnotes <Andrew@.discussions.microsoft.com> wrote in
news:0F041009-08FF-4FF1-8AC7-D74968D16C05@.microsoft.com:

> We are calling stored procedures (sp) from our web app (asp.net).
> Since using Try...Catch... in asp.net with C# or VB.net is very
> expensive, we are considering to handle all database error in sp so
> that web app won't see any DB error.
> Do we have a way to catch all erros in sp and return back to callers
> gracefully, as if nothing wrong in DB?
> We tried to check IF @.@.ERROR > 0, but web app still get erros, such as
> Unique contraint violation, etc.
> Any ideas, reference papers, sample source code?
In SQL Server 2005 you can, I've used the following code sample to
demonstrate it in my classes (Using the pubs database):
set XACT_ABORT on
begin try
begin transaction
declare @.id int;
insert into jobs values ('Testjobb',10,10)
set @.id = @.@.identity;
insert into employee values ('123456789','Gates',null,'Bill',@.id+
1,10,'0877',GetDate());
commit transaction
end try
begin catch
select error_message();
rollback transaction
end catch
I'll leave it up to you to implement this in a stored procedure.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||What about SQL Server 2000? That is the one we are using.
"Ole Kristian Bang?s" wrote:

> examnotes <Andrew@.discussions.microsoft.com> wrote in
> news:0F041009-08FF-4FF1-8AC7-D74968D16C05@.microsoft.com:
>
> In SQL Server 2005 you can, I've used the following code sample to
> demonstrate it in my classes (Using the pubs database):
> set XACT_ABORT on
> begin try
> begin transaction
> declare @.id int;
> insert into jobs values ('Testjobb',10,10)
> set @.id = @.@.identity;
> insert into employee values ('123456789','Gates',null,'Bill',@.id+
> 1,10,'0877',GetDate());
> commit transaction
> end try
> begin catch
> select error_message();
> rollback transaction
> end catch
> I'll leave it up to you to implement this in a stored procedure.
> --
> Ole Kristian Bang?s
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
>|||examnotes <Andrew@.discussions.microsoft.com> wrote in
news:C25DD758-622D-4CB5-A870-F112B1C337C6@.microsoft.com:

> What about SQL Server 2000? That is the one we are using.
Sorry. This feature was introduced in SQL Server 2005. In SQL Server 2000
you have to check for errors after each and every command that may cause an
error (which may happen to be almost everyone)
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messagingsql

Thursday, March 22, 2012

can u ignore errors in a trigger

if there is an error in the trigger then the update to the table does not happen. is there a way to make sql ignore errors in a trigger and still update the tableI don't think so.

What kind of errors are you getting?|||you might be able to eat them in 2005 with try/catch. however it seems better to eliminate the root cause of the error, rather than covering it up.|||DROP TRIGGER <trigger_name>

But I would fix the trigger|||I suppose you could temorarily disable triggers. That seems like a dumb thing to do, but you could... I guess.

Can torn page detection fire from existing corruption?

Tuesday, March 20, 2012

Can torn page detection fire from existing corruption?

Does torn page detection only show new errors or errors during restore or
can it error due to a torn page that may have been in the database for a
while.
Thanks
PaulAFAIK, torn page detection is done every tome a page is accessed from disk. This mean that it
doesn't matter when the page was torn.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
news:O4XO3C8iFHA.3012@.TK2MSFTNGP12.phx.gbl...
> Does torn page detection only show new errors or errors during restore or can it error due to a
> torn page that may have been in the database for a while.
> Thanks
> Paul
>|||Thanks Tibor.
Paul
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OMf$BV8iFHA.2644@.TK2MSFTNGP09.phx.gbl...
> AFAIK, torn page detection is done every tome a page is accessed from
> disk. This mean that it doesn't matter when the page was torn.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
> news:O4XO3C8iFHA.3012@.TK2MSFTNGP12.phx.gbl...
>> Does torn page detection only show new errors or errors during restore or
>> can it error due to a torn page that may have been in the database for a
>> while.
>> Thanks
>> Paul
>>
>|||I believe it is only when the page is written assuming you have torn page
detection turned on at the time of the write.
--
Andrew J. Kelly SQL MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OMf$BV8iFHA.2644@.TK2MSFTNGP09.phx.gbl...
> AFAIK, torn page detection is done every tome a page is accessed from
> disk. This mean that it doesn't matter when the page was torn.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
> news:O4XO3C8iFHA.3012@.TK2MSFTNGP12.phx.gbl...
>> Does torn page detection only show new errors or errors during restore or
>> can it error due to a torn page that may have been in the database for a
>> while.
>> Thanks
>> Paul
>>
>|||Yes, the bits are flipped at write time (assuming db option is on). But detection (all 0 or all 1)
are at read time. At least that is how I read
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx (search for "torn")
and BOL
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\createdb.chm::/cm_8_des_03_6ohf.htm).
I'm not 100% positive whether checking of inconsistent bits are always performed or only when db
option is set, though...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OTUksY9iFHA.1968@.TK2MSFTNGP14.phx.gbl...
>I believe it is only when the page is written assuming you have torn page detection turned on at
>the time of the write.
> --
> Andrew J. Kelly SQL MVP
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:OMf$BV8iFHA.2644@.TK2MSFTNGP09.phx.gbl...
>> AFAIK, torn page detection is done every tome a page is accessed from disk. This mean that it
>> doesn't matter when the page was torn.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
>> news:O4XO3C8iFHA.3012@.TK2MSFTNGP12.phx.gbl...
>> Does torn page detection only show new errors or errors during restore or can it error due to a
>> torn page that may have been in the database for a while.
>> Thanks
>> Paul
>>
>|||The check is only performed when the dboption is set, and only for pages
that have been written out since the option was set.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23WoaD2DjFHA.1232@.TK2MSFTNGP15.phx.gbl...
> Yes, the bits are flipped at write time (assuming db option is on). But
> detection (all 0 or all 1) are at read time. At least that is how I read
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
> (search for "torn") and BOL
> (mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\createdb.chm::/cm_8_des_03_6ohf.htm).
> I'm not 100% positive whether checking of inconsistent bits are always
> performed or only when db option is set, though...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OTUksY9iFHA.1968@.TK2MSFTNGP14.phx.gbl...
>>I believe it is only when the page is written assuming you have torn page
>>detection turned on at the time of the write.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:OMf$BV8iFHA.2644@.TK2MSFTNGP09.phx.gbl...
>> AFAIK, torn page detection is done every tome a page is accessed from
>> disk. This mean that it doesn't matter when the page was torn.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
>> news:O4XO3C8iFHA.3012@.TK2MSFTNGP12.phx.gbl...
>> Does torn page detection only show new errors or errors during restore
>> or can it error due to a torn page that may have been in the database
>> for a while.
>> Thanks
>> Paul
>>
>>
>|||Ahh, thanks. I assume there must be some flag in the page header saying something like "torn pages
flagged/flipped" (i.e. the page was written while detection was on) by which the code can determine
whether to check for tp or not?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:ujX3$2IjFHA.2180@.TK2MSFTNGP15.phx.gbl...
> The check is only performed when the dboption is set, and only for pages that have been written
> out since the option was set.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%23WoaD2DjFHA.1232@.TK2MSFTNGP15.phx.gbl...
>> Yes, the bits are flipped at write time (assuming db option is on). But detection (all 0 or all
>> 1) are at read time. At least that is how I read
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx (search for
>> "torn") and BOL
>> (mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\createdb.chm::/cm_8_des_03_6ohf.htm).
>> I'm not 100% positive whether checking of inconsistent bits are always performed or only when db
>> option is set, though...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OTUksY9iFHA.1968@.TK2MSFTNGP14.phx.gbl...
>>I believe it is only when the page is written assuming you have torn page detection turned on at
>>the time of the write.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:OMf$BV8iFHA.2644@.TK2MSFTNGP09.phx.gbl...
>> AFAIK, torn page detection is done every tome a page is accessed from disk. This mean that it
>> doesn't matter when the page was torn.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
>> news:O4XO3C8iFHA.3012@.TK2MSFTNGP12.phx.gbl...
>> Does torn page detection only show new errors or errors during restore or can it error due to
>> a torn page that may have been in the database for a while.
>> Thanks
>> Paul
>>
>>
>>
>|||So torn page detection wil activate only for fresh corruption.
That helps ie our problems are current.
We are in one of those situations where the db is corrupting but our disks
and controllers say all is well. We have turned off caching (batt backed)
etc.
No luck. We have installed sp4 and -T818. No errors from that.
We have run the new sqliostress. No lost writes, stale reads etc but still
the corruptions continue.
System, dell pe 8450 raid 10, had been stable for over 2 years.
We've built another box today and will move tonight.
Wish us luck!
Paul
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uK7U$GJjFHA.2852@.TK2MSFTNGP15.phx.gbl...
> Ahh, thanks. I assume there must be some flag in the page header saying
> something like "torn pages flagged/flipped" (i.e. the page was written
> while detection was on) by which the code can determine whether to check
> for tp or not?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:ujX3$2IjFHA.2180@.TK2MSFTNGP15.phx.gbl...
>> The check is only performed when the dboption is set, and only for pages
>> that have been written out since the option was set.
>> --
>> Paul Randal
>> Dev Lead, Microsoft SQL Server Storage Engine
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:%23WoaD2DjFHA.1232@.TK2MSFTNGP15.phx.gbl...
>> Yes, the bits are flipped at write time (assuming db option is on). But
>> detection (all 0 or all 1) are at read time. At least that is how I read
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
>> (search for "torn") and BOL
>> (mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\createdb.chm::/cm_8_des_03_6ohf.htm).
>> I'm not 100% positive whether checking of inconsistent bits are always
>> performed or only when db option is set, though...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OTUksY9iFHA.1968@.TK2MSFTNGP14.phx.gbl...
>>I believe it is only when the page is written assuming you have torn
>>page detection turned on at the time of the write.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> wrote in message news:OMf$BV8iFHA.2644@.TK2MSFTNGP09.phx.gbl...
>> AFAIK, torn page detection is done every tome a page is accessed from
>> disk. This mean that it doesn't matter when the page was torn.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
>> news:O4XO3C8iFHA.3012@.TK2MSFTNGP12.phx.gbl...
>> Does torn page detection only show new errors or errors during
>> restore or can it error due to a torn page that may have been in the
>> database for a while.
>> Thanks
>> Paul
>>
>>
>>
>>
>|||> So torn page detection wil activate only for fresh corruption.
That is not how I read Paul's statement. Assuming you have had torn page detection on for the
lifetime of the database. Then, as I understand it, a torn page could have happened a year ago.
Assuming you haven't read that page since it happened until now, you wouldn't have discovered it
until now.
Above scenario (again, as I understand how it works) is not very likely, though.
First, torn pages should be detected by DBCC CHECKDB, which I assume you do regularly.
Also, a torn page is most likely to occur if the system is shut down unexpectedly, and during the
following startup and the recovery phase, the pages which are torn would be very likely to be read
as they are likely to be involved in the recovery work.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
news:%23yLeVqJjFHA.2484@.TK2MSFTNGP15.phx.gbl...
> So torn page detection wil activate only for fresh corruption.
> That helps ie our problems are current.
> We are in one of those situations where the db is corrupting but our disks and controllers say all
> is well. We have turned off caching (batt backed) etc.
> No luck. We have installed sp4 and -T818. No errors from that.
> We have run the new sqliostress. No lost writes, stale reads etc but still the corruptions
> continue.
> System, dell pe 8450 raid 10, had been stable for over 2 years.
> We've built another box today and will move tonight.
> Wish us luck!
> Paul
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:uK7U$GJjFHA.2852@.TK2MSFTNGP15.phx.gbl...
>> Ahh, thanks. I assume there must be some flag in the page header saying something like "torn
>> pages flagged/flipped" (i.e. the page was written while detection was on) by which the code can
>> determine whether to check for tp or not?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
>> news:ujX3$2IjFHA.2180@.TK2MSFTNGP15.phx.gbl...
>> The check is only performed when the dboption is set, and only for pages that have been written
>> out since the option was set.
>> --
>> Paul Randal
>> Dev Lead, Microsoft SQL Server Storage Engine
>> This posting is provided "AS IS" with no warranties, and confers no rights.
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:%23WoaD2DjFHA.1232@.TK2MSFTNGP15.phx.gbl...
>> Yes, the bits are flipped at write time (assuming db option is on). But detection (all 0 or all
>> 1) are at read time. At least that is how I read
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx (search for
>> "torn") and BOL
>> (mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\createdb.chm::/cm_8_des_03_6ohf.htm).
>> I'm not 100% positive whether checking of inconsistent bits are always performed or only when
>> db option is set, though...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OTUksY9iFHA.1968@.TK2MSFTNGP14.phx.gbl...
>>I believe it is only when the page is written assuming you have torn page detection turned on
>>at the time of the write.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:OMf$BV8iFHA.2644@.TK2MSFTNGP09.phx.gbl...
>> AFAIK, torn page detection is done every tome a page is accessed from disk. This mean that it
>> doesn't matter when the page was torn.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
>> news:O4XO3C8iFHA.3012@.TK2MSFTNGP12.phx.gbl...
>>> Does torn page detection only show new errors or errors during restore or can it error due
>>> to a torn page that may have been in the database for a while.
>>>
>>> Thanks
>>> Paul
>>>
>>>
>>
>>
>>
>|||I think I may have not been clear. We did not have torn page on but have
been doing a full checkdb/checkalloc using sqlmaint, once a week.
We did not have torn page on as our dell perc3/dc (rebadged megaraid 1600)
have battery backup and we have a hefty ups.
To try to resolve is we had a hardware problem we turned off write bac
cache. No luck we then installed sql sp4 and added -t818. This did not show
any errors.We then enabled torn page. We got a detection after a few hours.
I'm at a loss as to why the dell raid event log shows no errors. I've done a
raid consistency test and it came up clean. chkdsk is OK.
The new sqliostress looks like it an excellent simulation. I ran it with the
extra i/o checking flags. Again clean.
Well we should know by tomorrow night if it was hardware. If it is, then we
may have a very expensive door stop. We'll nuke the box and rebuild it but
I'd feel scared to go back to a box that shows no errors but corrupts data.
I guess we'll run all dells diags for an age, memory etc. This was my dream
machine. I wanted to spec out a great server. 24 small disks, raid 10, 6
channels, 128Mb cache per controller etc.
Perhaps the secret is keep it simple, two big mirrors and just throw a ton
of memory in.
What a fortnight.
Paul
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23FbYj6JjFHA.1044@.tk2msftngp13.phx.gbl...
>> So torn page detection wil activate only for fresh corruption.
> That is not how I read Paul's statement. Assuming you have had torn page
> detection on for the lifetime of the database. Then, as I understand it, a
> torn page could have happened a year ago. Assuming you haven't read that
> page since it happened until now, you wouldn't have discovered it until
> now.
> Above scenario (again, as I understand how it works) is not very likely,
> though.
> First, torn pages should be detected by DBCC CHECKDB, which I assume you
> do regularly.
> Also, a torn page is most likely to occur if the system is shut down
> unexpectedly, and during the following startup and the recovery phase, the
> pages which are torn would be very likely to be read as they are likely to
> be involved in the recovery work.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
> news:%23yLeVqJjFHA.2484@.TK2MSFTNGP15.phx.gbl...
>> So torn page detection wil activate only for fresh corruption.
>> That helps ie our problems are current.
>> We are in one of those situations where the db is corrupting but our
>> disks and controllers say all is well. We have turned off caching (batt
>> backed) etc.
>> No luck. We have installed sp4 and -T818. No errors from that.
>> We have run the new sqliostress. No lost writes, stale reads etc but
>> still the corruptions continue.
>> System, dell pe 8450 raid 10, had been stable for over 2 years.
>> We've built another box today and will move tonight.
>> Wish us luck!
>> Paul
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:uK7U$GJjFHA.2852@.TK2MSFTNGP15.phx.gbl...
>> Ahh, thanks. I assume there must be some flag in the page header saying
>> something like "torn pages flagged/flipped" (i.e. the page was written
>> while detection was on) by which the code can determine whether to check
>> for tp or not?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
>> news:ujX3$2IjFHA.2180@.TK2MSFTNGP15.phx.gbl...
>> The check is only performed when the dboption is set, and only for
>> pages that have been written out since the option was set.
>> --
>> Paul Randal
>> Dev Lead, Microsoft SQL Server Storage Engine
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> wrote in message news:%23WoaD2DjFHA.1232@.TK2MSFTNGP15.phx.gbl...
>> Yes, the bits are flipped at write time (assuming db option is on).
>> But detection (all 0 or all 1) are at read time. At least that is how
>> I read
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
>> (search for "torn") and BOL
>> (mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\createdb.chm::/cm_8_des_03_6ohf.htm).
>> I'm not 100% positive whether checking of inconsistent bits are always
>> performed or only when db option is set, though...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OTUksY9iFHA.1968@.TK2MSFTNGP14.phx.gbl...
>>I believe it is only when the page is written assuming you have torn
>>page detection turned on at the time of the write.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> wrote in message news:OMf$BV8iFHA.2644@.TK2MSFTNGP09.phx.gbl...
>>> AFAIK, torn page detection is done every tome a page is accessed
>>> from disk. This mean that it doesn't matter when the page was torn.
>>>
>>> --
>>> Tibor Karaszi, SQL Server MVP
>>> http://www.karaszi.com/sqlserver/default.asp
>>> http://www.solidqualitylearning.com/
>>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>>
>>>
>>> "Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
>>> news:O4XO3C8iFHA.3012@.TK2MSFTNGP12.phx.gbl...
>>> Does torn page detection only show new errors or errors during
>>> restore or can it error due to a torn page that may have been in
>>> the database for a while.
>>>
>>> Thanks
>>> Paul
>>>
>>>
>>>
>>
>>
>>
>>
>sql

Can this Stored Proc be more efficient?

Hey all,
Figured I'd get everyone's input on this. The stored proc below works
fine, no errors, however the ASP.NET page which calls it takes forever
to load (it averages 25 seconds per search). Anyone have any insight on
how I can boost the speed? 25-35 seconds is too dang long. For those
who want a full perspective: I have a sortable datagrid with custom
paging. On the site there is a textbox where one can search by
"containernum." As you can see below, the core of the search uses LIKE
'%'+@.con_num+'%' which is where the slowdown seems to occur. I have a
full-text index on the containernum field but perhaps I did it wrong
(it's the first time I've used that feature) because there appears to
be no gain in speed. Help! :-)
BTW, there are about a million records in my test table, the real table
has almost 5 million records, so I can just imagine the slowdown on
that one. :-(
CREATE PROCEDURE [Get_Data]
@.CurrentPage int,
@.PageSize int,
@.SortField nvarchar(50),
@.TotalRecords int output,
@.con_num nvarchar(8)
AS
SET NOCOUNT ON
CREATE TABLE #TempTable
(
ID int IDENTITY PRIMARY KEY,
uid uniqueidentifier NOT NULL,
event nvarchar(6) NOT NULL,
bookingnum nvarchar(50) NOT NULL,
vanowner nvarchar(6) NOT NULL,
containernum nvarchar(8) NULL,
tcn nvarchar(20) NULL,
poe nvarchar(50) NULL,
pod nvarchar(6) NULL,
shipname nvarchar(50) NULL,
vdn nvarchar(8) NULL,
eventlocation nvarchar(50) NOT NULL,
pcfn nvarchar(8) NULL
)
INSERT INTO #TempTable
(
uid,
event,
bookingnum,
vanowner,
containernum,
tcn,
poe,
pod,
shipname,
vdn,
eventlocation,
pcfn
)
SELECT
uid,
event,
bookingnum,
vanowner,
containernum,
tcn,
poe,
pod,
shipname,
vdn,
eventlocation,
pcfn
FROM
dbo.new315_itv
WHERE
containernum LIKE '%'+@.con_num+'%'
ORDER BY
CASE
WHEN @.SortField = 'event' THEN event
WHEN @.SortField = 'bookingnum' THEN bookingnum
WHEN @.SortField = 'containernum' THEN containernum
WHEN @.SortField = 'tcn' THEN tcn
WHEN @.SortField = 'poe' THEN poe
WHEN @.SortField = 'pod' THEN pod
WHEN @.SortField = 'shipname' THEN shipname
WHEN @.SortField = 'vdn' THEN vdn
WHEN @.SortField = 'eventlocation' THEN eventlocation
WHEN @.SortField = 'pcfn' THEN pcfn
END
DECLARE @.FirstRec int, @.LastRec int
SELECT @.FirstRec = (@.CurrentPage - 1) * @.PageSize
SELECT @.LastRec = (@.CurrentPage * @.PageSize + 1)
SELECT
uid,
event,
bookingnum,
vanowner,
containernum,
tcn,
poe,
pod,
shipname,
vdn,
eventlocation,
pcfn
FROM
#TempTable
WHERE
ID > @.FirstRec AND ID < @.LastRec
SELECT @.TotalRecords = COUNT(*) FROM #TempTable
GOANytime you use Like with a % at the beginning of the value, as in
containernum LIKE '%'+@.con_num+'%'
you automatically induce a full table scan. this is why your query is so
slow.
You need to extract the @.con_num portion of the data into a separate column
and index that... change the query so that it uses = instead of like, or at
least so that there is no % at the beginning...
Also the idea you are using of creating a temp table of all the values, and
then extracting only one pages wrth to return over the wire is a good one,
but you cancarry this a step furthur... Instead of using a temp table, use a
table variable, with an identity Primary Key RowNum, and ONLY put the keys
into this table variable... (You are incurring an enormous amount of
overhead right now stuffing ALL the data into the temp table, not just the
data you will eventually return to client)
Declare @.T Table (RowNum Integer Primary Key Identity Not Null,
PK Integer Not Null)
Insert @.T
Select uid
From ....
Then, at the end just use this table variable t o join back to your main
table, based on which StartRownNum and EndRowwNum defines the page you want
SELECT uid,event,bookingnum,
vanowner,containernum,tcn,
poe,pod,shipname,
vdn,eventlocation,pcfn
FROM new315_itv O Join @.T
On T.RowNum = O.uid
WHERE RowNum Between @.FirstRec AND @.LastRec
"roy.anderson@.gmail.com" wrote:

> Hey all,
> Figured I'd get everyone's input on this. The stored proc below works
> fine, no errors, however the ASP.NET page which calls it takes forever
> to load (it averages 25 seconds per search). Anyone have any insight on
> how I can boost the speed? 25-35 seconds is too dang long. For those
> who want a full perspective: I have a sortable datagrid with custom
> paging. On the site there is a textbox where one can search by
> "containernum." As you can see below, the core of the search uses LIKE
> '%'+@.con_num+'%' which is where the slowdown seems to occur. I have a
> full-text index on the containernum field but perhaps I did it wrong
> (it's the first time I've used that feature) because there appears to
> be no gain in speed. Help! :-)
> BTW, there are about a million records in my test table, the real table
> has almost 5 million records, so I can just imagine the slowdown on
> that one. :-(
>
> CREATE PROCEDURE [Get_Data]
> @.CurrentPage int,
> @.PageSize int,
> @.SortField nvarchar(50),
> @.TotalRecords int output,
> @.con_num nvarchar(8)
> AS
> SET NOCOUNT ON
> CREATE TABLE #TempTable
> (
> ID int IDENTITY PRIMARY KEY,
> uid uniqueidentifier NOT NULL,
> event nvarchar(6) NOT NULL,
> bookingnum nvarchar(50) NOT NULL,
> vanowner nvarchar(6) NOT NULL,
> containernum nvarchar(8) NULL,
> tcn nvarchar(20) NULL,
> poe nvarchar(50) NULL,
> pod nvarchar(6) NULL,
> shipname nvarchar(50) NULL,
> vdn nvarchar(8) NULL,
> eventlocation nvarchar(50) NOT NULL,
> pcfn nvarchar(8) NULL
> )
> INSERT INTO #TempTable
> (
> uid,
> event,
> bookingnum,
> vanowner,
> containernum,
> tcn,
> poe,
> pod,
> shipname,
> vdn,
> eventlocation,
> pcfn
> )
> SELECT
> uid,
> event,
> bookingnum,
> vanowner,
> containernum,
> tcn,
> poe,
> pod,
> shipname,
> vdn,
> eventlocation,
> pcfn
> FROM
> dbo.new315_itv
> WHERE
> containernum LIKE '%'+@.con_num+'%'
> ORDER BY
> CASE
> WHEN @.SortField = 'event' THEN event
> WHEN @.SortField = 'bookingnum' THEN bookingnum
> WHEN @.SortField = 'containernum' THEN containernum
> WHEN @.SortField = 'tcn' THEN tcn
> WHEN @.SortField = 'poe' THEN poe
> WHEN @.SortField = 'pod' THEN pod
> WHEN @.SortField = 'shipname' THEN shipname
> WHEN @.SortField = 'vdn' THEN vdn
> WHEN @.SortField = 'eventlocation' THEN eventlocation
> WHEN @.SortField = 'pcfn' THEN pcfn
> END
> DECLARE @.FirstRec int, @.LastRec int
> SELECT @.FirstRec = (@.CurrentPage - 1) * @.PageSize
> SELECT @.LastRec = (@.CurrentPage * @.PageSize + 1)
> SELECT
> uid,
> event,
> bookingnum,
> vanowner,
> containernum,
> tcn,
> poe,
> pod,
> shipname,
> vdn,
> eventlocation,
> pcfn
> FROM
> #TempTable
> WHERE
> ID > @.FirstRec AND ID < @.LastRec
> SELECT @.TotalRecords = COUNT(*) FROM #TempTable
> GO
>|||Hey CB,
Thanks for the terrific knowledge. I learned something new today! :-)
Having said that... while using a table variable has increased the
performance, it's only saved me 3 or 4 seconds on average. Here's the
weird thing, you would imagine that using LIKE '%'+@.con_num+'%' would
slow down the search, but it doesn't. In fact, the opposite occurs!
When I use LIKE @.con_num+'%' or LIKE '%'+@.con_num the average time is
27 seconds. When I use LIKE '%'+@.con_num+'%' the average time is 24
seconds.
I'm clueless as to why this is occuring. :-( The only thing I can
come up with is that the "containernum" field is a nvarchar and
includes a mishmash of char's and integers, which may be distorting the
scans somehow.|||This is an indication that your quuery optimizer has decided NOT to use the
index on containernum, and is still doing table scan... (There IS an index o
n
containernum , right ?)
This can happen when there are a large number of records which are a "match"
for the criteria you are passing in... If this query the only query you are
running on this table than you might cnsider mking the index on containernum
the clustered index.. That would improve perfoemce when using ...
containernum Where Like @.con_Num + '%'
What xactly is containernum, and what kind of values are stored in there?
"roy.anderson@.gmail.com" wrote:

> Hey CB,
> Thanks for the terrific knowledge. I learned something new today! :-)
> Having said that... while using a table variable has increased the
> performance, it's only saved me 3 or 4 seconds on average. Here's the
> weird thing, you would imagine that using LIKE '%'+@.con_num+'%' would
> slow down the search, but it doesn't. In fact, the opposite occurs!
> When I use LIKE @.con_num+'%' or LIKE '%'+@.con_num the average time is
> 27 seconds. When I use LIKE '%'+@.con_num+'%' the average time is 24
> seconds.
> I'm clueless as to why this is occuring. :-( The only thing I can
> come up with is that the "containernum" field is a nvarchar and
> includes a mishmash of char's and integers, which may be distorting the
> scans somehow.
>|||"containernum" is an nvarchar(8) field and contains various
alphanumeric characters. The length of entries in that field varies
from 1 to 8. I did have an clustered index on "containernum"
originally, but I was getting slow results and one of my coworkers
suggested enabled a Full-Text Index using "containernum." When I did
that it deleted the original "containernum" clustered index and
replaced it with a "UID" clustered index (UID is the PK for table
new315_itv). I'm currently using the full-text index. Should I delete
it and switch back to using the containernum clustered index?
Am I making sense? :-)|||<roy.anderson@.gmail.com> wrote in message
news:1112638709.920563.71130@.f14g2000cwb.googlegroups.com...
> "containernum" is an nvarchar(8) field and contains various
> alphanumeric characters. The length of entries in that field varies
> from 1 to 8. I did have an clustered index on "containernum"
> originally, but I was getting slow results and one of my coworkers
> suggested enabled a Full-Text Index using "containernum." When I did
> that it deleted the original "containernum" clustered index and
> replaced it with a "UID" clustered index (UID is the PK for table
> new315_itv). I'm currently using the full-text index. Should I delete
> it and switch back to using the containernum clustered index?
> Am I making sense? :-)
>
I think CBretana was suggesting that you reconsider you table design.
Specifically, the containernum column seems to contain composite data; the
container number plus whatever the alphabetic data represents. If these
pieces of data were separated out into discreet columns, the query analyzer
could take advantage of the index on the container_number column. In the
alternative, if you are unable to alter the table model, you might consider
creating an indexed view on the new315_itv table that includes a calculated
expression to extract the actual container number from the containernum
column. Here's a proof of concept on how to extract a number from an
alphanumeric string:
DECLARE @.s NVARCHAR(8)
SET @.s = 'abc123de'
SELECT SUBSTRING(
@.s,
PATINDEX('%[0-9]%',@.s),
CASE PATINDEX('%[0-9]',@.s)
WHEN 0 THEN PATINDEX('%[0-9][^0-9]%',@.s) - PATINDEX('%[0-9]%',@.s) + 1
ELSE PATINDEX('%[0-9]',@.s) - PATINDEX('%[0-9]%',@.s) + 1
END
)
Also, have you considered using VARCHAR instead of NVARCHAR for your textual
data. NVARCHAR requires twice the storage of VARCHAR an should only be used
if your textual data includes Unicode characters. Here's an article:
http://aspfaq.com/show.asp?id=2354
Finally, here's an article that compares various methods of paging through a
recordset. It includes an example of a stored procedure that makes use of a
temp table as well as other stored procedure examples with better
performance.
http://aspfaq.com/show.asp?id=2120
HTH
-Chris Hohmann|||I agree w/Chris... If you can, Redesign the table so that each discreet data
element is in it's own column... But yes, you should switch back to using
Clustered index on the column that the query predicate (Whats in the Where
Clause) uses... ESPECIALLY If the query extracts a range of values.
And that is what ...
Where X Like @.Value + '%' will be doing, since it translates to Where X >=
@.Value And <= @.Value + 'ZZZZZZZZZZZZZZZZZZZZZZZZ' (actually whatever the
Query Parser determines is the last possible value in sort order that will
satisfy the Like.)
"Chris Hohmann" wrote:

> <roy.anderson@.gmail.com> wrote in message
> news:1112638709.920563.71130@.f14g2000cwb.googlegroups.com...
> I think CBretana was suggesting that you reconsider you table design.
> Specifically, the containernum column seems to contain composite data; the
> container number plus whatever the alphabetic data represents. If these
> pieces of data were separated out into discreet columns, the query analyze
r
> could take advantage of the index on the container_number column. In the
> alternative, if you are unable to alter the table model, you might conside
r
> creating an indexed view on the new315_itv table that includes a calculate
d
> expression to extract the actual container number from the containernum
> column. Here's a proof of concept on how to extract a number from an
> alphanumeric string:
> DECLARE @.s NVARCHAR(8)
> SET @.s = 'abc123de'
> SELECT SUBSTRING(
> @.s,
> PATINDEX('%[0-9]%',@.s),
> CASE PATINDEX('%[0-9]',@.s)
> WHEN 0 THEN PATINDEX('%[0-9][^0-9]%',@.s) - PATINDEX('%[0-9]%',@.s) + 1
> ELSE PATINDEX('%[0-9]',@.s) - PATINDEX('%[0-9]%',@.s) + 1
> END
> )
> Also, have you considered using VARCHAR instead of NVARCHAR for your textu
al
> data. NVARCHAR requires twice the storage of VARCHAR an should only be use
d
> if your textual data includes Unicode characters. Here's an article:
> http://aspfaq.com/show.asp?id=2354
> Finally, here's an article that compares various methods of paging through
a
> recordset. It includes an example of a stored procedure that makes use of
a
> temp table as well as other stored procedure examples with better
> performance.
> http://aspfaq.com/show.asp?id=2120
>
> HTH
> -Chris Hohmann
>
>|||Thanks for the info you two. I'll be sure to check out those articles
Chris.
Actually, believe it or not, I have the Stored Proc at a comfortable
spot now. When a user searches using at least 3 characters, the results
return in under a second, when searches occur with less than 3
characters, the results can take up to 35 seconds to return (but this
warning is now noted on the site). See my new stored proc below. I
utilized CB's table variable idea but maintained the full-text index.
Basically (I believe) if one uses LIKE sqlserver looks for a regular
index, if one uses CONTAINS or FREETEXT sqlserver looks for a full-text
index. However, for whatever reason, I can't use CONTAINS on character
searches that contain less than 3 characters. It doesn't error out, it
just doesn't display anything. Probably an idiosyncrasy of full-text
searches.
CREATE PROCEDURE [Get_Data]
@.CurrentPage int,
@.PageSize int,
@.TotalRecords int output,
@.con_num nvarchar(8)
AS
SET NOCOUNT ON
DECLARE @.T Table
(
RowNum INTEGER PRIMARY KEY Identity NOT NULL,
PK UNIQUEIDENTIFIER NOT NULL
)
IF LEN(@.con_num) >= 3
BEGIN
SET @.con_num = '"'+@.con_num+'*"'
INSERT INTO @.T (PK)
SELECT
uid
FROM
dbo.new315_itv
WHERE CONTAINS (containernum, @.con_num)
END
ELSE
BEGIN
INSERT INTO @.T (PK)
SELECT
uid
FROM
dbo.new315_itv
WHERE containernum LIKE '%'+@.con_num+'%'
END
DECLARE @.FirstRec int, @.LastRec int
SELECT @.FirstRec = (@.CurrentPage - 1) * @.PageSize
SELECT @.LastRec = (@.CurrentPage * @.PageSize + 1)
SELECT
A.uid,
A.event,
A.bookingnum,
A.vanowner,
A.containernum,
A.tcn,
A.poe,
A.pod,
A.shipname,
A.vdn,
A.eventlocation,
A.pcfn
FROM
dbo.new315_itv A INNER JOIN @.T T ON T.PK = A.uid
WHERE
T.RowNum BETWEEN @.FirstRec AND @.LastRec
SELECT @.TotalRecords = COUNT(*) FROM @.T
GO

Thursday, March 29, 2012

Can we handle ALL errors within a stored proceudre?

Can we handle all errors within a stored proceudre.

Thursday, March 22, 2012

can u ignore errors in a trigger

Can torn page detection fire from existing corruption?

Tuesday, March 20, 2012

Can torn page detection fire from existing corruption?

Can torn page detection fire from existing corruption?

Can this Stored Proc be more efficient?

sql reports

Blog Archive

About Me