December 16, 2009 at 5:00 pm
I wrote a script to dynamically reorg/rebuild indexes and I ran it. The tlog grew out of control and ended up running out of space. I would like to not break the recovery chain. Does it break the recovery chain to put the database into Bulk-Log mode during the operation? Will that stop the tlog growth? What are some of my options?
December 16, 2009 at 5:26 pm
Switching the recovery model from Full to Bulk-logged will not break the log chain and will allow your reindex job to complete without blowing up the log file. Check out the link below for full details. An alternative is to increase the frequency of log backups during the index rebuilds.
http://msdn.microsoft.com/en-us/library/ms190203(SQL.90).aspx
December 16, 2009 at 5:41 pm
More options include altering the job so only Indexes that meet certain criteria are re-orged or re-indexed (frag % and number of pages are common delimiters). Make those criteria adequate enough to not require all tables being done in a single run of the script.
Another option is to rebuild by groups of x number of tables or x size in relation to the database.
One we employee is to defrag based on filegroup.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 17, 2009 at 7:49 am
According to the link, users are not allowed in the database during the bulk-logged time. I am trying to do a rebuild/reorg online. Is there a way to control the growth of the tlog while still allowing users to access the system.
For a database that uses full recovery, switching to the bulk-logged recovery model temporarily for bulk operations improves performance. However, if data loss is unacceptable, to prevent data loss, we recommend that you switch to the bulk-logged recovery model only under the following conditions:
Users are currently not allowed in the database.
No modifications are made during bulk processing that are not recoverable without depending on taking a log backup; for example, by re-running the bulk processes.
We recommend that:
Before switching to the bulk-logged recovery model, you back up the log.
This is important because, under the bulk-logged recovery model, if the database fails, backing up the log for bulk operations requires access to the data.
After performing the bulk operations, you immediately switch back to full recovery mode.
After switching back to the full recovery model, you back up the log again.
December 17, 2009 at 11:03 am
jason.stephens (12/16/2009)
I wrote a script to dynamically reorg/rebuild indexes and I ran it. The tlog grew out of control and ended up running out of space. I would like to not break the recovery chain. Does it break the recovery chain to put the database into Bulk-Log mode during the operation? Will that stop the tlog growth? What are some of my options?
Hi,
How big ur index ?
What's the size of t-log before/after rebuild.
If ur disk running out of space then u plan to increase the disk space first.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
December 17, 2009 at 11:13 am
Users can access the DB while it's in bulk-logged recovery. No limitations on that.
You missed a part of the page:
However, if data loss is unacceptable, to prevent data loss, we recommend that you switch to the bulk-logged recovery model only under the following conditions:
* Users are currently not allowed in the database.
This is because, while log backups can be taken with the DB in bulk-logged and while the log chain is not broken by a switch to bulk-logged, point-in-time recovery may not be possible. While in bulk-logged, any log backup that contains bulk-operations can only be restored in totality.
So, let's say you switch to bulk logged at 10pm and do log backups every 15 minutes then switch back to full at 11pm. Further we'll assume that there are bulk operations in all of the log backups.
If, for some reason, you were required to restore to a point during that hour period, the only valid options would be 10:00, 10:15, 10:30 and 10:45. This is different from full recovery where the STOPAT option can be used to roll the log forward to any time.
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
December 23, 2009 at 8:22 am
As Muthukumaran suggests, it may turn out that your only option is to get a bigger disk. This is because your log file needs to have at least enough space to rebuild the largest index... no matter what recovery mode your database is in.
John
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply