Is there a particular reason you are specifying TRUNCATEONLY
? As per the documentation on DBCC SHRINKFILE
:
TRUNCATEONLY
Releases all free space at the end of the file to the operating system
but does not perform any page movement inside the file. The data file
is shrunk only to the last allocated extent.
target_size is ignored if specified with TRUNCATEONLY.
If that is not the intended behavior you were looking for (which it doesn't sound like it is), then you could try without that option set.
Also, there are other factors that hinder database file shrinking as well, such as text
`ntext\
image` BLOB data. So it is worth looking into the possibility of the existence of data with these data types.
Both offline and online reorg/rebuild operations consume log space. This is true for ALL operations that modify a database in any way, even for minimally logged operations such as TRUNCATE TABLE
. This is how SQL Server maintains transactional consistency. "Consistency" is impossible unless every action is logged so it can be rolled back or forward if necessary.
Index reorganization uses less log space than an index rebuild. Reorganization defragments the index page-by-page, whereas a rebuild creates an entirely new copy of the index being rebuilt, then drops the old copy of the index. If an individual index is 1GB, rebuilding it will require at least 1GB, plus space for any rollback to take place that may happen as a result of the rebuild either failing or being cancelled. Since any rollback operation will also be logged, log space is reserved prior to the start of all transactions to ensure the transaction can be rolled back successfully. This indicates the maximum potential log space required for an index rebuild would be double the size of the index at the time the rebuild operation begins. So for a 1GB index, you should ensure you can support at least 2GB of log space in use by the rebuild operation for the duration of the operation. This is not a guarantee that much space will be used, it is simply the maximum the operation might need. Any other simultaneous transactions occurring will need additional space.
Any operations that consume tempdb also consume tempdb log space. If you enable SORT_IN_TEMPDB
, you need enough room in tempdb log to support sorting the entire index; this amount depends greatly on the structure of the index, including things like how large the key columns are.
If index reorg/rebuild operations are performed serially, that is one-after-another, then you'll need space in the transaction log for the largest index size x 2. i.e. if your largest index is 1GB, to be safe you need to ensure 2GB of transaction log is available for your index operation. If you have simple logging in effect at the time of the reorg/rebuild job, and all operations aren't in a single transaction, transaction log space will be re-used by each individual operation. If you have full transaction logging enabled, and no transaction log backups occur during the reorg/rebuild job, then the log needs to be size at the total size of all indexes to be rebuilt + the size of the largest index to be rebuilt. This can be mitigated somewhat by running log backup jobs during the rebuild process.
Best Answer
We have been using the approach in your posted link since first reading about it in Sept 2017. Here is a link with comments from MS SQL Architect Conor Cunningham about the need to re-index in Azure SQL Database Link to Conor Cunningham comments.