SQL Server Forums - delete statement execution time

Home | Weblogs | Forums | SQL Server Links

Active Forum Topics | Popular Articles | All Articles by Tag | SQL Server Books | About

SQL Server Forums

Register Now and get your question answered!

Username:	Password:
Save Password
Forgot your Password?

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

delete statement execution time

New Topic

Reply to Topic

Printer Friendly

Author

Topic

cognos79
Posting Yak Master

USA
241 Posts

Posted - 01/17/2007 : 13:40:41

I do have a table with 60,000 records. when i execute delete statement : delete from tablename it takes 58 secs. I am deleting all the records from table. does it really takes 58 secs??? or something wrong with the table.

tkizer
Almighty SQL Goddess

USA
35007 Posts

Posted - 01/17/2007 : 14:00:41

Well it's fully logged, so yes it could take a bit. If you don't have any child tables to worry about, then I'd suggest TRUNCATE TABLE instead.

Tara Kizer

cognos79
Posting Yak Master

USA
241 Posts

Posted - 01/17/2007 : 14:03:38

tara...what does fully logged mean. I will try truncate and see

tkizer
Almighty SQL Goddess

USA
35007 Posts

Posted - 01/17/2007 : 14:07:11

It has to do with the transaction log. All rows will be logged in it. For TRUNCATE TABLE though, only the pages deallocations are logged I believe. Someone correct me if I'm wrong. It's one of those days...

Tara Kizer

cognos79
Posting Yak Master

USA
241 Posts

Posted - 01/17/2007 : 15:45:48

tara...can i view the transaction log?

tkizer
Almighty SQL Goddess

USA
35007 Posts

Posted - 01/17/2007 : 15:51:17

You need to purchase a third party tool such as Lumigent's Log Explorer to do that.

Tara Kizer

cognos79
Posting Yak Master

USA
241 Posts

Posted - 01/17/2007 : 16:03:39

thanks a lot for your answers