Sql-server – Can the LOG file fill up from index rebuilds where the indexes are not fragmented

The DBCC SHRINKFILE command will be mirrored from the principal to the mirrored database. Here's some proof.

Create a sample database on the principal:

create database MirroredDb;
go

Create the same database from a backup with NORECOVERY:

restore database MirroredDb
from disk = '\\backupdir\MirroredDb.bak'
with norecovery;
go

Setup your mirroring session however which way you choose.

On the principal database look at the database file sizes:

use MirroredDb;
go

select
    name,
    size
from sys.database_files;

My result set looks like this following:

name            size
MirroredDb      392
MirroredDb_log  104

On the mirror database, create a snapshot and look at the same information:

create database MirroredDbss
on
(
    name = 'MirroredDb',
    filename = 'c:\sqlserver\MirroedDb.ss'
)as snapshot of MirroredDb;

use MirroredDbss;
go

select
    name,
    size
from sys.database_files;

My result set looks like the following:

name            size
MirroredDb      392
MirroredDb_log  104

Now grow the transaction log file on the principal database (I brought it to 1 GB):

alter database MirroredDb
modify file
(
    name = MirroredDb_log,
    size = 1GB
);
go

Looking at the principal database's transaction log size, we now see the adjusted size:

use MirroredDb;
go

select
    name,
    size
from sys.database_files;

My result set looks like the following:

name            size
MirroredDb      392
MirroredDb_log  131072

Create another snapshot on the mirrored database, and look at the transaction log file size there:

create database MirroredDbss2
on
(
    name = 'MirroredDb',
    filename = 'c:\sqlserver\MirroedDb2.ss'
)as snapshot of MirroredDb;

use MirroredDbss2;
go

select
    name,
    size
from sys.database_files;

My result set looks like the following:

name            size
MirroredDb      392
MirroredDb_log  131072

Now do the DBCC SHRINKFILE on the principal:

use MirroredDb;
go

dbcc shrinkfile('MirroredDb_log', 0);
go

select
    name,
    size
from sys.database_files;

My result set is the following:

name            size
MirroredDb      392
MirroredDb_log  104

Create a third and final snapshot on the mirrored database, and look at the size:

create database MirroredDbss3
on
(
    name = 'MirroredDb',
    filename = 'c:\sqlserver\MirroedDb3.ss'
)as snapshot of MirroredDb;

use MirroredDbss3;
go

select
    name,
    size
from sys.database_files;

And I get the following result set:

name            size
MirroredDb      392
MirroredDb_log  104

So as you can see here, the DBCC SHRINKFILE command is in fact mirrored to the mirror database.

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.