Cannot copy table between servers

[Luvsql]

I was able to copy all tables from a 6Gb database to our new server 2 weeks ago (it only took 30 minutes manually ie not via a job). As well, I had a sp that queries 2 sql servers. Executing this usually averaged under 2 minutes. Today, the sp doesn't seem to run at all. I've executed and had to stop it at 10 minutes (then took 2 more minutes to stop). The issue seems to be with the query from Server A (ie the non-local server), which seems to hang.

I have since tried to setup a DTS package to bring those tables that the sp are using to the local server, but in trying this, it keeps erroring "error at destination for row 845, unspecified error." I have it set to just copy table, delete destination, no identity insert. This worked 2 weeks ago, but now, it fails.

If I try another database, it works, but is 50 times slower than it was 2 weeks ago.

I have regular db maintenance running each week (last time run was on Sunday), the CPU and memory usage on that server is very low, even with the sp executing.

I have tried DBCC CHECKTABLE on the table that is failing, but it doesn't find any errors. We are using sql 2000 with sp 3a.

The sp doesn't run when querying the second server and I can't copy that table to the second server. Any suggestions?

 

[donutman]

Check your transaction log and hard drive, see if it has plenty of room to grow. What happens if you just do a table import from EM?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[HogHunter]

I agree that you ought to look at your transaction log whenever performance suddenly changes.

How large are the tables you are copying? Are you copying the entire table or selected rows?

You may want to do a two phase process for moving the data. Many ETL products work this way. Export the data to an text file. The import the text file into the other server. This eliminates the possibility that transactions are be blocked by each other. DTS can does this but make sure the export is a separate step from the import. You may even want two spearate jobs. We use a very expensive ETL tool here to much gigs of data every night and that's our methodology.