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 |
|
jubilanttiger
Starting Member
19 Posts |
 Posted - 2006-05-09 : 13:50:07
|
| Hi, I am running a dozen or so of DTS Scheduled jobs which call a DTS package each. The packages basically are pooling data from views of the Production server with SQL Server 2000 to another Reporting Server with SQL Server 2000 again (Refer a representation below). Production Server(SQL Sever 2000) View ------DATA-----> Tables of Reporting Server(SQL Sever 2000) The packages have logging enabled. The jobs are scheduled to run at 1 AM everyday. The problem which I am seeing is that the jobs seem to run fine most of the times. But sometimes sporadically 1 or 2 jobs (without much of a pattern of which job it is) HANG up. HANG in the sense; the jobs are the Execution state for more than 10-15 hours, without ever completing. I have error mailing enabled in the jobs, but since jobs (which hang) are typically not failing, so I am not getting notified that the jobs did not complete. If I just go ahead and stop the job, restart it again it runs through successfully! My questions are 1. Why do these jobs HANG up? Is there anything I can do to avoid this from happening? 2. Can I can automate the process of checking that jobs did not omplete after a particular interval of time, then stop the job and restart the job again. 3. How can I get notified by e-mail if these jobs remain in the HANG state after a particular interval of time since the job started? Thanks a lot for your thoughts! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-05-09 : 13:54:55
|
| You'll need to run SQL Profiler to determine what it is doing. Run it on both servers and then view the trace when you catch a hang. Have you considered using replication instead of DTS for your reporting data? It would be more reliable and possibly faster. You could even have the reporting data only be a few seconds behind from production.Tara Kizeraka tduggan |
 |
|
|
jubilanttiger
Starting Member
19 Posts |
Posted - 2006-05-09 : 14:53:57
|
| Hi Tara,Thanks a lot for your reply!I think that Replication is a concept where you want to synchronize two databases. But for my case, I am getting the data from bunch of views which encapsulate the business logic. I then transfer the data from the views (which is de-nomarlized for reporting purposes) into the tables of the Reporting Database. If you yet think that Replication is an idea to look up, can you direct to some resources which are relevant to my problem.Thanks. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-05-09 : 15:39:26
|
| Replication can still do what you want, but it might take some work. You can replicate tables, then instead of using INSERT INTO statements to replicate the data, you can choose to use stored procedures instead. In the stored procedure code (which you would take replication's generic stored procedures then re-write them), you would handle the denormalization. After setting up replication the first time and specifying stored procedures instead of DML, you'll have stored procedures named like this: sp_MS<DMLOperation>_<TableName>. These are the ones to rewrite.I don't have any resources to give you about it though.Tara Kizeraka tduggan |
|
|
|
shell_l_d
Starting Member
17 Posts |
Posted - 2009-12-05 : 21:30:01
|
| JubilantTiger... did you ever find your answers please? I have the same problems/questions. My DTS processes Analysis (OLAP) Cubes (some of which have fact tables that are based on views). :) |
|
|
|
|
|
|
|
|
DTS Scheduled package HANGS sporadically