Hi All,

We have a cluster SQL Server 2012 EE instance with mirroring. We are trying to run reorg/rebuild indexes using Ola Hallengren's script.

The database we run this is mirrored and about 105GB but when we run the rebuild index script (Which is only "ONLINE"), the log file grows to 240GB where we ran out of space and stop the process (Not using TEMPDB). Especially one table makes the log file grow this far. I will give the specifics of the table below. Please tell me why the log file grows much more than the whole database size ?

The table has more than 1 billion rows (1,070,093,688). The column data types and indexes below.

[int] NOT NULL ----> Primary Key - Clustered
[decimal](18, 0) NOT NULL,
[decimal](18, 0) NULL,
[int] NULL,
[char](1) NULL,
[datetime] NULL,
[datetime] NULL,
[varchar](250) NULL,

PRIMARY KEY CLUSTERED
(
    [CLO1] ASC,
    [COL2] ASC

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[MyTABLE]  WITH NOCHECK ADD  CONSTRAINT [FK_KEYNAME] FOREIGN KEY([COL1])
REFERENCES [dbo].[OTHERTable] ([COLOT])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[MyTABLE] CHECK CONSTRAINT [FK_FK_KEYNAME]
GO

Hi

One thing you could do to minimise the impact would be to alter your database to "BULK LOGGED", rebuild your indexes, and then set the database back to "FULL".

Another option you could look at is if you are using Enterprise Edition on all the Servers (for the host databases and the replicated database(s)) you could consider using Table Partiti

Thanks for your input but you are forgetting that the Bulk Logged option is not supported in MIRRORED databases.

I made some caclulation and arrived at a table size of 95 GB (I had to guess on the size of the varchar column). 240 GB to log the deletion of the old pages and loading of the new does not seem unreasonable to me.

You could stick to the REORGANIZE option for this which always is an ONLINE operation. Since it works with small transactions, the log will not row in the same manner (as long as you back up the log, that is!)

Thanks Erland. We will add more disk space and continue with rebuild indexing since the database never had a rebuild index run.

share output of following query also check following fro ref.

SELECT  name,log_reuse_wait_desc FROM sys.databases ;

However, this query just returns a keyword. To understand the cause of the different values of thelog_reuse_wait_desc, we need to dig a little deeper.

Log Records

SQL Server uses the transaction log to guarantee the ACID properties, particularly the durability requirement. For every transaction, enough information to redo as well as undo that transaction it is written to the log file. Before any transaction can commit, SQL Server waits to get a confirmation from the hard drive that the log record was written successfully.

The transaction log gives SQL Server enough information to redo the operations that made up the transaction, should the actual change not make it into the data files, for example because of a crash. Because SQL Server has this information secured in the log file, it does not need to wait for the changes themselves being written to the data files and can commit the transaction while the data page changes still reside only in memory.

Every once in a while SQL Server will execute a checkpoint operation. During this operation data pages that were altered by previous or current transactions are written back to disk.

As a side note, it is possible for data pages containing changes of open transaction to be written to disk during a checkpoint. However, SQL Server has enough information in the transaction log to undo those changes, should the need arise. To have this flexibility SQL Server stores both the redo and the undo information in the log.

Log Reuse

Once every change to any data page that was executed by a single transaction was successfully saved to disk, the log record for that transaction is not needed anymore and its space in the log file can be reused.

To accommodate for reuse, SQL Server organizes the log files as a ring buffer of several containers called virtual log files. There is no direct way to influence their size and or number. SQL Server manages that automatically.

The virtual log files keep track of all their transaction log records and note if they are still needed, for example in an open transaction. Once all log records within a virtual log file are not used anymore, the virtual log file itself is marked as ready for reuse, and SQL Server will overwrite it with new log records once it gets around to that place in the ring buffer. The process of marking one or more virtual log files as reusable is called log truncation.

Log Reuse Wait

The above section described the general behavior of log reuse. There can however be several reasons for a log record to still be required by SQL Server for (potential) future operations. With that its virtual log file cannot be reused. If that happens for an extended period of time, SQL Server might run out of virtual log files and has to add additional ones. For that the physical file has to grow. If autogrowth is enabled for the log file and there is enough room on the drive this will happen automatically. If automatic growth is not possible, the database becomes effectively read-only, causing all write attempts to fail until the situation has been resolved.

The query shown at the beginning of this article allows us to find the most prevalent reason why virtual log files can't currently be reused. As of SQL Server 2012 it can return 10 different values:

• NOTHING
• CHECKPOINT
• LOG_BACKUP
• ACTIVE_BACKUP_OR_RESTORE
• ACTIVE_TRANSACTION
• DATABASE_MIRRORING
• REPLICATION
• DATABASE_SNAPSHOT_CREATION
• LOG_SCAN
• OTHER_TRANSIENT

The first one, NOTHING, means that there are still free virtual log files available. The last one,OTHER_TRANSIENT, is currently not used. That leaves eight real reasons why your log file might be growing.