RaisError in a function? Any ideas

Writing a function and need to raise an error under certain conditions. This is a gross over-simplification of the function. But you get the idea....I need to use @.@.ROWCOUNT

Code Snippet

create Function GetTheValue

(@.Input INT)

returns Bigint

as

begin

declare @.Output bigint

select TheValue

from TheTable

where RowID = @.Input

IF @.@.ROWCOUNT = 0

RAISERROR('Oops',15,1)

return @.INput

END

Basically, the way this usually works is that you return an invalid value and check for it. Like in this case, you can never get a negative value for @.@.rowcount, just return -1 or something like this.

You can force an error to occur by doing something like 0/1, but I was never fond of that method since the error message doesn't correspond to the actual error.

|||

As you noticed, RAISERROR is NOT allowed in a FUNCTION.

A FUNCTION cannot have any output EXCEPT the FUNCTION value. Of course, it will 'crash' when unacceptable actions occur, such as divide by zero.

Louis indicated an acceptable method of handling a data anomaly.

|||

Thanks....

I'll probably use:

Code Snippet

if @.@.rowcount =0

select 1/0

RAISERROR does not cause SQL task to fail - why?

Greetings,

I have a stored procedure with a TRY / CATCH block. In the catch block I capture information about the error. I then use RAISERROR to "rethrow" the exception so that it will be available to SSIS.

I execute the stored procedure through a SQL task. I observe that SSIS reports the SQL task succeeds (the task box turns green) when RAISERROR is invoked. If I comment the catch block with RAISERROR then SSIS reports the task failed. (I created a simple procedure that does a divide by zero to force an error.) The expected error message is displayed when the sproc is run from the SQL Server Management Studio command line so I believe that the stored procedure is doing what I intended.

I would like to handle an error within my stored procedure without destroying SSIS's ability to detect a task failure. Is this possible? Is there an alternative to using RAISERROR?

Thanks,

BCB

But is it a message or an output? That is, which tab in SSMS does the results of the procedure end up on? "Results" or "Messages"?|||Can you please post code for both versions of the stored procedure?|||

Thanks for the replies.

Phil, to answer your question, the error message is displayed on the Messages tab in Management Studio.

I'm including two versions of the sproc as well as the invocation code I used to run it within Management Studio. I'm successfuly capturing the error info in the CATCH block and passing it back to SSIS. The point of having the RAISERROR at the end of the CATCH is to make SSIS fail the task.

This is the sproc with no CATCH:

Code Snippet

-- This is the stripped procedure with no CATCH block. The OUTPUT parms do nothing.
-- This sproc will cause an SQL task to fail.
ALTER PROCEDURE [dbo].[sp_ThrowException]
@.ERROR_NUMBER INT OUTPUT,
@.ERROR_MESSAGE NVARCHAR(4000) OUTPUT,
@.ERROR_SEVERITY INT OUTPUT,
@.ERROR_STATE INT OUTPUT,
@.ERROR_PROCEDURE NVARCHAR(126) OUTPUT,
@.ERROR_LINE INT OUTPUT,
@.FormattedMessage NVARCHAR(4000) OUTPUT
AS

BEGIN

SET NOCOUNT ON

-- this forces a "divide by zero" exception
SELECT 1 / 0 AS 'TRY' FROM view_OilRigs_REPORT R

END

This is the sproc as I really want it. It has the RAISERROR in the CATCH block.

Code Snippet

-- This is the desired procedure with the CATCH block. The error information is being returned
-- to the SQL task through the OUTPUT parms.
-- This sproc will allow an SQL task to succeed.

ALTER PROCEDURE [dbo].[sp_ThrowException]
@.ERROR_NUMBER INT OUTPUT,
@.ERROR_MESSAGE NVARCHAR(4000) OUTPUT,
@.ERROR_SEVERITY INT OUTPUT,
@.ERROR_STATE INT OUTPUT,
@.ERROR_PROCEDURE NVARCHAR(126) OUTPUT,
@.ERROR_LINE INT OUTPUT,
@.FormattedMessage NVARCHAR(4000) OUTPUT
AS

BEGIN TRY

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

-- this forces a "divide by zero" exception
SELECT 1 / 0 AS 'TRY' FROM view_OilRigs_REPORT R

END TRY

BEGIN CATCH

SELECT
@.ERROR_NUMBER = ERROR_NUMBER(),
@.ERROR_MESSAGE = ERROR_MESSAGE(),
@.ERROR_SEVERITY = ERROR_SEVERITY(),
@.ERROR_STATE = ERROR_STATE(),
@.ERROR_PROCEDURE = ERROR_PROCEDURE(),
@.ERROR_LINE = ERROR_LINE(),
-- this renders the error message in command line format
@.FormattedMessage = 'Msg ' + CAST(ERROR_NUMBER() AS NVARCHAR(20)) + ', ' +
'Level ' + CAST(ERROR_SEVERITY() AS NVARCHAR(20)) + ', ' +
'State ' + CAST(ERROR_STATE() AS NVARCHAR(20)) + ', ' +
'Procedure ' + ERROR_PROCEDURE() + ', ' +
'Line ' + CAST(ERROR_LINE() AS NVARCHAR(20)) + ', ' +
'Message: ' + ERROR_MESSAGE()

-- Use RAISERROR inside the CATCH block to return error
-- information about the original error that caused
-- execution to jump to the CATCH block.
RAISERROR
(
@.ERROR_MESSAGE, -- Message text
@.ERROR_SEVERITY, -- Severity
@.ERROR_STATE -- State
)

END CATCH

I use this code to run the sproc in Management Studio.

Code Snippet

-- I use this to run the sproc within Management Studio.

SET NOCOUNT ON;

DECLARE @.local_ERROR_NUMBER INT
DECLARE @.local_ERROR_MESSAGE NVARCHAR(4000)
DECLARE @.local_ERROR_SEVERITY INT
DECLARE @.local_ERROR_STATE INT
DECLARE @.local_ERROR_PROCEDURE NVARCHAR(126)
DECLARE @.local_ERROR_LINE INT
DECLARE @.local_FormattedMessage NVARCHAR(4000)

EXEC [dbo].[sp_ThrowException]
@.ERROR_NUMBER = @.local_ERROR_NUMBER OUTPUT,
@.ERROR_MESSAGE = @.local_ERROR_MESSAGE OUTPUT,
@.ERROR_SEVERITY = @.local_ERROR_SEVERITY OUTPUT,
@.ERROR_STATE = @.local_ERROR_STATE OUTPUT,
@.ERROR_PROCEDURE = @.local_ERROR_PROCEDURE OUTPUT,
@.ERROR_LINE = @.local_ERROR_LINE OUTPUT,
@.FormattedMessage = @.local_FormattedMessage OUTPUT
SELECT
@.local_ERROR_NUMBER AS ERROR_NUMBER,
@.local_ERROR_MESSAGE AS ERROR_MESSAGE,
@.local_ERROR_SEVERITY AS ERROR_SEVERITY,
@.local_ERROR_STATE AS ERROR_STATE,
@.local_ERROR_PROCEDURE AS ERROR_PROCEDURE,
@.local_ERROR_LINE AS ERROR_LINE,
@.local_FormattedMessage AS FormattedMessage

Thanks for looking at this.

BCB

|||As long as it's posted to the messages tab, to my knowledge, you cannot capture that inside SSIS.|||

BlackCatBone wrote:

Thanks for the replies.

Phil, to answer your question, the error message is displayed on the Messages tab in Management Studio.

I'm including two versions of the sproc as well as the invocation code I used to run it within Management Studio. I'm successfuly capturing the error info in the CATCH block and passing it back to SSIS. The point of having the RAISERROR at the end of the CATCH is to make SSIS fail the task.

I don't think you can have it both ways. If the task fails, you're not going to be able to use it to capture the output parameters.

I'm personally surprised that SSIS does not fail the task when you use RAISERROR in your procedure code. I would have assumed that explicitly raising an error would cause the task to fail, but it does not. It's far too late for me to dig into this. Perhaps someone else can shed some light on the "why" of this behavior.

With that said, this is the approach that I would recommend if I needed to capture the error information from the stored procedure and have the package respond to the error as well:

Remove the RAISERROR from the procedure. Leave the TRY CATCH block in the procedure. Have the Execute SQL task call the procedure and capture the output parameters. Have precedence constraints below the Execute SQL task that follow a "success" path or an "error" path based on the values of the output parameters - likely the error number.

RAISERROR dilema

Hello,

I am tring to use the RAISERROR command to log an error when it happens and pass a parameter into it. This is what I am looking for in a nutshell. I know this syntax is incorrect.

RAISEERROR('The following process has encountered an error' +space(1) + @.var, 16,1, @.with_log='TRUE')

Any help would be appreciated...

Thanks,

Dave

To make this work, you need to create your message before calling RAISERROR.

DECLARE @.ErrMessage varchar(250)

SET @.ErrMessage = ( 'The following process has encountered an error' + ' ' + @.var )

And then call RAISERROR using the @.ErrMessage, e.g.,

RAISEERROR( @.ErrMessage, 16, 1, @.with_log='TRUE' )

Of course, if @.Var is numeric,it will have to be cast as varchar to concatenate.

sql

RAISERROR Behavior Question

I have a RAISERROR statement being displayed to the screen prior to a print statement, however the print statement was executed before the RAISERROR statement. Why would this happen? The print statement is not part of any conditional logic. It is executed sequentially followed by an IF statement that generates the RAISERROR.

Step 1. Loop through all Databases and dynamically run DBCC ShowContig
Step 2. End the loop and Print "SCANNING COMPLETED"
Step 3. Declare Cursor to read DBCC ShowContig results
Step 4. Check current time to see if it is ok to continue processing
If it is NOT ok, RAISERROR
Else begin DBReindex process
Step 5. Close and Deallocate Cursor

If I replace RAISERROR with a print statement, everything prints in order. What gives?

DaveIf you post the code it would be easier to find solution. However, If you are not saving the value of @.@.error in a variable it resets to Zero when you go back to check it again .

In other words You should

