Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
 Posted - 2013-02-11 : 13:44:20
|
| I have a maintenance plan that performs an index reorg 3 times per week. I have never had an issue with it in terms of causing tlog bloat. Yesterday we migrated our DB from Rackspace to AWS and after the migration was done I ran a reorg and it caused about 100 GB of tlogs. Our database is about 130 GB. It used up all the space on that drive and made the DB unresponsive until I dealt with it. I'm now afraid to run it again until I understand why it behaved so differently in the new environment. Anyone know why the move to the new environment would apparently result in this behavior? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
Posted - 2013-02-11 : 15:27:19
|
| The other thing I forgot to mention that could be important is that we migrated from 2008 to 2012. I wonder if the reorg was affected by that. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ovc
Starting Member
35 Posts |
Posted - 2013-04-09 : 18:38:47
|
| The issue can be caused by the upgrade. When a database is being upgraded from one SQL Server version to a newer one, there are some updates performed in the physical structure of the database which can increase the fragmentation. After such a database upgrade you should defenetly perform a defragmentation and update statistics on that particular database.As the transaction log almost hit the size of the database, this tells us that you have a highly fragmented database. In this situation it would be a better idea to run at the moment an index rebuild on the database. Afterwards you should switch back to index reorg.The recommended way, would be to defragment the indexes according to the percentage of fragmentation which can be found out from the sys.dm_db_index_physical_stats (http://msdn.microsoft.com/en-us/library/ms188917.aspx)You can use the script from the D Section of the page. This would perform a rebuild index if the fragmentation is higher than 30% (which should be more optimal from the performance point of view and from the transaction log usage in comparison to reorganize index)#############################Attaching the script:-- Ensure a USE <databasename> statement has been executed first.SET NOCOUNT ON;DECLARE @objectid int;DECLARE @indexid int;DECLARE @partitioncount bigint;DECLARE @schemaname nvarchar(130); DECLARE @objectname nvarchar(130); DECLARE @indexname nvarchar(130); DECLARE @partitionnum bigint;DECLARE @partitions bigint;DECLARE @frag float;DECLARE @command nvarchar(4000); -- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function -- and convert object and index IDs to names.SELECT object_id AS objectid, index_id AS indexid, partition_number AS partitionnum, avg_fragmentation_in_percent AS fragINTO #work_to_doFROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;-- Declare the cursor for the list of partitions to be processed.DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;-- Open the cursor.OPEN partitions;-- Loop through the partitions.WHILE (1=1) BEGIN; FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag; IF @@FETCH_STATUS < 0 BREAK; SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name) FROM sys.objects AS o JOIN sys.schemas as s ON s.schema_id = o.schema_id WHERE o.object_id = @objectid; SELECT @indexname = QUOTENAME(name) FROM sys.indexes WHERE object_id = @objectid AND index_id = @indexid; SELECT @partitioncount = count (*) FROM sys.partitions WHERE object_id = @objectid AND index_id = @indexid;-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding. IF @frag < 30.0 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE'; IF @frag >= 30.0 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD'; IF @partitioncount > 1 SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10)); EXEC (@command); PRINT N'Executed: ' + @command; END;-- Close and deallocate the cursor.CLOSE partitions;DEALLOCATE partitions;-- Drop the temporary table.DROP TABLE #work_to_do;GO |
|
|
|
|
|
|
|
|
Index Reorg and TLog Bloat After Migration