Job Scheduler fails and Managing Tempdb

Hello,
I have two issues, hoping someone can help.
Issue 1. I have various DTS packages that copy data from Progress
Database to Sql Data Warehouse. The ODBC Connection is stable and
packages have been auto scheduled by creating a job that is managed by
the SQL Agent service to run daily at night. The packages work fine
only in two cases, when running manually by using the DTSRun.exe
command line utility and when the job is manually run in the SQL Agent
service. Auto run is alwayz showing a status failer, I'm now using
windows scheduler and my packs are now always a success.
My question is why is SQL Agent not reliable? I'm 100% sure that there
is absolutely nothing wrong with the packets as they run error free
when manually ran.
Issue 2. My data warehouse is 28 G in size this makes the tempdb to
grow bigger everyday. To downsize it I stop and start the sql service
manually, can you please assist me with a batch command to auto start
and stop the service?
Thanking you in advance
Babalwa
1) This most likely is a permissions issue. Check that the account that SQL
Agent is running under has the appropriate permissions to execute everything
in your DTS package. It can be running under the LocalSystem account, which
doesn't have any permissions outside the local computer.
2) It's not much use shrinking tempdb, as it will grow again when you use
the server. And when tempdb auto grows that will only slow down your server.
You can check however if there are any transaction that stay open in tempdb
for a long time, cause extra space to be used, and prevent reuse of the
transaction log. Use DBCC OPENTRAN ('tempdb') to see if you have any old
transactions.
Otherwise the size of tempdb is something you have to live with, with your
current setup. Check if you can either change your applications and
databases, so tempdb is needed less, or you might have to get more harddisk
space.
Jacco Schalkwijk
SQL Server MVP
"Babalwa Magwentshu" <babalwa@.hotmail.com> wrote in message
news:5999368f.0407280128.75bd9322@.posting.google.c om...
> Hello,
> I have two issues, hoping someone can help.
> Issue 1. I have various DTS packages that copy data from Progress
> Database to Sql Data Warehouse. The ODBC Connection is stable and
> packages have been auto scheduled by creating a job that is managed by
> the SQL Agent service to run daily at night. The packages work fine
> only in two cases, when running manually by using the DTSRun.exe
> command line utility and when the job is manually run in the SQL Agent
> service. Auto run is alwayz showing a status failer, I'm now using
> windows scheduler and my packs are now always a success.
> My question is why is SQL Agent not reliable? I'm 100% sure that there
> is absolutely nothing wrong with the packets as they run error free
> when manually ran.
> Issue 2. My data warehouse is 28 G in size this makes the tempdb to
> grow bigger everyday. To downsize it I stop and start the sql service
> manually, can you please assist me with a batch command to auto start
> and stop the service?
> Thanking you in advance
> Babalwa
|||Thank you for your response Jacco,
Let me briefly expand on the issue, I am the domain administrator and
have designed the data warehouse packets and it's schedules under my
administrative password. I have checked the permissions, security
won't be the issue. Correct me if I'm wrong, this is how I understand
the security concept - for packages created under a Microsoft Windows
NT 4.0 or Microsoft Windows 2000 account, the job runs under the
security context of the account that started SQL Server Agent.
Thank you again,
babalwa@.hotmail.com (Babalwa Magwentshu) wrote in message news:<5999368f.0407280128.75bd9322@.posting.google. com>...
> Hello,
> I have two issues, hoping someone can help.
> Issue 1. I have various DTS packages that copy data from Progress
> Database to Sql Data Warehouse. The ODBC Connection is stable and
> packages have been auto scheduled by creating a job that is managed by
> the SQL Agent service to run daily at night. The packages work fine
> only in two cases, when running manually by using the DTSRun.exe
> command line utility and when the job is manually run in the SQL Agent
> service. Auto run is alwayz showing a status failer, I'm now using
> windows scheduler and my packs are now always a success.
> My question is why is SQL Agent not reliable? I'm 100% sure that there
> is absolutely nothing wrong with the packets as they run error free
> when manually ran.
> Issue 2. My data warehouse is 28 G in size this makes the tempdb to
> grow bigger everyday. To downsize it I stop and start the sql service
> manually, can you please assist me with a batch command to auto start
> and stop the service?
> Thanking you in advance
> Babalwa