|
Home > Archive > MS SQL Server > March 2006 > Reuse of space after truncating
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Reuse of space after truncating
|
|
|
| Hi,
Will space used by an table be unallocated if the table is truncated or
will it only be marked as unused?
TIA,
| |
|
|
|
| Hi,
well,
as I have understood space allocation in SQL Server it will allocate 1
extent when needing more space for data (not talking about mixed extents
here). If the new data is only occupying 1 page in the extent the whole
extent is counted as allocated and 7 pages as unused.
Am I far off in my understanding this issue? If so, please explain...
TIA,
"Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote
in news:OuSKkGKVGHA.196@TK2MSFTNGP10.phx.gbl:
> Can you explain the difference between unallocated and unused?
>
| |
| Tibor Karaszi 2006-03-31, 7:24 am |
| Your understanding about pages and extent allocation seems to be correct. When you do TRUNCATE
TABLE, all the extents are deallocated for the table and its indexes. If you do it in a transaction,
the deallocated pages are not available for re-use until COMMIT. But essentially, you end up with
the same situation as if you just had created the table (and indexes) and before you insert the
first row into the table.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/
"Gurba" <gurbao@hotmail.com> wrote in message
news:Xns9797852E1C29
6gurbaohotmailcom@12
9.250.171.68...
> Hi,
>
> well,
> as I have understood space allocation in SQL Server it will allocate 1
> extent when needing more space for data (not talking about mixed extents
> here). If the new data is only occupying 1 page in the extent the whole
> extent is counted as allocated and 7 pages as unused.
>
> Am I far off in my understanding this issue? If so, please explain...
>
> TIA,
>
> "Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote
> in news:OuSKkGKVGHA.196@TK2MSFTNGP10.phx.gbl:
>
>
|
|
|
|
|