intermittently my SSIS Packages which are run through the JOB are failing

Hi SSIS Guru's,

I am facing a very strange problem.

We have 2 Physical Servers (A and B) on which we have installed the SQL Server, one primary (A) and other as secondary (B). And there is a cluster (C) available to acces the running server. I have created some SSIS packages which we installed on the Server A (Primary), and created the job on the cluster server which initiates the SSIS packages, whcih are installed in the File System.

The problem i am facing is the some thing related to Connection time out. And interestingly i am not getting this error Always. Approxiamtely For Every 5 Times once it;s Failing. I am copying the errors Which i encountered in the different runs.

The thing i am confused is why i am not geting the error all the time? And Why am i getting this error all the time in a different data flow task. My SSIS Package structure is I have created one master package and 6 Child packages. I am getting the connection string for the Data base from the Configuration file which is defined in the XML File.

The connection string that i am using is

Data Source=<<server name>>;User ID=DOMAIN\user;Initial Catalog=DatabaseName;Provider=SQLNCLI.1;Integrated Security=SSPI;

*************************************************************************************************************************************

RUN 1 - Error
Executed as user: AMR\sys_calyp. ...sion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights

reserved. Started: 2:00:07 PM Error: 2007-09-15 14:02:35.92 Code: 0xC0202009 Source: ssis_emp Connection manager

"DBCONNECTION" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An

OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unable to complete

login process due to delay in opening server connection". End Error Error: 2007-09-15 14:02:35.92 Code: 0xC020801C

Source: infr_char Get the Records from emp 1 [72] Description: SSIS Error Code

DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager

"DBCONNECTION" failed with error code 0xC0202009. There may be error messages posted before this with more information on

why the AcquireConnection method... The package execution fa... The step failed.

*************************************************************************************************************************************

*************************************************************************************************************************************

RUN 2 - Error
Message
Executed as user: AMR\sys_calyp. ...sion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights

reserved. Started: 9:15:01 AM Error: 2007-09-15 09:17:01.64 Code: 0xC0202009 Source: ssis_emp Connection manager

"DBCONNECTION" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An

OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unable to complete

login process due to delay in opening server connection". End Error Error: 2007-09-15 09:17:01.64 Code: 0xC020801C

Source: Data Flow Task Get the Records from emp [473] Description: SSIS Error Code

DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager

"DBCONNECTION" failed with error code 0xC0202009. There may be error messages posted before this with more information on

why the AcquireConnection me... The package execution fa... The step failed.

*************************************************************************************************************************************

*************************************************************************************************************************************

Run -3 Error
Message
Executed as user: AMR\sys_calyp. ...sion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights

reserved. Started: 11:30:01 PM Error: 2007-09-14 23:32:21.28 Code: 0xC0202009 Source: ssis_dept Connection

manager "DBCONNECTION" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code:

0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unable

to complete login process due to delay in opening server connection". End Error Error: 2007-09-14 23:32:21.28 Code:

0xC020801C Source: Data Flow Task Get the Records from dept [632] Description: SSIS Error Code

DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager

"DBCONNECTION" failed with error code 0xC0202009. There may be error messages posted before this with more information on

why the AcquireConnection method ... The package execution fa... The step failed.

*************************************************************************************************************************************

*************************************************************************************************************************************

Run - 4 Error

Message
Executed as user: AMR\sys_calyp. ...sion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights

reserved. Started: 11:00:02 PM Error: 2007-09-14 23:02:21.46 Code: 0xC0202009 Source: ssis_emp Connection

manager "DBCONNECTION" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code:

0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unable

to complete login process due to delay in opening server connection". End Error Error: 2007-09-14 23:02:21.46 Code:

0xC020801C Source: infr_itm_char_val Get the Records from emp_master [1] Description: SSIS Error Code

DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager

"DBCONNECTION" failed with error code 0xC0202009. There may be error messages posted before this with more information on

why the AcquireCon... The package execution fa... The step failed.

*************************************************************************************************************************************

*************************************************************************************************************************************

Run -5 Error

Message
Executed as user: AMR\sys_calyp. ...Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp

1984-2005. All rights reserved. Started: 9:10:59 PM Error: 2007-09-14 21:12:23.25 Code: 0xC0202009 Source:

ssis_salgrade Connection manager "DBCONNECTION" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred.

Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005

Description: "Unable to complete login process due to delay in opening server connection". End Error Error: 2007-09-14

21:12:23.25 Code: 0xC020801C Source: Data Flow Task - ssis_salgrade get salgrade [3227] Description: SSIS Error

Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager

"DBCONNECTION" failed with error code 0xC0202009. There may be error messages posted before this with more information on

why the AcquireConnection method c. The step failed.

*************************************************************************************************************************************

I thought i found the solution my self... I changed the delay validation to all the packages to true and the problem dissappeared

intermittently my SSIS Packages which are run through the JOB are failing

Hi SSIS Guru's,

I am facing a very strange problem.

We have 2 Physical Servers (A and B) on which we have installed the SQL Server, one primary (A) and other as secondary (B). And there is a cluster (C) available to acces the running server. I have created some SSIS packages which we installed on the Server A (Primary), and created the job on the cluster server which initiates the SSIS packages, whcih are installed in the File System.

The problem i am facing is the some thing related to Connection time out. And interestingly i am not getting this error Always. Approxiamtely For Every 5 Times once it;s Failing. I am copying the errors Which i encountered in the different runs.

The thing i am confused is why i am not geting the error all the time? And Why am i getting this error all the time in a different data flow task. My SSIS Package structure is I have created one master package and 6 Child packages. I am getting the connection string for the Data base from the Configuration file which is defined in the XML File.

The connection string that i am using is

Data Source=<<server name>>;User ID=DOMAIN\user;Initial Catalog=DatabaseName;Provider=SQLNCLI.1;Integrated Security=SSPI;

*************************************************************************************************************************************

RUN 1 - Error
Executed as user: AMR\sys_calyp. ...sion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights

reserved. Started: 2:00:07 PM Error: 2007-09-15 14:02:35.92 Code: 0xC0202009 Source: ssis_emp Connection manager

"DBCONNECTION" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An

OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unable to complete

login process due to delay in opening server connection". End Error Error: 2007-09-15 14:02:35.92 Code: 0xC020801C

Source: infr_char Get the Records from emp 1 [72] Description: SSIS Error Code

DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager

"DBCONNECTION" failed with error code 0xC0202009. There may be error messages posted before this with more information on

why the AcquireConnection method... The package execution fa... The step failed.

*************************************************************************************************************************************

*************************************************************************************************************************************

RUN 2 - Error
Message
Executed as user: AMR\sys_calyp. ...sion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights

reserved. Started: 9:15:01 AM Error: 2007-09-15 09:17:01.64 Code: 0xC0202009 Source: ssis_emp Connection manager

"DBCONNECTION" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An

OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unable to complete

login process due to delay in opening server connection". End Error Error: 2007-09-15 09:17:01.64 Code: 0xC020801C

Source: Data Flow Task Get the Records from emp [473] Description: SSIS Error Code

DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager

"DBCONNECTION" failed with error code 0xC0202009. There may be error messages posted before this with more information on

why the AcquireConnection me... The package execution fa... The step failed.

*************************************************************************************************************************************

*************************************************************************************************************************************

Run -3 Error
Message
Executed as user: AMR\sys_calyp. ...sion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights

reserved. Started: 11:30:01 PM Error: 2007-09-14 23:32:21.28 Code: 0xC0202009 Source: ssis_dept Connection

manager "DBCONNECTION" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code:

0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unable

to complete login process due to delay in opening server connection". End Error Error: 2007-09-14 23:32:21.28 Code:

0xC020801C Source: Data Flow Task Get the Records from dept [632] Description: SSIS Error Code

DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager

"DBCONNECTION" failed with error code 0xC0202009. There may be error messages posted before this with more information on

why the AcquireConnection method ... The package execution fa... The step failed.

*************************************************************************************************************************************

*************************************************************************************************************************************

Run - 4 Error

Message
Executed as user: AMR\sys_calyp. ...sion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights

reserved. Started: 11:00:02 PM Error: 2007-09-14 23:02:21.46 Code: 0xC0202009 Source: ssis_emp Connection

manager "DBCONNECTION" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code:

0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unable

to complete login process due to delay in opening server connection". End Error Error: 2007-09-14 23:02:21.46 Code:

0xC020801C Source: infr_itm_char_val Get the Records from emp_master [1] Description: SSIS Error Code

DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager

"DBCONNECTION" failed with error code 0xC0202009. There may be error messages posted before this with more information on

why the AcquireCon... The package execution fa... The step failed.

*************************************************************************************************************************************

*************************************************************************************************************************************

Run -5 Error

Message
Executed as user: AMR\sys_calyp. ...Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp

1984-2005. All rights reserved. Started: 9:10:59 PM Error: 2007-09-14 21:12:23.25 Code: 0xC0202009 Source:

ssis_salgrade Connection manager "DBCONNECTION" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred.

Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005

Description: "Unable to complete login process due to delay in opening server connection". End Error Error: 2007-09-14

21:12:23.25 Code: 0xC020801C Source: Data Flow Task - ssis_salgrade get salgrade [3227] Description: SSIS Error

Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager

"DBCONNECTION" failed with error code 0xC0202009. There may be error messages posted before this with more information on

why the AcquireConnection method c. The step failed.

*************************************************************************************************************************************

I thought i found the solution my self... I changed the delay validation to all the packages to true and the problem dissappeared

Intermittent SQLSERVERAGENT error "unable to send job completion..

A batch job that executes once daily is configured to send job completion
notification on success or failure. Usually it works. Twice in the past
week it didn't, and this message is in the Application Log:
Event Type:Error
Event Source:SQLSERVERAGENT
Event Category:Job Engine
Event ID:210
Date:8/8/2005
Time:11:10:07 PM
User:N/A
Computer:xxxxxxx (name withheld to protect the guilty)
Description:Unable to send job completion notification email to operator
'DBA - SQLExec' for job 'Maintenance and backup'.
Both before and after the occurence of the problem, I am able to
successfully send mail with a 'test' of the operator 'DBA - SQLExec'.
Our environment is as follows:
SQL Server 2000 SP3a on Windows 2000 Server.
Outlook 2000 on the SQL Server box.
WAN; and the SQL box is geographically remote from the Exchange server.
MS Exchange email system (newly implemented).
Any ideas would be appreciated. Thanks in advance.
Hi,
Thanks for your post.
From your descriptions, I understood your SQLAgent intermittently failed to
report the error message that unable to send job completion notification
email to operator 'DBA - SQLExec' for job. If I have misunderstood your
concern, please feel free to point it out.
I have noticed that this issue will not happen all the time and and based
on my knowledge it is most possible that the Exchange server is unavailable
at that time of name resolution the above error will occur. Check logs of
exchange server to go further for this loss of connectivity between the
MAPI client and Exchange server
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Intermittent SQLSERVERAGENT error "unable to send job completion..

A batch job that executes once daily is configured to send job completion
notification on success or failure. Usually it works. Twice in the past
week it didn't, and this message is in the Application Log:
Event Type: Error
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 210
Date: 8/8/2005
Time: 11:10:07 PM
User: N/A
Computer: xxxxxxx (name withheld to protect the guilty)
Description:Unable to send job completion notification email to operator
'DBA - SQLExec' for job 'Maintenance and backup'.
Both before and after the occurence of the problem, I am able to
successfully send mail with a 'test' of the operator 'DBA - SQLExec'.
Our environment is as follows:
SQL Server 2000 SP3a on Windows 2000 Server.
Outlook 2000 on the SQL Server box.
WAN; and the SQL box is geographically remote from the Exchange server.
MS Exchange email system (newly implemented).
Any ideas would be appreciated. Thanks in advance.Hi,
Thanks for your post.
From your descriptions, I understood your SQLAgent intermittently failed to
report the error message that unable to send job completion notification
email to operator 'DBA - SQLExec' for job. If I have misunderstood your
concern, please feel free to point it out.
I have noticed that this issue will not happen all the time and and based
on my knowledge it is most possible that the Exchange server is unavailable
at that time of name resolution the above error will occur. Check logs of
exchange server to go further for this loss of connectivity between the
MAPI client and Exchange server
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Intermittent SQLSERVERAGENT error "unable to send job completion..

A batch job that executes once daily is configured to send job completion
notification on success or failure. Usually it works. Twice in the past
week it didn't, and this message is in the Application Log:
Event Type: Error
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 210
Date: 8/8/2005
Time: 11:10:07 PM
User: N/A
Computer: xxxxxxx (name withheld to protect the guilty)
Description:Unable to send job completion notification email to operator
'DBA - SQLExec' for job 'Maintenance and backup'.
Both before and after the occurence of the problem, I am able to
successfully send mail with a 'test' of the operator 'DBA - SQLExec'.
Our environment is as follows:
SQL Server 2000 SP3a on Windows 2000 Server.
Outlook 2000 on the SQL Server box.
WAN; and the SQL box is geographically remote from the Exchange server.
MS Exchange email system (newly implemented).
Any ideas would be appreciated. Thanks in advance.Hi,
Thanks for your post.
From your descriptions, I understood your SQLAgent intermittently failed to
report the error message that unable to send job completion notification
email to operator 'DBA - SQLExec' for job. If I have misunderstood your
concern, please feel free to point it out.
I have noticed that this issue will not happen all the time and and based
on my knowledge it is most possible that the Exchange server is unavailable
at that time of name resolution the above error will occur. Check logs of
exchange server to go further for this loss of connectivity between the
MAPI client and Exchange server
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Intermittent SQL Server shutdown

I am running a scheduled job through SQL Server Agent every morning, Tuesday
to Saturday, at 3:00 am.
It runs perfectly for ws, sometimes months. Then, one morning, something
happens to cause SQL Server
and SQL Server Agent to stop running. *Nothing* has changed in the scheduled
job.
When this situation occurs, it always occurs while running the scheduled job
(i.e., the machine is not idle).
The scheduled job executes my stored procedure si_ImportMaster.
Furthermore, si_ImportMaster simply does some error checking and then execut
es a series of 10 stored procedures,
and when the error happens, it is always when si_ImportMaster is executing t
he same stored procedure (of those 10).
When I come in to work and realize what has happened, I re-start SQL Server
and SQL Server Agent
and execute the rest of the stored procedures in the process (including the
one it stopped on).
That "re-run" process completes without a hitch.
I would be happy to post the contents of the stored procedure in question, b
ut my thinking was that
if it runs correctly for ws or months, it can't be the fault of the SQL c
ode. Can it . . . ?
I have been given the job of troubleshooting this. I am much more familiar w
ith SQL Server programming
than the DBA side of things, so any and all assistance is appreciated.
We're running SQL 7.0 SP2.
If you need me to post anything else, let me know.
Thanks in advance -- Carl
Following is some error log output. I was able to find 4 relevant log files:
...\LOG\ERRORLOG.1 -- excerpted below
...\LOG\SQLAGENT.1 -- excerpted below
...\LOG\SQL00031.dmp -- 48K of mostly technical stuff (core dump info, etc
.)
...\LOG\SQL00032.dmp -- same as above.
Anyway,
Here's some output from ...\LOG\ERRORLOG.1
2005-03-13 19:05:06.75 kernel Microsoft SQL Server 7.00 - 7.00.842 (Intel
X86)
Mar 2 2000 06:49:37
Copyright (c) 1988-1998 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 2)
----
--
2005-03-15 04:18:04.09 spid9 Process 9 unlocking unowned resource: KEY: 7
:1451737566:5 (57028e42a833)
2005-03-15 04:18:04.12 spid9 Process 9 unlocking unowned resource: KEY: 7
:1451737566:5 (57028e42a833)
2005-03-15 04:18:04.28 spid9 Using 'sqlimage.dll' version '4.0.5'
Dump thread - spid = 9, PSS = 0x354df154, EC = 0x23a40084
Stack Dump being sent to E:\log\SQL00031.dmp
****************************************
************************************
***
*
* BEGIN STACK DUMP:
* 03/15/05 04:18:17 spid 9
*
* Input Buffer 40 bytes -
* E X E C s i _ I m p o r t M a s t e r
*
****************************************
************************************
***
----
--
Short Stack Dump
0x77f94956 Module(ntdll+14956) (ZwGetContextThread+b)
0x00784f83 Module(sqlservr+384f83) (utassert_fail+19f)
0x005b572c Module(sqlservr+1b572c) (ExecutionContext::Cleanup+9d)
0x004eb421 Module(sqlservr+eb421) (ExecutionContext::Purge+45)
0x004eb182 Module(sqlservr+eb182) (stopsubprocess+e5)
0x004e9d4e Module(sqlservr+e9d4e) (subproc_main+174)
0x41092a47 Module(ums+2a47) (ProcessWorkRequests+ec)
0x4109326b Module(ums+326b) (ThreadStartRoutine+138)
0x7800c9eb Module(MSVCRT+c9eb) (beginthread+ce)
0x7c57438b Module(KERNEL32+438b) (TlsSetValue+f0)
2005-03-15 04:18:17.81 kernel SQL Server Assertion: File: <proc.c>, line=1
927
Failed Assertion = 'm_activeSdesList.Head () == NULL'.
2005-03-15 04:18:17.87 spid9 Using 'sqlimage.dll' version '4.0.5'
Dump thread - spid = 9, PSS = 0x354df154, EC = 0x23a40084
Stack Dump being sent to E:\log\SQL00032.dmp
****************************************
************************************
***
*
* BEGIN STACK DUMP:
* 03/15/05 04:18:22 spid 9
*
* Input Buffer 40 bytes -
* E X E C s i _ I m p o r t M a s t e r
*
****************************************
************************************
***
----
--
Short Stack Dump
0x77f94956 Module(ntdll+14956) (ZwGetContextThread+b)
0x0077b371 Module(sqlservr+37b371) (ex_raise2+35b)
0x0077b3dd Module(sqlservr+37b3dd) (ex_raise+5f)
0x00784f16 Module(sqlservr+384f16) (utassert_fail+37d)
0x005b572c Module(sqlservr+1b572c) (ExecutionContext::Cleanup+9d)
0x004eb421 Module(sqlservr+eb421) (ExecutionContext::Purge+45)
0x004eb182 Module(sqlservr+eb182) (stopsubprocess+e5)
0x004e9d4e Module(sqlservr+e9d4e) (subproc_main+174)
0x41092a47 Module(ums+2a47) (ProcessWorkRequests+ec)
0x4109326b Module(ums+326b) (ThreadStartRoutine+138)
0x7800c9eb Module(MSVCRT+c9eb) (beginthread+ce)
0x7c57438b Module(KERNEL32+438b) (TlsSetValue+f0)
2005-03-15 04:18:23.32 spid9 Error: 1203, Severity: 20, State: 1
2005-03-15 04:18:23.32 spid9 Process ID 9 attempting to unlock unowned re
source KEY: 7:1451737566:5 (57028e42a833)..
2005-03-15 04:18:23.32 spid9 Error: 1203, Severity: 20, State: 1
2005-03-15 04:18:23.32 spid9 Process ID 9 attempting to unlock unowned re
source KEY: 7:1451737566:5 (57028e42a833)..
Here's the relevant output from ...\LOG\SQLAGENT.1
3/15/2005 4:18:23 AM - ! [298] SQLServer Error: 4, Connection broken. [SQLST
ATE 08S01]
3/15/2005 4:18:23 AM - ! [298] SQLServer Error: 109, ConnectionRead (GetOver
LappedResult()). [SQLSTATE 01000]
3/15/2005 4:18:23 AM - ! [382] Logon to server '(local)' failed (ConnAttempt
CachableOp)
3/15/2005 4:18:28 AM - + [360] SQLServerAgent initiating shutdown following
MSSQLServer shutdown
3/15/2005 4:18:44 AM - + [188] Scheduler engine timed out (after 15 seconds)
waiting for 1 jobs(s) to stop
3/15/2005 4:18:44 AM - ! [359] The local host server is not running
3/15/2005 4:18:45 AM - + [098] SQLServerAgent terminated (forcefully)We had this problem too when I used to work as DBA.
The problem was with the Code that was written in the SP that was being
called in a Job. It used to crash
the SQLServer Engine (and along with it SQLAgent) and this is used to happen
randomly.
Finally we traced it to that part of the code. Fortunately, Microsoft has a
fix for this.
You dont seem to have the latest SQLServer 7 Service Pack. May be your
problem has been addressed
in one of them.
Did you the SQLServer Log file for any errors, Assertions ... etc.
Gopi
"Carl Imthurn" <nospam@.all.com> wrote in message
news:ua9EVkYKFHA.1096@.tk2msftngp13.phx.gbl...
>I am running a scheduled job through SQL Server Agent every morning,
>Tuesday to Saturday, at 3:00 am.
> It runs perfectly for ws, sometimes months. Then, one morning,
> something happens to cause SQL Server
> and SQL Server Agent to stop running. *Nothing* has changed in the
> scheduled job.
> When this situation occurs, it always occurs while running the scheduled
> job (i.e., the machine is not idle).
> The scheduled job executes my stored procedure si_ImportMaster.
> Furthermore, si_ImportMaster simply does some error checking and then
> executes a series of 10 stored procedures,
> and when the error happens, it is always when si_ImportMaster is executing
> the same stored procedure (of those 10).
> When I come in to work and realize what has happened, I re-start SQL
> Server and SQL Server Agent
> and execute the rest of the stored procedures in the process (including
> the one it stopped on).
> That "re-run" process completes without a hitch.
> I would be happy to post the contents of the stored procedure in question,
> but my thinking was that
> if it runs correctly for ws or months, it can't be the fault of the SQL
> code. Can it . . . ?
> I have been given the job of troubleshooting this. I am much more familiar
> with SQL Server programming
> than the DBA side of things, so any and all assistance is appreciated.
> We're running SQL 7.0 SP2.
> If you need me to post anything else, let me know.
> Thanks in advance -- Carl
> Following is some error log output. I was able to find 4 relevant log
> files:
> ...\LOG\ERRORLOG.1 -- excerpted below
> ...\LOG\SQLAGENT.1 -- excerpted below
> ...\LOG\SQL00031.dmp -- 48K of mostly technical stuff (core dump info,
> etc.)
> ...\LOG\SQL00032.dmp -- same as above.
> Anyway,
> Here's some output from ...\LOG\ERRORLOG.1
> 2005-03-13 19:05:06.75 kernel Microsoft SQL Server 7.00 - 7.00.842
> (Intel X86)
> Mar 2 2000 06:49:37
> Copyright (c) 1988-1998 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 2)
> ----
--
> 2005-03-15 04:18:04.09 spid9 Process 9 unlocking unowned resource: KEY:
> 7:1451737566:5 (57028e42a833)
> 2005-03-15 04:18:04.12 spid9 Process 9 unlocking unowned resource: KEY:
> 7:1451737566:5 (57028e42a833)
> 2005-03-15 04:18:04.28 spid9 Using 'sqlimage.dll' version '4.0.5'
> Dump thread - spid = 9, PSS = 0x354df154, EC = 0x23a40084
> Stack Dump being sent to E:\log\SQL00031.dmp
> ****************************************
**********************************
*****
> *
> * BEGIN STACK DUMP:
> * 03/15/05 04:18:17 spid 9
> *
> * Input Buffer 40 bytes -
> * E X E C s i _ I m p o r t M a s t e r
> *
> ****************************************
**********************************
*****
> ----
--
> Short Stack Dump
> 0x77f94956 Module(ntdll+14956) (ZwGetContextThread+b)
> 0x00784f83 Module(sqlservr+384f83) (utassert_fail+19f)
> 0x005b572c Module(sqlservr+1b572c) (ExecutionContext::Cleanup+9d)
> 0x004eb421 Module(sqlservr+eb421) (ExecutionContext::Purge+45)
> 0x004eb182 Module(sqlservr+eb182) (stopsubprocess+e5)
> 0x004e9d4e Module(sqlservr+e9d4e) (subproc_main+174)
> 0x41092a47 Module(ums+2a47) (ProcessWorkRequests+ec)
> 0x4109326b Module(ums+326b) (ThreadStartRoutine+138)
> 0x7800c9eb Module(MSVCRT+c9eb) (beginthread+ce)
> 0x7c57438b Module(KERNEL32+438b) (TlsSetValue+f0)
> 2005-03-15 04:18:17.81 kernel SQL Server Assertion: File: <proc.c>,
> line=1927
> Failed Assertion = 'm_activeSdesList.Head () == NULL'.
> 2005-03-15 04:18:17.87 spid9 Using 'sqlimage.dll' version '4.0.5'
> Dump thread - spid = 9, PSS = 0x354df154, EC = 0x23a40084
> Stack Dump being sent to E:\log\SQL00032.dmp
> ****************************************
**********************************
*****
> *
> * BEGIN STACK DUMP:
> * 03/15/05 04:18:22 spid 9
> *
> * Input Buffer 40 bytes -
> * E X E C s i _ I m p o r t M a s t e r
> *
> ****************************************
**********************************
*****
> ----
--
> Short Stack Dump
> 0x77f94956 Module(ntdll+14956) (ZwGetContextThread+b)
> 0x0077b371 Module(sqlservr+37b371) (ex_raise2+35b)
> 0x0077b3dd Module(sqlservr+37b3dd) (ex_raise+5f)
> 0x00784f16 Module(sqlservr+384f16) (utassert_fail+37d)
> 0x005b572c Module(sqlservr+1b572c) (ExecutionContext::Cleanup+9d)
> 0x004eb421 Module(sqlservr+eb421) (ExecutionContext::Purge+45)
> 0x004eb182 Module(sqlservr+eb182) (stopsubprocess+e5)
> 0x004e9d4e Module(sqlservr+e9d4e) (subproc_main+174)
> 0x41092a47 Module(ums+2a47) (ProcessWorkRequests+ec)
> 0x4109326b Module(ums+326b) (ThreadStartRoutine+138)
> 0x7800c9eb Module(MSVCRT+c9eb) (beginthread+ce)
> 0x7c57438b Module(KERNEL32+438b) (TlsSetValue+f0)
> 2005-03-15 04:18:23.32 spid9 Error: 1203, Severity: 20, State: 1
> 2005-03-15 04:18:23.32 spid9 Process ID 9 attempting to unlock unowned
> resource KEY: 7:1451737566:5 (57028e42a833)..
> 2005-03-15 04:18:23.32 spid9 Error: 1203, Severity: 20, State: 1
> 2005-03-15 04:18:23.32 spid9 Process ID 9 attempting to unlock unowned
> resource KEY: 7:1451737566:5 (57028e42a833)..
>
> Here's the relevant output from ...\LOG\SQLAGENT.1
> 3/15/2005 4:18:23 AM - ! [298] SQLServer Error: 4, Connection broken.
> [SQLSTATE 08S01]
> 3/15/2005 4:18:23 AM - ! [298] SQLServer Error: 109, ConnectionRead
> (GetOverLappedResult()). [SQLSTATE 01000]
> 3/15/2005 4:18:23 AM - ! [382] Logon to server '(local)' failed
> (ConnAttemptCachableOp)
> 3/15/2005 4:18:28 AM - + [360] SQLServerAgent initiating shutdown
> following MSSQLServer shutdown
> 3/15/2005 4:18:44 AM - + [188] Scheduler engine timed out (after 15
> seconds) waiting for 1 jobs(s) to stop
> 3/15/2005 4:18:44 AM - ! [359] The local host server is not running
> 3/15/2005 4:18:45 AM - + [098] SQLServerAgent terminated (forcefully)
>|||Thanks Gopi -- I had wondered about that myself.
I will install the latest SP and see if that cures the problem.
Thanks for your time.
rgn wrote:
> We had this problem too when I used to work as DBA.
> The problem was with the Code that was written in the SP that was being
> called in a Job. It used to crash
> the SQLServer Engine (and along with it SQLAgent) and this is used to happ
en
> randomly.
> Finally we traced it to that part of the code. Fortunately, Microsoft has
a
> fix for this.
> You dont seem to have the latest SQLServer 7 Service Pack. May be your
> problem has been addressed
> in one of them.
> Did you the SQLServer Log file for any errors, Assertions ... etc.
> Gopi
> "Carl Imthurn" <nospam@.all.com> wrote in message
> news:ua9EVkYKFHA.1096@.tk2msftngp13.phx.gbl...
>
>
>sql

