xp_sqlmaint Rebuilding Index
-
My job keeps failing due to transaction log filling up. I do not want to increase size of transaction log. Is there anyway to have the below job NOT write to the transaction log?
exec master..xp_sqlmaint '-D db_name -Rpt d:\mssql\work\logs\db_name_Rebldldx.rpt -RebldIdx 100'
-
if your DRP allows you to, you may want to switch the recovery_option of your db to bulk-logged during the time you rebuild the indexes. This will use less logspace than with full-logged mode.
Otherwize, you'll have to extend your log-filegroup so it can be used to perform the full reindex.
You might also perform the reindex base on the stats of your indexes and only rebuild the ones wich have degraded below x percent (95). Check BOL for DBCC SHOWCONTIG ... TABLERESULTS .
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Everything I've seen says the index rebuild causes a heavy use of the transaction log. It needs to do that in case the job is interrupted and rolls back. Rebuilding the index is not like defragging the index. When you defrag, it can do a part of the index at a time; a rebuild requires the whole thing to be done or none of it gets done - therefore you need to be able to rollback.
-SQLBill
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply