Free the pages after deletion of data

Question

Hi expert

   I have one confusion, I have one table in that I insert some data and I checked SP_SPACEUSED its showing 16kb data, But in next step when I delete all data from table  and check it once again using SP_SPACEUSED still its showing 16 kb data . It means sql server engine not releasing
the pages after deletion of data.

    So I want to know that  is there any option we have to release  these blank pages for another use? or if no then when sql server engine will release it automatically?

 

---

Regards Vikas Pathak

Answer 1

Run DBCC UPDATEUSAGE first then run sp_spaceused. see BOL for more info on dbcc updateusage

vt

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 2

If you deleted data from the table,  it Freeing space for deleted records, sql server marks pages containing deleted records as free pages in it,

It notifies that they belong to the singleObject(ex- table). When new data are inserted against on the same object, they are put into those pages first. Once those pages are filled up as per the fillfactor, SQL Server will allocate new pages.

---

Rama Udaya.K ramaudaya.blogspot.com ---------------------------------------- Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 3

Hope above responses answer your query & additionally to reduce the size the file size you can look at following t-sql:

USE <Database_Name>;
GO
DBCC SHRINKFILE (<Database_File_Name>, <Size_To_Reduce_To>);

Answer 4

@v. vt

  Thanks for reply but I checked with DBCC UPDATEUSAGE  , there is no effect still its showing 16kb data.

---

Regards Vikas Pathak

Answer 5

@Udaya_Sequel

Thanks for giving reply.  If you are right then suppose I have a table with huge data ,now its ocupies more than 50 pages, and due to some change in bussiness rule I delete 90% of data. see In this situation according to you, how much memory sql server will  west..... 

---

Regards Vikas Pathak

Answer 6

Vikas - has you know that group of 8Pages is equl to 1 extents,so I hope if you  use the below link it explians clearly-

http://blogs.msdn.com/b/psssql/archive/2008/04/08/how-it-works-sql-server-page-allocations.aspx

also how much memory sql server will  west..... 

Didn't get this one.

---

---

Rama Udaya.K ramaudaya.blogspot.com ---------------------------------------- Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 7

Hi

Deleting data will not reduce the file size - it will alter the ratio between Used Space and Free Space. Ie you will have more free space in your data files.

this script will show you what the Free space to used space is for each file. Then you can run anuragsh's script to shirnk the files (although you'd do better to leave it with the free space)

SELECT name AS 'File Name',
physical_name AS 'Physical Name',
size/128 AS 'Total Size in MB',
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB'
FROM sys.database_files; 

Seth





---

http://lqqsql.wordpress.com

Answer 8

Vikas,

When you delete data in a table, the data is NOT *physically* removed from the data pages instead it is just marked as Ghosted. There is a separate background process "Ghost Cleanup" wakes up and removes "n" number of pages per interval and I think its different in SQL Server 2005 vs 2008 and later. I am adding few references below and go thru them. Depending on the volume of data that's deleted, sometimes Ghost Cleanup process can't keep up and you may want to rebuild the index. 

http://www.sqlskills.com/BLOGS/PAUL/post/Inside-the-Storage-Engine-Ghost-cleanup-in-depth.aspx

http://www.sqlskills.com/BLOGS/PAUL/post/Ghost-cleanup-redux.aspx

http://www.sqlskills.com/blogs/jonathan/post/An-XEvent-a-Day-(15-of-31)-Tracking-Ghost-Cleanup.aspx

http://www.sqlskills.com/BLOGS/PAUL/post/Turning-off-the-ghost-cleanup-task-for-a-performance-gain.aspx

http://dba.stackexchange.com/questions/14615/sql-server-empty-table-is-slow-after-deleting-all-12-million-records

---

Sankar Reddy

Blog: http://SankarReddy.com/
Twitter: http://twitter.com/SankarReddy13/