Interesting issue

I tried to create a new SQL Agent job today and couldn't. Got this error msg
.
"Error 0: This server has been disconnected. You must reconnect to perform
this operation. The job was not saved." I got this message on every server I
connected to via EM, dozens - EXCEPT the ones that weren't default instances
.
My colleague had the same problem but no-one else in the team did. My
colleague and I have different PC builds in that we have XP SP2 and have SQL
2005 tools installed as well as 2000. Sensing the problem was something to
do with port 1433 as it only affected default instances, I changed our clien
t
connectivity protocols so that Named Pipes was above TCP/IP, and now we don'
t
have a problem. Strange. Anyone shed any light on why connecting to default
instance SQL Servers via EM using TCP/IP connections stops us saving new job
s
in SQL Agent? Many thanks.Solved it. New security software had a 'MS SQL Job Scheduling' intrusion
Prevention rule. When that was omitted, we could create jobs using TCP/IP
connections.
"Shirley" wrote:

> I tried to create a new SQL Agent job today and couldn't. Got this error m
sg.
> "Error 0: This server has been disconnected. You must reconnect to perform
> this operation. The job was not saved." I got this message on every server
I
> connected to via EM, dozens - EXCEPT the ones that weren't default instanc
es.
> My colleague had the same problem but no-one else in the team did. My
> colleague and I have different PC builds in that we have XP SP2 and have S
QL
> 2005 tools installed as well as 2000. Sensing the problem was something t
o
> do with port 1433 as it only affected default instances, I changed our cli
ent
> connectivity protocols so that Named Pipes was above TCP/IP, and now we do
n't
> have a problem. Strange. Anyone shed any light on why connecting to defaul
t
> instance SQL Servers via EM using TCP/IP connections stops us saving new j
obs
> in SQL Agent? Many thanks.|||Just out of curiosity, what kind of security software?|||Symantec.
"Curt" wrote:

> Just out of curiosity, what kind of security software?
>sql

Interesting issue

I tried to create a new SQL Agent job today and couldn't. Got this error msg.
"Error 0: This server has been disconnected. You must reconnect to perform
this operation. The job was not saved." I got this message on every server I
connected to via EM, dozens - EXCEPT the ones that weren't default instances.
My colleague had the same problem but no-one else in the team did. My
colleague and I have different PC builds in that we have XP SP2 and have SQL
2005 tools installed as well as 2000. Sensing the problem was something to
do with port 1433 as it only affected default instances, I changed our client
connectivity protocols so that Named Pipes was above TCP/IP, and now we don't
have a problem. Strange. Anyone shed any light on why connecting to default
instance SQL Servers via EM using TCP/IP connections stops us saving new jobs
in SQL Agent? Many thanks.Solved it. New security software had a 'MS SQL Job Scheduling' intrusion
Prevention rule. When that was omitted, we could create jobs using TCP/IP
connections.
"Shirley" wrote:
> I tried to create a new SQL Agent job today and couldn't. Got this error msg.
> "Error 0: This server has been disconnected. You must reconnect to perform
> this operation. The job was not saved." I got this message on every server I
> connected to via EM, dozens - EXCEPT the ones that weren't default instances.
> My colleague had the same problem but no-one else in the team did. My
> colleague and I have different PC builds in that we have XP SP2 and have SQL
> 2005 tools installed as well as 2000. Sensing the problem was something to
> do with port 1433 as it only affected default instances, I changed our client
> connectivity protocols so that Named Pipes was above TCP/IP, and now we don't
> have a problem. Strange. Anyone shed any light on why connecting to default
> instance SQL Servers via EM using TCP/IP connections stops us saving new jobs
> in SQL Agent? Many thanks.|||Just out of curiosity, what kind of security software?|||Symantec.
"Curt" wrote:
> Just out of curiosity, what kind of security software?
>

Integrity job error

Hi folks,

I created a job to check the integrity of my databases every week using SQL Server Maintenance Plan Wizard.
The job is failing every time...
For tb_basico database it works fine, but with the other one (tb_cep) it doesn't work...

Does someone have an idea to solve this problem?

The message is:

[1] Database tb_basico: Check Data and Index Linkage...

** Execution Time: 0 hrs, 0 mins, 11 secs **

[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC SQL Server Driver][SQL Server]Database state cannot be changed while other users are using the database 'tb_cep'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
[2] Database tb_cep: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode.

The following errors were found:

[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **The problem is that other users are in the database while you are attempting to run the job created by the wizard. The wizard created job needs exclusive use of the database to complete the tasks you've asked it to do.

If you want to continue to run the wizard task, then I'd do an ALTER DATABASE mydb SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_4e5h.asp), then run the job created by the wizard. After the job completes, you can ALTER DATABASE mydb SET MULTI_USER to allow the users back into the database.

-PatP

Integrity Checks job failing

Hi,

SQl Server 7

I have Daily User DB Integrity Checks job running daily
From past 2 days i am getting below error.

[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 35 consistency errors in table 'Prod_Hist' (object ID 2098106515).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 99 consistency errors in database 'Ucatalog'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Ucatalog repair_fast).
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Please suggest..

Thanks in Advance
AdilHi,

SQl Server 7

I have Daily User DB Integrity Checks job running daily
From past 2 days i am getting below error.

[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 35 consistency errors in table 'Prod_Hist' (object ID 2098106515).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 99 consistency errors in database 'Ucatalog'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Ucatalog repair_fast).
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC execution completed. If DBCC printed error messages, contact your system administrator.


Please suggest..

Thanks in Advance
Adil

1. Contact your DBA with the stated information and ask for his help or
2. Fix the table and database corruption ... either by running checkdb with the repair option stated or restore from a previous backup. Checkdb is telling you that you will probably lose data by allowing it to repair the corruption ... be prepared.

Integrity checks job failed

Hello, I had a DB Maintenance plan, the schedule is every day, but today I found teh 'Integrity checks job is failed". What is that mean? How to check this. Thanks.You need to run DBCC CHECKDB and see if one of your databases fail the check. Maintenance plans are useful, unless you want to troubleshoot of course.|||I found one database failed, I found one user didn't exit the database last night, is that will affect the database backup and integrity check? Another thing, what is integrity check means? How to trouble shoot? Thank you very much! I am a new in sql server.|||Oh my...you have a lot to learn. :) What was the error on the database that failed?|||I know i need to learn, can you tell me which book is good for me to learn? I need to learn quickly, I found the error for the job history, that :

Executed as user: DOM\ServerAdmin. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
Is that help you to figure out?|||http://www.dbforums.com/t812439.html

Gotta love microsoft. Looks like you have a classic generic error message. Could mean "disk is full", or "login failed" or almost anything. Kind of like the engine light in your car. I would start with running

DBCC Checkdb with no_infomsgs

on all of your databases to see if any of them have suddenly become corrupt. It is unlikely, but it is something you want to know about immediately. The user in the database could be the cause, but I can not say that for certain. Good luck.|||http://www.dbforums.com/t812439.html

Gotta love microsoft. Looks like you have a classic generic error message. Could mean "disk is full", or "login failed" or almost anything. Kind of like the engine light in your car. I would start with running

DBCC Checkdb with no_infomsgs

on all of your databases to see if any of them have suddenly become corrupt. It is unlikely, but it is something you want to know about immediately. The user in the database could be the cause, but I can not say that for certain. Good luck.

Hey, Listen...I'm alway one to give M$ a shot...

They all do it...

You can NEVER believe an Error Message

You always have to look outside the box...

DAMN, did I just say that?|||I didn't mean the error that the stupid maintenance plan gave you:

Executed as user: DOM\ServerAdmin. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.

This is AS USUAL for MS Maintenance Plans absolutely useless. Run the DBCC CHECKDB for the database that failed. Don't do it with a maintenance plan. Run it in good old Query Analyzer. Let us know what the error is you get from that, which will hopefully, and usually is, be more informative then gee, you seem to have a problem.

:) That's like one of our developers who came up to me today and said "Derrick, there's a problem with one of my programs." and then just stood there. "Yeah...SO....GO AWAY!!!!!"|||Listen...you CRACK ME UP...

ok, Now you want storeis?

Had an Ireland near shore ask me if it was a good idea to do a join or a 12 level nested cursor...

PLEASE!

I'll trade with you in a heart beat....|||12 level? WOW. You should have told him it was a great idea. It probably would have been a good contract after he got fired. :) Hey....FREEEEEE Trip.|||Can you nest 12 cursors? And more importantly, can you run anything else on the box when it kicks off?|||Hi, guys, i was away yesterday, i check today, everything is fine, No any problem, i think the database should be in single user mode. Thank you all.
But i have another question, why one user use sql server database, they have two process ID with same user and database?|||Open up two Query Analyzer connections. You'll see the same thing for you. :)|||But they are database user (Client), Why they also have two Process ID, also, i found the two host are different, one is their own host, another is not, why that happen? Thanks.

58 0 DOM\ssapienza Notary sleeping 0 AWAITING COMMAND Microsoft Office XP 0 not waiting 953 0 0 7/27/2004 11:02 7/27/2004 11:53 S27567 Named Pipes 00105A6D68CD 0 0
59 0 DOM\ssapienza Notary sleeping 0 AWAITING COMMAND Microsoft Office XP 0 not waiting 1048 6 91 7/27/2004 11:02 7/27/2004 11:53 D27450 Named Pipes 00105A6D68CD 0 0|||Use Profiler and trace SP:Completed and RPC:Completed with a filter on this user. Find out what they're doing. Does this user use Access to do things in the SQL Server database by any chance?|||Thanks, I will try. First i need to learn how to use profile, Is that use the memory? My server memory space is limit.

Integrity Checks job causing SQL 7 database to enter single-user mode

Hi,
I've encountered with very strange problem on this issue on one of my my SQL 7 Servers.
The integrity checks job on the database failed, and caused the database to enter the single user mode, thus preventing applications to access the database. I had to manually switch the database to normal mode to repair the problem.
Right now, every time i try to run the integrity checks job, it fails and causes the database to enter the single user mode again.
Any ideas how to solve the problem?
Thanks in advance,
BarakThis looks like a known issue. Have you seen this?
http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B259551
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"Barak Turovsky" <barak.turovsky@.comverse.com> wrote in message
news:7DA294DF-065F-431A-91D6-AA23B4AA50CE@.microsoft.com...
Hi,
I've encountered with very strange problem on this issue on one of my my SQL
7 Servers.
The integrity checks job on the database failed, and caused the database to
enter the single user mode, thus preventing applications to access the
database. I had to manually switch the database to normal mode to repair the
problem.
Right now, every time i try to run the integrity checks job, it fails and
causes the database to enter the single user mode again.
Any ideas how to solve the problem?
Thanks in advance,
Barak

Integrity Checks Fail

I'm not sure what my response to this error should be.
JOB RUN:'Integrity Checks Job for DB Maintenance Plan 'DB Maintenance
Plan2'' was run on 11/26/2006 at 00:00:03
DURATION:0 hours, 1 minutes, 14 seconds
STATUS: Failed
MESSAGES:The job failed. The Job was invoked by Schedule 190 (Schedule 1).
The last step to run was step 1 (Step 1).
CheckDB reported
CHECKDB found 0 allocation errors and 0 consistency errors in database
'ATOMS'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
However CheckCatalog reports:
Server: Msg 2513, Level 16, State 2, Line 1
Table error: Object ID 231723928 (object '231723928') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
(many more)
And
Table error: Object ID 423724612 (object '423724612') does not match between
'SYSINDEXES' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 1
Help appreciated.
Regards,
Jamie
You might want to have a look at these:
http://blogs.msdn.com/sqlserverstorageengine/archive/tags/DBCC/default.aspx
Andrew J. Kelly SQL MVP
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:1162D111-8C87-40B9-99FD-91BF48290290@.microsoft.com...
> I'm not sure what my response to this error should be.
> JOB RUN: 'Integrity Checks Job for DB Maintenance Plan 'DB Maintenance
> Plan2'' was run on 11/26/2006 at 00:00:03
> DURATION: 0 hours, 1 minutes, 14 seconds
> STATUS: Failed
> MESSAGES: The job failed. The Job was invoked by Schedule 190 (Schedule
> 1).
> The last step to run was step 1 (Step 1).
> CheckDB reported
> CHECKDB found 0 allocation errors and 0 consistency errors in database
> 'ATOMS'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> However CheckCatalog reports:
> Server: Msg 2513, Level 16, State 2, Line 1
> Table error: Object ID 231723928 (object '231723928') does not match
> between
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> (many more)
> And
> Table error: Object ID 423724612 (object '423724612') does not match
> between
> 'SYSINDEXES' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 1
> Help appreciated.
> --
> Regards,
> Jamie

Integrity Checks Fail

I'm not sure what my response to this error should be.
JOB RUN: 'Integrity Checks Job for DB Maintenance Plan 'DB Maintenance
Plan2'' was run on 11/26/2006 at 00:00:03
DURATION: 0 hours, 1 minutes, 14 seconds
STATUS: Failed
MESSAGES: The job failed. The Job was invoked by Schedule 190 (Schedule 1).
The last step to run was step 1 (Step 1).
CheckDB reported
CHECKDB found 0 allocation errors and 0 consistency errors in database
'ATOMS'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
However CheckCatalog reports:
Server: Msg 2513, Level 16, State 2, Line 1
Table error: Object ID 231723928 (object '231723928') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
(many more)
And
Table error: Object ID 423724612 (object '423724612') does not match between
'SYSINDEXES' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 1
Help appreciated.
--
Regards,
JamieYou might want to have a look at these:
http://blogs.msdn.com/sqlserverstorageengine/archive/tags/DBCC/default.aspx
Andrew J. Kelly SQL MVP
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:1162D111-8C87-40B9-99FD-91BF48290290@.microsoft.com...
> I'm not sure what my response to this error should be.
> JOB RUN: 'Integrity Checks Job for DB Maintenance Plan 'DB Maintenance
> Plan2'' was run on 11/26/2006 at 00:00:03
> DURATION: 0 hours, 1 minutes, 14 seconds
> STATUS: Failed
> MESSAGES: The job failed. The Job was invoked by Schedule 190 (Schedule
> 1).
> The last step to run was step 1 (Step 1).
> CheckDB reported
> CHECKDB found 0 allocation errors and 0 consistency errors in database
> 'ATOMS'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> However CheckCatalog reports:
> Server: Msg 2513, Level 16, State 2, Line 1
> Table error: Object ID 231723928 (object '231723928') does not match
> between
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> (many more)
> And
> Table error: Object ID 423724612 (object '423724612') does not match
> between
> 'SYSINDEXES' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 1
> Help appreciated.
> --
> Regards,
> Jamie|||And a very short summary of options and recommendations:
http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OGNhkfkFHHA.1240@.TK2MSFTNGP03.phx.gbl...
> You might want to have a look at these:
> http://blogs.msdn.com/sqlserverstorageengine/archive/tags/DBCC/default.aspx
>
> --
> Andrew J. Kelly SQL MVP
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:1162D111-8C87-40B9-99FD-91BF48290290@.microsoft.com...
>> I'm not sure what my response to this error should be.
>> JOB RUN: 'Integrity Checks Job for DB Maintenance Plan 'DB Maintenance
>> Plan2'' was run on 11/26/2006 at 00:00:03
>> DURATION: 0 hours, 1 minutes, 14 seconds
>> STATUS: Failed
>> MESSAGES: The job failed. The Job was invoked by Schedule 190 (Schedule 1).
>> The last step to run was step 1 (Step 1).
>> CheckDB reported
>> CHECKDB found 0 allocation errors and 0 consistency errors in database
>> 'ATOMS'.
>> DBCC execution completed. If DBCC printed error messages, contact your
>> system administrator.
>> However CheckCatalog reports:
>> Server: Msg 2513, Level 16, State 2, Line 1
>> Table error: Object ID 231723928 (object '231723928') does not match between
>> 'SYSCOLUMNS' and 'SYSOBJECTS'.
>> (many more)
>> And
>> Table error: Object ID 423724612 (object '423724612') does not match between
>> 'SYSINDEXES' and 'SYSOBJECTS'.
>> Server: Msg 2513, Level 16, State 1, Line 1
>> Help appreciated.
>> --
>> Regards,
>> Jamie
>

Integrity Checks Fail

I'm not sure what my response to this error should be.
JOB RUN: 'Integrity Checks Job for DB Maintenance Plan 'DB Maintenance
Plan2'' was run on 11/26/2006 at 00:00:03
DURATION: 0 hours, 1 minutes, 14 seconds
STATUS: Failed
MESSAGES: The job failed. The Job was invoked by Schedule 190 (Schedule 1).
The last step to run was step 1 (Step 1).
CheckDB reported
CHECKDB found 0 allocation errors and 0 consistency errors in database
'ATOMS'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
However CheckCatalog reports:
Server: Msg 2513, Level 16, State 2, Line 1
Table error: Object ID 231723928 (object '231723928') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
(many more)
And
Table error: Object ID 423724612 (object '423724612') does not match between
'SYSINDEXES' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 1
Help appreciated.
Regards,
JamieYou might want to have a look at these:
http://blogs.msdn.com/sqlserverstor...CC/default.aspx
Andrew J. Kelly SQL MVP
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:1162D111-8C87-40B9-99FD-91BF48290290@.microsoft.com...
> I'm not sure what my response to this error should be.
> JOB RUN: 'Integrity Checks Job for DB Maintenance Plan 'DB Maintenance
> Plan2'' was run on 11/26/2006 at 00:00:03
> DURATION: 0 hours, 1 minutes, 14 seconds
> STATUS: Failed
> MESSAGES: The job failed. The Job was invoked by Schedule 190 (Schedule
> 1).
> The last step to run was step 1 (Step 1).
> CheckDB reported
> CHECKDB found 0 allocation errors and 0 consistency errors in database
> 'ATOMS'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> However CheckCatalog reports:
> Server: Msg 2513, Level 16, State 2, Line 1
> Table error: Object ID 231723928 (object '231723928') does not match
> between
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> (many more)
> And
> Table error: Object ID 423724612 (object '423724612') does not match
> between
> 'SYSINDEXES' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 1
> Help appreciated.
> --
> Regards,
> Jamie|||And a very short summary of options and recommendations:
http://www.karaszi.com/SQLServer/in..._suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OGNhkfkFHHA.1240@.TK2MSFTNGP03.phx.gbl...
> You might want to have a look at these:
> [url]http://blogs.msdn.com/sqlserverstorageengine/archive/tags/DBCC/default.aspx[/url
]
>
> --
> Andrew J. Kelly SQL MVP
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:1162D111-8C87-40B9-99FD-91BF48290290@.microsoft.com...
>

Integrity Check set in Maintenance Plan failed....

I'm trying to troubleshoot why this job failed. Welcome any suggestion
DURATION: 0 hours, 0 minutes, 49 second
STATUS: Faile
MESSAGES: The job failed. The Job was invoked by Schedule 35 (Schedule 1). The last step to run was step 1 (Step 1)Make sure that you have defined a report file for the maint job and check for error messages in the
report file.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Janice" <anonymous@.discussions.microsoft.com> wrote in message
news:EDF91295-5A27-43C3-AA1C-402F35FB629F@.microsoft.com...
> I'm trying to troubleshoot why this job failed. Welcome any suggestions
>
> DURATION: 0 hours, 0 minutes, 49 seconds
> STATUS: Failed
> MESSAGES: The job failed. The Job was invoked by Schedule 35 (Schedule 1). The last step to run
was step 1 (Step 1).
>|||Thanks TIBOR - I viewed the report and discovered that db needs to be in single user mode.
-- Tibor Karaszi wrote: --
Make sure that you have defined a report file for the maint job and check for error messages in the
report file.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Janice" <anonymous@.discussions.microsoft.com> wrote in message
news:EDF91295-5A27-43C3-AA1C-402F35FB629F@.microsoft.com...
> I'm trying to troubleshoot why this job failed. Welcome any suggestions
>> DURATION: 0 hours, 0 minutes, 49 seconds
> STATUS: Failed
> MESSAGES: The job failed. The Job was invoked by Schedule 35 (Schedule 1). The last step to run
was step 1 (Step 1).
>|||The maint plan will try to set the db in single user, but that will fail if there are users in the
database. Also, some of the system databases cannot be set in single user mode.
I suggest you remove the option "attempt to repair minor problems" from the integrity checks. If you
do run into problems, you want to be there and contemplate over what you want to do to fix it.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Janice" <anonymous@.discussions.microsoft.com> wrote in message
news:8FF8A86A-F50A-4EB8-BF6D-058B99EF2455@.microsoft.com...
> Thanks TIBOR - I viewed the report and discovered that db needs to be in single user mode.
> -- Tibor Karaszi wrote: --
> Make sure that you have defined a report file for the maint job and check for error messages
in the
> report file.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Janice" <anonymous@.discussions.microsoft.com> wrote in message
> news:EDF91295-5A27-43C3-AA1C-402F35FB629F@.microsoft.com...
> > I'm trying to troubleshoot why this job failed. Welcome any suggestions
> >> DURATION: 0 hours, 0 minutes, 49 seconds
> > STATUS: Failed
> > MESSAGES: The job failed. The Job was invoked by Schedule 35 (Schedule 1). The last step
to run
> was step 1 (Step 1).
> >

Integrity Check Job Failure

