|
Home > Archive > MS SQL Data Warehousing > September 2005 > Very Very Urgent, Please Help
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Very Very Urgent, Please Help
|
|
| DKRReddy 2005-09-05, 8:23 pm |
| Recently we have migrated the datawarehouse server to windows 2003 server
from windows 2000 server.
Since then all dts packages are getting timedout with the following error.
Step Error Source: Microsoft OLE DB Provider for SQL Server
Step Error Description:Timeout expired
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:0
The new server is very high end server which has 8 processors and 12GB RAM,
and All dts packages are having simple deletes and data transfer tasks.
Daily updatestats was running during off hours and proper indices are
there.
Ran the checkdb on all dbs, found no errors.
I never seen these kind of erros on windows2000 and sql 2000 ee sp3.
Is there any reason that sql2000 EE SP3 is not fully supported by Windows
2003 server.
I have verified the query timeout settings on source and destination server
which is 0 (unlimited)
Please let me know if settings need to be checked.
(Migration : dettahed the databases from windows 2000 server and sql200 EE
sp3 and attached to the windows2003 server and Sql 200 EE sp3)
If any queries that were run from query analyzer are failing with the
following error afetr few minutes :
[Microsoft][ODBC SQL Server Driver]& #91;DBNETLIB]Connect
ionRead (WrapperRead()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
| |
| DKRReddy 2005-09-05, 8:23 pm |
| Info :
No new logins, all jobs are using "sa" login.
Recreated the jobs and dts packages on the new server.
Only user databases were dettached and attached and ran the checkdb against
all dbs found no errors.
"DKRReddy" <dkrreddy@hotmail.com> wrote in message
news:OeJz4AlsFHA.3604@tk2msftngp13.phx.gbl...
> Recently we have migrated the datawarehouse server to windows 2003 server
> from windows 2000 server.
> Since then all dts packages are getting timedout with the following error.
>
> Step Error Source: Microsoft OLE DB Provider for SQL Server
> Step Error Description:Timeout expired
> Step Error code: 80004005
> Step Error Help File:
> Step Error Help Context ID:0
>
>
> The new server is very high end server which has 8 processors and 12GB
RAM,
> and All dts packages are having simple deletes and data transfer tasks.
>
> Daily updatestats was running during off hours and proper indices are
> there.
> Ran the checkdb on all dbs, found no errors.
>
> I never seen these kind of erros on windows2000 and sql 2000 ee sp3.
>
> Is there any reason that sql2000 EE SP3 is not fully supported by Windows
> 2003 server.
>
> I have verified the query timeout settings on source and destination
server
> which is 0 (unlimited)
>
> Please let me know if settings need to be checked.
>
>
>
> (Migration : dettahed the databases from windows 2000 server and sql200
EE
> sp3 and attached to the windows2003 server and Sql 200 EE sp3)
>
>
> If any queries that were run from query analyzer are failing with the
> following error afetr few minutes :
>
> [Microsoft][ODBC SQL Server Driver]& #91;DBNETLIB]Connect
ionRead
(WrapperRead()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
>
>
>
| |
|
| can you create a new empty package and create a new connection to the
database?
have you validated the connections in your packages?
"DKRReddy" <dkrreddy@hotmail.com> wrote in message
news:OeJz4AlsFHA.3604@tk2msftngp13.phx.gbl...
> Recently we have migrated the datawarehouse server to windows 2003 server
> from windows 2000 server.
> Since then all dts packages are getting timedout with the following error.
>
> Step Error Source: Microsoft OLE DB Provider for SQL Server
> Step Error Description:Timeout expired
> Step Error code: 80004005
> Step Error Help File:
> Step Error Help Context ID:0
>
>
> The new server is very high end server which has 8 processors and 12GB
> RAM,
> and All dts packages are having simple deletes and data transfer tasks.
>
> Daily updatestats was running during off hours and proper indices are
> there.
> Ran the checkdb on all dbs, found no errors.
>
> I never seen these kind of erros on windows2000 and sql 2000 ee sp3.
>
> Is there any reason that sql2000 EE SP3 is not fully supported by Windows
> 2003 server.
>
> I have verified the query timeout settings on source and destination
> server
> which is 0 (unlimited)
>
> Please let me know if settings need to be checked.
>
>
>
> (Migration : dettahed the databases from windows 2000 server and sql200
> EE
> sp3 and attached to the windows2003 server and Sql 200 EE sp3)
>
>
> If any queries that were run from query analyzer are failing with the
> following error afetr few minutes :
>
> [Microsoft][ODBC SQL Server Driver]& #91;DBNETLIB]Connect
ionRead
> (WrapperRead()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
>
>
>
| |
| DKRReddy 2005-09-05, 8:23 pm |
| My Main dts package calls many other simple dts packages.
When my main dts package calls other dts packages getting timeout
immediately.
Is there any settings at package level to increase the timeout.
"Jéjé" < willgart@AAAhotmailB
BB.com> wrote in message
news:OITN%23MmsFHA.332@tk2msftngp13.phx.gbl...
> can you create a new empty package and create a new connection to the
> database?
>
> have you validated the connections in your packages?
>
>
>
> "DKRReddy" <dkrreddy@hotmail.com> wrote in message
> news:OeJz4AlsFHA.3604@tk2msftngp13.phx.gbl...
server[color=darkred
]
error.[color=darkred]
Windows[color=darkre
d]
>
| |
|
| does your packages are stored in the MSDB database?
Have you changed your main package to use the right SQL Server instance?
if your main dts package try to open a package on the <old server> and if
this server is down, then you'll receive a timeout error.
"DKRReddy" <dkrreddy@hotmail.com> wrote in message
news:Osp3hXnsFHA.236@TK2MSFTNGP11.phx.gbl...
> My Main dts package calls many other simple dts packages.
> When my main dts package calls other dts packages getting timeout
> immediately.
>
> Is there any settings at package level to increase the timeout.
>
>
>
> "Jéjé" < willgart@AAAhotmailB
BB.com> wrote in message
> news:OITN%23MmsFHA.332@tk2msftngp13.phx.gbl...
> server
> error.
> Windows
>
>
| |
| DKRReddy 2005-09-06, 3:23 am |
| All packages are refering to the new server only.If I run the package from
enterprise manager runs fine no timeout errors.
"Jéjé" < willgart@AAAhotmailB
BB.com> wrote in message
news:OHhuUhnsFHA.3404@TK2MSFTNGP09.phx.gbl...
> does your packages are stored in the MSDB database?
> Have you changed your main package to use the right SQL Server instance?
>
> if your main dts package try to open a package on the <old server> and if
> this server is down, then you'll receive a timeout error.
>
>
> "DKRReddy" <dkrreddy@hotmail.com> wrote in message
> news:Osp3hXnsFHA.236@TK2MSFTNGP11.phx.gbl...
12GB[color=darkred]
tasks.[color=darkred]
are[color=darkred]
>
| |
| Darren Gosbell 2005-09-07, 3:23 am |
| This sounds like a permissions issue. If you can run the packages from=20
Enterprise manager then the issue is not necessarily with the packages=20
themselves.=20
If the packages are executed via a SQL agent job that launches them=20
using dtsrun, then I suggest you check which account the SQL Agent=20
service is running under. It might also have something to do with the=20
owner of the Agent jobs.
Packages run via Enterprise Manager run in the security context of the=20
currently logged on user. Packages launched via SQL Agent run in the=20
context of the account that the SQL Agent Service logs in under.
--=20
Regards
Darren Gosbell
< dgosbell_at_yahoo_do
t_com>
Blog: http://www.geekswithblogs.net/darrengosbell
In article <eP$UU6osFHA.3164@TK2MSFTNGP14.phx.gbl>, dkrreddy@hotmail.com=20
says...
> All packages are refering to the new server only.If I run the package fro=
m
> enterprise manager runs fine no timeout errors.
>=20
>=20
>=20
> "J=E9j=E9" < willgart@AAAhotmailB
BB.com> wrote in message
> news:OHhuUhnsFHA.3404@TK2MSFTNGP09.phx.gbl...
?[color=darkred]
if[color=darkred]
e[color=darkred]
3[color=darkred]
g[color=darkred]
> 12GB
| |
| DKRReddy 2005-09-08, 1:23 pm |
| When running the dts package as a job , it's failing while opening the
package itself.
Security log in keep filling, is this the cause?
"Darren Gosbell" < dgosbell_at_yahoo_do
t_com> wrote in message
news:MPG. 1d88f7c738031b6f9896
85@news.microsoft.com...
This sounds like a permissions issue. If you can run the packages from
Enterprise manager then the issue is not necessarily with the packages
themselves.
If the packages are executed via a SQL agent job that launches them
using dtsrun, then I suggest you check which account the SQL Agent
service is running under. It might also have something to do with the
owner of the Agent jobs.
Packages run via Enterprise Manager run in the security context of the
currently logged on user. Packages launched via SQL Agent run in the
context of the account that the SQL Agent Service logs in under.
--
Regards
Darren Gosbell
< dgosbell_at_yahoo_do
t_com>
Blog: http://www.geekswithblogs.net/darrengosbell
In article <eP$UU6osFHA.3164@TK2MSFTNGP14.phx.gbl>, dkrreddy@hotmail.com
says...
> All packages are refering to the new server only.If I run the package from
> enterprise manager runs fine no timeout errors.
>
>
>
> "Jéjé" < willgart@AAAhotmailB
BB.com> wrote in message
> news:OHhuUhnsFHA.3404@TK2MSFTNGP09.phx.gbl...
if[color=darkred]
> 12GB
| |
|
| does the SQL Agent service run under a known account?
"DKRReddy" <dkrreddy@hotmail.com> wrote in message
news:%23SWDRmJtFHA.664@tk2msftngp13.phx.gbl...
> When running the dts package as a job , it's failing while opening the
> package itself.
> Security log in keep filling, is this the cause?
>
>
>
> "Darren Gosbell" < dgosbell_at_yahoo_do
t_com> wrote in message
> news:MPG. 1d88f7c738031b6f9896
85@news.microsoft.com...
> This sounds like a permissions issue. If you can run the packages from
> Enterprise manager then the issue is not necessarily with the packages
> themselves.
>
> If the packages are executed via a SQL agent job that launches them
> using dtsrun, then I suggest you check which account the SQL Agent
> service is running under. It might also have something to do with the
> owner of the Agent jobs.
>
> Packages run via Enterprise Manager run in the security context of the
> currently logged on user. Packages launched via SQL Agent run in the
> context of the account that the SQL Agent Service logs in under.
>
> --
> Regards
> Darren Gosbell
> < dgosbell_at_yahoo_do
t_com>
> Blog: http://www.geekswithblogs.net/darrengosbell
>
> In article <eP$UU6osFHA.3164@TK2MSFTNGP14.phx.gbl>, dkrreddy@hotmail.com
> says...
> if
>
>
| |
| Darren Gosbell 2005-09-08, 8:23 pm |
| It sounds like you are running the SQL Agent service under the local=20
system account which does not have access to the DTS packages.
The way I normally set up SQL Agent is to run it under a domain user=20
account. This has 2 advantages.
1) It can then access network resources such as file shares (Local=20
System can only access resources on the SQL Server itself)
2) You can also "debug" security issues such as the one you are now=20
having because you can log in under the same account as the SQL Agent=20
and run the packages from Enterprise manager it is often easier to see=20
exactly where your problems are occurring.
I would recommend setting up this user with the minimum require security=20
privileges. RESIST the temptation to set this user up as an=20
administrator as it could mean that any user that can schedule a job can=20
run a process under this account. If someone cracks an SA password, they=20
can schedule a job to give them full access to your machine at the OS=20
level and from there possibly out into your network.
--=20
Regards
Darren Gosbell
< dgosbell_at_yahoo_do
t_com>
Blog: http://www.geekswithblogs.net/darrengosbell
In article <#bi0pnMtFHA.596@TK2MSFTNGP12.phx.gbl>,=20
willgart@AAAhotmailB
BB.com says...[color=darkred]
> does the SQL Agent service run under a known account?
>=20
> "DKRReddy" <dkrreddy@hotmail.com> wrote in message=20
> news:%23SWDRmJtFHA.664@tk2msftngp13.phx.gbl...
m[color=darkred]
=20[color=darkred]
nd[color=darkred]
| |
| DKRReddy 2005-09-09, 11:23 am |
| No, production servers services will always run under domian admin account.
This is not the issue.
"Darren Gosbell" < dgosbell_at_yahoo_do
t_com> wrote in message
news:MPG. 1d8b89989fc5c0c79896
88@news.microsoft.com...
It sounds like you are running the SQL Agent service under the local
system account which does not have access to the DTS packages.
The way I normally set up SQL Agent is to run it under a domain user
account. This has 2 advantages.
1) It can then access network resources such as file shares (Local
System can only access resources on the SQL Server itself)
2) You can also "debug" security issues such as the one you are now
having because you can log in under the same account as the SQL Agent
and run the packages from Enterprise manager it is often easier to see
exactly where your problems are occurring.
I would recommend setting up this user with the minimum require security
privileges. RESIST the temptation to set this user up as an
administrator as it could mean that any user that can schedule a job can
run a process under this account. If someone cracks an SA password, they
can schedule a job to give them full access to your machine at the OS
level and from there possibly out into your network.
--
Regards
Darren Gosbell
< dgosbell_at_yahoo_do
t_com>
Blog: http://www.geekswithblogs.net/darrengosbell
In article <#bi0pnMtFHA.596@TK2MSFTNGP12.phx.gbl>,
willgart@AAAhotmailB
BB.com says...[color=darkred]
> does the SQL Agent service run under a known account?
>
> "DKRReddy" <dkrreddy@hotmail.com> wrote in message
> news:%23SWDRmJtFHA.664@tk2msftngp13.phx.gbl...
and[color=darkred]
| |
|
| does the profiler display something?
do you see any MSDB access when you try to open/execute a package?
have you try to force the server access by creating an alias?
you can force the TCP/IP usage or netbios usage; also try an IP address
instead-of a netbios or DNS name
"DKRReddy" <dkrreddy@hotmail.com> wrote in message
news:%23wjNDdVtFHA.3896@TK2MSFTNGP15.phx.gbl...
> No, production servers services will always run under domian admin
> account.
> This is not the issue.
>
>
> "Darren Gosbell" < dgosbell_at_yahoo_do
t_com> wrote in message
> news:MPG. 1d8b89989fc5c0c79896
88@news.microsoft.com...
> It sounds like you are running the SQL Agent service under the local
> system account which does not have access to the DTS packages.
>
> The way I normally set up SQL Agent is to run it under a domain user
> account. This has 2 advantages.
>
> 1) It can then access network resources such as file shares (Local
> System can only access resources on the SQL Server itself)
>
> 2) You can also "debug" security issues such as the one you are now
> having because you can log in under the same account as the SQL Agent
> and run the packages from Enterprise manager it is often easier to see
> exactly where your problems are occurring.
>
> I would recommend setting up this user with the minimum require security
> privileges. RESIST the temptation to set this user up as an
> administrator as it could mean that any user that can schedule a job can
> run a process under this account. If someone cracks an SA password, they
> can schedule a job to give them full access to your machine at the OS
> level and from there possibly out into your network.
>
> --
> Regards
> Darren Gosbell
> < dgosbell_at_yahoo_do
t_com>
> Blog: http://www.geekswithblogs.net/darrengosbell
>
> In article <#bi0pnMtFHA.596@TK2MSFTNGP12.phx.gbl>,
> willgart@AAAhotmailB
BB.com says...
> and
>
>
| |
| Darren Gosbell 2005-09-11, 7:23 am |
| Try logging onto the server under the domain account that the SQL Agent
services is running under and try launching the packages interactively
from Enterprise Manager.
--
Regards
Darren Gosbell
< dgosbell_at_yahoo_do
t_com>
Blog: http://www.geekswithblogs.net/darrengosbell
In article <uWxyZDatFHA.3188@TK2MSFTNGP14.phx.gbl>,
willgart@AAAhotmailB
BB.com says...[color=darkred]
> does the profiler display something?
> do you see any MSDB access when you try to open/execute a package?
>
> have you try to force the server access by creating an alias?
> you can force the TCP/IP usage or netbios usage; also try an IP address
> instead-of a netbios or DNS name
>
>
> "DKRReddy" <dkrreddy@hotmail.com> wrote in message
> news:%23wjNDdVtFHA.3896@TK2MSFTNGP15.phx.gbl...
| |
| DKRReddy 2005-09-19, 8:23 pm |
| Yes there was msdb access.The following is from sql trace.
exec sp_executesql N'exec msdb.. sp_log_dtsstep_begin
@P1, @P2, @P3', N'@P1
uniqueidentifier,@P2
nvarchar(27),@P3 datetime',
'4A1665AF-4C92-457F-B1B1-75B2BCD18E88', N'DTS_Error_Log_DB_D
AILY_Step', 'Sep
6 2005 1:42:57:000AM'
SELECT N'Testing Connection...'
EXECUTE msdb.dbo. sp_sqlagent_get_perf
_counters
exec sp_executesql N'exec msdb..sp_log_dtsstep_end @P1, @P2, @P3, @P4, @P5,
@P6, @P7, @P8', N'@P1 bigint,@P2 int,@P3 int,@P4 datetime,
@P5 float,@P6 int,@P7
nvarchar(185),@P8 bigint', 48814, 4, 1, 'Sep 6 2005 1:43:13:000AM',
1. 634300000000000e+001
, -2147467259,
N'
Step Error Source:
Microsoft OLE DB Provider for SQL Server
Step Error
Description:Timeout expired
Step Error code: 80004005
Step Error Help File:
Step Error Help Context
ID:0
', 0
exec msdb..sp_get_dtspackage N'DTS_Policy_Driver_
SSN',
'{C7FF64C3-9006-42C9-BEB7-79775F09A2D2}', null
exec msdb..sp_get_dtspackage N'DTS_DM_To_DW_ALL_D
AILY',
'{06A28751-CA51-410E-9421-907A507A1E04}', null
"Jéjé" < willgart@AAAhotmailB
BB.com> wrote in message
news:uWxyZDatFHA.3188@TK2MSFTNGP14.phx.gbl...
> does the profiler display something?
> do you see any MSDB access when you try to open/execute a package?
>
> have you try to force the server access by creating an alias?
> you can force the TCP/IP usage or netbios usage; also try an IP address
> instead-of a netbios or DNS name
>
>
> "DKRReddy" <dkrreddy@hotmail.com> wrote in message
> news:%23wjNDdVtFHA.3896@TK2MSFTNGP15.phx.gbl...
the[color=darkred]
from[color=darkred]
packages[color=darkr
ed]
the[color=darkred]
package[color=darkre
d]
>
|
|
|
|
|