SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 Index Reorg and TLog Bloat After Migration
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ferrethouse
Constraint Violating Yak Guru

340 Posts

Posted - 02/11/2013 :  13:44:20  Show Profile  Reply with Quote
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

USA
37142 Posts

Posted - 02/11/2013 :  14:29:53  Show Profile  Visit tkizer's Homepage  Reply with Quote
I doubt the move caused it, believe it was coincidental. You likely just hit a large index that was not fragmented enough until now. Unfortunately I don't believe the maintenance plans log what it does, hence one of the reasons to use custom code.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

ferrethouse
Constraint Violating Yak Guru

340 Posts

Posted - 02/11/2013 :  15:27:19  Show Profile  Reply with Quote
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.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37142 Posts

Posted - 02/11/2013 :  16:46:48  Show Profile  Visit tkizer's Homepage  Reply with Quote
Depends how you did the upgrade. Was it just backup/restore or detach/attach?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

ferrethouse
Constraint Violating Yak Guru

340 Posts

Posted - 02/11/2013 :  17:14:02  Show Profile  Reply with Quote
quote:
Originally posted by tkizer

Depends how you did the upgrade. Was it just backup/restore or detach/attach?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog



Backup/restore were done a day before and then tlog shipping setup. We turned off the 2008 server, applied the last incremental to 2012 server and made a DNS change.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37142 Posts

Posted - 02/11/2013 :  18:25:18  Show Profile  Visit tkizer's Homepage  Reply with Quote
My guess is that the upgrade didn't cause it. I am not aware of an upgrade bloating the tlog like that.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

ovc
Starting Member

Romania
35 Posts

Posted - 04/09/2013 :  18:38:47  Show Profile  Reply with Quote
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 frag
INTO #work_to_do
FROM 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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000