tags:

views:

129

answers:

2

I plan to delete an entire table with over 930,000 rows of data. Which is the best way to do it without increasing the Log size or increasing the DB size.

I am on a live site and my hosting has given me 150 MB of space..I am already using 125 MB and hence need to be careful of the DB size since increase in log will increase the size of my DB

+4  A: 

use truncate table.

Vincent Buck
thanks for answering
lols
+8  A: 

If you do not wish or need to fully log the deletion activity(i.e. you do not need to be able to recover your database to a specific point in time) then you can flush/deallocate the contents of the table by using the TRUNCATE TABLE command.

If on the other hand you wish to log the event fully then you should delete the data in batches in order to maximise performance, see the following article for details on how to do this:

Performing Fast SQL Server Delete Operations

John Sansom
that did it...thanks..How do I reclaim the space..the db still shows the same size..i thought it would reclaim atleast 25 MB of space when I truncated the table
lols
Ok..I did SHRINKDATABASE and that did it :)
lols
Good stuff. Let me know if you need any further assistance.
John Sansom