Declare @.err int
And while checking @.@.error use Select @.err = @.@.error and then check @.err value which will stay stored . I am not sure if this is what you needed to know .. Like I said , code posting might help|||The logic itself works fine so posting the code most likely won't help. The RAISERROR is generated after a conditional statement checking the length of a local variable. There is no need to check @.@.ERROR at that time. The only issue is why a RAISERROR gets sent to the screen before a print statement when the print statement executed first. I was told a few hours ago that Print statements are first sent to the buffer and its possible a RAISERROR does not hit the buffer. That may explain why it appears first.

Thanks, Dave|||what's the severity level and state values? i've experimented for a couple of minutes with different severity levels including 20 and up, at which point the print statement does not get displayed at all.|||Raiserror ("My error message", 16, 1) with log

Dave|||i put print 'test' before your raiserror and got this:

test
Server: Msg 50000, Level 16, State 1, Line 2
My error message|||I'll be testing all of the error routines again this week. Once I recreate the issue I'll see if the code is small enough to post.

Thanks, Dave

raiserror / terminate a users connection

Hi,
For slightly convoluted reasons I'd like to be able to terminate a users
connection from within a stored procedure, in a similar way as if I'd raised
an error with a severity of 20 or above. Is this possible?
The circumstances are that I have a number of security tests that I run at
various places, and there is one possible outcome which can only mean that a
user has been active for over 20 hours (practically impossible) or that the
system has been broken into in some way - I'd like to just completely kill
the connection immediately if this condition is detected, and wrap it in a
single stored procedure (i.e. take the key in question, test it, and then
raise an error and throw the user out if it fails).
Can it be done?
Thanks
NickYou can use KILL, but it won't be a neat error message for that connection..
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Nick Stansbury" <nick.stansbury@.sage-removepartners.com> wrote in message
news:cutjph$rm0$1@.pop-news.nl.colt.net...
> Hi,
> For slightly convoluted reasons I'd like to be able to terminate a user
s
> connection from within a stored procedure, in a similar way as if I'd rais
ed
> an error with a severity of 20 or above. Is this possible?
> The circumstances are that I have a number of security tests that I run at
> various places, and there is one possible outcome which can only mean that
a
> user has been active for over 20 hours (practically impossible) or that th
e
> system has been broken into in some way - I'd like to just completely kill
> the connection immediately if this condition is detected, and wrap it in a
> single stored procedure (i.e. take the key in question, test it, and then
> raise an error and throw the user out if it fails).
> Can it be done?
> Thanks
> Nick
>|||But won't "Kill" only work for other connections other than the current
connection? What I need to do is Kill the current connection
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23MC5e55EFHA.2564@.tk2msftngp13.phx.gbl...
> You can use KILL, but it won't be a neat error message for that
connection...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Nick Stansbury" <nick.stansbury@.sage-removepartners.com> wrote in message
> news:cutjph$rm0$1@.pop-news.nl.colt.net...
users
raised
at
that a
the
kill
a
then
>|||Sorry, I thought you want to execute this from the outside the connection, h
ence the reference to
KILL
I'm afraid that I can't see an easy way to accomplish this unless the user i
s symin. If so, you
can use RAISERROR with severity 20 or higher.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Nick Stansbury" <nick.stansbury@.sage-removepartners.com> wrote in message
news:cuv15k$al6$1@.pop-news.nl.colt.net...
> But won't "Kill" only work for other connections other than the current
> connection? What I need to do is Kill the current connection
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:%23MC5e55EFHA.2564@.tk2msftngp13.phx.gbl...
> connection...
> users
> raised
> at
> that a
> the
> kill
> a
> then
>

Raiserror

Is there a way to raise an exception inside a user function in the Sqlserver2000?.Guess not

Did you check out BOL for specifics?

CREATE FUNCTION udf_myFunction99 (@.x varchar(8000))
RETURNS int
AS
BEGIN
RAISERROR 50001 'Error Raised'
RETURN -1
END
GO|||Originally posted by Brett Kaiser
Guess not

Did you check out BOL for specifics?

CREATE FUNCTION udf_myFunction99 (@.x varchar(8000))
RETURNS int
AS
BEGIN
RAISERROR 50001 'Error Raised'
RETURN -1
END
GO



I got the error

Server: Msg 443, Level 16, State 2, Procedure udf_myFunction99, Line 5
Invalid use of 'RAISEERROR' within a function.|||Yeah, I know...That's why I said:

"guess not"

Check out books online for a better description of Functions...

I know there a few limitations...

What are you trying to do?|||Originally posted by Brett Kaiser
Yeah, I know...That's why I said:

"guess not"

Check out books online for a better description of Functions...

I know there a few limitations...

What are you trying to do?

There are many limitations I know, but this one I didn't found.
I supose its not allowed to use RAISERROR in functions.
I am generating a calc module from a CASE system using only functions. So far I found a solution for every limitation. Otherwise I will have to rebuild the entire system using only stored procedure instead of functions.

That1s why I am looking for some "magic" to raise the exception.

Thank You.|||...or we could hope for a miracle...

Since you haven't been able to raise out in the other functions, why do you want to with this one?

And yeah, sprocs would have been the way to go (MOO)...how are you using the functions?

BOL

The following statements are allowed in the body of a multi-statement function. Statements not in this list are not allowed in the body of a function:

Assignment statements.

Control-of-Flow statements.

DECLARE statements defining data variables and cursors that are local to the function.

SELECT statements containing select lists with expressions that assign values to variables that are local to the function.

Cursor operations referencing local cursors that are declared, opened, closed, and deallocated in the function. Only FETCH statements that assign values to local variables using the INTO clause are allowed; FETCH statements that return data to the client are not allowed.

INSERT, UPDATE, and DELETE statements modifying table variables local to the function.

EXECUTE statements calling an extended stored procedures.sql

RAISERROR

Hello,

I am raising an error on my SQL 2005 procedure as follows:

RAISERROR(@.ErrorMessage, @.ErrorSeverity, 1)

How can I access it in my ASP.NET code?

Thanks,

Miguel

See the following post

http://forums.asp.net/p/639921/639921.aspx#639921

raiserror

Hi
I want to make a trigger that prevent users from deleting rows that they
didnt insert and make en raiseror that tells them why the couldnt remove the
row, the table contains a column that says what sql login made the insert'
does anyone have an idea?you can do this in two ways.
1. write a query such that u delete only the rows the user inserted:
DELETE FROM <TABLE> WHERE user_ID = user
or
2.
In the trigger, check the deleted table
IF NOT EXISTS(SELECT * FROM DELETED where user_id = user)
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"LeSurfer" wrote:

> Hi
> I want to make a trigger that prevent users from deleting rows that they
> didnt insert and make en raiseror that tells them why the couldnt remove t
he
> row, the table contains a column that says what sql login made the insert?
?
> does anyone have an idea?|||Hi
I don′t want the trigger to delete any rows, i want the trigger to prevent
users from deleteing rows which they ditn′t insert.
I have a program with a table with customers, when a new customer is created
it also inserts a column with the sql user login id name of the person how
made the insert. Now i want to make a trigger, so that you can only remove
rows which you inserted!
Fredrik
"Chandra" wrote:
> you can do this in two ways.
> 1. write a query such that u delete only the rows the user inserted:
> DELETE FROM <TABLE> WHERE user_ID = user
> or
> 2.
> In the trigger, check the deleted table
> IF NOT EXISTS(SELECT * FROM DELETED where user_id = user)
> please let me know if u have any questions
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "LeSurfer" wrote:
>|||WHERE user_ID = user is the key as Chandra stated.
You can only delete the rows that match the currently logged in user.
Whatever logic you are using to delete the row in the trigger then add the
user_ID = user condition. Therefore if dbo logs in and tries to delete rows
'LeSurfer' entered he will not be able coz it wont meet the condition.
Your raiserror can be scripted as follows:
GOTO ERRORPOINT
ERROR_POINT:
raiserror(@.text,16,-1)
ROLLBACK TRANSACTION
GOTO FINISH
FINISH:
End
"LeSurfer" wrote:
> Hi
> I don′t want the trigger to delete any rows, i want the trigger to preven
t
> users from deleteing rows which they ditn′t insert.
> I have a program with a table with customers, when a new customer is creat
ed
> it also inserts a column with the sql user login id name of the person how
> made the insert. Now i want to make a trigger, so that you can only remove
> rows which you inserted!
> Fredrik
> "Chandra" wrote:
>|||On Tue, 6 Sep 2005 03:25:02 -0700, LeSurfer wrote:

>Hi
>I want to make a trigger that prevent users from deleting rows that they
>didnt insert and make en raiseror that tells them why the couldnt remove th
e
>row, the table contains a column that says what sql login made the insert'
>does anyone have an idea?
Hi LeSurfer,
Something like this, maybe?
CREATE TRIGGER MyTrigger
ON MyTable FOR DELETE
AS
IF EXISTS (SELECT *
FROM deleted
WHERE TheColumnWithTheUserID <> CURRENT_USER)
BEGIN
RAISERROR ('You can''t delete rows that were inserted by someone
else', 16, 1)
ROLLBACK TRANSACTION
END
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Perfekt, thanks a lot.........
"Hugo Kornelis" wrote:

> On Tue, 6 Sep 2005 03:25:02 -0700, LeSurfer wrote:
>
> Hi LeSurfer,
> Something like this, maybe?
> CREATE TRIGGER MyTrigger
> ON MyTable FOR DELETE
> AS
> IF EXISTS (SELECT *
> FROM deleted
> WHERE TheColumnWithTheUserID <> CURRENT_USER)
> BEGIN
> RAISERROR ('You can''t delete rows that were inserted by someone
> else', 16, 1)
> ROLLBACK TRANSACTION
> END
> go
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

RaiseError not getting what you what!!

Not PARSING "..." is this a BUG!!!!!

[ BEGIN
RAISERROR ('The employee is already in the list of overtime for this
date!..............................', 16,1)
RETURN -1
END
] = [ BEGIN
RAISERROR ('The employee is already in the list of overtime for this
date!', 16,1)
RETURN -1
END
]

============The script you posed is not a valid Transact-SQL. It is unclear to me what
you are trying to accomplish but below is an example.

CREATE PROC CheckOvertime
@.EmployeeId int,
@.OvertimeDate smalldatetime
AS
IF EXISTS
(
SELECT *
FROM OverTime
WHERE EmployeeID = @.EmployeeId AND
OvertimeDate = @.OvertimeDate
)
BEGIN
RAISERROR ('The employee is already in the list of overtime for this>
date!..............................', 16,1)
RETURN -1
END
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

<squirrelv5@.yahoo.com> wrote in message
news:1102408111.648976.196080@.z14g2000cwz.googlegr oups.com...
> Not PARSING "..." is this a BUG!!!!!
> [ BEGIN
> RAISERROR ('The employee is already in the list of overtime for this
> date!..............................', 16,1)
> RETURN -1
> END
> ] = [ BEGIN
> RAISERROR ('The employee is already in the list of overtime for this
> date!', 16,1)
> RETURN -1
> END
> ]
> ============

RAISEERROR from CLR stored procedures

I'm writing:

SqlContext.Pipe.ExecuteAndSend(new SqlCommand("RAISERROR ( '" + e.Message + "', 11, 1)"));

to execute a RAISEERROR from a CLR stored procedure.

It works, but in the SqlError list I get in the client, there are two more errors that say something like '{System.Data.SqlClient.SqlError: A .NET Framework error occurred during execution of user defined routine or aggregate 'Customer__Update':
System.Data.SqlClient.SqlException: noupdate ...' ...

Is there any way to just get the message I'm sending?

Thanks

The issue here is that by ExecuteAndSend a command which executes a RAISERROR, your RAISERROR will be called in the SQL layer, and an error will be raised to the client (which is good and is what you see as the first error). However, this error will be caught by the CLR when ExecuteAndSend returns., and it will be seen as a unhandled error and wrapped by SQL when your method returns as error 6522 (CLR Error).
The way to handle this is to have your Pipe.ExecuteAndSend in a try block and then have an empty catch block which will eat the error coming back from SQL:

try { p.ExecuteAndSend(cmd); } catch { }

Hope this helps!
Niels

|||Thanks Neils.

It got better, now I get to SqlErrors in the Errors collection one with my message and another saying 'The statement has been terminated.'

However, with or without the try/catch, I got a 50000 error, not the 6522.

I got the 6522 when I created the SqlCommand and executed it directly. If I execute using the SqlPipe.ExecuteAndSend, I got 50000|||

aaguiar wrote:

It got better, now I get to SqlErrors in the Errors collection one with my message and another saying 'The statement has been terminated.'

Hmm, I don't see that here, I only get one message. However if I execute the CLR proc from inside a T-SQL proc I get all kind of weird errors - that's a bug in this build, and will be fixed. Can you post your code, both server side as well as client side?

aaguiar wrote:

However, with or without the try/catch, I got a 50000 error, not the 6522.
I got the 6522 when I created the SqlCommand and executed it directly. If I execute using the SqlPipe.ExecuteAndSend, I got 50000

Sure, but when you do ExecuteAndSend (without the dummy try/catch), I'm willing to bet your second error is 6522. 50000 is expected as that is "user defined error".
Niels|||

I tried this but the error is not caught by a begin try/end try ... begin catch/end catch block in transact sql code. Is there a way to send a custom error which can be caught in TSQL code?

|||I am having this problem too. The RAISERROR call from my CLR Routine is not being caught in the TRY/CATCH T-SQL block. Can anyone tell me why?sql

RAISEERROR from CLR stored procedures

I'm writing:

SqlContext.Pipe.ExecuteAndSend(new SqlCommand("RAISERROR ( '" + e.Message + "', 11, 1)"));

to execute a RAISEERROR from a CLR stored procedure.

It works, but in the SqlError list I get in the client, there are two more errors that say something like '{System.Data.SqlClient.SqlError: A .NET Framework error occurred during execution of user defined routine or aggregate 'Customer__Update':
System.Data.SqlClient.SqlException: noupdate ...' ...

Is there any way to just get the message I'm sending?

Thanks

The issue here is that by ExecuteAndSend a command which executes a RAISERROR, your RAISERROR will be called in the SQL layer, and an error will be raised to the client (which is good and is what you see as the first error). However, this error will be caught by the CLR when ExecuteAndSend returns., and it will be seen as a unhandled error and wrapped by SQL when your method returns as error 6522 (CLR Error).
The way to handle this is to have your Pipe.ExecuteAndSend in a try block and then have an empty catch block which will eat the error coming back from SQL:

try { p.ExecuteAndSend(cmd); } catch { }

Hope this helps!
Niels

|||Thanks Neils.

It got better, now I get to SqlErrors in the Errors collection one with my message and another saying 'The statement has been terminated.'

However, with or without the try/catch, I got a 50000 error, not the 6522.

I got the 6522 when I created the SqlCommand and executed it directly. If I execute using the SqlPipe.ExecuteAndSend, I got 50000|||

aaguiar wrote:

It got better, now I get to SqlErrors in the Errors collection one with my message and another saying 'The statement has been terminated.'

Hmm, I don't see that here, I only get one message. However if I execute the CLR proc from inside a T-SQL proc I get all kind of weird errors - that's a bug in this build, and will be fixed. Can you post your code, both server side as well as client side?

aaguiar wrote:

However, with or without the try/catch, I got a 50000 error, not the 6522.
I got the 6522 when I created the SqlCommand and executed it directly. If I execute using the SqlPipe.ExecuteAndSend, I got 50000

Sure, but when you do ExecuteAndSend (without the dummy try/catch), I'm willing to bet your second error is 6522. 50000 is expected as that is "user defined error".
Niels|||

I tried this but the error is not caught by a begin try/end try ... begin catch/end catch block in transact sql code. Is there a way to send a custom error which can be caught in TSQL code?

|||I am having this problem too. The RAISERROR call from my CLR Routine is not being caught in the TRY/CATCH T-SQL block. Can anyone tell me why?

RAISEERROR from CLR stored procedures

I'm writing:

SqlContext.Pipe.ExecuteAndSend(new SqlCommand("RAISERROR ( '" + e.Message + "', 11, 1)"));

to execute a RAISEERROR from a CLR stored procedure.

It works, but in the SqlError list I get in the client, there are two more errors that say something like '{System.Data.SqlClient.SqlError: A .NET Framework error occurred during execution of user defined routine or aggregate 'Customer__Update':
System.Data.SqlClient.SqlException: noupdate ...' ...

Is there any way to just get the message I'm sending?

Thanks

The issue here is that by ExecuteAndSend a command which executes a RAISERROR, your RAISERROR will be called in the SQL layer, and an error will be raised to the client (which is good and is what you see as the first error). However, this error will be caught by the CLR when ExecuteAndSend returns., and it will be seen as a unhandled error and wrapped by SQL when your method returns as error 6522 (CLR Error).
The way to handle this is to have your Pipe.ExecuteAndSend in a try block and then have an empty catch block which will eat the error coming back from SQL:

try { p.ExecuteAndSend(cmd); } catch { }

Hope this helps!
Niels

|||Thanks Neils.

It got better, now I get to SqlErrors in the Errors collection one with my message and another saying 'The statement has been terminated.'

However, with or without the try/catch, I got a 50000 error, not the 6522.

I got the 6522 when I created the SqlCommand and executed it directly. If I execute using the SqlPipe.ExecuteAndSend, I got 50000

|||

aaguiar wrote:

It got better, now I get to SqlErrors in the Errors collection one with my message and another saying 'The statement has been terminated.'

Hmm, I don't see that here, I only get one message. However if I execute the CLR proc from inside a T-SQL proc I get all kind of weird errors - that's a bug in this build, and will be fixed. Can you post your code, both server side as well as client side?

aaguiar wrote:

However, with or without the try/catch, I got a 50000 error, not the 6522.
I got the 6522 when I created the SqlCommand and executed it directly. If I execute using the SqlPipe.ExecuteAndSend, I got 50000

Sure, but when you do ExecuteAndSend (without the dummy try/catch), I'm willing to bet your second error is 6522. 50000 is expected as that is "user defined error".
Niels|||

I tried this but the error is not caught by a begin try/end try ... begin catch/end catch block in transact sql code. Is there a way to send a custom error which can be caught in TSQL code?

|||I am having this problem too. The RAISERROR call from my CLR Routine is not being caught in the TRY/CATCH T-SQL block. Can anyone tell me why?

raiseerror does not raise exception

Hi friends

i've a stored proc (sql 2005) that'll raiseerror statement when something violated.

but my C# application that calls this stored proc does not any throw exception when this happens !!

i remember visual basic used to through an exception for this type of things.

is it different in C# and how do we handle this scenario ?

Thanks for ur ideas.

can you show us the SP code?

moving thread to the SQL Forums

|||am doing something like below

BEGIN TRY
BEGIN TRAN

/* one of below statements may result in error*/
insert into mytable1 values (...blah..)
insert into mytable1 values (...blah..)

COMMIT
END TRY
BEGIN CATCH
DECLARE

@.ErrorMessage VARCHAR(4000)
SELECT @.ErrorMessage = 'Message: '+ ERROR_MESSAGE();

raiserror (@.ErrorMessage)
END CATCH;

i used executescalar ,executereader (in C#) but none of them throw any exception but i could access data CATCH returns though|||

Hi prk,

You'll need to specify the severity and state during the RAISERROR call:

raiserror (@.ErrorMessage,16,1)

Cheers,

Rob

|||

Thanks Rob

will give that a try

raise message from Update Trigger from External App

Hello,
I can raise this message from an Update Trigger in Query Analyzer when I
update the recordID field of my table:
RAISERROR ('this is a test message from SubDetail Update 7', 16, 10)
Is it possible to raise this message from an external app? How is this
achieved? Actually, I am sure this is possible because I remember doing it.
I just can't remember what I did because I did not document it.
Thanks,
RichWhat is this "external app"? An application that you write yourself? A TSQL
error (which is what you
raise using RAISERROR) is returned to the client application. The client app
lications is connected
to the database using an API, like ADO.NET. And, sure, you can have your dat
abase application
connect to SQL Server and issue a RAISERROR command, and have that error mes
sage be returned to the
same app, but that sounds a bit ... meaningless. If you give us more informa
tion, we can probably
give some suggestion.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:54A80BEF-6285-4F6A-8B60-F06007C1BDEF@.microsoft.com...
> Hello,
> I can raise this message from an Update Trigger in Query Analyzer when I
> update the recordID field of my table:
> RAISERROR ('this is a test message from SubDetail Update 7', 16, 10)
> Is it possible to raise this message from an external app? How is this
> achieved? Actually, I am sure this is possible because I remember doing i
t.
> I just can't remember what I did because I did not document it.
> Thanks,
> Rich|||The external app in this case is an Access ADP. I had to modify a trigger a
few months ago, and I added a raiseerror message at the end to see my result
s
in QA - not error results - just checking what parameter was being used. I
accidentally left the raiseerror message in the trigger, and then I got a
call from an End User stating that this message was coming up all of a sudde
n
when she made updates to the table.
I found the table and reactivated the raiseerror message and I get it when I
updaet a field. The only thing I noticed is that the field I update in this
table (the master table) is not a key field. In the Detail table when I
update the RecordID field this action does not raise the message like in the
Master table. I guess my question is if this is something fundamental that
I
am missing or is it something that I need to dig around to see what is going
on?
"Tibor Karaszi" wrote:

> What is this "external app"? An application that you write yourself? A TSQ
L error (which is what you
> raise using RAISERROR) is returned to the client application. The client a
pplications is connected
> to the database using an API, like ADO.NET. And, sure, you can have your d
atabase application
> connect to SQL Server and issue a RAISERROR command, and have that error m
essage be returned to the
> same app, but that sounds a bit ... meaningless. If you give us more infor
mation, we can probably
> give some suggestion.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:54A80BEF-6285-4F6A-8B60-F06007C1BDEF@.microsoft.com...
>|||Well, I was able to raise that message if I physically update the RecordID -
meaning I go to the live table in the Access ADP which is the same thing tha
t
was going on with the Master table - where the End user was physically
writing to the table through a form. But if I update the table
programmatically from the ADP, then the message does not come up.
While I am at it, I want to alter/replace my raiseerror message. I used the
sp_addmessage sp. Since my message already exists as 50001, I don't want to
add another message. I want to alter this one. But I get an error message
in QA saying that I need to use REPLACE to alter the message for ID 50001.
I
don't know the syntax for this. I have tried variations such as:
USE master
EXEC sp_addmessage 50001, 16,
select replace('This is a test custome message', 'custome', 'custom')
and placing REplace in other locations with no success. Any suggestions how
to do this correctly would be greatly appreciated.
"Rich" wrote:
> The external app in this case is an Access ADP. I had to modify a trigger
a
> few months ago, and I added a raiseerror message at the end to see my resu
lts
> in QA - not error results - just checking what parameter was being used.
I
> accidentally left the raiseerror message in the trigger, and then I got a
> call from an End User stating that this message was coming up all of a sud
den
> when she made updates to the table.
> I found the table and reactivated the raiseerror message and I get it when
I
> updaet a field. The only thing I noticed is that the field I update in th
is
> table (the master table) is not a key field. In the Detail table when I
> update the RecordID field this action does not raise the message like in t
he
> Master table. I guess my question is if this is something fundamental tha
t I
> am missing or is it something that I need to dig around to see what is goi
ng
> on?
> "Tibor Karaszi" wrote:
>|||You more or less lost me on the logic part, but from a technical viewpoint:
If you see the error when executing a statement which will result in the tri
gger being called using
TSQL but not when using ADP, then probably ADP is masking that error and you
should check with an
Access group to see how you can rectify this behavior in Access. Use Profile
r to catch the TSQL
command being submitted from Access just to make sure of what is happening o
n the server level.
Here is how you replace a message text:
EXEC sp_addmessage 50001, 16,
N'Old message text'
GO
EXEC sp_addmessage 50001, 16,
N'NEW message text', @.replace = 'replace'
GO
RAISERROR(50001, -1, 1)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:4B003260-9016-4FE1-B324-F48A24007CD7@.microsoft.com...
> Well, I was able to raise that message if I physically update the RecordID
-
> meaning I go to the live table in the Access ADP which is the same thing t
hat
> was going on with the Master table - where the End user was physically
> writing to the table through a form. But if I update the table
> programmatically from the ADP, then the message does not come up.
> While I am at it, I want to alter/replace my raiseerror message. I used t
he
> sp_addmessage sp. Since my message already exists as 50001, I don't want
to
> add another message. I want to alter this one. But I get an error messag
e
> in QA saying that I need to use REPLACE to alter the message for ID 50001.
I
> don't know the syntax for this. I have tried variations such as:
> USE master
> EXEC sp_addmessage 50001, 16,
> select replace('This is a test custome message', 'custome', 'custom')
> and placing REplace in other locations with no success. Any suggestions h
ow
> to do this correctly would be greatly appreciated.
>
> "Rich" wrote:
>|||Thank you for explaining how to replace a custome message.
"Tibor Karaszi" wrote:

> You more or less lost me on the logic part, but from a technical viewpoint
:
> If you see the error when executing a statement which will result in the t
rigger being called using
> TSQL but not when using ADP, then probably ADP is masking that error and y
ou should check with an
> Access group to see how you can rectify this behavior in Access. Use Profi
ler to catch the TSQL
> command being submitted from Access just to make sure of what is happening
on the server level.
> Here is how you replace a message text:
> EXEC sp_addmessage 50001, 16,
> N'Old message text'
> GO
> EXEC sp_addmessage 50001, 16,
> N'NEW message text', @.replace = 'replace'
> GO
> RAISERROR(50001, -1, 1)
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:4B003260-9016-4FE1-B324-F48A24007CD7@.microsoft.com...
>

raise error with 2 procedures

I have 2 procedures.
1 procedure calls second procedure and in second procedure I use raise error
statement:
RAISEEROR(60005,1,1)
But first procedure doesn't get an error, @.@.error=0, so transaction in first
procedure is not rolled back.
Any idea?
I can use parameter like this:
exec @.eror=firstProcedureName
if @.eror=1 then
begin
ROLLBACK TRAN
RETURN
end
and in 2 procedure I return 1, if error is done.
But I wonder is there any automation?
Simonsimon
RAISERROR is intend for information messages not for ERRORS. Also look up
for NESTED operations in the BOL
"simon" <simon.zupan@.stud-moderna.si> wrote in message
news:e2Y7jRHKFHA.1396@.TK2MSFTNGP10.phx.gbl...
> I have 2 procedures.
> 1 procedure calls second procedure and in second procedure I use raise
error
> statement:
> RAISEEROR(60005,1,1)
> But first procedure doesn't get an error, @.@.error=0, so transaction in
first
> procedure is not rolled back.
> Any idea?
> I can use parameter like this:
> exec @.eror=firstProcedureName
> if @.eror=1 then
> begin
> ROLLBACK TRAN
> RETURN
> end
> and in 2 procedure I return 1, if error is done.
> But I wonder is there any automation?
> Simon
>|||What? From BOL, "Returns a user-defined error message and sets a system
flag to record that an error has occurred. "
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23YevxzHKFHA.4056@.TK2MSFTNGP14.phx.gbl...
> simon
> RAISERROR is intend for information messages not for ERRORS. Also look up
> for NESTED operations in the BOL
> "simon" <simon.zupan@.stud-moderna.si> wrote in message
> news:e2Y7jRHKFHA.1396@.TK2MSFTNGP10.phx.gbl...
> error
> first
>|||Your severity is not "high" enough to automatically cause the error logic.
Either use a higher severity or use the seterror option.
raiserror (60005,1,1)
select @.@.error
raiserror (60005,11,1)
select @.@.error
raiserror (60005,1,1) with seterror
select @.@.error
"simon" <simon.zupan@.stud-moderna.si> wrote in message
news:e2Y7jRHKFHA.1396@.TK2MSFTNGP10.phx.gbl...
> I have 2 procedures.
> 1 procedure calls second procedure and in second procedure I use raise
error
> statement:
> RAISEEROR(60005,1,1)
> But first procedure doesn't get an error, @.@.error=0, so transaction in
first
> procedure is not rolled back.
> Any idea?
> I can use parameter like this:
> exec @.eror=firstProcedureName
> if @.eror=1 then
> begin
> ROLLBACK TRAN
> RETURN
> end
> and in 2 procedure I return 1, if error is done.
> But I wonder is there any automation?
> Simon
>|||Scott
I mean 'Info' to inform the end-users about the error and not returning
actual error message.
"Scott Morris" <bogus@.bogus.com> wrote in message
news:u88agtJKFHA.2628@.tk2msftngp13.phx.gbl...
> What? From BOL, "Returns a user-defined error message and sets a system
> flag to record that an error has occurred. "
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23YevxzHKFHA.4056@.TK2MSFTNGP14.phx.gbl...
up
>|||It CAN be used to generate an informational message, but it is not limited
to such usage. When the proper invocation is used, the client application
sees the error in the same manner as any other dbms error (e.g., a
constraint violation).
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23T8dF%23JKFHA.2728@.TK2MSFTNGP10.phx.gbl...
> Scott
> I mean 'Info' to inform the end-users about the error and not returning
> actual error message.
>
> "Scott Morris" <bogus@.bogus.com> wrote in message
> news:u88agtJKFHA.2628@.tk2msftngp13.phx.gbl...
look
> up
raise
in
>sql

Raise an error in the dataflow

HI, I have a lookup that find a specific row. If that row does not exist, I need to create a new one. If it exists, I need to raise an error and trap it with the "on_error" event of the dataflow in order to log it. Is there a way to raise an error and specify the error message from the dataflow? I was thinking using the conditional split and verify if the value returned from the lookup (by set the error config to ignore the error) is not or not. But how can I raise an error when the value is not null?

Thank you,

Ccote

You'll need a script component in order to do this.

The syntax is:

ComponentMetadata.FireError(...)

-Jamie

RAID-Best Practices ?

I am moving from an Oracle Db server to a SQL server running on a new w2k3
member server. Prior to purchase I would like to select the appropriate RAID
level for the server which gives me the best performance and recovery options.
I heard something about RAID 10?
Appreciate it!
RPM
Here are the improvements in order of importance. Go as far down the list
as you have budget for.
1) Tlogs and Data on separate physical devices. If necessary, OS and TLogs
can share the same physical disks without too much of an impact IF it is a
dedicated and properly tuned SQL server.
2) Tlogs on RAID 1 or 1+0. These are sequentially written files and are
critical to transactional performance. Slow writes to the TLOGS are death
to a SQL server. Data can survive on RAID5.
3) Data and TLogs on different controllers. Much better for recovery in
case of hardware failure.
4) Data on RAID 1+0. Much faster than RAID5. About a five times faster for
transactional updates, depending on the number of drives in the physical
array. RAID 5 slows down on writes with more spindles. RAID 1+0 speeds up.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ron P" <RonP@.discussions.microsoft.com> wrote in message
news:3ED1EBCB-0A73-4BAF-9AC4-5AD52D59DC62@.microsoft.com...
> I am moving from an Oracle Db server to a SQL server running on a new w2k3
> member server. Prior to purchase I would like to select the appropriate
RAID
> level for the server which gives me the best performance and recovery
options.
> I heard something about RAID 10?
> Appreciate it!
> RPM
|||In addition to Geoff's excellent advice, separate filegroups for data and
nonclustered indexes can help performance if they're on separate
drives/controllers. All this depends on how many drives/controllers you
have. I would place this tip between 3 and 4 on Geoff's list below.
Thanks,
Michael C#, MCDBA
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:eiQ3kZyCFHA.3812@.TK2MSFTNGP15.phx.gbl...
> Here are the improvements in order of importance. Go as far down the list
> as you have budget for.
> 1) Tlogs and Data on separate physical devices. If necessary, OS and
> TLogs
> can share the same physical disks without too much of an impact IF it is a
> dedicated and properly tuned SQL server.
> 2) Tlogs on RAID 1 or 1+0. These are sequentially written files and are
> critical to transactional performance. Slow writes to the TLOGS are death
> to a SQL server. Data can survive on RAID5.
> 3) Data and TLogs on different controllers. Much better for recovery in
> case of hardware failure.
> 4) Data on RAID 1+0. Much faster than RAID5. About a five times faster
> for
> transactional updates, depending on the number of drives in the physical
> array. RAID 5 slows down on writes with more spindles. RAID 1+0 speeds
> up.
>
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Ron P" <RonP@.discussions.microsoft.com> wrote in message
> news:3ED1EBCB-0A73-4BAF-9AC4-5AD52D59DC62@.microsoft.com...
> RAID
> options.
>
|||First, you should evaluate your needs in term of capacity, performance and
reliability before making the choice for the RAID. Simply saying that you
want the *best* worths nothing in an evaluation.
S. L.
"Ron P" <RonP@.discussions.microsoft.com> wrote in message
news:3ED1EBCB-0A73-4BAF-9AC4-5AD52D59DC62@.microsoft.com...
>I am moving from an Oracle Db server to a SQL server running on a new w2k3
> member server. Prior to purchase I would like to select the appropriate
> RAID
> level for the server which gives me the best performance and recovery
> options.
> I heard something about RAID 10?
> Appreciate it!
> RPM
|||Thank you all for the input.
To clarify, If I have 20-25 users hitting this dedicated SQL server (DELL
2850 2.0 GB RAM, Dual Processors) which has the OS on a Raid 1 setup, would I
be better off with RAID 1+0 or Raid 1 for the data partition. Should I put
the TLogs on the OS or on the data partition?
TX again
Ron P
"Ron P" wrote:

> I am moving from an Oracle Db server to a SQL server running on a new w2k3
> member server. Prior to purchase I would like to select the appropriate RAID
> level for the server which gives me the best performance and recovery options.
> I heard something about RAID 10?
> Appreciate it!
> RPM
|||"Ron P" <RonP@.discussions.microsoft.com> wrote in message
news:F8A7A612-C982-4912-B04A-BB0B63E5160E@.microsoft.com...
> Thank you all for the input.
> To clarify, If I have 20-25 users hitting this dedicated SQL server (DELL
> 2850 2.0 GB RAM, Dual Processors) which has the OS on a Raid 1 setup,
would I
> be better off with RAID 1+0 or Raid 1 for the data partition. Should I put
> the TLogs on the OS or on the data partition?
I'd probably put the OS and data on the same partition and the Tlogs on a
separete physical partition.
But my first question would be, "do you really need to?". Is Disk I/O your
biggest bottleneck here?
[vbcol=seagreen]
> TX again
> Ron P
> "Ron P" wrote:
w2k3[vbcol=seagreen]
RAID[vbcol=seagreen]
options.[vbcol=seagreen]
|||Ron,
I think you need to study up a bit more.
RAID 1+0 is definately the highest performance RAID leve, but it is also the
most expensive.
a huge percentage of production systems use RAID 5. RAID 5 is not the best
for performance, but it provides fault tolerance and is relatively
inexpensive.
It sounds to me like placing OS and Logs on a RAID 1 Volume and the Data and
Indexes on RAID 5 might be the ticket.
Another important issue with RAID is, the MORE physical disks you have the
better. In other words, if you need 140GB of storage, you are better off
purchasing 4 36GB Drives as opposed to using 2 72GB Drives.
RAID Controller is also important. You need a Controller with Battery Backed
Cache for performance and for Fault Tolerance.
There are TONS of articles on RAID on the net. Just "Google" it up.
if you have more questions, you can email me directly
Greg Jackson
Portland, Oregon
|||My standard build recommendation on a 2850 is Mirrored drives (RAID 1) on
one channel for OS and TLOGs. Definitely 15KRPM, most likely 73GB. The
other four drive slots can be used for RAID 1+ 0 data on the second channel.
Depending on your actual data space requirements, you can use either 73GB
15KRPM drives or 146GB 10KRPM drives. Remember to order the split backplane
module to take advantage of the extra controller channel.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ron P" <RonP@.discussions.microsoft.com> wrote in message
news:F8A7A612-C982-4912-B04A-BB0B63E5160E@.microsoft.com...
> Thank you all for the input.
> To clarify, If I have 20-25 users hitting this dedicated SQL server (DELL
> 2850 2.0 GB RAM, Dual Processors) which has the OS on a Raid 1 setup,
would I[vbcol=seagreen]
> be better off with RAID 1+0 or Raid 1 for the data partition. Should I put
> the TLogs on the OS or on the data partition?
> TX again
> Ron P
> "Ron P" wrote:
w2k3[vbcol=seagreen]
RAID[vbcol=seagreen]
options.[vbcol=seagreen]
|||The choice between RAID 1 and RAID 5 is highly dependent on the ratio of
reads vs. writes. If you perform a large proportion of writes, RAID 5 will
cause a performance hit. As you mentioned, RAID 5 is not good for
Transaction Logs, regardless of how you decide to store data and indexes.
Also, the OP mentions separate partitions - how many physical drives do you
have Ron? As Greg pointed out, Physical Drives and separate controllers are
important performance factors. Separate partitions on the same hard drive
probably won't help, and might hinder, performance.
Thanks,
Michael C#, MCDBA
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:OVe0OSHDFHA.3596@.TK2MSFTNGP12.phx.gbl...
> Ron,
> I think you need to study up a bit more.
> RAID 1+0 is definately the highest performance RAID leve, but it is also
> the most expensive.
> a huge percentage of production systems use RAID 5. RAID 5 is not the best
> for performance, but it provides fault tolerance and is relatively
> inexpensive.
> It sounds to me like placing OS and Logs on a RAID 1 Volume and the Data
> and Indexes on RAID 5 might be the ticket.
> Another important issue with RAID is, the MORE physical disks you have the
> better. In other words, if you need 140GB of storage, you are better off
> purchasing 4 36GB Drives as opposed to using 2 72GB Drives.
> RAID Controller is also important. You need a Controller with Battery
> Backed Cache for performance and for Fault Tolerance.
> There are TONS of articles on RAID on the net. Just "Google" it up.
> if you have more questions, you can email me directly
>
> Greg Jackson
> Portland, Oregon
>
|||If Windows and TLogs are on RAID1 and DB's are on RAID5, where is the best
place for SQL backup files?

RAID-Best Practices ?

I am moving from an Oracle Db server to a SQL server running on a new w2k3
member server. Prior to purchase I would like to select the appropriate RAID
level for the server which gives me the best performance and recovery option
s.
I heard something about RAID 10?
Appreciate it!
RPMHere are the improvements in order of importance. Go as far down the list
as you have budget for.
1) Tlogs and Data on separate physical devices. If necessary, OS and TLogs
can share the same physical disks without too much of an impact IF it is a
dedicated and properly tuned SQL server.
2) Tlogs on RAID 1 or 1+0. These are sequentially written files and are
critical to transactional performance. Slow writes to the TLOGS are death
to a SQL server. Data can survive on RAID5.
3) Data and TLogs on different controllers. Much better for recovery in
case of hardware failure.
4) Data on RAID 1+0. Much faster than RAID5. About a five times faster for
transactional updates, depending on the number of drives in the physical
array. RAID 5 slows down on writes with more spindles. RAID 1+0 speeds up.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ron P" <RonP@.discussions.microsoft.com> wrote in message
news:3ED1EBCB-0A73-4BAF-9AC4-5AD52D59DC62@.microsoft.com...
> I am moving from an Oracle Db server to a SQL server running on a new w2k3
> member server. Prior to purchase I would like to select the appropriate
RAID
> level for the server which gives me the best performance and recovery
options.
> I heard something about RAID 10?
> Appreciate it!
> RPM|||In addition to Geoff's excellent advice, separate filegroups for data and
nonclustered indexes can help performance if they're on separate
drives/controllers. All this depends on how many drives/controllers you
have. I would place this tip between 3 and 4 on Geoff's list below.
Thanks,
Michael C#, MCDBA
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:eiQ3kZyCFHA.3812@.TK2MSFTNGP15.phx.gbl...
> Here are the improvements in order of importance. Go as far down the list
> as you have budget for.
> 1) Tlogs and Data on separate physical devices. If necessary, OS and
> TLogs
> can share the same physical disks without too much of an impact IF it is a
> dedicated and properly tuned SQL server.
> 2) Tlogs on RAID 1 or 1+0. These are sequentially written files and are
> critical to transactional performance. Slow writes to the TLOGS are death
> to a SQL server. Data can survive on RAID5.
> 3) Data and TLogs on different controllers. Much better for recovery in
> case of hardware failure.
> 4) Data on RAID 1+0. Much faster than RAID5. About a five times faster
> for
> transactional updates, depending on the number of drives in the physical
> array. RAID 5 slows down on writes with more spindles. RAID 1+0 speeds
> up.
>
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Ron P" <RonP@.discussions.microsoft.com> wrote in message
> news:3ED1EBCB-0A73-4BAF-9AC4-5AD52D59DC62@.microsoft.com...
> RAID
> options.
>|||First, you should evaluate your needs in term of capacity, performance and
reliability before making the choice for the RAID. Simply saying that you
want the *best* worths nothing in an evaluation.
S. L.
"Ron P" <RonP@.discussions.microsoft.com> wrote in message
news:3ED1EBCB-0A73-4BAF-9AC4-5AD52D59DC62@.microsoft.com...
>I am moving from an Oracle Db server to a SQL server running on a new w2k3
> member server. Prior to purchase I would like to select the appropriate
> RAID
> level for the server which gives me the best performance and recovery
> options.
> I heard something about RAID 10?
> Appreciate it!
> RPM|||Thank you all for the input.
To clarify, If I have 20-25 users hitting this dedicated SQL server (DELL
2850 2.0 GB RAM, Dual Processors) which has the OS on a Raid 1 setup, would
I
be better off with RAID 1+0 or Raid 1 for the data partition. Should I put
the TLogs on the OS or on the data partition?
TX again
Ron P
"Ron P" wrote:

