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 |
|
drandall
Starting Member
2 Posts |
 Posted - 2009-05-12 : 18:50:42
|
| Hey everyone. I've run into a wall on this issue and could use some input.Something in the SQL server environment/hardware or elsewhere has created a situation where inserting into a temp table will not complete. It processes for several minutes before I have to end the process. I recently updated 3-4 of our customer report queries to use temp tables and they initially work for a few hours before the INSERT (shown below) begins to fail or not complete. There are no locks in the db and no uncompleted transactions running in tempdb (separate drive). There is lots of space on both drives and no restrictive growth on tempdb or the customers. And like I said, this query works fine for a couple hours then seem jam on this section. Restarting SQL fixes the problem temporarly, but not always. CREATE TABLE #tmp (field1 INT, field2 INT, ... field9 INT) INSERT INTO #tmp EXEC dbo.qryMyQueryAlso, if I just run the EXEC it finishes in 1 sec. So the issue seems to be the actual insert. EXEC dbo.qryMyQueryThe customer is currently running SQL 2000 Desktop SP 3 (build 760) on Server 2003 SP1 with a single 3ghz Xeon processor and 2 gb ram. Average concurrent users around 6-10. We will be moving them to SP4 in a couple days to see if by chance this alleviates the problem, but I'd like to get some ideas ready in case the SP4 upgrade does not work. Yeah, there's always 2005 Express...Thanks in advance,Dave |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-05-13 : 11:21:44
|
| how many approx records does EXEC dbo.qryMyQuery return? |
 |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2009-05-13 : 11:22:00
|
| couple of things : (1) Unless you have custom support agreement, SQL Server 2000 is out of support. (2) Yes you should at least get SP4 ASAP (3) there might be too much contention on sysobjects constantly creating and dropping objects.. try dropping the table explicitly if you are not already doing so. If that doesnt help, see if you can modify the temp table into a permanent one, and use a SPID or some kind of unique identifier to identify different executions and empty the table on a daily/hourly basis depending on the nature of your workload.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-05-13 : 11:50:22
|
You also seems to run MSDE.If possible, download SQL Server 2008 Express Edition and update.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
drandall
Starting Member
2 Posts |
Posted - 2009-05-14 : 12:01:54
|
| I ran aba_lockinfo (see link below) and found that is was locking on itself with an expression I was using in the GROUP BY (which works fine, but not on the insert for some odd reason?). The expression that caused the locks was (FLOOR(CAST(DATEDIFF(d, @DateX, @WkDate + 6) as real)/7)) which I used to group on week number based on a week begin date of @WkDate. This I replaced with the following CASE (below) which is not as flexable but does the job for a 4 week period. (CASE WHEN DATEDIFF(d, @WkDate, DateOccurred) BETWEEN 0 AND 6 THEN 1 WHEN DATEDIFF(d, @WkDate, DateOccurred) BETWEEN -7 AND -1 THEN 2 WHEN DATEDIFF(d, @WkDate, DateOccurred) BETWEEN -14 AND -8 THEN 3 WHEN DATEDIFF(d, @WkDate, DateOccurred) BETWEEN -21 AND -15 THEN 4 END)Thanks for the feedback everyone. Someday soon well upgrade everyone to 2005/2008-- but that will require a major software re-test which we don't have the time for at the moment ;). SQL 2000 SP3 has been a very stable environment for us until now. aba_lock (highly recommended SP for showing locks)http://www.sommarskog.se/sqlutil/aba_lockinfo.html~Dave |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
|
|
|
|
|
|
|
HELP--Insert into Temp Table not completing