March 27, 2012 at 12:46 pm
Hi Guys,
Can I change the recovery model of the database to Bulk-Logged" from FULL, while the rebuild index job works in 3 steps?
1. Alter database set recovery Bulk -Logged.
2. Rebuild indexes of the database
3. Alter database set recovery full
Will this break the log ship chain?
I need to change this....since the LDF file grows too high to copy to the DR node.
Please assist !!
Thanks.
March 27, 2012 at 1:34 pm
Your log chain will be fine, but it will not help your log file size:
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 27, 2012 at 10:24 pm
Many Thanks
Thanks.
March 27, 2012 at 10:32 pm
You're welcome.
Have you looked at upgrading? Compressed backups may help you.
More work, but you could also try spreading you index rebuilds out over time so your log backups do not become too large.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 27, 2012 at 10:49 pm
Have you looked at upgrading? Compressed backups may help you.
[Unfortunately, the sql server is SQL 2005...so no compressed bkp possible..]
More work, but you could also try spreading you index rebuilds out over time so your log backups do not become too large.
[Are you suggesting to go for a complete understanding of the indexes and rebuilding indexes over batches]
Thanks.
March 28, 2012 at 10:02 am
Sourav-657741 (3/27/2012)
Have you looked at upgrading? Compressed backups may help you.[Unfortunately, the sql server is SQL 2005...so no compressed bkp possible..]
I understand, that is why I asked if you were open to upgrading.
More work, but you could also try spreading you index rebuilds out over time so your log backups do not become too large.
[Are you suggesting to go for a complete understanding of the indexes and rebuilding indexes over batches]
Yes, that is what I am suggesting. Batches so that your transaction logs will stay at a manageable size for your DR instance.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 28, 2012 at 10:23 am
Under the bulk-logged recovery model, if a log backup covers any bulk operations, the log backup contains both log records and the data pages that were changed by bulk operations. This is necessary to capture the results of the bulk-logged operations. The incorporated data extents can make a log backup very large. Additionally, backing up the log requires access to the data files that contain the bulk-logged transactions. If any affected database file is inaccessible, the transaction log cannot be backed up and all operations committed in that log are lost.
Can you please take a look at the above suggestion?
Even if the bulk logged recovery model actually minimizes the size of the LDF file, still the backup file (.TRN) will be larger since it actually contains the log backup + changes in the data files...
-- So, above all do you think changing the rec model to "Bulk-Log" is a fair choice?
-- Also, since I want to automize the task of rebuild index to a VLDB, so discarding the option of "running the reindex" stuff in batches.
Any more suggestion please!!
Thanks.
March 28, 2012 at 10:38 am
Sourav-657741 (3/28/2012)
-- So, above all do you think changing the rec model to "Bulk-Log" is a fair choice?
If you are doing so to reduce the size of the log backup, no. It will not have the desired effect.
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
March 28, 2012 at 10:46 am
which mode will offer the overall improvement:
Rec Model with "Full" or "Bulk-Logged" ?
Considering the fact that ... my database is a VLDB with highly fragmented indexes!
Thanks.
March 28, 2012 at 10:54 am
I would leave it in full recovery model. What I would look at is developing a set of processes that spreads out your index rebuilds so that it minimizes the impact on your transaction log backups.
One thing to look at as well is what indexes are getting fragmented. If your clustered indexes are getting highly fragmented, you may not have the best clustered index for those particular tables and may need to reconsider the indexing strategies. This is also important when looking at database mirroring, especially over slower WAN links.
March 28, 2012 at 10:55 am
Sourav-657741 (3/28/2012)
which mode will offer the overall improvement:Rec Model with "Full" or "Bulk-Logged" ?
In terms of the size of log backup? Probably neither has an advantage over the other.
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
March 28, 2012 at 1:12 pm
Hello,
I want to consider two aspects --
1. LDF file should not grow too much...so that at Production server, my drive doesn't run out of space.
2. The size of the log backup should not be too much , so that it can copy it across the DR side smoothly. Note: the prod and DR server connection speed is not very fast!
There is no provision at this moment to configure it DB mirror...so , lets not discuss the features of DB-mirroring.
The DB is close to 80 GB and I am looking for a suitable approach to complete the DB-reindexing work which will run weekly once and will go smoothly.
Can you suggest something more?
Thanks.
March 28, 2012 at 1:18 pm
Sourav-657741 (3/28/2012)
Hello,I want to consider two aspects --
1. LDF file should not grow too much...so that at Production server, my drive doesn't run out of space.
2. The size of the log backup should not be too much , so that it can copy it across the DR side smoothly. Note: the prod and DR server connection speed is not very fast!
There is no provision at this moment to configure it DB mirror...so , lets not discuss the features of DB-mirroring.
The DB is close to 80 GB and I am looking for a suitable approach to complete the DB-reindexing work which will run weekly once and will go smoothly.
Can you suggest something more?
To run once weekly? No. Not much more to say here, sorry.
You could try spreading out the index defrags over many hours on a single night, once per week, with log backups in between to keep the .trn files manageable in and of themselves. But cumulatively, it will be what it will be.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 28, 2012 at 1:21 pm
This:
opc.three (3/28/2012)
More work, but you could also try spreading you index rebuilds out over time so your log backups do not become too large.
[Are you suggesting to go for a complete understanding of the indexes and rebuilding indexes over batches]
Yes, that is what I am suggesting. Batches so that your transaction logs will stay at a manageable size for your DR instance.
and this:
Lynn Pettis (3/28/2012)
I would leave it in full recovery model. What I would look at is developing a set of processes that spreads out your index rebuilds so that it minimizes the impact on your transaction log backups.One thing to look at as well is what indexes are getting fragmented. If your clustered indexes are getting highly fragmented, you may not have the best clustered index for those particular tables and may need to reconsider the indexing strategies. This is also important when looking at database mirroring, especially over slower WAN links.
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
March 28, 2012 at 1:23 pm
Sourav-657741 (3/28/2012)
Hello,I want to consider two aspects --
1. LDF file should not grow too much...so that at Production server, my drive doesn't run out of space.
2. The size of the log backup should not be too much , so that it can copy it across the DR side smoothly. Note: the prod and DR server connection speed is not very fast!
There is no provision at this moment to configure it DB mirror...so , lets not discuss the features of DB-mirroring.
The DB is close to 80 GB and I am looking for a suitable approach to complete the DB-reindexing work which will run weekly once and will go smoothly.
Can you suggest something more?
Wasn't considering mirroring, I was discussing index fragmentation on clustered indexes. It is something to be considered for log shipping and database mirroring (which if you look at how MS does it, it is a form of log shipping). If your clustered indexes are becoming highly fragmented, any time you rebuild them you also rebuild the nonclustered indexes on those tables as well.
What I was suggesting was if you are experiencing high fragmentation on the clustered indexes of some tables to revisit those indexes and determine if they are appropriate or if there may be a better candidate for a clustered index that may not become as fragmented or as quickly.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply