I need to delete thousands of records from a table, probably best via SQL Query Analyser and SQL code, but the TEMPDB keeps filling-up and making the d/b "suspect" - so I cannot use it thereafter. I cannot use the TRUNCATE command because I want to delete "some" records from the table, not "all" of them, and the "delete from ..." command seems to add transactions whether I like it or not. I am not a SQL expert and am having real probs here ... can anyone help?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
SQL 6.5 question re: TEMPDB filling up !!
- Thread starter CInman
- Start date
rajeevnandanmishra
Programmer
Hi CInman,
It seems one of the situation,
1. you are deleting a large bunch of data in one shot.
2. you are deleting a large bunch of data within BEGIN TRANS / END TRANS
3. Your tempdb size is quite small.
In Case of 1 and 2 please delete data in small shots like stating :
SET ROWCOUNT 10000
--Run In a Loop as per your choice
DELETE myTable WHERE myCondition
--Loop End
SET ROWCOUNT 0
In this way you are not going to increase the tempdb.
If it is case 3, increase the size of tempdb.
It seems one of the situation,
1. you are deleting a large bunch of data in one shot.
2. you are deleting a large bunch of data within BEGIN TRANS / END TRANS
3. Your tempdb size is quite small.
In Case of 1 and 2 please delete data in small shots like stating :
SET ROWCOUNT 10000
--Run In a Loop as per your choice
DELETE myTable WHERE myCondition
--Loop End
SET ROWCOUNT 0
In this way you are not going to increase the tempdb.
If it is case 3, increase the size of tempdb.
- Thread starter
- #3
rajeevnandanmishra
Programmer
Yep! Defenitely!
You should put rowcount statement outside the loop because it is of no use to set rowcount 0 and then again set it to 2000 each time.
You should put rowcount statement outside the loop because it is of no use to set rowcount 0 and then again set it to 2000 each time.
The solution of breaking up your delete architecture is really the only way to go here. We had the exact same issue and we had tried everything within SQL Server to try and eliminate the TempDB Fillup. It is important to run the query analyzer and "watch" the tempDB closely. You should never let it get above 50% of the total size of the TempDB. If it does, simply reduce your "rowcount". Also, make sure that it truncates after each iteration within the loop. Joseph Logan
jlogan@softsource.net
jlogan@softsource.net
Joseph!
I need a clarification on your answer, and that is because I don't have a lot experience with SQL 6.5:
Truncate after each interaction or after each batch?
Also when you say truncate would that be checkpoint?
Thanks
Al AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"
I need a clarification on your answer, and that is because I don't have a lot experience with SQL 6.5:
Truncate after each interaction or after each batch?
Also when you say truncate would that be checkpoint?
Thanks
Al AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"
- Status
Similar threads
- Question