> I am moving from an Oracle Db server to a SQL server running on a new w2k3
> member server. Prior to purchase I would like to select the appropriate RA
ID
> level for the server which gives me the best performance and recovery opti
ons.
> I heard something about RAID 10?
> Appreciate it!
> RPM|||"Ron P" <RonP@.discussions.microsoft.com> wrote in message
news:F8A7A612-C982-4912-B04A-BB0B63E5160E@.microsoft.com...
> Thank you all for the input.
> To clarify, If I have 20-25 users hitting this dedicated SQL server (DELL
> 2850 2.0 GB RAM, Dual Processors) which has the OS on a Raid 1 setup,
would I
> be better off with RAID 1+0 or Raid 1 for the data partition. Should I put
> the TLogs on the OS or on the data partition?
I'd probably put the OS and data on the same partition and the Tlogs on a
separete physical partition.
But my first question would be, "do you really need to?". Is Disk I/O your
biggest bottleneck here?
[vbcol=seagreen]
> TX again
> Ron P
> "Ron P" wrote:
>
w2k3[vbcol=seagreen]
RAID[vbcol=seagreen]
options.[vbcol=seagreen]|||Ron,
I think you need to study up a bit more.
RAID 1+0 is definately the highest performance RAID leve, but it is also the
most expensive.
a huge percentage of production systems use RAID 5. RAID 5 is not the best
for performance, but it provides fault tolerance and is relatively
inexpensive.
It sounds to me like placing OS and Logs on a RAID 1 Volume and the Data and
Indexes on RAID 5 might be the ticket.
Another important issue with RAID is, the MORE physical disks you have the
better. In other words, if you need 140GB of storage, you are better off
purchasing 4 36GB Drives as opposed to using 2 72GB Drives.
RAID Controller is also important. You need a Controller with Battery Backed
Cache for performance and for Fault Tolerance.
There are TONS of articles on RAID on the net. Just "Google" it up.
if you have more questions, you can email me directly
Greg Jackson
Portland, Oregon|||My standard build recommendation on a 2850 is Mirrored drives (RAID 1) on
one channel for OS and TLOGs. Definitely 15KRPM, most likely 73GB. The
other four drive slots can be used for RAID 1+ 0 data on the second channel.
Depending on your actual data space requirements, you can use either 73GB
15KRPM drives or 146GB 10KRPM drives. Remember to order the split backplane
module to take advantage of the extra controller channel.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ron P" <RonP@.discussions.microsoft.com> wrote in message
news:F8A7A612-C982-4912-B04A-BB0B63E5160E@.microsoft.com...
> Thank you all for the input.
> To clarify, If I have 20-25 users hitting this dedicated SQL server (DELL
> 2850 2.0 GB RAM, Dual Processors) which has the OS on a Raid 1 setup,
would I[vbcol=seagreen]
> be better off with RAID 1+0 or Raid 1 for the data partition. Should I put
> the TLogs on the OS or on the data partition?
> TX again
> Ron P
> "Ron P" wrote:
>
w2k3[vbcol=seagreen]
RAID[vbcol=seagreen]
options.[vbcol=seagreen]|||The choice between RAID 1 and RAID 5 is highly dependent on the ratio of
reads vs. writes. If you perform a large proportion of writes, RAID 5 will
cause a performance hit. As you mentioned, RAID 5 is not good for
Transaction Logs, regardless of how you decide to store data and indexes.
Also, the OP mentions separate partitions - how many physical drives do you
have Ron? As Greg pointed out, Physical Drives and separate controllers are
important performance factors. Separate partitions on the same hard drive
probably won't help, and might hinder, performance.
Thanks,
Michael C#, MCDBA
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:OVe0OSHDFHA.3596@.TK2MSFTNGP12.phx.gbl...
> Ron,
> I think you need to study up a bit more.
> RAID 1+0 is definately the highest performance RAID leve, but it is also
> the most expensive.
> a huge percentage of production systems use RAID 5. RAID 5 is not the best
> for performance, but it provides fault tolerance and is relatively
> inexpensive.
> It sounds to me like placing OS and Logs on a RAID 1 Volume and the Data
> and Indexes on RAID 5 might be the ticket.
> Another important issue with RAID is, the MORE physical disks you have the
> better. In other words, if you need 140GB of storage, you are better off
> purchasing 4 36GB Drives as opposed to using 2 72GB Drives.
> RAID Controller is also important. You need a Controller with Battery
> Backed Cache for performance and for Fault Tolerance.
> There are TONS of articles on RAID on the net. Just "Google" it up.
> if you have more questions, you can email me directly
>
> Greg Jackson
> Portland, Oregon
>

RAID-Best Practices ?

