Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have created a table : Table1. Inserted 100 thousand rows and deleted. I did the same around 100 times ( inserting and deleting). Will it cause of increasing the database and table size every time? ( I am using Delete instead of Truncate).

share|improve this question

migrated from stackoverflow.com Jan 19 '12 at 11:34

This question came from our site for professional and enthusiast programmers.

2 Answers 2

up vote 3 down vote accepted

WHen you use delete the rows actually not deleted but marked as GHOST records, which will finally deleted by dedicated process named GHOST CLEANUP which works relatively slow in background

To force deletion of ghost records run:

DBCC FORCEGHOSTCLEANUP
share|improve this answer
    
Thanks for giving the command to delete the ghost records forcefully. Thanks for your information. –  Venkateswarlu Avula Jan 19 '12 at 10:05

No, actually deletion doesn't free data space instantly but marks corresponding data pages as free for that table. So when you insert data for the same table, those particular pages get filled up first. In any case, after a while the background process de-allocates the free pages and finally reduces the page size.

Here is a good article on this I think.

share|improve this answer
    
The article is very nice and helped a lot. Thanks for your help. –  Venkateswarlu Avula Jan 19 '12 at 10:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.