August 11, 2005 at 9:59 am
When a truncate table statement is issued what is happening with the data files after they are disconnected from the header pages?
August 12, 2005 at 12:51 pm
Umm...not sure if I understand. Truncate table will delete all rows from the table that it is issued on but will preserve the data columns, formatting, constraints and so on. All data is removed from the table. The following is from Books On Line:
"TRUNCATE TABLE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log.
TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column. If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement."
My hovercraft is full of eels.
August 12, 2005 at 12:58 pm
i think the question is what happens to the deallocated data pages...guess they get reallocated again when needed..........?!?!
**ASCII stupid question, get a stupid ANSI !!!**
August 12, 2005 at 1:16 pm
yup ... scan BOL for the following: table and index architecture ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
August 12, 2005 at 1:38 pm
Thanks...adding the word 'pages' makes the question make more sense.
I would assume the empty pages would remain unless you were to issue a shrink command.
My hovercraft is full of eels.
August 15, 2005 at 11:04 am
thanks for the replays, I don't think they are reallocated since they contain data, so somehow all that data pages have to be deleted at some point otherwise will result in data files having a unjustified size.
Interesting for me is when, when the database is recovering, or there is some internal mechanism that looks for deallocated pages and delete them?
August 15, 2005 at 11:19 am
not having done any research on this - but my immediate guess is that since "page deallocations are recorded in the transaction log"... that they would be reallocated as soon as the need comes up....
we'll wait for rudy/sswords/someone else to confirm/negate...
**ASCII stupid question, get a stupid ANSI !!!**
August 15, 2005 at 12:06 pm
This is from the MSDN site:
"
Since the pages are completely deallocated, the original table would not necessarily reallocate the same ones when it grew as they'd be free for any other resource to grab them. The remaining empty pages would be kept within the data file until either used or a Shrink file were issued which would remove all the empty pages that it could (or up to whatever value was specified in the command).
It logicially follows then, since a restore could be issued after this point, that the restore command would not necessarily allocate the same pages back to a table that originally owned them. It would be similar to deleting files from a disk, defragmenting the drive, then restoring some of the deleted files. The files would be restored, but most likely in a different physical location. At least that's how I understand it.
My hovercraft is full of eels.
August 15, 2005 at 2:17 pm
I found also in MSDN that
"TRUNCATE TABLE immediately frees all the space occupied by that table's data and indexes."
that will indicate in my opinion that indeed there is some mechanism that is deleting the deallocated pages.
I don't think is efficient to delete the content of the deallocated pages plus deleting the content would assume that that empty pages are required sometime in the future. I think is more efficient to delete the data pages entirely.
August 15, 2005 at 2:32 pm
The pages are not deleted from the file, they are simply deallocated from the table. Actually, I would assume that absolutely nothing (not a single byte) is changed on a page when the table it is allocated to is truncated. The page is just deallocated from the table by modifying the IAM, GAM and SGAM pages as necessary to indicate that the page is now unused.
Later, when a table (any table) needs to allocate a page it might allocate this 'old' page. In that case the page header would be modified at that time to indicate the new table it is now allocated to, along with all the other header information such as slot count, next and previous page etc. The data in the page does not need to be cleared since that page will have a slot offset array that points to where each row is located on the page, and each row header has the inormation necessary to tell where a row ends.
I am not sure what you mean with "delete the data pages entirely", but I guess you mean delete them from the file, which of course means deallocate them. This would be extremely inefficient so naturally that is not what happens.
August 15, 2005 at 2:50 pm
I am not sure what you mean by
"The data in the page does not need to be cleared since that page will have a slot offset array that points to where each row is located on the page, and each row header has the inormation necessary to tell where a row ends."
regardless the location of the rows in the data page since the 'old' data pages have different data then the 'new' data pages, the system will have to clean the 'old' one before putting the new data.
August 15, 2005 at 4:00 pm
Lets say we have this (simplified) page owned (allocated to) by object 2123456:
Header:
PageID=123 ObjectId=2123456 NextPID=124 PrevPID=122 ...
Data:
0x60: AAAA 1234
0x80: BBBB 5678
...
Slot offset array:
0 - 0x60
1 - 0x80
...
In hex, this would all look something like (all just makeup hex digits)
0x00: 00AA00A00A0A0A0A -- lets call this the header
...
0x60: 41414141000004D2 -- this represents the AAAA 1234 row
0x80: 424242420000162E -- this represents the BBBB 5678 row
...
...
0x1FFC: 6080 -- this is the slot offset array, somewhere near the end of the page
Now, the table that this page is allocated to is truncated, so the page is deallocated by modifying the IAM/GAM/SGAM pages that says it is allocated to the table (in fact I guess the IAM page does not need to be modified since it would also be deallocated from the object). Nothing needs to be changed on the page though.
Later then the page is allocated to an object, say with id 2654321, and one row is stored on the page. For simplicity lets say the rows are the same size as they were in the old page. The hex would now look like this if a row CCCC 1000 where inserted:
0x00: 0F0F0F0F0F0F0F0F -- this is the header incl the new object id
...
0x60: 43434343000003E8 -- this represents the CCCC 1000 row
0x80: 424242420000162E -- this represents the OLD BBBB 5678 row
...
...
0x1FFE: 60 -- this is the slot offset array, somewhere near the end of the page
As you can see, the new information overwrites the old information, but since there is nothing in the new object stored on position 0x80 it does not matter that the old data is still stored there. It will never be read anyway, and as soon as a new row is stored it will be overwritten with the new data.
August 16, 2005 at 10:06 am
So, if I understood it correctly you think that instead of delete and write it is just an overwrite.
That would make sense from the writing speed and page allocation perspective but it can result in data pages that will not be used, hanging in the data files. So, if you truncate a big table you might end up having your database several GB bigger then it should be with all the side effects (backup, recovery, restore, ...).
Also why the MSDN is saying that
"TRUNCATE TABLE immediately frees all the space occupied by that table's data and indexes." ?
August 16, 2005 at 4:06 pm
Yes, that is exactly how it works. Anything else would be inefficient to say the least. The pages can (and will) be used for later allocations, so there is no problem in leaving them in a file. If you want to reorganize the pages and shrink the file(s) you need to do so manually.
Also why the MSDN is saying that
"TRUNCATE TABLE immediately frees all the space occupied by that table's data and indexes." ?
Because "frees" does not mean remove pages from a file, it means precisely to free (deallocate) the pages for use by other objects.
August 22, 2005 at 10:53 am
Chris, it looks that you are right. I done some testing and indeed after the truncate the pages are still there with data in.
The only differences that I could find are in the Allocation Status entry
--before truncate
Allocation Status
-----------------
GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED
PFS (1:1) = 0x64 MIXED_EXT ALLOCATED 100_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
--after truncate
Allocation Status
-----------------
GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:1) = 0x24 MIXED_EXT 100_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
the SGAM and PFS entries.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply