DeadLock Trace

We set our Deadlock Trace on with the following command:

DBCC TRACEOn (1204, -1)
DBCC TRACEOFF (3605, -1);
DBCC TRACESTATUS (-1)
GO

In 2000 this worked fine but in 2005 we get the following error:

Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:

What do I need to do to fix this so we can see the cause of the deadlock in the SQL Server logs?

The statements above do work on SQL2005 and will output deadlock graphs to the errorlog.

There is a new trace flag in SQL2005 that enhances the information output on deadlock: trace flag 1222.

What log viewer are you using to view the errorlogs? Can you open the errorlog files with notepad instead?

|||Thanks for the information. I use the SQL Server Log viewer in Microsoft SQL Server Management Studio to view the logs.|||

Hi Guys,

Unlike SQL 2000, Trace flag 3605 does not work in SQL Server 2005.

regards

Jag

DeadLock Trace

We set our Deadlock Trace on with the following command:

DBCC TRACEOn (1204, -1)
DBCC TRACEOFF (3605, -1);
DBCC TRACESTATUS (-1)
GO

In 2000 this worked fine but in 2005 we get the following error:

Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:

What do I need to do to fix this so we can see the cause of the deadlock in the SQL Server logs?

The statements above do work on SQL2005 and will output deadlock graphs to the errorlog.

There is a new trace flag in SQL2005 that enhances the information output on deadlock: trace flag 1222.

What log viewer are you using to view the errorlogs? Can you open the errorlog files with notepad instead?

|||Thanks for the information. I use the SQL Server Log viewer in Microsoft SQL Server Management Studio to view the logs.|||

Hi Guys,

Unlike SQL 2000, Trace flag 3605 does not work in SQL Server 2005.

regards

Jag

dead lock

Hi,
Yestoday I checked the SQL SERVER management information, it showed one
process blocked the others, the last TSQL command batch of the blocking
process was SET NOEXEC OFF, what's the reason that this process blocked
other processes, how to solve it?
Thanks
Hi,
I feel that the process mentioned by you may not be the probable case for
deadlock. COuld you activate the trace flag 1204 in server
to get more information on deadloak.
DBCC TRACEON(1204,-1)
This trace flag will put the information regarding the deadlock into sql
server error log for investigating the cause for deadlock.
Thanks
Hari
SQL Server MVP
"Taoge" <luxiaotao@.hotmail.com> wrote in message
news:u76Cq5MYFHA.1240@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Yestoday I checked the SQL SERVER management information, it showed one
> process blocked the others, the last TSQL command batch of the blocking
> process was SET NOEXEC OFF, what's the reason that this process blocked
> other processes, how to solve it?
> Thanks
>
|||Actually, 1204 only forces a full scan debug of the deadlock chain. 3604
sends output to the client and 3605 sends output to the server error log.
However, at some pointI believe SP3the 3605 flag was no longer required,
but it has gone back and forth a few times. So, I usually explicitly set
3605 as well.
Sincerely,
Anthony Thomas

"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:O7iETYPYFHA.3584@.TK2MSFTNGP12.phx.gbl...
Hi,
I feel that the process mentioned by you may not be the probable case for
deadlock. COuld you activate the trace flag 1204 in server
to get more information on deadloak.
DBCC TRACEON(1204,-1)
This trace flag will put the information regarding the deadlock into sql
server error log for investigating the cause for deadlock.
Thanks
Hari
SQL Server MVP
"Taoge" <luxiaotao@.hotmail.com> wrote in message
news:u76Cq5MYFHA.1240@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Yestoday I checked the SQL SERVER management information, it showed one
> process blocked the others, the last TSQL command batch of the blocking
> process was SET NOEXEC OFF, what's the reason that this process blocked
> other processes, how to solve it?
> Thanks
>

dead lock

Hi,
Yestoday I checked the SQL SERVER management information, it showed one
process blocked the others, the last TSQL command batch of the blocking
process was SET NOEXEC OFF, what's the reason that this process blocked
other processes, how to solve it?
ThanksHi,
I feel that the process mentioned by you may not be the probable case for
deadlock. COuld you activate the trace flag 1204 in server
to get more information on deadloak.
DBCC TRACEON(1204,-1)
This trace flag will put the information regarding the deadlock into sql
server error log for investigating the cause for deadlock.
Thanks
Hari
SQL Server MVP
"Taoge" <luxiaotao@.hotmail.com> wrote in message
news:u76Cq5MYFHA.1240@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Yestoday I checked the SQL SERVER management information, it showed one
> process blocked the others, the last TSQL command batch of the blocking
> process was SET NOEXEC OFF, what's the reason that this process blocked
> other processes, how to solve it?
> Thanks
>|||Actually, 1204 only forces a full scan debug of the deadlock chain. 3604
sends output to the client and 3605 sends output to the server error log.
However, at some pointI believe SP3the 3605 flag was no longer required,
but it has gone back and forth a few times. So, I usually explicitly set
3605 as well.
Sincerely,
Anthony Thomas
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:O7iETYPYFHA.3584@.TK2MSFTNGP12.phx.gbl...
Hi,
I feel that the process mentioned by you may not be the probable case for
deadlock. COuld you activate the trace flag 1204 in server
to get more information on deadloak.
DBCC TRACEON(1204,-1)
This trace flag will put the information regarding the deadlock into sql
server error log for investigating the cause for deadlock.
Thanks
Hari
SQL Server MVP
"Taoge" <luxiaotao@.hotmail.com> wrote in message
news:u76Cq5MYFHA.1240@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Yestoday I checked the SQL SERVER management information, it showed one
> process blocked the others, the last TSQL command batch of the blocking
> process was SET NOEXEC OFF, what's the reason that this process blocked
> other processes, how to solve it?
> Thanks
>

dead lock

Hi,
Yestoday I checked the SQL SERVER management information, it showed one
process blocked the others, the last TSQL command batch of the blocking
process was SET NOEXEC OFF, what's the reason that this process blocked
other processes, how to solve it?
ThanksHi,
I feel that the process mentioned by you may not be the probable case for
deadlock. COuld you activate the trace flag 1204 in server
to get more information on deadloak.
DBCC TRACEON(1204,-1)
This trace flag will put the information regarding the deadlock into sql
server error log for investigating the cause for deadlock.
Thanks
Hari
SQL Server MVP
"Taoge" <luxiaotao@.hotmail.com> wrote in message
news:u76Cq5MYFHA.1240@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Yestoday I checked the SQL SERVER management information, it showed one
> process blocked the others, the last TSQL command batch of the blocking
> process was SET NOEXEC OFF, what's the reason that this process blocked
> other processes, how to solve it?
> Thanks
>|||Actually, 1204 only forces a full scan debug of the deadlock chain. 3604
sends output to the client and 3605 sends output to the server error log.
However, at some point?I believe SP3?the 3605 flag was no longer required,
but it has gone back and forth a few times. So, I usually explicitly set
3605 as well.
Sincerely,
Anthony Thomas
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:O7iETYPYFHA.3584@.TK2MSFTNGP12.phx.gbl...
Hi,
I feel that the process mentioned by you may not be the probable case for
deadlock. COuld you activate the trace flag 1204 in server
to get more information on deadloak.
DBCC TRACEON(1204,-1)
This trace flag will put the information regarding the deadlock into sql
server error log for investigating the cause for deadlock.
Thanks
Hari
SQL Server MVP
"Taoge" <luxiaotao@.hotmail.com> wrote in message
news:u76Cq5MYFHA.1240@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Yestoday I checked the SQL SERVER management information, it showed one
> process blocked the others, the last TSQL command batch of the blocking
> process was SET NOEXEC OFF, what's the reason that this process blocked
> other processes, how to solve it?
> Thanks
>

dcdiag error

Hi all,
This morning my dcdiag command show this error
Starting test: systemlog
An Error Event occured. EventID: 0x00000457
Time Generated: 08/24/2007 08:41:46
(Event String could not be retrieved)
An Error Event occured. EventID: 0x00000457
Time Generated: 08/24/2007 08:41:48
(Event String could not be retrieved)
................... myDC failed test systemlog
Anybody can help me with this? or explain to me the error.
Tks
JFBHi
This is not a SQL Server issue, you may want to post in a more appropriate
newsgroup.
John
"JFB" wrote:
> Hi all,
> This morning my dcdiag command show this error
> Starting test: systemlog
> An Error Event occured. EventID: 0x00000457
> Time Generated: 08/24/2007 08:41:46
> (Event String could not be retrieved)
> An Error Event occured. EventID: 0x00000457
> Time Generated: 08/24/2007 08:41:48
> (Event String could not be retrieved)
> ................... myDC failed test systemlog
> Anybody can help me with this? or explain to me the error.
> Tks
> JFB
>
>|||Ups... sorry...
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:5A1ADB7C-D22F-4A9B-A1A3-0DA7A68F5437@.microsoft.com...
> Hi
> This is not a SQL Server issue, you may want to post in a more appropriate
> newsgroup.
> John
> "JFB" wrote:
>> Hi all,
>> This morning my dcdiag command show this error
>> Starting test: systemlog
>> An Error Event occured. EventID: 0x00000457
>> Time Generated: 08/24/2007 08:41:46
>> (Event String could not be retrieved)
>> An Error Event occured. EventID: 0x00000457
>> Time Generated: 08/24/2007 08:41:48
>> (Event String could not be retrieved)
>> ................... myDC failed test systemlog
>> Anybody can help me with this? or explain to me the error.
>> Tks
>> JFB
>>

DBSTATUS_UNAVAILABLE with OLEDB Command

Hello,

I have an issue with the OLEDB Command in a package of mine where it used to work and now it doesn't. I had 4 different packages where I was bringing in a flat file, massaging the data, and calling 1 of 4 Stored procedures using the OLEDB Command. Everything was working great, then we decided to use the SSIS EBCDIC conversion in the flat file connection rather than converting to ASCII outside of SSIS. This wasn't a problem for 3 of the 4. The one I have an issue with seems to be somehow corrupt, I keep getting the error "Invalid character value for cast specification", as well as "DBSTATUS_UNAVAILABLE", for a particular column. When I re-map that column in the OLEDB Command task, re-run the package, it then tells me a different column has the exact same error. So in going through and re-mapping all of the columns, it goes back to the 1st column I had an error with and gives me the exact same error... ARRRGGGGHHHH!!!!

It seems like something is corrupt in SSIS or something. Does anyone have any thoughts, other than re-writing the package from scratch?

I know there's no issue with the data, I imported the same file into a test table without problems.

Hi, I solved my problem.

When I switched over to using EBCDIC instead of ASCII, normally I get an error for any string values coming in that I need to convert them from Code Page 37 to Code Page 1252. Also, normally I get an error anytime I'm trying to insert a value into a column if I have a different datatype on the input as opposed to the column, i.e. my input is a string and the column is an Integer. I wasn't getting an error for this, in fact I was getting error's for other columns that didn't have any issues. Perhaps this is because i'm calling a Stored Procedure rather than using a SQL Server destination, as I never seem to have this problem with a SS Destination.

Very weird and error messages were not intuitive at all. Oh well, at least it's resolved.

Andy

|||

Ok.. finnaly, what did you made for fix this?

I got the same problem but using a dataconversion from NUMERIC to STR

|||I only got the problem for strings. Just create another column with that value and make the new column ascii.|||

If you decide to do EBCDIC conversion in SSIS at the beginning of your stream you might want to check out the following component.

http://www.aminosoftware.com

It is a custom source component which allows you to do the EBCDIC conversion (including packed decimal, occurs, redefine, etc) all within the source component so that what comes out is ASCII columns.

DBSTATUS_UNAVAILABLE with OLEDB Command

Hello,

I have an issue with the OLEDB Command in a package of mine where it used to work and now it doesn't. I had 4 different packages where I was bringing in a flat file, massaging the data, and calling 1 of 4 Stored procedures using the OLEDB Command. Everything was working great, then we decided to use the SSIS EBCDIC conversion in the flat file connection rather than converting to ASCII outside of SSIS. This wasn't a problem for 3 of the 4. The one I have an issue with seems to be somehow corrupt, I keep getting the error "Invalid character value for cast specification", as well as "DBSTATUS_UNAVAILABLE", for a particular column. When I re-map that column in the OLEDB Command task, re-run the package, it then tells me a different column has the exact same error. So in going through and re-mapping all of the columns, it goes back to the 1st column I had an error with and gives me the exact same error... ARRRGGGGHHHH!!!!

It seems like something is corrupt in SSIS or something. Does anyone have any thoughts, other than re-writing the package from scratch?

I know there's no issue with the data, I imported the same file into a test table without problems.

Hi, I solved my problem.

When I switched over to using EBCDIC instead of ASCII, normally I get an error for any string values coming in that I need to convert them from Code Page 37 to Code Page 1252. Also, normally I get an error anytime I'm trying to insert a value into a column if I have a different datatype on the input as opposed to the column, i.e. my input is a string and the column is an Integer. I wasn't getting an error for this, in fact I was getting error's for other columns that didn't have any issues. Perhaps this is because i'm calling a Stored Procedure rather than using a SQL Server destination, as I never seem to have this problem with a SS Destination.

Very weird and error messages were not intuitive at all. Oh well, at least it's resolved.

Andy

|||

Ok.. finnaly, what did you made for fix this?

I got the same problem but using a dataconversion from NUMERIC to STR

|||I only got the problem for strings. Just create another column with that value and make the new column ascii.|||

If you decide to do EBCDIC conversion in SSIS at the beginning of your stream you might want to check out the following component.

http://www.aminosoftware.com

It is a custom source component which allows you to do the EBCDIC conversion (including packed decimal, occurs, redefine, etc) all within the source component so that what comes out is ASCII columns.

DBSTATUS_UNAVAILABLE with OLEDB Command

Hello,

I have an issue with the OLEDB Command in a package of mine where it used to work and now it doesn't. I had 4 different packages where I was bringing in a flat file, massaging the data, and calling 1 of 4 Stored procedures using the OLEDB Command. Everything was working great, then we decided to use the SSIS EBCDIC conversion in the flat file connection rather than converting to ASCII outside of SSIS. This wasn't a problem for 3 of the 4. The one I have an issue with seems to be somehow corrupt, I keep getting the error "Invalid character value for cast specification", as well as "DBSTATUS_UNAVAILABLE", for a particular column. When I re-map that column in the OLEDB Command task, re-run the package, it then tells me a different column has the exact same error. So in going through and re-mapping all of the columns, it goes back to the 1st column I had an error with and gives me the exact same error... ARRRGGGGHHHH!!!!

It seems like something is corrupt in SSIS or something. Does anyone have any thoughts, other than re-writing the package from scratch?

I know there's no issue with the data, I imported the same file into a test table without problems.

Hi, I solved my problem.

When I switched over to using EBCDIC instead of ASCII, normally I get an error for any string values coming in that I need to convert them from Code Page 37 to Code Page 1252. Also, normally I get an error anytime I'm trying to insert a value into a column if I have a different datatype on the input as opposed to the column, i.e. my input is a string and the column is an Integer. I wasn't getting an error for this, in fact I was getting error's for other columns that didn't have any issues. Perhaps this is because i'm calling a Stored Procedure rather than using a SQL Server destination, as I never seem to have this problem with a SS Destination.

Very weird and error messages were not intuitive at all. Oh well, at least it's resolved.

Andy

|||

Ok.. finnaly, what did you made for fix this?

I got the same problem but using a dataconversion from NUMERIC to STR

|||I only got the problem for strings. Just create another column with that value and make the new column ascii.|||

If you decide to do EBCDIC conversion in SSIS at the beginning of your stream you might want to check out the following component.

http://www.aminosoftware.com

It is a custom source component which allows you to do the EBCDIC conversion (including packed decimal, occurs, redefine, etc) all within the source component so that what comes out is ASCII columns.

DBSTATUS_UNAVAILABLE error

