April 9, 2003 at 9:23 am
Hi ,
I have a question regarding deletes.
If i delete a large now of rows from a table,can i use the space freed by these deletes for other purpose.I have tables which are once lodaed will not be added any additional data.we found some of the data is not needed and by deleteing this data we may use the freed space for future new tables.so my question is will these delets reset/shrink the table size Or the freed space is shown as unused space and will be used by the same table for future data.
Is there any way i can shrink the table to re use the freed up space for new tables??
Thanks,
MK
April 9, 2003 at 9:32 am
Empty pages of data are not released to the SO unless you want to. They are kept wihtin SQL. And SQL will allocate any free page when it need it. The free pages are not specific to any object. So if you delete data and free some pages from a table, SQL will use them to allocate new objects if when needed.
April 9, 2003 at 9:37 am
Thanks for the resopnse.AS you have mention if i want i can do it.How can i do it??
And is there a way i can see these free pages??
Thanks,
Meena
April 9, 2003 at 9:43 am
Well, pages are 8 kb allocation units. And remember that 8 pages also form an Extent.
you the undocumented command DBCC PAGE you can check how many rows you have in a page. But there is not much to see about empty pages..because they are empty.
When you delete rows from a table, you are deleting the data on the pages. So what you are doing is freeing the pages.
April 9, 2003 at 9:46 am
Run sp_spaceused @updateusage = true before and after the deletion and you will see the difference (unused pages) in your database.
April 9, 2003 at 9:49 am
I know about that.But how can i re use the Unused space for some other objects??
I am still not clear about it.
April 9, 2003 at 9:58 am
As racosta already explained, the fress spaces can be used by any objects. If your database does not have sufficient spaces when you insert data or create new tables, the database will automatically allocate more disk spaces as long as the auto growth is turned on. Or you have to manually incrase database spaces allocation before you perform the insert. If the database auto growth is turned off and database does not have enough spaces for the insert, the insert operation will fail and the transactions will be rolled back.
April 9, 2003 at 10:06 am
If you delete rows from a table, SQL will used the free space to new inserts from the same table or any athoer table.
quote:
Is there any way i can shrink the table to re use the freed up space for new tables??
You don't have to do anything. SQL automatically do it for you.
April 10, 2003 at 6:04 am
Racosts
could you please tell me the syntax of dbcc page.
What are the parameters that are to be given.
Regards
Murthy
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply