January 14, 2010 at 9:42 am
I would not change the recovery model during production hours and this will allow the transaction data's integrity for recoverability. If this is an SQL Server 2005 environment and the system has reasonable computer resources, online reindex is possible with minimal performance impact.
If the reindex is, ideally, performed during non-production hours, setting the database to Simple recovery model will boot the performance. However, as a good practice, this operation will require a full backup before and after the event. If a log shipping application associates with this database, a full database restore is expected after the completion of the reindexing.
January 14, 2010 at 9:43 am
Duplicate - Deleted.
January 14, 2010 at 10:40 am
If you are only concerned with the log size space then shrink the log file and not the entire database.
That should take care of the log space.
Thanks!
Amol Naik
January 14, 2010 at 11:29 am
What is your acceptable level of data loss? are you performing log backups? What is your restore strategy?
I would never recommend taking a production system to Simple unless you can accept a 24 hour loss of data (even if it is just temporary).
Are you mirroring?
This works with full
Are you log shipping?
this work with bulk logged and full but not simple
When is your maintenance window? Are you performing Indexing during that period of time? Are you re-indexing during the day or does your servers day never end is it a 24/7 server?
the suggestion Paul made was right on, there are also the folks that suggest to only rebuild based off of fragmentation level.
If you can do it the best bet would be to have
1. intelligent builds based off of fragmentation level
2. do it during a maintenance window
3. change the server from Full Recovery to Bulk Logged Recovery
4. After the process is done return to Full
5. Back up the log
just like Paul suggested.
If you have additonal business conditions placed on your server, list them they could effect the suggestions folks are giving
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
January 14, 2010 at 12:27 pm
I would never recommend taking a production system to Simple unless you can accept a 24 hour loss of data (even if it is just temporary).
Why would the data loss be of 24 hours if there is a Full backup performed before and after the reindex job??
I have performed re-indexing on a table with 300 million + rows on a production database by placing the database in Bulk logged recovery mode and the job runs like a charm.
Having good backups is the key to avoid Data loss..
Thanks...
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
January 14, 2010 at 12:56 pm
you comment on Simple mode, and then you talk about bulk logged mode......
kinda like yelling at spock and punching kirk
Go to bulk logged, i'm fine with that
Go to simple it's not my butt on the line
but Simple is a bad idea period if you have mirroring, or log shipping, or a host of other things
that is why I asked all the questions...nice to see you can paraphrase
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
January 14, 2010 at 1:16 pm
You still haven't answered my original question..how did you arrive at the conclusion that there will be a 24 hours data loss..??
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
January 14, 2010 at 1:23 pm
24 hours if you are in simple and run one backup.
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
January 14, 2010 at 1:31 pm
snu155 (1/14/2010)
you comment on Simple mode, and then you talk about bulk logged mode......kinda like yelling at spock and punching kirk
Go to bulk logged, i'm fine with that
Go to simple it's not my butt on the line
but Simple is a bad idea period if you have mirroring, or log shipping, or a host of other things
that is why I asked all the questions...nice to see you can paraphrase
Thanks for the response snu..I am a DBA by choice and I like being cool.
However, its good to know we are all in agreement that a full backup will not risk a 24 hour data loss (what I was trying to say :hehe:).
Also, I liked your reply as it was more detailed, covering all aspects such as mirroring and replication...which otherwise not everyone mentions in their replies unless otherwise specified.
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
January 14, 2010 at 1:38 pm
I hear you, DBA by choice as well. Agreed it doesn't mean 24 hours worth of loss, I have dealt with a lot of clients lately that had been putting thier databases into Simple Mode to save from having the T-log grow endlessly:w00t: that didn't know you should be running T-log backups.
...you know pesky databases that get a lot of activity like service desks, human resources, stuff like that ;-)....
forgive me I've been playing the boogey man lately and Simple Recovery mode has been my bane, I think I took it to heart too much....been a long day
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
January 14, 2010 at 5:44 pm
Thank you snu155 for helping out there! I hope Vivek is still reading this and the message has now sunk in.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 15, 2010 at 3:24 am
If your database allow for switching recovery models for rebuilding idexes, then its acceptable.
I was saying what if you can't switch from Full to any other model due to any such requirements and also your logfile size is increasing due to rebuilding indexes, would you let it go?
January 15, 2010 at 3:38 am
I would say it depends on the size of your database, it's structure, and if you have a maintenance window to operate in.
if the DB is a very large DB, then taking advantage of partitioning rebuilding an index on a particular partition can reduce the over head.
or re-indexing using the online option if you do not have a maintenance window.
The fact is that rebuilding indexes in full mode will grow the log. the purpose of the log is to capture all transactions executed against the database when in Full Recovery Mode.
backing up the log files and using them in conjunction with Full backups and differential backups allows you to restore to very close to a point in time, should a failure occur.
So the increased log size should be accounted for if you cannot leave full mode. However backing up before, and executing a log backup after, will remove committed transactions from a log and while the file size will not shrink externally internally there will be more free room.
execute DBCC sqlperf(logspace) to see the size of log files as well as what the free space internally is.
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply