I wrote 50 store procedures to get and reorginize data from DB A to DB B on the same DB server(MS SQL 2000 w 2CPU/1G ram).
The size of DB A is 3.5GB.
And I run those store procedures as a batch in every night to get the data from DB A to DB B.
Those store procedures delete table from DB B first, get data from DB A, create table on DB B.
I run the store procedures on server.
But the Query analysis is often show me "connection broken".
Some one could tell me what's going on here?
There are a plethora of possibilities for why you get connection broken errors. Without me knowing any details about your network infrastructure, SQL Server hardware, or the general approach you take in your stored procedures, it is hard to determine where the problem(s) lie.
If you have a lot of general network problems with connections dying, you may want to examine and analyze how you are doing your stored procedures to see if you can improve performance there. Do things in individual batches separated by GOs and do your work using transactions.
I dunno know if this helps any...but I'd start with the SP route to make them as efficient as possible from the network I/O and latency perspective.