Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from serverfault.com Jun 2 '11 at 1:38

This question came from our site for professional system and network administrators.

add comment

2 Answers 2

up vote 8 down vote accepted

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

http://technet.microsoft.com/en-us/library/cc966402.aspx

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.
SELECT 
    gt.ServerName
    , 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; 
--
share|improve this answer
    
Thank you heaps! –  Manjot Jun 10 '11 at 4:28
add comment

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. "

share|improve this answer
    
Thank you very much –  Manjot Jun 10 '11 at 4:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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