Frustration.. slow performace when editing a DTS

I have tried many things and even the worst case thing which I was trying to avoid i.e. uninstalled MSDE SP3 and Analysis Services and service packs but the problem is still not solved...

Whenever I open a DTS in design view and double click on the link (the line connecting the two) between the source and destination servers the PC goes to sleep and comes back after a long time and then the same problem occurs when I press on the transformation tab...

I know this may sound weird but that really is the case :eek:

Please help :oSounds like the DTS designer can not connect to one of the machines that the Data Transform Task is moving data to and/or from. Check the connection parameters, and make sure you can connect to all of the machines.|||Thanks for the reply.

The source is a MS-SQL server on the LAN while the destination is MSDE SP3 on my box. I have been doing similar data importing related tasks (with the same settings, source, and destination) in the past but never experienced this problem. I can connect to both machines so that part is also covered.

Connection parameters is new for me so can you please guide how to go about checking the connection parameters i.e. from where are these defined and what should be the settings??|||While still trying to solve the problem, I brought up task manager and I have concluded that everytime the problem happens the MMC.EXE process is choking the CPU...

Does this provide any clues?|||Connection parameters is new for me so can you please guide how to go about checking the connection parameters i.e. from where are these defined and what should be the settings??

Just double-click on the connections and check the login, machine name, and database. If all are good, then it is something else.

frequent periods of slow response

We are running SQL 7.0 on a quad-pentium (? MHz)server.
We have 10-12 production databases on the server, a couple
of them, financial/personnel & Prosecutor Dialog
(atty/courts)) are used quite heavily, but still should
not be enough to bring processing to a stop, quite often.
The system runs OK most of the time (50-60%), slow (20-30)
and dog-slow (10-20%). I have ran performance monitor &
cannot see any coorelation between any values there &
response. I'm not sure of our current patch levels (OS-
W2000 or SQL7.0). Tech mgr. says it appears that one or
more programs is starting & then not releasing the memory
back. Main memory fills and then swap space fills. If
this is the case, something clears the space, because the
system does slow down & sometimes stops for 30-60-180
seconds, but then speeds up, slow & quick, slow, stop &
quick, over & over. No pattern during operations hours,
and no specific processing going on, large searches, etc.
How can I identify which program(s) may be hogging memory,
or what other potential problems could there be, e.g.
behind on patch levels, server and/or SQL7.0 optimization
variables not set correctly, whatever. Any ideas on where
to keep looking to resolve significant response problem.If you've got a paging problem, you've likely got a fundamental resource
allocation / configuration problem on that server.
In an attempt to make an easy kill, I'd first use the task manager to look
for processes with excessive memory usage during a period of slow
responsiveness. You could set up an alert to inform you when this occurs
immediately so you can get to the console in time, or perhaps even start a
log.
You should trace the PagingFile object under a SystemMonitor (perfmon if
you're under NT4) log & check it after periods of slow response. This will
confirm whether you have a paging problem. It's not uncommon for SQL Server
to consume the available memory on the server (as it should) but then some
other application require memory unexpectedly. This is sometimes as simple
as a virus checker or other scheduled app..
I'd also track the Process\Working Set counter for the SQL Server process
instance to see if it's SQL Server causing the problem in a log.
Whatever the case, it sounds like you need to establish which app is causing
the memory usage spike & plan memory allocation on that server more
carefully. It might be a case of setting a max memory config for the SQL
Server so other apps can get at memory when they need it without causing
paging on the server.
HTH
Regards,
Greg Linwood
SQL Server MVP
"errol" <mikeu@.co.davis.ut.us> wrote in message
news:8d8601c37efa$eb26acd0$a601280a@.phx.gbl...
> We are running SQL 7.0 on a quad-pentium (? MHz)server.
> We have 10-12 production databases on the server, a couple
> of them, financial/personnel & Prosecutor Dialog
> (atty/courts)) are used quite heavily, but still should
> not be enough to bring processing to a stop, quite often.
> The system runs OK most of the time (50-60%), slow (20-30)
> and dog-slow (10-20%). I have ran performance monitor &
> cannot see any coorelation between any values there &
> response. I'm not sure of our current patch levels (OS-
> W2000 or SQL7.0). Tech mgr. says it appears that one or
> more programs is starting & then not releasing the memory
> back. Main memory fills and then swap space fills. If
> this is the case, something clears the space, because the
> system does slow down & sometimes stops for 30-60-180
> seconds, but then speeds up, slow & quick, slow, stop &
> quick, over & over. No pattern during operations hours,
> and no specific processing going on, large searches, etc.
> How can I identify which program(s) may be hogging memory,
> or what other potential problems could there be, e.g.
> behind on patch levels, server and/or SQL7.0 optimization
> variables not set correctly, whatever. Any ideas on where
> to keep looking to resolve significant response problem.