I have a pretty complex Data Flow that finishes with OLE DB Command. This calls a Stored Procedure with parameters (60-70 of them). The packgaes compiles OK and in run time, I get error below. When I go ahead and edit the field - remove it - save the package - add it back and re-run the package - it generates same error for a different field. When I re-assign all fields and re-run it again - it starts over to give me same error.

Help!

Error: 0xC0202009 at Load Divisions, Insert New Division Data record [82749]: An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Invalid character value for cast specification".
Error: 0xC020901C at Load Divisions, Insert New Division Data record [82749]: There was an error with input column "exists_divid" (86315) on input "OLE DB Command Input" (82754). The column status returned was: "DBSTATUS_UNAVAILABLE".
Error: 0xC0209029 at Load Divisions, Insert New Division Data record [82749]: The "input "OLE DB Command Input" (82754)" failed because error code 0xC020906E occurred, and the error row disposition on "input "OLE DB Command Input" (82754)" specifies failure on error. An error occurred on the specified object of the specified component.
Error: 0xC0047022 at Load Divisions, DTS.Pipeline: The ProcessInput method on component "Insert New Division Data record" (82749) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
Error: 0xC0047021 at Load Divisions, DTS.Pipeline: Thread "WorkThread2" has exited with error code 0xC0209029.
Error: 0xC0047039 at Load Divisions, DTS.Pipeline: Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at Load Divisions, DTS.Pipeline: Thread "WorkThread1" has exited with error code 0xC0047039.
Error: 0xC0047039 at Load Divisions, DTS.Pipeline: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at Load Divisions, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.

heh...it works now. It was varchar source column and target INT column. The issue is it never errored this column - but told me about some other issue not even related.

Once I manually matched each field and fixed it - it started going through.

It's gotta be fixed...

|||Also - the same error message appears when your OLE DB command invalidates constraints - for example I experienced this error when I was trying to do UPDATE of a Column with a NULL value - and the column had not null constraint.

Thanks Microsoft for such a "helpful" error message!

DBSTATUS_UNAVAILABLE error

I have a pretty complex Data Flow that finishes with OLE DB Command. This calls a Stored Procedure with parameters (60-70 of them). The packgaes compiles OK and in run time, I get error below. When I go ahead and edit the field - remove it - save the package - add it back and re-run the package - it generates same error for a different field. When I re-assign all fields and re-run it again - it starts over to give me same error.

Help!

Error: 0xC0202009 at Load Divisions, Insert New Division Data record [82749]: An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Invalid character value for cast specification".
Error: 0xC020901C at Load Divisions, Insert New Division Data record [82749]: There was an error with input column "exists_divid" (86315) on input "OLE DB Command Input" (82754). The column status returned was: "DBSTATUS_UNAVAILABLE".
Error: 0xC0209029 at Load Divisions, Insert New Division Data record [82749]: The "input "OLE DB Command Input" (82754)" failed because error code 0xC020906E occurred, and the error row disposition on "input "OLE DB Command Input" (82754)" specifies failure on error. An error occurred on the specified object of the specified component.
Error: 0xC0047022 at Load Divisions, DTS.Pipeline: The ProcessInput method on component "Insert New Division Data record" (82749) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
Error: 0xC0047021 at Load Divisions, DTS.Pipeline: Thread "WorkThread2" has exited with error code 0xC0209029.
Error: 0xC0047039 at Load Divisions, DTS.Pipeline: Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at Load Divisions, DTS.Pipeline: Thread "WorkThread1" has exited with error code 0xC0047039.
Error: 0xC0047039 at Load Divisions, DTS.Pipeline: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at Load Divisions, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.

heh...it works now. It was varchar source column and target INT column. The issue is it never errored this column - but told me about some other issue not even related.

Once I manually matched each field and fixed it - it started going through.

It's gotta be fixed...

|||Also - the same error message appears when your OLE DB command invalidates constraints - for example I experienced this error when I was trying to do UPDATE of a Column with a NULL value - and the column had not null constraint.

Thanks Microsoft for such a "helpful" error message!