I am moving from an Oracle Db server to a SQL server running on a new w2k3
member server. Prior to purchase I would like to select the appropriate RAID
level for the server which gives me the best performance and recovery options.
I heard something about RAID 10?
Appreciate it!
RPMHere are the improvements in order of importance. Go as far down the list
as you have budget for.
1) Tlogs and Data on separate physical devices. If necessary, OS and TLogs
can share the same physical disks without too much of an impact IF it is a
dedicated and properly tuned SQL server.
2) Tlogs on RAID 1 or 1+0. These are sequentially written files and are
critical to transactional performance. Slow writes to the TLOGS are death
to a SQL server. Data can survive on RAID5.
3) Data and TLogs on different controllers. Much better for recovery in
case of hardware failure.
4) Data on RAID 1+0. Much faster than RAID5. About a five times faster for
transactional updates, depending on the number of drives in the physical
array. RAID 5 slows down on writes with more spindles. RAID 1+0 speeds up.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ron P" <RonP@.discussions.microsoft.com> wrote in message
news:3ED1EBCB-0A73-4BAF-9AC4-5AD52D59DC62@.microsoft.com...
> I am moving from an Oracle Db server to a SQL server running on a new w2k3
> member server. Prior to purchase I would like to select the appropriate
RAID
> level for the server which gives me the best performance and recovery
options.
> I heard something about RAID 10?
> Appreciate it!
> RPM|||In addition to Geoff's excellent advice, separate filegroups for data and
nonclustered indexes can help performance if they're on separate
drives/controllers. All this depends on how many drives/controllers you
have. I would place this tip between 3 and 4 on Geoff's list below.
Thanks,
Michael C#, MCDBA
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:eiQ3kZyCFHA.3812@.TK2MSFTNGP15.phx.gbl...
> Here are the improvements in order of importance. Go as far down the list
> as you have budget for.
> 1) Tlogs and Data on separate physical devices. If necessary, OS and
> TLogs
> can share the same physical disks without too much of an impact IF it is a
> dedicated and properly tuned SQL server.
> 2) Tlogs on RAID 1 or 1+0. These are sequentially written files and are
> critical to transactional performance. Slow writes to the TLOGS are death
> to a SQL server. Data can survive on RAID5.
> 3) Data and TLogs on different controllers. Much better for recovery in
> case of hardware failure.
> 4) Data on RAID 1+0. Much faster than RAID5. About a five times faster
> for
> transactional updates, depending on the number of drives in the physical
> array. RAID 5 slows down on writes with more spindles. RAID 1+0 speeds
> up.
>
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Ron P" <RonP@.discussions.microsoft.com> wrote in message
> news:3ED1EBCB-0A73-4BAF-9AC4-5AD52D59DC62@.microsoft.com...
>> I am moving from an Oracle Db server to a SQL server running on a new
>> w2k3
>> member server. Prior to purchase I would like to select the appropriate
> RAID
>> level for the server which gives me the best performance and recovery
> options.
>> I heard something about RAID 10?
>> Appreciate it!
>> RPM
>|||First, you should evaluate your needs in term of capacity, performance and
reliability before making the choice for the RAID. Simply saying that you
want the *best* worths nothing in an evaluation.
S. L.
"Ron P" <RonP@.discussions.microsoft.com> wrote in message
news:3ED1EBCB-0A73-4BAF-9AC4-5AD52D59DC62@.microsoft.com...
>I am moving from an Oracle Db server to a SQL server running on a new w2k3
> member server. Prior to purchase I would like to select the appropriate
> RAID
> level for the server which gives me the best performance and recovery
> options.
> I heard something about RAID 10?
> Appreciate it!
> RPM|||Thank you all for the input.
To clarify, If I have 20-25 users hitting this dedicated SQL server (DELL
2850 2.0 GB RAM, Dual Processors) which has the OS on a Raid 1 setup, would I
be better off with RAID 1+0 or Raid 1 for the data partition. Should I put
the TLogs on the OS or on the data partition?
TX again
Ron P
"Ron P" wrote:
> I am moving from an Oracle Db server to a SQL server running on a new w2k3
> member server. Prior to purchase I would like to select the appropriate RAID
> level for the server which gives me the best performance and recovery options.
> I heard something about RAID 10?
> Appreciate it!
> RPM|||"Ron P" <RonP@.discussions.microsoft.com> wrote in message
news:F8A7A612-C982-4912-B04A-BB0B63E5160E@.microsoft.com...
> Thank you all for the input.
> To clarify, If I have 20-25 users hitting this dedicated SQL server (DELL
> 2850 2.0 GB RAM, Dual Processors) which has the OS on a Raid 1 setup,
would I
> be better off with RAID 1+0 or Raid 1 for the data partition. Should I put
> the TLogs on the OS or on the data partition?
I'd probably put the OS and data on the same partition and the Tlogs on a
separete physical partition.
But my first question would be, "do you really need to?". Is Disk I/O your
biggest bottleneck here?
> TX again
> Ron P
> "Ron P" wrote:
> > I am moving from an Oracle Db server to a SQL server running on a new
w2k3
> > member server. Prior to purchase I would like to select the appropriate
RAID
> > level for the server which gives me the best performance and recovery
options.
> >
> > I heard something about RAID 10?
> > Appreciate it!
> >
> > RPM|||Ron,
I think you need to study up a bit more.
RAID 1+0 is definately the highest performance RAID leve, but it is also the
most expensive.
a huge percentage of production systems use RAID 5. RAID 5 is not the best
for performance, but it provides fault tolerance and is relatively
inexpensive.
It sounds to me like placing OS and Logs on a RAID 1 Volume and the Data and
Indexes on RAID 5 might be the ticket.
Another important issue with RAID is, the MORE physical disks you have the
better. In other words, if you need 140GB of storage, you are better off
purchasing 4 36GB Drives as opposed to using 2 72GB Drives.
RAID Controller is also important. You need a Controller with Battery Backed
Cache for performance and for Fault Tolerance.
There are TONS of articles on RAID on the net. Just "Google" it up.
if you have more questions, you can email me directly
Greg Jackson
Portland, Oregon|||My standard build recommendation on a 2850 is Mirrored drives (RAID 1) on
one channel for OS and TLOGs. Definitely 15KRPM, most likely 73GB. The
other four drive slots can be used for RAID 1+ 0 data on the second channel.
Depending on your actual data space requirements, you can use either 73GB
15KRPM drives or 146GB 10KRPM drives. Remember to order the split backplane
module to take advantage of the extra controller channel.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ron P" <RonP@.discussions.microsoft.com> wrote in message
news:F8A7A612-C982-4912-B04A-BB0B63E5160E@.microsoft.com...
> Thank you all for the input.
> To clarify, If I have 20-25 users hitting this dedicated SQL server (DELL
> 2850 2.0 GB RAM, Dual Processors) which has the OS on a Raid 1 setup,
would I
> be better off with RAID 1+0 or Raid 1 for the data partition. Should I put
> the TLogs on the OS or on the data partition?
> TX again
> Ron P
> "Ron P" wrote:
> > I am moving from an Oracle Db server to a SQL server running on a new
w2k3
> > member server. Prior to purchase I would like to select the appropriate
RAID
> > level for the server which gives me the best performance and recovery
options.
> >
> > I heard something about RAID 10?
> > Appreciate it!
> >
> > RPM|||The choice between RAID 1 and RAID 5 is highly dependent on the ratio of
reads vs. writes. If you perform a large proportion of writes, RAID 5 will
cause a performance hit. As you mentioned, RAID 5 is not good for
Transaction Logs, regardless of how you decide to store data and indexes.
Also, the OP mentions separate partitions - how many physical drives do you
have Ron? As Greg pointed out, Physical Drives and separate controllers are
important performance factors. Separate partitions on the same hard drive
probably won't help, and might hinder, performance.
Thanks,
Michael C#, MCDBA
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:OVe0OSHDFHA.3596@.TK2MSFTNGP12.phx.gbl...
> Ron,
> I think you need to study up a bit more.
> RAID 1+0 is definately the highest performance RAID leve, but it is also
> the most expensive.
> a huge percentage of production systems use RAID 5. RAID 5 is not the best
> for performance, but it provides fault tolerance and is relatively
> inexpensive.
> It sounds to me like placing OS and Logs on a RAID 1 Volume and the Data
> and Indexes on RAID 5 might be the ticket.
> Another important issue with RAID is, the MORE physical disks you have the
> better. In other words, if you need 140GB of storage, you are better off
> purchasing 4 36GB Drives as opposed to using 2 72GB Drives.
> RAID Controller is also important. You need a Controller with Battery
> Backed Cache for performance and for Fault Tolerance.
> There are TONS of articles on RAID on the net. Just "Google" it up.
> if you have more questions, you can email me directly
>
> Greg Jackson
> Portland, Oregon
>|||If Windows and TLogs are on RAID1 and DB's are on RAID5, where is the best
place for SQL backup files?|||Another server.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:F8350DA6-12A5-4930-B131-9CD2C217BBC4@.microsoft.com...
> If Windows and TLogs are on RAID1 and DB's are on RAID5, where is the best
> place for SQL backup files?|||I agree that would be my first choice. However in the senario where there is
no option but to have the SQL backups on the same server, where is the best
place for an successful recovery.|||USB drive so you can attach it to a recovery server.
Local backups aren't.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:F87D91FF-1B21-4646-83DB-4DE7BD2C3725@.microsoft.com...
>I agree that would be my first choice. However in the senario where there
>is
> no option but to have the SQL backups on the same server, where is the
> best
> place for an successful recovery.
>|||As Geoff suggests, you need to get the data off the server. However, if
your .mdf data files are on the RAID 5, and you need to first backup to the
local disks, backup to the other physical disks on your server. It sounds
like your only option is the RAID 1 partition. If two of your RAID 5 disks
die, you still have your latest backup on the RAID 1 partition.
Mark
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:F8350DA6-12A5-4930-B131-9CD2C217BBC4@.microsoft.com...
> If Windows and TLogs are on RAID1 and DB's are on RAID5, where is the best
> place for SQL backup files?

RAID5 for all files?

In the last few months I've run across two places that had all their
files, including both data and logs, on big, fat RAID5 partitions.
In fact, in one place even the OS and pagefile were on RAID5!
Is this, like, a good idea all of a sudden, and nobody told me?
I hautily informed them that putting in separate physical drives for a
RAID1 set for logs, might provide a load/scalability/performance
factor of 2x all by itself. Is it at all likely that this is actually
the case? Just wondering.
Thanks.
Josh
No...Raid 5 still sucks. The baarf web site is still in
operation - http://www.baarf.com/
Logs being separated out on a Raid 1 or Raid 10 is still
recommended - see the Storage Best Practices:
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/storage-top-10.mspx
-Sue
On Sun, 05 Aug 2007 20:17:18 -0700, JXStern
<JXSternChangeX2R@.gte.net> wrote:

>In the last few months I've run across two places that had all their
>files, including both data and logs, on big, fat RAID5 partitions.
>In fact, in one place even the OS and pagefile were on RAID5!
>Is this, like, a good idea all of a sudden, and nobody told me?
>I hautily informed them that putting in separate physical drives for a
>RAID1 set for logs, might provide a load/scalability/performance
>factor of 2x all by itself. Is it at all likely that this is actually
>the case? Just wondering.
>Thanks.
>Josh
|||As Sue mentions it is still not the best practice to use Raid5 for a busy
OLTP system.
Andrew J. Kelly SQL MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:kd4db35ek3ge7ldau600i4tk7igv6irns6@.4ax.com...
> In the last few months I've run across two places that had all their
> files, including both data and logs, on big, fat RAID5 partitions.
> In fact, in one place even the OS and pagefile were on RAID5!
> Is this, like, a good idea all of a sudden, and nobody told me?
> I hautily informed them that putting in separate physical drives for a
> RAID1 set for logs, might provide a load/scalability/performance
> factor of 2x all by itself. Is it at all likely that this is actually
> the case? Just wondering.
> Thanks.
> Josh
>
|||On Mon, 6 Aug 2007 08:50:20 -0400, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:

>As Sue mentions it is still not the best practice to use Raid5 for a busy
>OLTP system.
And even less good for a busy ETL system building gigabyte tables and
output files?
J.
sql

RAID5 for all files?

In the last few months I've run across two places that had all their
files, including both data and logs, on big, fat RAID5 partitions.
In fact, in one place even the OS and pagefile were on RAID5!
Is this, like, a good idea all of a sudden, and nobody told me?
I hautily informed them that putting in separate physical drives for a
RAID1 set for logs, might provide a load/scalability/performance
factor of 2x all by itself. Is it at all likely that this is actually
the case? Just wondering.
Thanks.
JoshNo...Raid 5 still sucks. The baarf web site is still in
operation - http://www.baarf.com/
Logs being separated out on a Raid 1 or Raid 10 is still
recommended - see the Storage Best Practices:
[url]http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/storage-top-10.mspx[
/url]
-Sue
On Sun, 05 Aug 2007 20:17:18 -0700, JXStern
<JXSternChangeX2R@.gte.net> wrote:

>In the last few months I've run across two places that had all their
>files, including both data and logs, on big, fat RAID5 partitions.
>In fact, in one place even the OS and pagefile were on RAID5!
>Is this, like, a good idea all of a sudden, and nobody told me?
>I hautily informed them that putting in separate physical drives for a
>RAID1 set for logs, might provide a load/scalability/performance
>factor of 2x all by itself. Is it at all likely that this is actually
>the case? Just wondering.
>Thanks.
>Josh|||As Sue mentions it is still not the best practice to use Raid5 for a busy
OLTP system.
Andrew J. Kelly SQL MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:kd4db35ek3ge7ldau600i4tk7igv6irns6@.
4ax.com...
> In the last few months I've run across two places that had all their
> files, including both data and logs, on big, fat RAID5 partitions.
> In fact, in one place even the OS and pagefile were on RAID5!
> Is this, like, a good idea all of a sudden, and nobody told me?
> I hautily informed them that putting in separate physical drives for a
> RAID1 set for logs, might provide a load/scalability/performance
> factor of 2x all by itself. Is it at all likely that this is actually
> the case? Just wondering.
> Thanks.
> Josh
>|||On Mon, 6 Aug 2007 08:50:20 -0400, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:

>As Sue mentions it is still not the best practice to use Raid5 for a busy
>OLTP system.
And even less good for a busy ETL system building gigabyte tables and
output files?
J.

RAID5 for all files?

In the last few months I've run across two places that had all their
files, including both data and logs, on big, fat RAID5 partitions.
In fact, in one place even the OS and pagefile were on RAID5!
Is this, like, a good idea all of a sudden, and nobody told me?
I hautily informed them that putting in separate physical drives for a
RAID1 set for logs, might provide a load/scalability/performance
factor of 2x all by itself. Is it at all likely that this is actually
the case? Just wondering. :)
Thanks.
JoshNo...Raid 5 still sucks. The baarf web site is still in
operation - http://www.baarf.com/
Logs being separated out on a Raid 1 or Raid 10 is still
recommended - see the Storage Best Practices:
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/storage-top-10.mspx
-Sue
On Sun, 05 Aug 2007 20:17:18 -0700, JXStern
<JXSternChangeX2R@.gte.net> wrote:
>In the last few months I've run across two places that had all their
>files, including both data and logs, on big, fat RAID5 partitions.
>In fact, in one place even the OS and pagefile were on RAID5!
>Is this, like, a good idea all of a sudden, and nobody told me?
>I hautily informed them that putting in separate physical drives for a
>RAID1 set for logs, might provide a load/scalability/performance
>factor of 2x all by itself. Is it at all likely that this is actually
>the case? Just wondering. :)
>Thanks.
>Josh|||As Sue mentions it is still not the best practice to use Raid5 for a busy
OLTP system.
--
Andrew J. Kelly SQL MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:kd4db35ek3ge7ldau600i4tk7igv6irns6@.4ax.com...
> In the last few months I've run across two places that had all their
> files, including both data and logs, on big, fat RAID5 partitions.
> In fact, in one place even the OS and pagefile were on RAID5!
> Is this, like, a good idea all of a sudden, and nobody told me?
> I hautily informed them that putting in separate physical drives for a
> RAID1 set for logs, might provide a load/scalability/performance
> factor of 2x all by itself. Is it at all likely that this is actually
> the case? Just wondering. :)
> Thanks.
> Josh
>|||On Mon, 6 Aug 2007 08:50:20 -0400, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:
>As Sue mentions it is still not the best practice to use Raid5 for a busy
>OLTP system.
And even less good for a busy ETL system building gigabyte tables and
output files?
J.

Raid5 and Suspect DB

I have a DB on a Raid 5 of 4 disks. One of the disks failed, freezed the
screen and everything else,
I was obliged to reboot
After reboot, My DB was in suspectmode, First question, if I am not
mistaken, as far as it is raid 5, the DB must not be in suspect mode
Ok, I used the last good full backup, DB came back on line. 2 hours or more
the same thing happened.
I wonder what is the efficiency of RAID 5
Perhaps the database was exactly in the middle of IOs and the Raid did not
cover it...Was the Raid array continuously available?
Asingle disk failure on Raid 5 should not have affected anything.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"SalamElias" <eliassal@.online.nospam> wrote in message
news:C7D83256-BDCA-41D4-A45C-214CF0F47202@.microsoft.com...
> I have a DB on a Raid 5 of 4 disks. One of the disks failed, freezed the
> screen and everything else,
> I was obliged to reboot
> After reboot, My DB was in suspectmode, First question, if I am not
> mistaken, as far as it is raid 5, the DB must not be in suspect mode
> Ok, I used the last good full backup, DB came back on line. 2 hours or
more
> the same thing happened.
> I wonder what is the efficiency of RAID 5
|||Thanks for your response, when you say DB was in the middle of IOs, I
understand when reading Books on line or any other docs regarding DBs
recovery plan should be able to handle these events (rol back or roll forward
in case of disk failure. AmI mistaken?
I confirm it wa a one disk failure. Also one more strange thing happened, I
changed the faulty disk, deleted the database, tried to recreate it, It was
not possible (got error messages). I changed the physical datafile place (on
the raid 0 log disk), DB was created. Any ideas or thoughts.
Thanks again
"Wayne Snyder" wrote:

> Perhaps the database was exactly in the middle of IOs and the Raid did not
> cover it...Was the Raid array continuously available?
> Asingle disk failure on Raid 5 should not have affected anything.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "SalamElias" <eliassal@.online.nospam> wrote in message
> news:C7D83256-BDCA-41D4-A45C-214CF0F47202@.microsoft.com...
> more
>
>

Raid5 and Suspect DB

I have a DB on a Raid 5 of 4 disks. One of the disks failed, freezed the
screen and everything else,
I was obliged to reboot
After reboot, My DB was in suspectmode, First question, if I am not
mistaken, as far as it is raid 5, the DB must not be in suspect mode
Ok, I used the last good full backup, DB came back on line. 2 hours or more
the same thing happened.
I wonder what is the efficiency of RAID 5Perhaps the database was exactly in the middle of IOs and the Raid did not
cover it...Was the Raid array continuously available?
Asingle disk failure on Raid 5 should not have affected anything.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"SalamElias" <eliassal@.online.nospam> wrote in message
news:C7D83256-BDCA-41D4-A45C-214CF0F47202@.microsoft.com...
> I have a DB on a Raid 5 of 4 disks. One of the disks failed, freezed the
> screen and everything else,
> I was obliged to reboot
> After reboot, My DB was in suspectmode, First question, if I am not
> mistaken, as far as it is raid 5, the DB must not be in suspect mode
> Ok, I used the last good full backup, DB came back on line. 2 hours or
more
> the same thing happened.
> I wonder what is the efficiency of RAID 5|||Thanks for your response, when you say DB was in the middle of IOs, I
understand when reading Books on line or any other docs regarding DBs
recovery plan should be able to handle these events (rol back or roll forwar
d
in case of disk failure. AmI mistaken?
I confirm it wa a one disk failure. Also one more strange thing happened, I
changed the faulty disk, deleted the database, tried to recreate it, It was
not possible (got error messages). I changed the physical datafile place (on
the raid 0 log disk), DB was created. Any ideas or thoughts.
Thanks again
"Wayne Snyder" wrote:

> Perhaps the database was exactly in the middle of IOs and the Raid did not
> cover it...Was the Raid array continuously available?
> Asingle disk failure on Raid 5 should not have affected anything.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "SalamElias" <eliassal@.online.nospam> wrote in message
> news:C7D83256-BDCA-41D4-A45C-214CF0F47202@.microsoft.com...
> more
>
>

RAID1 vs RAID5 for transaction log

I was hoping that someone could point me to some good documentation on
selecting an optimum RAID configuration. While performing heavy updates
during upgrades - I've noticed dramatic performance difference between RAID
1 and RAID 5 for the transaction log.
It seems that it is less important for the data files themselves (RAID 5
doesn't seem to hurt that much).
Does this make sense where the RAID Configuration for the T-Log is more
important than the Data?
If someone could shed some light on this or point me to some good
documantation - I would greatly appreciate it.
Thanks in advance
Raid 5 performs very poorly on writes as does hp/compaq's ADG (Advanced data
guarding).
If your database is mainly reads then raid 5 is ok for the datafiles.
Also consider that if a disk fails and the array is having to construct a
phantom disk on the fly then the performance of your system will probably
make it unusable.
It's worth pulling a disk on a system before it goes live and practice the
recovery.
Personally I use a raid 10 array of 4 disks for my log (and raid 10 arrays
for my data too). A mirror pair may do for your log.
Paul
"TJT" <TJT@.nospam.com> wrote in message
news:uDWYpug5FHA.1248@.TK2MSFTNGP14.phx.gbl...
>I was hoping that someone could point me to some good documentation on
> selecting an optimum RAID configuration. While performing heavy updates
> during upgrades - I've noticed dramatic performance difference between
> RAID
> 1 and RAID 5 for the transaction log.
> It seems that it is less important for the data files themselves (RAID 5
> doesn't seem to hurt that much).
> Does this make sense where the RAID Configuration for the T-Log is more
> important than the Data?
> If someone could shed some light on this or point me to some good
> documantation - I would greatly appreciate it.
> Thanks in advance
>
|||I've always found this website to be a good RAID level overview (even
though it's a vendor website):
http://www.acnc.com/raid.html
It's not very detailed, just brief pros & cons and how the RAID level is
constructed, but it's good info nonetheless.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
TJT wrote:

>I was hoping that someone could point me to some good documentation on
>selecting an optimum RAID configuration. While performing heavy updates
>during upgrades - I've noticed dramatic performance difference between RAID
>1 and RAID 5 for the transaction log.
>It seems that it is less important for the data files themselves (RAID 5
>doesn't seem to hurt that much).
>Does this make sense where the RAID Configuration for the T-Log is more
>important than the Data?
>If someone could shed some light on this or point me to some good
>documantation - I would greatly appreciate it.
>Thanks in advance
>
>
|||RAID5 is terrible for heavily updated data, and the Transaction Log is the
prototypical worst case. Go for RAID 1. For database files/filegroups it
really depends on the update load. In general the feeling has been that
RAID 10 (aka 1+0) is better for databases since you get maximum performance
and availability. But if you have lightly updated tables then RAID 5 is
going to be OK.
If I remember correctly Kalen Delaney's "Inside SQL Server 2000" book has a
good discussion about this.
Hal Berenson, President
PredictableIT, LLC
www.predictableit.com
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:e6mn%23ll5FHA.1276@.TK2MSFTNGP09.phx.gbl...
> I've always found this website to be a good RAID level overview (even
> though it's a vendor website):
> http://www.acnc.com/raid.html
> It's not very detailed, just brief pros & cons and how the RAID level is
> constructed, but it's good info nonetheless.
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> TJT wrote:
>
sql