|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Delete Without Writing To Transaction LogI am using a simple
DELETE FROM tblMyTable I was told the reason it takes 4.5 hours is because of writing to the transaction log. Can I add something to this statement to bypass the transaction log writing? Thanks so much in advance. Truncate table tbl_MyTable.
Perayu Show quote >I am using a simple > DELETE > FROM tblMyTable > > I was told the reason it takes 4.5 hours is because of writing to the > transaction log. > Can I add something to this statement to bypass the transaction log > writing? > > Thanks so much in advance. Thanks for your help.
It won't let me do that. Server: Msg 4712, Level 16, State 1, Line 1 Cannot truncate table 'tblMyTable' because it is being referenced by a FOREIGN KEY constraint. Show quote "Perayu" wrote: > Truncate table tbl_MyTable. > > Perayu > > > >I am using a simple > > DELETE > > FROM tblMyTable > > > > I was told the reason it takes 4.5 hours is because of writing to the > > transaction log. > > Can I add something to this statement to bypass the transaction log > > writing? > > > > Thanks so much in advance. > > > How is your foreign key constaint set up? Do you want to delete from child
table as well? If so, drop the contraint first, truncate parent table. Delete from child table by joining to parent table using Right OUTER JOIN clause. Recreate the constraint. Otherwise, spend 5 hours in wait until the transaction is fully committed. Nitin Show quote "alison" wrote: > Thanks for your help. > It won't let me do that. > Server: Msg 4712, Level 16, State 1, Line 1 > Cannot truncate table 'tblMyTable' because it is being referenced > by a FOREIGN KEY constraint. > > "Perayu" wrote: > > > Truncate table tbl_MyTable. > > > > Perayu > > > > > > >I am using a simple > > > DELETE > > > FROM tblMyTable > > > > > > I was told the reason it takes 4.5 hours is because of writing to the > > > transaction log. > > > Can I add something to this statement to bypass the transaction log > > > writing? > > > > > > Thanks so much in advance. > > > > > > No, you have to write to the log? Who told you it takes 4.5 hours because
of this? Do you have evidence of this, or is someone just pulling a guess out of their...back pocket :) -- Show quote---------------------------------------------------------------------------- Louis Davidson - http://spaces.msn.com/members/drsql/ SQL Server MVP "Arguments are to be avoided: they are always vulgar and often convincing." (Oscar Wilde) "alison" <ali***@discussions.microsoft.com> wrote in message news:5A0D2986-E67D-4C09-AE9B-90BA5D77EE4B@microsoft.com... >I am using a simple > DELETE > FROM tblMyTable > > I was told the reason it takes 4.5 hours is because of writing to the > transaction log. > Can I add something to this statement to bypass the transaction log > writing? > > Thanks so much in advance. I'm sorry if I'm being a pain. I'm new at this.
Yesterday I did a simple DELETE statement before I left the office. When I came in this morning I saw that it ran for 4.5 hours. The conversion on this table takes even longer. I don't need this table to develop the reports, but it's in the conversion ..cmd script and I was told not to DROP it. I do really appreciate everyone who's helped me so far. We're still throwing ideas around here too. Show quote "Louis Davidson" wrote: > No, you have to write to the log? Who told you it takes 4.5 hours because > of this? Do you have evidence of this, or is someone just pulling a guess > out of their...back pocket :) > > -- > ---------------------------------------------------------------------------- > Louis Davidson - http://spaces.msn.com/members/drsql/ > SQL Server MVP > "Arguments are to be avoided: they are always vulgar and often convincing." > (Oscar Wilde) > > "alison" <ali***@discussions.microsoft.com> wrote in message > news:5A0D2986-E67D-4C09-AE9B-90BA5D77EE4B@microsoft.com... > >I am using a simple > > DELETE > > FROM tblMyTable > > > > I was told the reason it takes 4.5 hours is because of writing to the > > transaction log. > > Can I add something to this statement to bypass the transaction log > > writing? > > > > Thanks so much in advance. > > > Is this a standalone table? No indexes, no keys, no triggers, etc? If so,
then use TRUNCATE TABLE for sure. Either way, open up perfmon and watch the Disk Queue items (amongst others), and see if you have a lot of queuing, and check CPU, etc. You may have really slow disk performance. It is hard to tell without doing more research. But if TRUNCATE works, then this is the best idea. For loading the data, consider dropping any indexes if no one is using the table during thr conversion, building the indexes once is cheaper than maintaining them during a large load. -- Show quote---------------------------------------------------------------------------- Louis Davidson - http://spaces.msn.com/members/drsql/ SQL Server MVP "Arguments are to be avoided: they are always vulgar and often convincing." (Oscar Wilde) "alison" <ali***@discussions.microsoft.com> wrote in message news:77DD37EE-C6BC-4E0E-A28F-21524ED8429E@microsoft.com... > I'm sorry if I'm being a pain. I'm new at this. > Yesterday I did a simple DELETE statement before I left the office. > When I came in this morning I saw that it ran for 4.5 hours. The > conversion > on this table takes even longer. > I don't need this table to develop the reports, but it's in the conversion > .cmd script and I was told not to DROP it. > I do really appreciate everyone who's helped me so far. We're still > throwing > ideas around here too. > > "Louis Davidson" wrote: > >> No, you have to write to the log? Who told you it takes 4.5 hours >> because >> of this? Do you have evidence of this, or is someone just pulling a >> guess >> out of their...back pocket :) >> >> -- >> ---------------------------------------------------------------------------- >> Louis Davidson - http://spaces.msn.com/members/drsql/ >> SQL Server MVP >> "Arguments are to be avoided: they are always vulgar and often >> convincing." >> (Oscar Wilde) >> >> "alison" <ali***@discussions.microsoft.com> wrote in message >> news:5A0D2986-E67D-4C09-AE9B-90BA5D77EE4B@microsoft.com... >> >I am using a simple >> > DELETE >> > FROM tblMyTable >> > >> > I was told the reason it takes 4.5 hours is because of writing to the >> > transaction log. >> > Can I add something to this statement to bypass the transaction log >> > writing? >> > >> > Thanks so much in advance. >> >> >> You guys are awesome. Thanks so much for the time to help me with this.
I think we're going to copy the conversion scripts to my local drive, and in the .cmd file, comment out this table. Well, that's the initial plan. We need to look at it a bit further. But that way I won't have to deal with it when I delete, restore, or convert the database. I just wanted to say how greatful I am for your help, and for not making this newbie feel dumb. Thanks again. Show quote "Louis Davidson" wrote: > Is this a standalone table? No indexes, no keys, no triggers, etc? If so, > then use TRUNCATE TABLE for sure. > > Either way, open up perfmon and watch the Disk Queue items (amongst others), > and see if you have a lot of queuing, and check CPU, etc. You may have > really slow disk performance. It is hard to tell without doing more > research. > > But if TRUNCATE works, then this is the best idea. For loading the data, > consider dropping any indexes if no one is using the table during thr > conversion, building the indexes once is cheaper than maintaining them > during a large load. > > -- > ---------------------------------------------------------------------------- > Louis Davidson - http://spaces.msn.com/members/drsql/ > SQL Server MVP > "Arguments are to be avoided: they are always vulgar and often convincing." > (Oscar Wilde) > > "alison" <ali***@discussions.microsoft.com> wrote in message > news:77DD37EE-C6BC-4E0E-A28F-21524ED8429E@microsoft.com... > > I'm sorry if I'm being a pain. I'm new at this. > > Yesterday I did a simple DELETE statement before I left the office. > > When I came in this morning I saw that it ran for 4.5 hours. The > > conversion > > on this table takes even longer. > > I don't need this table to develop the reports, but it's in the conversion > > .cmd script and I was told not to DROP it. > > I do really appreciate everyone who's helped me so far. We're still > > throwing > > ideas around here too. > > > > "Louis Davidson" wrote: > > > >> No, you have to write to the log? Who told you it takes 4.5 hours > >> because > >> of this? Do you have evidence of this, or is someone just pulling a > >> guess > >> out of their...back pocket :) > >> > >> -- > >> ---------------------------------------------------------------------------- > >> Louis Davidson - http://spaces.msn.com/members/drsql/ > >> SQL Server MVP > >> "Arguments are to be avoided: they are always vulgar and often > >> convincing." > >> (Oscar Wilde) > >> > >> "alison" <ali***@discussions.microsoft.com> wrote in message > >> news:5A0D2986-E67D-4C09-AE9B-90BA5D77EE4B@microsoft.com... > >> >I am using a simple > >> > DELETE > >> > FROM tblMyTable > >> > > >> > I was told the reason it takes 4.5 hours is because of writing to the > >> > transaction log. > >> > Can I add something to this statement to bypass the transaction log > >> > writing? > >> > > >> > Thanks so much in advance. > >> > >> > >> > > > Additionaly,
check if there are any jobs running at the same time affecting this table. There could be other transactions holding locks on the rows you are trying to delete. If that is the case, truncate may also take a lot of time to complete. *** Sent via Developersdex http://www.developersdex.com *** On Thu, 8 Sep 2005 18:13:02 -0700, alison wrote:
>I'm sorry if I'm being a pain. I'm new at this. Hi alison,>Yesterday I did a simple DELETE statement before I left the office. >When I came in this morning I saw that it ran for 4.5 hours. The conversion >on this table takes even longer. >I don't need this table to develop the reports, but it's in the conversion >.cmd script and I was told not to DROP it. >I do really appreciate everyone who's helped me so far. We're still throwing >ideas around here too. Here are a few possible reasons for a delete that takes 4.5 hours. - Blocking: maybe it's been waiting to get an exclusive lock on some resource that is held by other connections, and has spent 4.5 hours doing basically nothing? - Autogrow of transaction log: all information needed to undo the transaction in case of a rollback has to go to the transaction log. This takes lots of space. If the log file is not big enough, AND it is configured to autogrow (which is the default setting), you'd see the server spending most of it's time waiting until the OS has added yet another small chunk to the log file. This can be remedied by manually growing the log file to the requirede size, and/or using batches to do the delete. (see below) - A very large table, that is refered to by many foreign key constraints in other tables - and that are probably lacking a supporting index. They might even be cascading foreign key constraints, in which case your delete operation will delete many rows from other tables as well. (But that might be the actual required effect). Adding supporting indexes might help if this is the case, as well as finding and deleting the rows to be deleted from the other tables first. - My final theory - last but certainly not least: maybe there's a trigger on the table? And in that case, most probably a very inefficient one. Good triggers use set-based logic. Bad triggers use cursors or other twisted methods to iterate over the rows one by one. The fix in this case is to optimize the trigger, or to temporary disable it (see ALTER TABLE in BOL for the syntax). (The worst example I've seen is a trigger that used SET ROWCOUNT 1, then SELECT @var = col FROM inserted ORDER BY col to get the "first", and SELECT @var = col FROM inserted WHERE col > @var ORDER BY col to get each "next" value - resulting in 10 10-row sorts if 10 rows were affected, 100 100-row sorts if 100 rows were affected, and ... well, you can guess how it slowed down on mega-multi-row operations.....) Example of doing the delete in batches: (Note: this should NOT be enclosed in a transaction!!!) -- Modify the command below, or remove it - but don't forget -- to backup manually before the delete, since the TRUNCATE_ONLY below -- will trash your ability to recover BACKUP DATABASE MyDatabase TO MyBackupDevice SET ROWCOUNT 100000 -- play around to find the "good" value WHILE 1 = 1 BEGIN DELETE FROM MyTable WHERE -- your where clause goes here IF @@ROWCOUNT = 0 BREAK -- Empty the transaction log BACKUP LOG MyDatabase WITH TRUNCATE_ONLY END SET ROWCOUNT 0 -- never forget to reset rowcount!! -- Modify the command below, or remove it - but don't forget -- to backup manually after the delete, since the TRUNCATE_ONLY above -- has trashed your ability to recover. BACKUP DATABASE MyDatabase TO MyBackupDevice Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address) Hugo,
I replied this in the wrong spot and wasn't sure if you read it. Thanks so much for the time to help me with this. You are awesome. I think we're going to copy the conversion scripts to my local drive, and in the .cmd file, comment out this table. Well, that's the initial plan. We need to look at it a bit further. But that way I won't have to deal with it when I delete, restore, or convert the database. I just wanted to say how greatful I am for your help, and for not making this newbie feel dumb. Thanks again. Show quote "Hugo Kornelis" wrote: > On Thu, 8 Sep 2005 18:13:02 -0700, alison wrote: > > >I'm sorry if I'm being a pain. I'm new at this. > >Yesterday I did a simple DELETE statement before I left the office. > >When I came in this morning I saw that it ran for 4.5 hours. The conversion > >on this table takes even longer. > >I don't need this table to develop the reports, but it's in the conversion > >.cmd script and I was told not to DROP it. > >I do really appreciate everyone who's helped me so far. We're still throwing > >ideas around here too. > > Hi alison, > > Here are a few possible reasons for a delete that takes 4.5 hours. > > - Blocking: maybe it's been waiting to get an exclusive lock on some > resource that is held by other connections, and has spent 4.5 hours > doing basically nothing? > > - Autogrow of transaction log: all information needed to undo the > transaction in case of a rollback has to go to the transaction log. This > takes lots of space. If the log file is not big enough, AND it is > configured to autogrow (which is the default setting), you'd see the > server spending most of it's time waiting until the OS has added yet > another small chunk to the log file. This can be remedied by manually > growing the log file to the requirede size, and/or using batches to do > the delete. (see below) > > - A very large table, that is refered to by many foreign key constraints > in other tables - and that are probably lacking a supporting index. They > might even be cascading foreign key constraints, in which case your > delete operation will delete many rows from other tables as well. (But > that might be the actual required effect). Adding supporting indexes > might help if this is the case, as well as finding and deleting the rows > to be deleted from the other tables first. > > - My final theory - last but certainly not least: maybe there's a > trigger on the table? And in that case, most probably a very inefficient > one. Good triggers use set-based logic. Bad triggers use cursors or > other twisted methods to iterate over the rows one by one. The fix in > this case is to optimize the trigger, or to temporary disable it (see > ALTER TABLE in BOL for the syntax). > (The worst example I've seen is a trigger that used SET ROWCOUNT 1, then > SELECT @var = col FROM inserted ORDER BY col to get the "first", and > SELECT @var = col FROM inserted WHERE col > @var ORDER BY col to get > each "next" value - resulting in 10 10-row sorts if 10 rows were > affected, 100 100-row sorts if 100 rows were affected, and ... well, you > can guess how it slowed down on mega-multi-row operations.....) > > > Example of doing the delete in batches: > (Note: this should NOT be enclosed in a transaction!!!) > > -- Modify the command below, or remove it - but don't forget > -- to backup manually before the delete, since the TRUNCATE_ONLY below > -- will trash your ability to recover > BACKUP DATABASE MyDatabase TO MyBackupDevice > SET ROWCOUNT 100000 -- play around to find the "good" value > WHILE 1 = 1 > BEGIN > DELETE FROM MyTable > WHERE -- your where clause goes here > IF @@ROWCOUNT = 0 BREAK > -- Empty the transaction log > BACKUP LOG MyDatabase WITH TRUNCATE_ONLY > END > SET ROWCOUNT 0 -- never forget to reset rowcount!! > -- Modify the command below, or remove it - but don't forget > -- to backup manually after the delete, since the TRUNCATE_ONLY above > -- has trashed your ability to recover. > BACKUP DATABASE MyDatabase TO MyBackupDevice > > > Best, Hugo > -- > > (Remove _NO_ and _SPAM_ to get my e-mail address) > no, but you can use truncate table, which minimizes logging.
Show quote "alison" <ali***@discussions.microsoft.com> wrote in message news:5A0D2986-E67D-4C09-AE9B-90BA5D77EE4B@microsoft.com... > I am using a simple > DELETE > FROM tblMyTable > > I was told the reason it takes 4.5 hours is because of writing to the > transaction log. > Can I add something to this statement to bypass the transaction log writing? > > Thanks so much in advance. Hi
Truncate can only minimize logging, contrary to popular myth that It avoids. Still if you face problem with delete you can probably do one of the following 1)PIN TABLE TO MEMORY, If you have enough memory 2)SPREAD TABLE TO different file groups to achive maximum cpu optimality. Regards R.D Show quote "Brian Selzer" wrote: > no, but you can use truncate table, which minimizes logging. > "alison" <ali***@discussions.microsoft.com> wrote in message > news:5A0D2986-E67D-4C09-AE9B-90BA5D77EE4B@microsoft.com... > > I am using a simple > > DELETE > > FROM tblMyTable > > > > I was told the reason it takes 4.5 hours is because of writing to the > > transaction log. > > Can I add something to this statement to bypass the transaction log > writing? > > > > Thanks so much in advance. > > > I imagine if it took four hours that pinning the table to memory is probably
not the best idea unless they have all the data, logs, os, mp3s, wmvs, etc on 1 500 GB drive :) The second one is a good possibility, but not the first place to go. -- Show quote---------------------------------------------------------------------------- Louis Davidson - http://spaces.msn.com/members/drsql/ SQL Server MVP "Arguments are to be avoided: they are always vulgar and often convincing." (Oscar Wilde) "R.D" <R*@discussions.microsoft.com> wrote in message news:408CFD0E-7B28-456D-B6CF-0140F80495AD@microsoft.com... > Hi > Truncate can only minimize logging, contrary to popular myth that It > avoids. > Still if you face problem with delete you can probably do one of the > following > 1)PIN TABLE TO MEMORY, If you have enough memory > 2)SPREAD TABLE TO different file groups to achive maximum cpu optimality. > > Regards > R.D > "Brian Selzer" wrote: > >> no, but you can use truncate table, which minimizes logging. >> "alison" <ali***@discussions.microsoft.com> wrote in message >> news:5A0D2986-E67D-4C09-AE9B-90BA5D77EE4B@microsoft.com... >> > I am using a simple >> > DELETE >> > FROM tblMyTable >> > >> > I was told the reason it takes 4.5 hours is because of writing to the >> > transaction log. >> > Can I add something to this statement to bypass the transaction log >> writing? >> > >> > Thanks so much in advance. >> >> >>  |
|||||||||||||||||||||||
Other interesting topics