| Author |
Topic |
|
djokerss
Starting Member
28 Posts |
 Posted - 2004-12-01 : 20:49:24
|
| i've made a proccess with a lot of temporary table, after proccess finished my disc space going low.( it proccess about over 1000 record ).so i have restart my computer to start another process to get enough disc space.what should i do so when proccess finished my disc space back to normal. fyi :my process using isql to read the script |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-12-01 : 20:51:54
|
| >> it proccess about over 1000 record>> after proccess finished my disc space going lowHow much disk space are you talking about here.This is a trivial amount of data it seems.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
djokerss
Starting Member
28 Posts |
Posted - 2004-12-01 : 21:08:14
|
| before proccessed the space about 1,2 Gb,After proceccess the space down to 500 - 600 MBAfter restart the space back to about 1 Gb |
|
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2004-12-01 : 21:16:17
|
I have more storage space on my digital camera card  Seriously though, What does the schema(s) look like on your temp table(s)Based on a simple equation it would appear you have some really long temp tables, are you handling ntext, text, or image data?Can you post the code.. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-12-01 : 21:30:45
|
"more storage space on my digital camera card"That's disgusting.  MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
djokerss
Starting Member
28 Posts |
Posted - 2004-12-01 : 21:40:48
|
| he..he...maybe my serious proble is my disc space ....back to the topic,the prossess i've made is to reproccess the transactionbecause i want to rebuild my program,the proccess like this :1. i call all transaction and store it to temporary ( select .... into #temp from xxx )2. then proses that record by record (...fetch next from ....)3. that proses is reposting transaction where i've add a trigger eacht table related with the transaction.4. every proccess transaction use begin tran .. end tranfyi : my default connection : cursor_close_on_commit not check |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-12-01 : 22:37:48
|
| for no.1, you could use a table variable instead of a temporary table, include a rowPosition as field and use that field to traverse the records using a while loopnot sure if this will make any difference at all--------------------keeping it simple... |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2004-12-05 : 09:55:22
|
| The temp tables are kept in the tempdb database. This database gets rebuilt every time that SQL gets restarted. This explains why your space gets restored.It sounds like you are using a lot of space in your temp tables relative to the amount of disk space available.There a a few options avaialble to you:1) Get more disk space.2) Move your tempdb to a different physical drive that has additional space.3) Allow the tempdb to auto-shrink.4) Perform your processing in smaller increments (e.g., Don't get ALL transactions at first)5) Perform a DBCC ShrinkDB on the tempdb after your procerssing.I'm sure there are other options. Perhaps someone can suggest a frew...HTH=========================================Let X = {All sets s such that s is not an element of s}(X element of X) ==> (X not element of X)(X not element of X) ==> (X element of X) (Bertrand Russell Paradox) |
|
|
|
|
temporary table vs disc space