Can truncate but not delete

Hi
I have a 43,000 row table but can no longer delete the data from it all of a
sudden..it just hangs on the process and the processor shoots up to 100%. Bu
t
I have tried truncating this and it works, I know deleting logs every row
deleted but am not sure why I am getting this problem or what to monitor to
solve it.
Any help or advice would be great thanks
SammySammy
Do you have a WHERE clause in your DELETE statement?
Actually , DELETE and TRUNCATE commands are 'slightly' different in terms
how they do the job
DELETE command is fully logged (every row was deleted is recorded in TL) ,
as opposed TRUNCATE command which
logs only the deallocation of whole data pages
"Sammy" <Sammy@.discussions.microsoft.com> wrote in message
news:1B557178-E1BA-4D16-B4E8-2B9519856649@.microsoft.com...
> Hi
> I have a 43,000 row table but can no longer delete the data from it all of
> a
> sudden..it just hangs on the process and the processor shoots up to 100%.
> But
> I have tried truncating this and it works, I know deleting logs every row
> deleted but am not sure why I am getting this problem or what to monitor
> to
> solve it.
> Any help or advice would be great thanks
> Sammy|||Hi Uri,
No its just delete quotedetail it always worked before... but I get no
errors it just hangs so not sure what to check and how to solve this issue
"Uri Dimant" wrote:

> Sammy
> Do you have a WHERE clause in your DELETE statement?
>
> Actually , DELETE and TRUNCATE commands are 'slightly' different in terms
> how they do the job
> DELETE command is fully logged (every row was deleted is recorded in TL) ,
> as opposed TRUNCATE command which
> logs only the deallocation of whole data pages
> "Sammy" <Sammy@.discussions.microsoft.com> wrote in message
> news:1B557178-E1BA-4D16-B4E8-2B9519856649@.microsoft.com...
>
>|||So , do you have WHERE clause or you don't?
It is possible that blocks are occured because if you don't have an index on
the column in WHERE clause , SQL Server may lock entire table.
How many rows do you trying to delete?
"Sammy" <Sammy@.discussions.microsoft.com> wrote in message
news:6B563C5B-9D6E-44E5-AA43-404DA791730B@.microsoft.com...
> Hi Uri,
> No its just delete quotedetail it always worked before... but I get no
> errors it just hangs so not sure what to check and how to solve this issue
> "Uri Dimant" wrote:
>|||Here's a couple of things to check...
1. Is there another connection which has a lock on the table?
2. Is there a delete trigger on the table? This would be executing and the
problem could be there.
Regards
Colin Dawson
www.cjdawson.com
"Sammy" <Sammy@.discussions.microsoft.com> wrote in message
news:6B563C5B-9D6E-44E5-AA43-404DA791730B@.microsoft.com...
> Hi Uri,
> No its just delete quotedetail it always worked before... but I get no
> errors it just hangs so not sure what to check and how to solve this issue
> "Uri Dimant" wrote:
>|||Sammy (Sammy@.discussions.microsoft.com) writes:
> I have a 43,000 row table but can no longer delete the data from it all
> of a sudden..it just hangs on the process and the processor shoots up to
> 100%. But I have tried truncating this and it works, I know deleting
> logs every row deleted but am not sure why I am getting this problem or
> what to monitor to solve it.
> Any help or advice would be great thanks
Let's first rule out a few things it can't be:
o Blocking. Had it been a blocking issue, your processor would stay calm.
o Referencing foreign key. A delete operation can take a very long time
if there are referencing foreign keys that are not indexed, or if there
cascading deletes/updates. However, had there been referencing FKs,
SQL Server would not permit you to run TRUNCATE TABLE.
That leaves two options:
o There is a trigger on the table, and the trigger performs something
wild. TRUNCATE TABLE does not fire triggers, so it could not cause
this problem.
o The log file (or the data file) is full, and needs to autogrow. If the
the database is large, and you have the default 10% autogrow, this can
give quite an impact. As TRUNCATE TABLE just logs the extent
deallocations, it causes far less load.
Thus you should examine if you have triggers on the table, and run
and run DBCC SQLPERF ( LOGSPACE ) to determine how log space you have
in use.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||No triggers or other processes accessing the table, its a 44,000 row table
and delete quotedetail (no where clause in the delete command) does not work
but truncate table quotedetail works with no problems.
Have u any other ideas how to reslove this as I am stumped?
"Colin Dawson" wrote:

> Here's a couple of things to check...
>
> 1. Is there another connection which has a lock on the table?
> 2. Is there a delete trigger on the table? This would be executing and th
e
> problem could be there.
>
> --
> Regards
> Colin Dawson
> www.cjdawson.com
>
> "Sammy" <Sammy@.discussions.microsoft.com> wrote in message
> news:6B563C5B-9D6E-44E5-AA43-404DA791730B@.microsoft.com...
>
>|||Hi Erland I discovered it was part of a snapshot replication, I did not thin
k
it was part of replication as I could truncate the table but I guess its onl
y
merge and transactional replication that does not allow you to truncate the
tables. But I don't think this should affect my problem.
My processor queue length goes up to 25 when the delete command is run and
I think my processor might be having problems now and may need to be replace
d
to solve this issue.
"Erland Sommarskog" wrote:

> Sammy (Sammy@.discussions.microsoft.com) writes:
> Let's first rule out a few things it can't be:
> o Blocking. Had it been a blocking issue, your processor would stay calm.
> o Referencing foreign key. A delete operation can take a very long time
> if there are referencing foreign keys that are not indexed, or if there
> cascading deletes/updates. However, had there been referencing FKs,
> SQL Server would not permit you to run TRUNCATE TABLE.
> That leaves two options:
> o There is a trigger on the table, and the trigger performs something
> wild. TRUNCATE TABLE does not fire triggers, so it could not cause
> this problem.
> o The log file (or the data file) is full, and needs to autogrow. If the
> the database is large, and you have the default 10% autogrow, this can
> give quite an impact. As TRUNCATE TABLE just logs the extent
> deallocations, it causes far less load.
> Thus you should examine if you have triggers on the table, and run
> and run DBCC SQLPERF ( LOGSPACE ) to determine how log space you have
> in use.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Sammy (Sammy@.discussions.microsoft.com) writes:
> Hi Erland I discovered it was part of a snapshot replication, I did not
> think it was part of replication as I could truncate the table but I
> guess its only merge and transactional replication that does not allow
> you to truncate the tables. But I don't think this should affect my
> problem. My processor queue length goes up to 25 when the delete command
> is run and I think my processor might be having problems now and may
> need to be replaced to solve this issue.
Ah, so replication set in? That was interesting to hear, as I'm not
doing much replication myself. Good to know, the next time someone
else asks a similar question.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||See some details/background data on "Live-Lock" vs. "Dead-Lock" in SQL
Server:
http://www.dbforums.com/showthread...099#post4505099
It also gives some coverage on data accessibility in SQL Server.
You can download as a FREE-WARE SQL Server package that detects
Live-lock situations on SQL Server (and notifies production DBA, if
configured).
(SQL Server Live_Lock_Monitor).
It also continuously monitors/logs SQL Server Response time (and
notifies DBA if very slow response detected).
Note: SQL Server can "Freese" when it:
1) Automatically "Expands" any database (DB options set to "Autogrow"-
NOT recommended in production)
2) When programmer misses the join (cross product or Cartesian product)
3) when a number of processes are lined up to access some resource
(e.g. table, being heavily updated)
4) table scans against large tables done
5) a table is locked (reindexation, DBCC operation etc)
6) a lot of data is being "pumped" to the client side (i.e. programmer
writes:
SELECT * from Users (against tables with millions of rows)
and in a lot of other scenarios.
Most of them (not all) should be detected by the package (since actual
response time is measured).
Alexei Akimov