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 stalling on a query

I've been trying to diagonose a strange problem with an application that I
inherited. Every few days, SQL Server will take between 3 to 4 minutes to
perform a really simple update.
The application that runs against the database is a real-time monitoring
program that communicates to a number of devices. It probably issues about
a dozen queries every second, 80% updates, 10% selects, 10% inserts. There
are no deletes being done. For the most part, all of the queries run in
less than a second. In the application, the CommandTimeout value on the
queries is set to 2 minutes. The application is setup to restart itself on
when a timeout occurs. And since the CommandTimeout is not high enough (2
mins vs 3+ mins), the application will into a non-stop cycle of restarting
itself... Until I go into Query Analyzer, and run a simple update statement
against an arbitrary table; which will take between 3 to 4 minutes to
complete, and then afterwards, the application will start running fine
again. I've seen instances where the application would stay in a restart
cycle for days at a time.
There does not appear to be a deadlock or open transaction. I've tried
running SP_WHO2, SP_LOCK, DBCC OPENTRAN, DBCC CHECKDB and didn't see any
issues.
The application is running on the same computer as the SQL server. It is
the sole application running against the database. The application uses the
SQLOLEDB provider.
The database is about 10 MB, the transaction log is 3 GB (mostly empty).
Server properties: Windows Server 2003 Standard Edition, Pentium 4 2.8 GHz
(hyperthreading-enabled), 512 MB RAM, and plenty of free drive space.
SQL Server settings:
- SQL Server 2000 8.00.818 (SP3)
- Memory is setup for dynamic allocation
- Processor is setup to use the two "instances"
- "Implicit transactions" is checked
- "Close cursor on COMMIT" is checked
- "Auto Grow" is enabled for both the data file and transaction log
- "Auto Update Statistics", "Auto Create Statistics", and "Torn Page
Detection" are checked
There are jobs to backup both the data file and transaction log.
I noticed that the timeouts tend to be from one of two queries (but about
99% of the time, it's one of these queries being called). Each of the
queries is literally a single update statement touching a single table,
wrapped in a stored procedure. And the tables that they update have at most
a dozen records in them.
Things, I've tried,
- I thought it was auto grow causing the stall, but I manually increased the
database size, and the application did not hiccup.
- I also thought it might be statistics related, so I ran a "sp_updatestats"
against the database. The next day, the problem came up again.
- I've tried adding "with recompile" to the stored procedure with no luck
either.
This seems like it might be server configuration issue, but I'm not sure
what else to check.
TYIA for any advice.My bet is still on the Autogrow. First off I assume you mean the db is 10GB
and not MB? If autogrow kicks in it will attempt to grow the file by about
1GB. On a slow disk subsystem this can take a while. If the connection
that issued the command that forced the autogrow times out the growth will
be canceled and you are back to where you started. The next insert or
update may force another growth which may or may not succeed. It is all
about timing and resources. When you manually grow the DB there is no
problem because there is not a statement (insert or update) in the process
of executing that forced the growth so it will succeed. You should always
have plenty of free space in the db and autogrow should never kick in. When
you run low you need to manually grow it at the appropriate time so you
always have enough free space. If you are that low you are too low anyway
since the first time you do a reindex you will need more space. If you
have auto shrink turned on then turn it off immediately. You should also
change the growth to be a fixed amount vs. a percentage. Make it an amount
that can grow in no more than 30 seconds or so. That way you won't get a
timeout if it does kick in.
Andrew J. Kelly SQL MVP
"John Smith" <john@.smith.com> wrote in message
news:OtaoqmFIFHA.2356@.TK2MSFTNGP12.phx.gbl...
> I've been trying to diagonose a strange problem with an application that I
> inherited. Every few days, SQL Server will take between 3 to 4 minutes to
> perform a really simple update.
> The application that runs against the database is a real-time monitoring
> program that communicates to a number of devices. It probably issues
> about a dozen queries every second, 80% updates, 10% selects, 10% inserts.
> There are no deletes being done. For the most part, all of the queries
> run in less than a second. In the application, the CommandTimeout value
> on the queries is set to 2 minutes. The application is setup to restart
> itself on when a timeout occurs. And since the CommandTimeout is not high
> enough (2 mins vs 3+ mins), the application will into a non-stop cycle of
> restarting itself... Until I go into Query Analyzer, and run a simple
> update statement against an arbitrary table; which will take between 3 to
> 4 minutes to complete, and then afterwards, the application will start
> running fine again. I've seen instances where the application would stay
> in a restart cycle for days at a time.
> There does not appear to be a deadlock or open transaction. I've tried
> running SP_WHO2, SP_LOCK, DBCC OPENTRAN, DBCC CHECKDB and didn't see any
> issues.
> The application is running on the same computer as the SQL server. It is
> the sole application running against the database. The application uses
> the SQLOLEDB provider.
> The database is about 10 MB, the transaction log is 3 GB (mostly empty).
> Server properties: Windows Server 2003 Standard Edition, Pentium 4 2.8 GHz
> (hyperthreading-enabled), 512 MB RAM, and plenty of free drive space.
> SQL Server settings:
> - SQL Server 2000 8.00.818 (SP3)
> - Memory is setup for dynamic allocation
> - Processor is setup to use the two "instances"
> - "Implicit transactions" is checked
> - "Close cursor on COMMIT" is checked
> - "Auto Grow" is enabled for both the data file and transaction log
> - "Auto Update Statistics", "Auto Create Statistics", and "Torn Page
> Detection" are checked
> There are jobs to backup both the data file and transaction log.
> I noticed that the timeouts tend to be from one of two queries (but about
> 99% of the time, it's one of these queries being called). Each of the
> queries is literally a single update statement touching a single table,
> wrapped in a stored procedure. And the tables that they update have at
> most a dozen records in them.
> Things, I've tried,
> - I thought it was auto grow causing the stall, but I manually increased
> the database size, and the application did not hiccup.
> - I also thought it might be statistics related, so I ran a
> "sp_updatestats" against the database. The next day, the problem came up
> again.
> - I've tried adding "with recompile" to the stored procedure with no luck
> either.
> This seems like it might be server configuration issue, but I'm not sure
> what else to check.
> TYIA for any advice.
>|||Thanks, I didn't realize the part about the autogrow being canceled on a
timeout.
I checked again, and was off a bit. It's at 30 MB, but still tiny. It
might have been the transaction log that was expanding (is it affected the
same way, by a connection timeout?).
I'll keep a closer eye on this then. Thanks again.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eRL2YkGIFHA.3336@.TK2MSFTNGP10.phx.gbl...
> My bet is still on the Autogrow. First off I assume you mean the db is
> 10GB and not MB? If autogrow kicks in it will attempt to grow the file by
> about 1GB. On a slow disk subsystem this can take a while. If the
> connection that issued the command that forced the autogrow times out the
> growth will be canceled and you are back to where you started. The next
> insert or update may force another growth which may or may not succeed. It
> is all about timing and resources. When you manually grow the DB there is
> no problem because there is not a statement (insert or update) in the
> process of executing that forced the growth so it will succeed. You
> should always have plenty of free space in the db and autogrow should
> never kick in. When you run low you need to manually grow it at the
> appropriate time so you always have enough free space. If you are that
> low you are too low anyway since the first time you do a reindex you will
> need more space. If you have auto shrink turned on then turn it off
> immediately. You should also change the growth to be a fixed amount vs. a
> percentage. Make it an amount that can grow in no more than 30 seconds or
> so. That way you won't get a timeout if it does kick in.
> --
> Andrew J. Kelly SQL MVP
>
> "John Smith" <john@.smith.com> wrote in message
> news:OtaoqmFIFHA.2356@.TK2MSFTNGP12.phx.gbl...
>|||Yes the tran log is essentially the same behavior in that any statements
that require logging will sit and wait until the log is finished expanding.
If you have a 30MB db and a 3GB log you are not doing something right. You
stated that you are doing regular Full and Log backups. If that were the
case and you didn't have any long running open transactions your log file
should only be a few hundred MB at best. Try running DBCC OPENTRAN and see
what it tells you. Also check your backups to make sure they are actually
happening with no errors.
Andrew J. Kelly SQL MVP
"John Smith" <john@.smith.com> wrote in message
news:OX4$t1IIFHA.608@.TK2MSFTNGP10.phx.gbl...
> Thanks, I didn't realize the part about the autogrow being canceled on a
> timeout.
> I checked again, and was off a bit. It's at 30 MB, but still tiny. It
> might have been the transaction log that was expanding (is it affected the
> same way, by a connection timeout?).
> I'll keep a closer eye on this then. Thanks again.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eRL2YkGIFHA.3336@.TK2MSFTNGP10.phx.gbl...
>

Intermittent connection error (with workaround)

Hi all,
I have a strange error when I try to connect to SqlServer from Bde
Administrator (and also with my Delphi program which uses the BDE) on
some computers (in this post I'll call this problematic computer
SERVER_A).
When I try to open a BDE alias which connects to the local SqlServer I
get (on 90 % of attempts) a "General Sql Error" Bde error 13059.
Sometimes (let's say 10% of attempts) it connects ok.
This is quite strange (it's not a password error) and I can't explain
this behavior.
I can connect (using Bde Administrator) with no errors from the same
computer (SERVER_A) to a SqlServer installed in another Pc (let's call
this computer PC_B) and I can connect (using Bde Administrator) with
no error from PC_B computer to the SqlServer installed on the
SERVER_A.
I use SqlServer security, the BDE uses ntwdblib.dll to connect (using
tcp/ip).
I've stopped all firewalls, proxies etc... but nothing helps.
The only thing that fixes the problem is to add a new route from
SERVER_A that goes out to a proxy instead of going to 127.0.0.1
address (the loopback address).
In other term if I add a new route in the SERVER_A pc for SERVER_A IP
address that goes out to a proxy with a metric that is less than the
127.0.0.1 one then all the connections are ok.
This is very strange: it seems a TCP-IP problem.....
Do you have any ideas about this strange behavior?
Thank you,
Bye, Rocco.
I have the same problem, BDE and Windows 2003 SP2.
I solved it temporarily by using the named pipes protocol in SQL
Server Client Configuration.
But I would like to solve it by continuing to use TCP/IP.
Perhaps a security problem in windows 2003 SP2.
I checked also with portqry, and the server listens to port 1433.
Any other ideas ?
Jean-Christophe

Intermittent connection error (with workaround)

Hi all,
I have a strange error when I try to connect to SqlServer from Bde
Administrator (and also with my Delphi program which uses the BDE) on
some computers (in this post I'll call this problematic computer
SERVER_A).
When I try to open a BDE alias which connects to the local SqlServer I
get (on 90 % of attempts) a "General Sql Error" Bde error 13059.
Sometimes (let's say 10% of attempts) it connects ok.
This is quite strange (it's not a password error) and I can't explain
this behavior.
I can connect (using Bde Administrator) with no errors from the same
computer (SERVER_A) to a SqlServer installed in another Pc (let's call
this computer PC_B) and I can connect (using Bde Administrator) with
no error from PC_B computer to the SqlServer installed on the
SERVER_A.
I use SqlServer security, the BDE uses ntwdblib.dll to connect (using
tcp/ip).
I've stopped all firewalls, proxies etc... but nothing helps.
The only thing that fixes the problem is to add a new route from
SERVER_A that goes out to a proxy instead of going to 127.0.0.1
address (the loopback address).
In other term if I add a new route in the SERVER_A pc for SERVER_A IP
address that goes out to a proxy with a metric that is less than the
127.0.0.1 one then all the connections are ok.
This is very strange: it seems a TCP-IP problem.....
Do you have any ideas about this strange behavior?
Thank you,
Bye, Rocco.I have the same problem, BDE and Windows 2003 SP2.
I solved it temporarily by using the named pipes protocol in SQL
Server Client Configuration.
But I would like to solve it by continuing to use TCP/IP.
Perhaps a security problem in windows 2003 SP2.
I checked also with portqry, and the server listens to port 1433.
Any other ideas ?
Jean-Christophe

Intermittent connection dramas: "Cannot open user default database..."

Hi,
I've been experiencing some very strange, intermittent failures connecting
to an MS SQL database... basically at seemingly random times the server
decides that it can't open the requested database (reporting error 4062:
"Cannot open user default database. Using master database instead"). I've
so far been unable to track down any useful background to this error
message; has anyone on this group come across similar problems? Any clues
as to what might be triggering this sort of fault? More specific details
follow:
* In the majority of cases, connection is established correctly (albeit
sometimes a little slowly).
* Connections are being made to the local machine, using straightforward,
static code. The problem isn't something trivial like a mis-typed
database name, incorrect password etc. Re-running the SAME CODE will
eventually succeed.
* The server isn't particularly heavily loaded, and I can perceive no
pattern to the fault (it seems no more prevalent when the
server's busy, nor after any particular events).
* Nothing relevant is documented in the Event Viewer, nor the server logs.
Am I looking in the right place?
* We're running SQL Server 2005 Express Edition, latest service pack...
are there any connection rate limitations on this software? All
documentation I've seen indicates that there are not, but this behaviour
almost suggests that there may be...
* Connecting (from local machine) using PHP mssql module (latest stable
version).
* Although possibly symptomatic of a DoS, there's no other evidence to
suggest that this is the case (TCP to the service is firewalled).
Would be grateful for any advice on this issue.
Cheers,
Alex.
Alex Craven wrote:
> Hi,
> I've been experiencing some very strange, intermittent failures connecting
> to an MS SQL database... basically at seemingly random times the server
> decides that it can't open the requested database (reporting error 4062:
> "Cannot open user default database. Using master database instead"). I've
> so far been unable to track down any useful background to this error
> message; has anyone on this group come across similar problems? Any clues
> as to what might be triggering this sort of fault? More specific details
> follow:
> * In the majority of cases, connection is established correctly (albeit
> sometimes a little slowly).
> * Connections are being made to the local machine, using straightforward,
> static code. The problem isn't something trivial like a mis-typed
> database name, incorrect password etc. Re-running the SAME CODE will
> eventually succeed.
> * The server isn't particularly heavily loaded, and I can perceive no
> pattern to the fault (it seems no more prevalent when the
> server's busy, nor after any particular events).
> * Nothing relevant is documented in the Event Viewer, nor the server logs.
> Am I looking in the right place?
> * We're running SQL Server 2005 Express Edition, latest service pack...
> are there any connection rate limitations on this software? All
> documentation I've seen indicates that there are not, but this behaviour
> almost suggests that there may be...
> * Connecting (from local machine) using PHP mssql module (latest stable
> version).
> * Although possibly symptomatic of a DoS, there's no other evidence to
> suggest that this is the case (TCP to the service is firewalled).
>
> Would be grateful for any advice on this issue.
> Cheers,
> Alex.
>
>
Make sure the "problem" database isn't configured for auto-close, also
make sure it's not the target for log-shipping, or some other process
that might take it offline to do a restore, etc.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Do you periodically rename, remove, or detach the database?
Do you have any jobs that put the database into single-user mode or
otherwise alter the availability of the database?
|||On Fri, 29 Sep 2006 08:14:30 -0500, Tracy McKibben uttered:
[...]
> Make sure the "problem" database isn't configured for auto-close, also
> make sure it's not the target for log-shipping, or some other process
> that might take it offline to do a restore, etc.
Hi Tracy et al,
Thanks for all your advice; so far it looks like the auto-close 'feature'
was most likely the root of my problems (or at least a major factor...);
the system seems to be performing rather more reliably with that disabled.
Cheers,
Alex.
|||Alex Craven wrote:
> On Fri, 29 Sep 2006 08:14:30 -0500, Tracy McKibben uttered:
> [...]
>
> Hi Tracy et al,
> Thanks for all your advice; so far it looks like the auto-close 'feature'
> was most likely the root of my problems (or at least a major factor...);
> the system seems to be performing rather more reliably with that disabled.
> Cheers,
> Alex.
>
That auto-close option should NEVER be enabled for a production
database. Another one to watch out for is auto-shrink.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
sql

Intermittent connection dramas: "Cannot open user default database..."

Hi,
I've been experiencing some very strange, intermittent failures connecting
to an MS SQL database... basically at seemingly random times the server
decides that it can't open the requested database (reporting error 4062:
"Cannot open user default database. Using master database instead"). I've
so far been unable to track down any useful background to this error
message; has anyone on this group come across similar problems? Any clues
as to what might be triggering this sort of fault? More specific details
follow:
* In the majority of cases, connection is established correctly (albeit
sometimes a little slowly).
* Connections are being made to the local machine, using straightforward,
static code. The problem isn't something trivial like a mis-typed
database name, incorrect password etc. Re-running the SAME CODE will
eventually succeed.
* The server isn't particularly heavily loaded, and I can perceive no
pattern to the fault (it seems no more prevalent when the
server's busy, nor after any particular events).
* Nothing relevant is documented in the Event Viewer, nor the server logs.
Am I looking in the right place'
* We're running SQL Server 2005 Express Edition, latest service pack...
are there any connection rate limitations on this software? All
documentation I've seen indicates that there are not, but this behaviour
almost suggests that there may be...
* Connecting (from local machine) using PHP mssql module (latest stable
version).
* Although possibly symptomatic of a DoS, there's no other evidence to
suggest that this is the case (TCP to the service is firewalled).
Would be grateful for any advice on this issue.
Cheers,
Alex.HI Alex
Have you checked for Anti-virus or intrusion detection software in the
server, this can cause connections to fail. If you have the autoclose option
set for the database or attaching the database when you connect then if the
AV software is scanning the files you will not be able to do this.
You may also want to eliminate any particular machine or see if this occurs
on the server, to rule out general networking issues. Check firewalls are
correctly configured and if you are using group policies that they are not
interfering.
You may also want to check if you are service packed to a recent version.
John
"Alex Craven" wrote:
> Hi,
> I've been experiencing some very strange, intermittent failures connecting
> to an MS SQL database... basically at seemingly random times the server
> decides that it can't open the requested database (reporting error 4062:
> "Cannot open user default database. Using master database instead"). I've
> so far been unable to track down any useful background to this error
> message; has anyone on this group come across similar problems? Any clues
> as to what might be triggering this sort of fault? More specific details
> follow:
> * In the majority of cases, connection is established correctly (albeit
> sometimes a little slowly).
> * Connections are being made to the local machine, using straightforward,
> static code. The problem isn't something trivial like a mis-typed
> database name, incorrect password etc. Re-running the SAME CODE will
> eventually succeed.
> * The server isn't particularly heavily loaded, and I can perceive no
> pattern to the fault (it seems no more prevalent when the
> server's busy, nor after any particular events).
> * Nothing relevant is documented in the Event Viewer, nor the server logs.
> Am I looking in the right place'
> * We're running SQL Server 2005 Express Edition, latest service pack...
> are there any connection rate limitations on this software? All
> documentation I've seen indicates that there are not, but this behaviour
> almost suggests that there may be...
> * Connecting (from local machine) using PHP mssql module (latest stable
> version).
> * Although possibly symptomatic of a DoS, there's no other evidence to
> suggest that this is the case (TCP to the service is firewalled).
>
> Would be grateful for any advice on this issue.
> Cheers,
> Alex.
>
>|||Alex Craven wrote:
> Hi,
> I've been experiencing some very strange, intermittent failures connecting
> to an MS SQL database... basically at seemingly random times the server
> decides that it can't open the requested database (reporting error 4062:
> "Cannot open user default database. Using master database instead"). I've
> so far been unable to track down any useful background to this error
> message; has anyone on this group come across similar problems? Any clues
> as to what might be triggering this sort of fault? More specific details
> follow:
> * In the majority of cases, connection is established correctly (albeit
> sometimes a little slowly).
> * Connections are being made to the local machine, using straightforward,
> static code. The problem isn't something trivial like a mis-typed
> database name, incorrect password etc. Re-running the SAME CODE will
> eventually succeed.
> * The server isn't particularly heavily loaded, and I can perceive no
> pattern to the fault (it seems no more prevalent when the
> server's busy, nor after any particular events).
> * Nothing relevant is documented in the Event Viewer, nor the server logs.
> Am I looking in the right place'
> * We're running SQL Server 2005 Express Edition, latest service pack...
> are there any connection rate limitations on this software? All
> documentation I've seen indicates that there are not, but this behaviour
> almost suggests that there may be...
> * Connecting (from local machine) using PHP mssql module (latest stable
> version).
> * Although possibly symptomatic of a DoS, there's no other evidence to
> suggest that this is the case (TCP to the service is firewalled).
>
> Would be grateful for any advice on this issue.
> Cheers,
> Alex.
>
>
Make sure the "problem" database isn't configured for auto-close, also
make sure it's not the target for log-shipping, or some other process
that might take it offline to do a restore, etc.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Do you periodically rename, remove, or detach the database?
Do you have any jobs that put the database into single-user mode or
otherwise alter the availability of the database?|||On Fri, 29 Sep 2006 08:14:30 -0500, Tracy McKibben uttered:
[...]
> Make sure the "problem" database isn't configured for auto-close, also
> make sure it's not the target for log-shipping, or some other process
> that might take it offline to do a restore, etc.
Hi Tracy et al,
Thanks for all your advice; so far it looks like the auto-close 'feature'
was most likely the root of my problems (or at least a major factor...);
the system seems to be performing rather more reliably with that disabled.
Cheers,
Alex.|||Alex Craven wrote:
> On Fri, 29 Sep 2006 08:14:30 -0500, Tracy McKibben uttered:
> [...]
>> Make sure the "problem" database isn't configured for auto-close, also
>> make sure it's not the target for log-shipping, or some other process
>> that might take it offline to do a restore, etc.
>
> Hi Tracy et al,
> Thanks for all your advice; so far it looks like the auto-close 'feature'
> was most likely the root of my problems (or at least a major factor...);
> the system seems to be performing rather more reliably with that disabled.
> Cheers,
> Alex.
>
That auto-close option should NEVER be enabled for a production
database. Another one to watch out for is auto-shrink.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Intermittent connection dramas: "Cannot open user default database..."

Hi,
I've been experiencing some very strange, intermittent failures connecting
to an MS SQL database... basically at seemingly random times the server
decides that it can't open the requested database (reporting error 4062:
"Cannot open user default database. Using master database instead"). I've
so far been unable to track down any useful background to this error
message; has anyone on this group come across similar problems? Any clues
as to what might be triggering this sort of fault? More specific details
follow:
* In the majority of cases, connection is established correctly (albeit
sometimes a little slowly).
* Connections are being made to the local machine, using straightforward,
static code. The problem isn't something trivial like a mis-typed
database name, incorrect password etc. Re-running the SAME CODE will
eventually succeed.
* The server isn't particularly heavily loaded, and I can perceive no
pattern to the fault (it seems no more prevalent when the
server's busy, nor after any particular events).
* Nothing relevant is documented in the Event Viewer, nor the server logs.
Am I looking in the right place'
* We're running SQL Server 2005 Express Edition, latest service pack...
are there any connection rate limitations on this software? All
documentation I've seen indicates that there are not, but this behaviour
almost suggests that there may be...
* Connecting (from local machine) using php mssql module (latest stable
version).
* Although possibly symptomatic of a DoS, there's no other evidence to
suggest that this is the case (TCP to the service is firewalled).
Would be grateful for any advice on this issue.
Cheers,
Alex.Alex Craven wrote:
> Hi,
> I've been experiencing some very strange, intermittent failures connecting
> to an MS SQL database... basically at seemingly random times the server
> decides that it can't open the requested database (reporting error 4062:
> "Cannot open user default database. Using master database instead"). I've
> so far been unable to track down any useful background to this error
> message; has anyone on this group come across similar problems? Any clues
> as to what might be triggering this sort of fault? More specific details
> follow:
> * In the majority of cases, connection is established correctly (albeit
> sometimes a little slowly).
> * Connections are being made to the local machine, using straightforward,
> static code. The problem isn't something trivial like a mis-typed
> database name, incorrect password etc. Re-running the SAME CODE will
> eventually succeed.
> * The server isn't particularly heavily loaded, and I can perceive no
> pattern to the fault (it seems no more prevalent when the
> server's busy, nor after any particular events).
> * Nothing relevant is documented in the Event Viewer, nor the server logs.
> Am I looking in the right place'
> * We're running SQL Server 2005 Express Edition, latest service pack...
> are there any connection rate limitations on this software? All
> documentation I've seen indicates that there are not, but this behaviour
> almost suggests that there may be...
> * Connecting (from local machine) using php mssql module (latest stable
> version).
> * Although possibly symptomatic of a DoS, there's no other evidence to
> suggest that this is the case (TCP to the service is firewalled).
>
> Would be grateful for any advice on this issue.
> Cheers,
> Alex.
>
>
Make sure the "problem" database isn't configured for auto-close, also
make sure it's not the target for log-shipping, or some other process
that might take it offline to do a restore, etc.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Do you periodically rename, remove, or detach the database?
Do you have any jobs that put the database into single-user mode or
otherwise alter the availability of the database?|||On Fri, 29 Sep 2006 08:14:30 -0500, Tracy McKibben uttered:
[...]
> Make sure the "problem" database isn't configured for auto-close, also
> make sure it's not the target for log-shipping, or some other process
> that might take it offline to do a restore, etc.
Hi Tracy et al,
Thanks for all your advice; so far it looks like the auto-close 'feature'
was most likely the root of my problems (or at least a major factor...);
the system seems to be performing rather more reliably with that disabled.
Cheers,
Alex.|||Alex Craven wrote:
> On Fri, 29 Sep 2006 08:14:30 -0500, Tracy McKibben uttered:
> [...]
>
> Hi Tracy et al,
> Thanks for all your advice; so far it looks like the auto-close 'feature'
> was most likely the root of my problems (or at least a major factor...);
> the system seems to be performing rather more reliably with that disabled.
> Cheers,
> Alex.
>
That auto-close option should NEVER be enabled for a production
database. Another one to watch out for is auto-shrink.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

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