truncate and delete issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

truncate and delete issue

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-11-13 : 08:17:20

There is a table on which when I perform delete operation

select count(*) from a;
--2000000 Rec
delete a;
select count(*) from a;
--1000000 Rec

Even with truncate the result is same, I am not able to delete all the records from this table.

Regards,
aak

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-13 : 08:23:28

when you delete, what message is returned?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-13 : 08:24:22

any trigger on the table ?

KH
[spoiler]Time is always against us[/spoiler]

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-11-13 : 08:51:09

when I issue delete/truncate it says 500000 records affected
when I issue count(*) it says 100000 records

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-11-13 : 08:55:00

there are no triggers on this table

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-13 : 09:17:08

1. Are you using MS SQL Server?
2. Truncate doesn't return # of records affected, which is why I specified delete.
3. If the answer to 1 is yes, then please show us the DDL for the table and you exact delete statement. Also show us what is returned from this: select object_name(object_id), * from sys.triggers

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-13 : 09:39:10

I don't buy it


CREATE TABLE #a (Col1 int IDENTITY(1,1), Col2 char(1))
GO

INSERT INTO #a (Col2)
SELECT 'a' UNION ALL SELECT 'b' UNION ALL SELECT 'c'
GO

SELECT COUNT(*) FROM #a
GO

DELETE #a
SELECT @@ROWCOUNT
GO

SELECT COUNT(*) FROM #a
GO

DROP TABLE #a
GO

There are no miracles

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-11-13 : 09:54:39

yes I am using sqlserver

I issued the below statement
truncate table ActivityTable

after 61 seconds I got below response
Command(s) completed successfully.

and now when I ran
select count(*) from ActivityTable
100000

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-11-13 : 09:58:47

select object_name(object_id), * from sys.triggers
--There are no records for this

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-11-13 : 10:03:12

to load data I am using ssma tool provided by MicroSoft

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-13 : 10:05:16

61 seconds to TRUNCATE a table?

I don't think so

POST ALL of your commands, ALL odf the DDL for the table (including PKs, FKs, Triggers, and all the constraints

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-11-13 : 11:05:56

truncate table ActivityTable

select count(*) from ActivityTable
--100000
drop table ActivityTable

select object_name(object_id), * from sys.triggers
--No records as there are no triggers in the db iteself

is it some were it is caching the records
now I have requested to shrink the db and re-try upload

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-11-13 : 11:09:55

Sounds like a SET ROWCOUNT issue to me.

Run SET ROWCOUNT 0 before your delete.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-11-13 : 11:57:19

I ran this

go
SET ROWCOUNT 0
truncate table ActivityTable
go

still it has records

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-13 : 12:15:57

DBCC CHECKDB('<dbname>')

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-11-13 : 12:32:06

I am getting the below error when I ran

DBCC CHECKDB('dbname')

Msg 21, Level 21, State 1, Line 1
Warning: Fatal error 926 occurred at Nov 13 2009. Note the error and time, and contact your system administrator.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-13 : 13:01:36

seriously...you need to supply the db you are working in

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-11-13 : 13:06:03

I am talking to my DBA even he is surprised, may be ssma has cached records for the large table, as I have loaded this table partially at first go,
due to failure I need to restart the process, hence was trying to truncate/delete existing records and upload the table.

I dropped the table, and when I recreated only structure using ssma, surprisingly it has records in it.

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-13 : 13:11:21

next you're gonna tell us that you're having steak for dinner

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-13 : 21:59:40

I like steak

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-11-16 : 00:05:27

quote:
Originally posted by X002548

seriously...you need to supply the db you are working in

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Dear Brett,
Ofcourse I am supplying my database name not dbname.

Regards,
aak

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources