In delete and truncate which one really free the memory after running?

Question

Post reply

In delete and truncate which one really free the memory after running?

forsqlserver

SSCoach

Points: 18902
More actions
October 20, 2011 at 1:10 am

#246685

In delete and truncate which one really free the memory after running?
Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

Not sure what you are asking. Are you worried about memory or disk or what?

CEWII

forsqlserver SSCoach Points: 18902 More actions · Answer 2

Thanks Eliott,

I just want to know if I run a delete and trucate to remove a row from the table(Suppose table has only single row) then which one actully free the memory(Memory means not Ram but Hard disk drive) for use of other process.

Will both commands free the memory if yes then If I am running a delete command for 100 rows in which total table rows has 1000.then in this case will the deleted rows memory(Memory means not Ram but Hard disk drive) be free for other processess.

Thanks

Ant-Green SSC Guru Points: 113507 More actions · Answer 3

please correct me if i am wrong on this sql guru's

when a row is deleted it is actually marked for deletion, then the internal clean up tasks comes along and removes the row from the data pages.

as a delete is not a deallocation of pages, the page(s) to which the rows have been deleted from are still assigned to that particular table and therefore are ready for new rows to be added into the table

a truncate is a deallocation of pages meaning when something writes to the table again a new data page has to be aquired

in both cases, the only thing that happens is white space in a page / data file is created

the database is not shrunk it is left as it is, therefore the "white space" is not available to other processes.

if you want to created more disk space then you will need to do something like dbcc shrinkfile or shrinkdatabase

forsqlserver SSCoach Points: 18902 More actions · Answer 4

forsqlserver

SSCoach

Points: 18902

October 20, 2011 at 3:10 am

#1397144

Thanks anthony,

For such idea...

Thanks

Ant-Green SSC Guru Points: 113507 More actions · Answer 5

also memory = RAM, storage = hard drive space, to prevent any confusion for future posts

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 6

You also have to take into account what happens with the transaction log. When you delete 1000 rows, they're recorded in the log that way, increasing the amount of information logged. When you truncate a table, that single operation is logged, causing less impact there.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 7

I find I can read your question 2 different ways.

The first is if I delete or truncate when do I get space back in the filesystem, the answer is that without additional work probably never.

The second is if I delete or truncate a table when can that space be reused, and the answer is basically right away.

The truncate is a deallocation of pages, which is considered a minimally logged operation, whereas a delete is a fully logged operation.

CEWII

SpringTownDBA SSCertifiable Points: 6917 More actions · Answer 8

truncate deallocates the pages.

delete flags rows as deleted, ghost cleanup task runs periodically in the background, and removes the rows from the page. If the page is then completely empty, ghost cleanup task will deallocate it.

Jared SSC Guru Points: 61793 More actions · Answer 9

anthony.green (10/20/2011)
please correct me if i am wrong on this sql guru's
when a row is deleted it is actually marked for deletion, then the internal clean up tasks comes along and removes the row from the data pages.

Exactly! Here is a link that goes into more detail if anyone is interested: http://sqlserverpedia.com/blog/sql-server-bloggers/fact-checking-ghost-cleanup-part-i/

Thanks,

Jared

Jared
CE - Microsoft