Last night i run DBCC INDEXDEFRAG and after few hours it complelety fill the log file, size of log file was about 30GB. Any idea that what happened and how to prevent from this in future. Thanks Executed as user: ...TE 01000] (Message 0) DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528) DBCC INDEXDEFRAG (0,database,ADJ_RECORD_NUMBER_IDX1) [SQLSTATE 01000] (Message 0) DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528) DBCC INDEXDEFRAG (0,database,CC_NUM_IDX1) [SQLSTATE 01000] (Message 0) DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528) DBCC INDEXDEFRAG (0,database,check_number) [SQLSTATE 01000] (Message 0) DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528) DBCC INDEXDEFRAG (0,database,dispatch_number) [SQLSTATE 01000] (Message 0) DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Mes... The step failed.
If you index is very fragmented then it will generate more log as DBCC IndexDefreg is fully logged operation. http://support.microsoft.com/default.aspx/kb/110139 & http://support.microsoft.com/default.aspx/kb/317375 -- Why T-Log has been filled up http://support.microsoft.com/kb/873235 -- Shrinking Database Files and http://support.microsoft.com/default.aspx/kb/873235 -- tips of how to prevent this to re-occure
How big is the data file? How many indexes are defragmenting with this statement? As explained by Hemant by default you will see such a change in log file size, its better to defragment required indexes.
