On SQL server 2005, if all the databases are in Full mode (with hourly transaction log backups), is it possible to determine if rebuilding all indexes of a database can grow log file of a database? And how much can it grow?

If there is no straight answer then any directions would be really appreciated.

Thanks in advance.

2 Answers 2


Yes, you should look at the excellent whitepaper on this topic although it refers to the online index rebuild, it still has lot of good info


If the log files are auto growing then you can find that information using the default trace after the action is completed.

DECLARE @filename VARCHAR(255) 
SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'  
FROM sys.traces   
WHERE is_default = 1;  

--Check if the data and log files auto-growed. Look for tempdb, log files etc.
    , gt.DatabaseName
    , gt.TextData
    , gt.StartTime
    , gt.Success
    , gt.HostName
    , gt.NTUserName
    , gt.NTDomainName
    , gt.ApplicationName
    , gt.LoginName
FROM [fn_trace_gettable](@filename, DEFAULT) gt 
JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id 
WHERE EventClass in ( 92, 93 ) --'Data File Auto Grow', 'Log File Auto Grow'
ORDER BY StartTime; 

It is not only possible to detect if index DDL operations increase the log file, it is the default action line. All index operations are logged in a database that uses the full recovery model.

I'd advise you to read the following article on MSDN: Determining Index Disk Space Requirements.

From the 3rd chapter - Transaction Log Disk Space for Index Operations of that article I will quote:

"Large-scale index operations can generate large data loads that can cause the transaction log to fill quickly. To make sure that the index operation can be rolled back, the transaction log cannot be truncated until the index operation has completed; however, the log can be backed up during the index operation. Therefore, the transaction log must have sufficient room to store both the index operation transactions and any concurrent user transactions for the duration of the index operation. This is true for both offline and online index operations. Because the underlying tables cannot be accessed during an offline index operation, there may be few user transactions and the log may not grow as quickly. Online index operations do not prevent concurrent user activity, therefore, large-scale online index operations combined with significant concurrent user transactions can cause continuous growth of the transaction log without an option to truncate the log. "


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.