Hi,
Psoted this is in Reporting Services, but no response:
I have the AdventureWorks2000 database (part of reporting services)
installed on a couple of servers. I have created a maintenance plan to run
the integrity check on all user servers. The job ran fine until the
AdventureWorks2000 db was installed. The jobs fails only on this db and work
s
just fine on other db's . Here is the
output from the maintenance plan:
Starting maintenance plan 'Master Server - All DB Integrity Check Job' on
4/10/2005 9:30:01 PM
[1] Database AdventureWorks2000: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]DBCC failed because the following SET options
have
incorrect settings: 'QUOTED_IDENTIFIER'.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed becau
se the
following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.
I tried setting different options, but nothing appears to solve the problem.
Any suggestions/ideas?
TIA,
DeeJay PuarLook here, your problem is described there:
http://support.microsoft.com/kb/q301292/
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"DeeJay Puar" <DeeJayPuar@.discussions.microsoft.com> schrieb im Newsbeitrag
news:197CD201-850D-447C-A461-C01EBD987657@.microsoft.com...
> Hi,
> Psoted this is in Reporting Services, but no response:
> I have the AdventureWorks2000 database (part of reporting services)
> installed on a couple of servers. I have created a maintenance plan to run
> the integrity check on all user servers. The job ran fine until the
> AdventureWorks2000 db was installed. The jobs fails only on this db and
> works
> just fine on other db's . Here is the
> output from the maintenance plan:
> Starting maintenance plan 'Master Server - All DB Integrity Check Job' on
> 4/10/2005 9:30:01 PM
> [1] Database AdventureWorks2000: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft]
[ODBC
> SQL
> Server Driver][SQL Server]DBCC failed because the following SET option
s
> have
> incorrect settings: 'QUOTED_IDENTIFIER'.
> The following errors were found:
> [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed bec
ause the
> following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.
> I tried setting different options, but nothing appears to solve the
> problem.
> Any suggestions/ideas?
> TIA,
> DeeJay Puar
>|||Hit the Nail on the Head...Thanks Jen.
I was only looking the Reporting Services Documentation.
DeeJay Puar
"Jens Sü?meyer" wrote:

> Look here, your problem is described there:
> http://support.microsoft.com/kb/q301292/
> HTH, Jens Sü?meyer.
> --
> http://www.sqlserver2005.de
> --
> "DeeJay Puar" <DeeJayPuar@.discussions.microsoft.com> schrieb im Newsbeitra
g
> news:197CD201-850D-447C-A461-C01EBD987657@.microsoft.com...
>
>

Integrity Check Job Failure

Hi,
Psoted this is in Reporting Services, but no response:
I have the AdventureWorks2000 database (part of reporting services)
installed on a couple of servers. I have created a maintenance plan to run
the integrity check on all user servers. The job ran fine until the
AdventureWorks2000 db was installed. The jobs fails only on this db and works
just fine on other db's . Here is the
output from the maintenance plan:
Starting maintenance plan 'Master Server - All DB Integrity Check Job' on
4/10/2005 9:30:01 PM
[1] Database AdventureWorks2000: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
Server Driver][SQL Server]DBCC failed because the following SET options have
incorrect settings: 'QUOTED_IDENTIFIER'.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed because the
following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.
I tried setting different options, but nothing appears to solve the problem.
Any suggestions/ideas?
TIA,
DeeJay Puar
Look here, your problem is described there:
http://support.microsoft.com/kb/q301292/
HTH, Jens Smeyer.
http://www.sqlserver2005.de
"DeeJay Puar" <DeeJayPuar@.discussions.microsoft.com> schrieb im Newsbeitrag
news:197CD201-850D-447C-A461-C01EBD987657@.microsoft.com...
> Hi,
> Psoted this is in Reporting Services, but no response:
> I have the AdventureWorks2000 database (part of reporting services)
> installed on a couple of servers. I have created a maintenance plan to run
> the integrity check on all user servers. The job ran fine until the
> AdventureWorks2000 db was installed. The jobs fails only on this db and
> works
> just fine on other db's . Here is the
> output from the maintenance plan:
> Starting maintenance plan 'Master Server - All DB Integrity Check Job' on
> 4/10/2005 9:30:01 PM
> [1] Database AdventureWorks2000: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC
> SQL
> Server Driver][SQL Server]DBCC failed because the following SET options
> have
> incorrect settings: 'QUOTED_IDENTIFIER'.
> The following errors were found:
> [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed because the
> following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.
> I tried setting different options, but nothing appears to solve the
> problem.
> Any suggestions/ideas?
> TIA,
> DeeJay Puar
>
|||Hit the Nail on the Head...Thanks Jen.
I was only looking the Reporting Services Documentation.
DeeJay Puar
"Jens Sü?meyer" wrote:

> Look here, your problem is described there:
> http://support.microsoft.com/kb/q301292/
> HTH, Jens Sü?meyer.
> --
> http://www.sqlserver2005.de
> --
> "DeeJay Puar" <DeeJayPuar@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:197CD201-850D-447C-A461-C01EBD987657@.microsoft.com...
>
>

Integrity Check Fails

I setup a DB Maint, Plan for several databases however,
the Integrity Check job fails for a few of my databases.
The error is "Repair statement not processed. Database
needs to be in single user mode."
Any thoughts on why this is happening?
Thanks,
DonPretty much what it says. What version and service pack of SQL Server? Note that you can't put
master in single user mode. I recommend that you remove that darn option to "attempt to repair minor
problems". :-)
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Don" <drduquette@.aol.com> wrote in message news:000001c36ca4$732448e0$a101280a@.phx.gbl...
> I setup a DB Maint, Plan for several databases however,
> the Integrity Check job fails for a few of my databases.
> The error is "Repair statement not processed. Database
> needs to be in single user mode."
> Any thoughts on why this is happening?
> Thanks,
> Don|||Thanks for the quick response! We are using SQLserver
2000 service pack 3. I realize that I could uncheck the
repair but that defeats the purpose if there is a problem
with the database. In addition, MS recommends that the
repair option be checked.
Is there any work around to this issue of putting the DB
in single user mode?
Thanks,
Don
>--Original Message--
>Pretty much what it says. What version and service pack
of SQL Server? Note that you can't put
>master in single user mode. I recommend that you remove
that darn option to "attempt to repair minor
>problems". :-)
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
>"Don" <drduquette@.aol.com> wrote in message
news:000001c36ca4$732448e0$a101280a@.phx.gbl...
>> I setup a DB Maint, Plan for several databases however,
>> the Integrity Check job fails for a few of my databases.
>> The error is "Repair statement not processed. Database
>> needs to be in single user mode."
>> Any thoughts on why this is happening?
>> Thanks,
>> Don
>
>.
>|||Don,
Tibor mentioned a restriction on master. In addition, if a database has any
open connections it cannot be changed to single user mode unless you use the
WITH ROLLBACK clauses.
ALTER DATABASE mydatabase
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
This breaks all unqualified connections and switches to single user mode.
(Much easier than the old method of writing a looping procedure to KILL
connections until they were finally all gone.)
Russell Fields
"Don" <drduquette@.aol.com> wrote in message
news:0e9901c36ca7$0659c2f0$a301280a@.phx.gbl...
> Thanks for the quick response! We are using SQLserver
> 2000 service pack 3. I realize that I could uncheck the
> repair but that defeats the purpose if there is a problem
> with the database. In addition, MS recommends that the
> repair option be checked.
> Is there any work around to this issue of putting the DB
> in single user mode?
> Thanks,
> Don
> >--Original Message--
> >Pretty much what it says. What version and service pack
> of SQL Server? Note that you can't put
> >master in single user mode. I recommend that you remove
> that darn option to "attempt to repair minor
> >problems". :-)
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at: http://groups.google.com/groups?oi=djq&as
> ugroup=microsoft.public.sqlserver
> >
> >
> >"Don" <drduquette@.aol.com> wrote in message
> news:000001c36ca4$732448e0$a101280a@.phx.gbl...
> >> I setup a DB Maint, Plan for several databases however,
> >> the Integrity Check job fails for a few of my databases.
> >> The error is "Repair statement not processed. Database
> >> needs to be in single user mode."
> >>
> >> Any thoughts on why this is happening?
> >>
> >> Thanks,
> >> Don
> >
> >
> >.
> >|||The purpose of CHECKDB is to get *notified* in the unlikely event of a problem. I surely don't want
some background thing try to repair the database if I run into a problem with it. I want to be
there, do a log backup first, think, etc etc etc.
Anyhow, master cannot be in single user mode, if this is the one which is causing your problem, then
you have to remove it from the plan, or don't do background repair.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Don" <drduquette@.aol.com> wrote in message news:0e9901c36ca7$0659c2f0$a301280a@.phx.gbl...
> Thanks for the quick response! We are using SQLserver
> 2000 service pack 3. I realize that I could uncheck the
> repair but that defeats the purpose if there is a problem
> with the database. In addition, MS recommends that the
> repair option be checked.
> Is there any work around to this issue of putting the DB
> in single user mode?
> Thanks,
> Don
> >--Original Message--
> >Pretty much what it says. What version and service pack
> of SQL Server? Note that you can't put
> >master in single user mode. I recommend that you remove
> that darn option to "attempt to repair minor
> >problems". :-)
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at: http://groups.google.com/groups?oi=djq&as
> ugroup=microsoft.public.sqlserver
> >
> >
> >"Don" <drduquette@.aol.com> wrote in message
> news:000001c36ca4$732448e0$a101280a@.phx.gbl...
> >> I setup a DB Maint, Plan for several databases however,
> >> the Integrity Check job fails for a few of my databases.
> >> The error is "Repair statement not processed. Database
> >> needs to be in single user mode."
> >>
> >> Any thoughts on why this is happening?
> >>
> >> Thanks,
> >> Don
> >
> >
> >.
> >

Integrity check and optimization job failed

I found the following error in my maintenance plan please help me on this

Under maintenace plan history i found this error:-

[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE STATISTICS failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.

and jobs failed with following error

i found this error in job history of optimization & integrity check jobs:-

Executed as user: XXXX\admin. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.

Please help me find out the solution

Thanks in advance

Regards,

Sumitdo not use maintenance plans. setup your own jobs for this stuff. easier to troubleshoot and you have more granular control.|||This error isn't caused by the maintenance plan, but rather occurs within the job the maintenance plan creates. The error is generated by UPDATE STATISTICS, and in my experience is related to attempts to update the stats on a table that is used to record or maintain table statistics (such as a table within the msdb database). Unfortunately, I have not been able to reliably reproduce the problem in order to debug it. When I run UPDATE STATS on the offending table a second time, it always completes succesfully. So far, it has not bothered my enough to make me want to delve into it any deeper.