April 23, 2008 at 12:15 pm
How do you manage reindexing on large databases where there is log shipping? We're doing intelligent reindexing using sys.dm_db_index_physical_stats but a 60GB trans log backup file results which impacts log shipping. This production database will be growing MUCH larger ( currently the mdf data file is 190GB ).
Since we reindex with the Online option I thought about doing a few indexes each hour throughout the day but since it is probably the rebuilding of a few indexes on large/busy tables that causes the huge transaction log backup, this may not help. Our log shipping standby sql server is slower and takes a long time to restore this backup, throwing log shipping out of sync.
April 23, 2008 at 12:30 pm
this is a common problem and you have various methods to resolve this.
1) stop log shipping - put database into simple- reindex - restart log shipping
2) intersperse your indexing with tlog backups to keep logs small, it'll also stop the overall size growing.
3) make sure you make most use of tempdb for index rebuilds
4) use on-line.
5) see if mirroring is better suited
The point remains however that a clustered index rebuild effectively rebuilds a table, a logged event, so this will have to be shipped.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
April 23, 2008 at 1:07 pm
Just a few thoughts about this.
>1) stop log shipping - put database into simple- reindex - restart log shipping
This means performing a full backup and a restore to initialize log shipping, doesn't it?
One thing that you could consider, if you're not already doing it, is to do a Reorganize on indexes with not so high fragmentation. That will generate less transaction log. Microsoft has some recommendations on this.
http://msdn2.microsoft.com/en-us/library/ms189858.aspx
I have a stored procedure that you can use for this if you like.
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
About database mirroring I have some experience with that on large databases. Index rebuilds does take longer time than with no database mirroring, but it works just fine.
Ola Hallengren
April 23, 2008 at 2:05 pm
We're using something like the following. Switching to LIMITED sampling might help a bit with the run time on the initial cursor creation, but it's really the one large trans backup file at issue. Many options are being considered and we've brought in a consultant since this 190GB database is expected to grow to several Terabytes over the next year. One manager mentioned replication but I've heard two things about that -- lots of hand-holding/oversight involved and your production schema can't necessarily be mimiced on the replicated database.
-- 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, 'SAMPLED')
WHERE avg_fragmentation_in_percent > 4.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.
FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag ;
WHILE @@FETCH_STATUS = 0
BEGIN;
SELECT @objectname = o.name,
@schemaname = s.name
FROM CollateralManager.sys.objects AS o
JOIN CollateralManager.sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid ;
SELECT @indexname = name
FROM CollateralManager.sys.indexes
WHERE object_id = @objectid
AND index_id = @indexid ;
SELECT @partitioncount = count(*)
FROM CollateralManager.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
BEGIN;
SELECT @command = 'ALTER INDEX ' + @indexname + ' ON ' + @schemaname + '.[' + @objectname + '] REORGANIZE' ;
IF @partitioncount > 1
SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum) ;
BEGIN TRY
EXEC ( @command ) ;
END TRY
BEGIN CATCH
PRINT 'Failed on command ' + @command ;
SELECT ERROR_MESSAGE() as ErrorMessage ;
END CATCH
END ;
IF @frag >= 30.0
BEGIN;
SELECT @command = 'ALTER INDEX ' + @indexname + ' ON ' + @schemaname + '.[' + @objectname + '] REBUILD WITH (ONLINE = ON)' ;
IF @partitioncount > 1
SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum) ;
BEGIN TRY
EXEC ( @command ) ;
END TRY
BEGIN CATCH
PRINT 'Failed on command ' + @command ;
SELECT ERROR_MESSAGE() as ErrorMessage ;
-- Try to reorganize the index if the rebuild fails.
SELECT @command = 'ALTER INDEX ' + @indexname + ' ON ' + @schemaname + '.[' + @objectname + '] REORGANIZE' ;
--IF @partitioncount > 1
--SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum) ;
BEGIN TRY
EXEC ( @command ) ;
END TRY
BEGIN CATCH
PRINT 'Failed on command ' + @command ;
SELECT ERROR_MESSAGE() as ErrorMessage ;
END CATCH
END CATCH ;
END ;
PRINT 'Executed ' + @command;
FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag ;
END ;
-- Close and deallocate the cursor.
CLOSE partitions ;
DEALLOCATE partitions ;
-- Drop the temporary table
DROP TABLE #work_to_do ;
April 23, 2008 at 3:28 pm
Could you describe some more about your high availability and disaster recovery strategy. Are you using only Log Shipping or is it combined with some other solution like Database Mirroring or Failover Clustering? What scenarios is it that you would like to have a protection against? Site failure, server failure, storage failure and / or human error data failure? What are your requirements for dataloss and failover? Does your system have to be available 24/7?
Is it a few really large tables that is growing a lot or is it many tables?
Are you using table partitioning today? It could be a good thing to only do something about the partitions with fragmentation. The not so good thing is that you can not do online rebuild on a partition.
It could be that your queries are not so negatively impacted by the fragmentation and that the cost of removing the fragmentation is higher than the benefit. It all depends on your workload and your indexes. It could be something worth looking at. Chad Boyd has an article on this.
Ola Hallengren
April 23, 2008 at 4:10 pm
It's a sql 2005 Enterprise active/passive cluster with key files stored on leased SAN space. We are in the process of getting our own SAN. The standby log shipped sql server is kept independent of any production hardware so no matter what goes wrong, you point your web/app servers at the standby.
It is considered 24/7 so we rebuild indexes online. We are not using multiple data/log files or table partitioning yet, but that is probably coming soon. The goal is to keep all of the data in one database as long as possible. ( application design factor ).
April 23, 2008 at 5:06 pm
>Our log shipping standby sql server is slower and takes a long time to restore this backup, throwing log shipping out of sync.
I would like to fully understand what happens. The secondary server is getting behind, but it will catch up after some time, won't it? How long time could that take?
One thing that you could consider is to use the bulk-logged recovery model. The index rebuild command is then minimally logged.
http://msdn2.microsoft.com/en-us/library/ms191484.aspx
I think that you could change to the bulk-logged recovery model before the index operations and then change back to full recovery model when you are finished.
Another thing is that your secondary log shipping server maybe needs to have a faster disk system.
Ola Hallengren
August 17, 2023 at 4:58 pm
I know this is an old post but just FYI that "bulk logged" mode will help with growth in your actual tran log, but won't do much for tran log backups - they will stay about the same size.
I am still curious, there has to be some better solution for shipping data warehouses.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply