October 30, 2010 at 8:59 pm
hi, to all
we implemented log shipping in ss 2005, db size-20gb, we have to do rebuild indexes during the weekends but that's time my log file has grown to very large
what can i do,
1)simply convert db->simple and then reapply log shipping or
2)increase log size or
please help me
October 31, 2010 at 3:02 am
Switch to bulk-logged recovery for the index rebuilds. Log won't grow as big, log backups will be same size
and/or
Rebuild only the indexes that need rebuilding. Don't use the maint plan that blindly rebuilds everything.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 1, 2010 at 9:13 am
@subbbkarth
As gail said, use this script to know which indexes need rebuilding, and perform on them only.
use database
GO
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'Production.Product'),
NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;
GO
/*if avg_fragemntation <30% then alter index reorganize
if avg_fragmnetaion >30% then alter index rebuild*/
Regards
Sushant Kumar
MCTS,MCP
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply