This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(137.6, 40%, 23%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ES%3C/text%3E%3C/svg%3E)
where there are still LOB allocation unit after the table is truncated ?
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(35.2, 30%, 13%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETH%3C/text%3E%3C/svg%3E)
The deallocation of storage is a deferred operation. It runs after you commit, in batches. See this: https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms177495(v=sql.105)?redirectedfrom=MSDN
Hi @sakuraime ,
why there are still LOB allocation unit after the table is truncated ?
No truncate on my side.
Could you please execute refresh after your execute the first query and then execute the second query?
And what is your script of your table “TableMetaData”?
BR,
Mia
If the reply is helped, please do “Accept Answer”.--Mia
Did you see my repy, Mia? The truncate is deferred, but you need at least 128 extents for it to bedeferred. The forum mangled the URL, but I updated it to something which is hopefully working now.
So in my case , I have 128 extent and below
According to the link I posted, deferred deallocation happens if you more than 128. So we must draw the conclusion that you have than 128 extents.
128 extents is talking about 128*64 pages,
I only one allocation unit for that table which is about 9 pages as shown. ....
Then apparently MS has changed the limit since SQL Server 2012. But the concept of deferred allocation still apply, evidently.
so whats your results ? I am using SQL 2016
If you post a full repro, i can run it on my 2019. But I see little point in trying to determine the threshold for deferred allocation for every version of SQL Server. It is evident that deferred allocation happens with less than 128 extents on 2016: you have seen this with your own eyes. But trying to determine the cut-off for all versions seems a little pointless. But, again, if you post a full T-SQL repro script, I'm happy to run it on my SQL Server.