Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
imsganesh
Starting Member
28 Posts |
 Posted - 2005-05-05 : 14:21:20
|
| All, i've already posted this in Admin forum. still i've this problemI've a database (db1 in server1) which is accessed by some of the DTS packages. One of the packages, is copying the data (100 million records) from one table & writing into another table (Its a simple Insert Into...select * from....). This package used to run in 45mins. For the last 2 weeks, FOR NO REASON, this package is not running at all. I tried to run over the weekend & it didn't complete even after 30hrs. So i had to cancel it. Initally i thought this could be a server problem. So, I copied one more db from (db2 from server2 which is same as server1 db & this db2 has 105 million recs) & ran the same job pointing to db2. The job ran in same 45mins. Is there any way to find out why the job suddenly stopped running while it was working fine 2 weeks before? FYI- =I didn't run any DBCC stmt or any thing of that sort!If I couldn't find out the problem, I am planning to delete this database & create a new db. In that case, do i need to change/re-create all the DTS packages pointing to this db (though the db name will be same)? (normally even when there is a change in the table definition, DTS transformation is asking to locate the table again)Please Advice!Thanks,Ganesh |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-05-09 : 04:19:59
|
| you might want to consider looking at job history, log files filling up, not enough disk space for the mdf file HTH--------------------keeping it simple... |
 |
|
|
SreenivasBora
Posting Yak Master
164 Posts |
Posted - 2005-05-09 : 16:58:29
|
| Hey Ganesh,What "jen" said is absolutely correct. Do some manual checks ...1. Check whether disk space is there or not?2. How many Indexes you have created on that table?3. Open Profiler and same time run the Same Package and identify which SQL Statement is taking lot of CPU time (milliseconds)4. Avoid Distinct and Order by combination in the QueryWith RegardsSreenivas Reddy B |
|
|
|
imsganesh
Starting Member
28 Posts |
Posted - 2005-05-10 : 00:37:25
|
| Hi Jen/Srini, First of all, Thanks for ur responses!!!1) Regd. the disk space & other things, i've enough free space for log/data files. 2) I've a composite index (of 3 fields) in this table. But I also removed the indexes & tried running the job, its not working. 3) Actually the job that is inserting the recs is a simple ONE insert qry (Insert into...Select * from...). So I donno how to check the qry using the Profiler. 4) Ofcourse, there are no DISTINCT, ORDER BY clauses in the query.Can I try re-creating the tables/databases?Once again, Thanks for your responses!-> Ganesh S |
|
|
|
|
|
|
|
|
Doubt in DTS pkgs