October 15, 2014 at 6:12 am
hi sql folks,
I have a procedure which delete the rows from 5 tables . i wrote 5 delete statements. still sql engine executing all 5 delete tables parallely. i want to execute one delete statement , after executing second delete table ...so on..
because of transactionlog got fulled, procedure got failed.
please help me on this..
October 15, 2014 at 6:20 am
If you wrote 5 delete statements in a single procedure, they will be executed one by one. SQL does not run multiple statements in a stored proc in parallel.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 15, 2014 at 6:23 am
Thanks for the quick response.. as always you are quick.. i always read ya replies to other posts and learn from you .
thanks again..
October 15, 2014 at 7:50 am
If the transaction log was filled by these deletes, you might be better off breaking them apart and calling them separately.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 15, 2014 at 8:41 am
If you're deleting all the rows from the tables, you might want to consider TRUNCATE.
October 15, 2014 at 9:20 am
i am not deleting all the records. i am Deletig on some logic.thats y i am using delete statement.
October 15, 2014 at 11:37 am
Consider posting your script so we can review it. How many rows are being deleted? You might want to consider deleting in batches and deleting a few at a time. But without seeing code it is next to impossible for us to help you figure out what the issue is.
One other thing...what is the database's log file to to grow by? Maybe it can't grow fast enough for the deletes.
-SQLBill
October 15, 2014 at 12:23 pm
Are running these 5 delete statements inside a transaction?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 15, 2014 at 1:36 pm
You don't have any triggers or cascading deletes here, do you?
October 16, 2014 at 4:04 am
i have 5 diferent tables minimum they contains 10 millions of records in it. every table contains column called date , and also it may contains nearly 25 days of inventory. all i need to find required dates , remaining i need to delete from the tables.
create proc proc_name as
logic for finding required dates and storing in to a table.
5 delete statements as follows.
delete from tableanem1 where date not (select date from temp table)
delete from tableanem2 where date not (select date from temp table)
delete from tableanem3 where date not (select date from temp table)
delete from tableanem4 where date not (select date from temp table)
delete from tableanem5 where date not (select date from temp table)
October 16, 2014 at 6:19 am
raghava_tg (10/16/2014)
i have 5 diferent tables minimum they contains 10 millions of records in it. every table contains column called date , and also it may contains nearly 25 days of inventory. all i need to find required dates , remaining i need to delete from the tables.create proc proc_name as
logic for finding required dates and storing in to a table.
5 delete statements as follows.
delete from tableanem1 where date not (select date from temp table)
delete from tableanem2 where date not (select date from temp table)
delete from tableanem3 where date not (select date from temp table)
delete from tableanem4 where date not (select date from temp table)
delete from tableanem5 where date not (select date from temp table)
This is really not very helpful. At the very least you should post the table definitions including indexes. Also some actual code instead of this pseudocode would be a big help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 16, 2014 at 6:47 am
Sean Lange (10/16/2014)
This is really not very helpful. At the very least you should post the table definitions including indexes. Also some actual code instead of this pseudocode would be a big help.
At least we know there's a NOT and a temp table. The DDL and actual DML will help.
October 16, 2014 at 8:18 am
If you are deleting the majority of the records and keeping only a few it might be better to copy only the ones you want to a new table.
October 16, 2014 at 9:10 am
You could try deleting the rows in batches. According to your recovery model, there'll be differences on how would you need to code it.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply