June 27, 2014 at 6:47 am
Hi
I am trying to import data from a database on SQL Server 6.5 to a SQL Server 2000. The DTSRun job had been running successfully for about 5 months. Now the job just keeps running and doesn't throw an error message it just keeps running. I tried rebooting both servers because we have been making some network changes and the reboots didn't resolve this issue. Has anyone else run into this or does anyone know of a solution?
Thanks
June 27, 2014 at 6:59 am
Sorry I see I added this question under SQL 2012 General. I am very new to the SQL Community. Should I have posted this under some other category? This data will eventually get to SQL Server 2012 but just trying to take the first steps to get it to 2000.
June 27, 2014 at 7:18 am
Since your question is 2000 specific, you might get better responses posting it in a 2000 forum. I haven't touched a 2000 server for several years now, let alone messed with DTS. And the last time I was anywhere near a 6.5 server was 1998, so I'm absolutely no help.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 27, 2014 at 7:28 am
Thanks--I've been a DBA for 2 months now and I have 6.5, 7, 2000, 2005, 2008R2, 2012, and soon 2014
June 27, 2014 at 8:07 am
Wow! Well done.
I'm mainly working 2008 & up, so I'll be happy to help with that stuff.
Good luck on the DTS issue.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 27, 2014 at 8:11 am
Are you able to open the packages and try to run them manually?
If so, have you tried that to see where the package is getting hung?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 27, 2014 at 8:17 am
1) Try to find out the EXACT spot/query/process where the DTS package is hanging and see if you can fix the root cause. If you have source code maybe make some additions to the package to do some logging so you can isolate the cause.
2) SQL Server 6.5 has sysprocess table that you can interrogate to check for activity and blocking. Enterprise Manager's activity viewer GUI references it and you may be able to use that if you haven't already to see if something is hung inside the server. You can review that for performance issues such as IO, CPU, blocking, network info, etc. IIRC there are some related tables too you may review.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 27, 2014 at 4:41 pm
kathy.plamann (6/27/2014)
I am trying to import data from a database on SQL Server 6.5 to a SQL Server 2000. The DTSRun job had been running successfully for about 5 months. Now the job just keeps running and doesn't throw an error message it just keeps running. I tried rebooting both servers because we have been making some network changes and the reboots didn't resolve this issue. Has anyone else run into this or does anyone know of a solution?
Oh, gods, that one. Please be aware that I'm digging around in antique memories, so grain of salt everything that follows.
If you can confirm that the package was in NO WAY altered, then you're most likely ending up with an unresolved lock conflict, as was alluded to above. I only saw this when I had multiple pipes for simultaneous transfers to or from the same location, so your milage may vary as to the appropriateness of this.
Troubleshooting: First, can you run this from the package designer without error? Next, is it a consistent error or occassional?
If you can't run it from the designer you've got a place to work from that's relatively easy, as long as you're not beating on production and leaving locks/transactions open until the system resets the spids. Rip out one piece at a time until the stall stops happening. That's 1 piece. Now, stick that back in and start from the other end of the simultaneous steps and see when it stops again. That's the other. Figure out what they have in common. Stop them from doing that at the same time.
That's the most likely case, at least from my experience. I'd typically look more towards the write than the read, but 6.5 was... well... not the finest developed piece of software for its time. 2k cleaned up a lot of issues.
If it's a single event causing the stall, post what you can of it here, and definately start digging into the spid events, blocking chains, etc while it's stalled up. You need to see what it's banging its head on.
There's no easy answer to troubleshooting DTS, unfortunately.
In the worst case scenario, rebuild the package, from scratch. Copy NOTHING (except maybe the text inside of things). Determine if it's having the same problem still. Why? Because things happen over time and you want to make sure that if you can't find the problem, that the problem is actually something you can see. A new shell and new objects will make sure nothing in the DTS is corrupted.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply