Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
debug2k
Starting Member
18 Posts |
 Posted - 2004-08-02 : 05:04:22
|
| Hello All,I am facing a frustrating problem. I am implementing a Reindexing job for my prod databases, the problem is that when I start reindexing job the Log file size increases to 1 GB and none of my databases are greater than 3 GB.The logs are getting backed up hence the recovery mode is set to 'FULL'.Please let me know a way in which i can avoid the expansion of Log File to such an extent.Regards,Debug |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-08-02 : 05:39:08
|
| You can't really. Reindexing is a log intensive operation. Some people use simple recovery mode whilst this stuff takes place, but that depends on what your recovery methods need. You could do the reindexing in smaller batches.-------Moo. :) |
 |
|
|
debug2k
Starting Member
18 Posts |
Posted - 2004-08-02 : 06:36:53
|
| thanks a lot mate !!But are u suggesting that , while i am reindexing the database, i should change the recovery mode to 'Simple' and after reindexing I should make it to full and carry on with the normal Trans log backups.Regards,Deepak Uniyal |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-08-02 : 07:21:51
|
Since you already have a job to backup the log file when it becomes full, i assume you're also truncating it so that it maintains its size?If your answer is yes, you could run the job before reindexing. If you have a job for reindex, just run step1 as backup and truncate and step 2 for reindex.quote:
Originally posted by debug2k
thanks a lot mate !!But are u suggesting that , while i am reindexing the database, i should change the recovery mode to 'Simple' and after reindexing I should make it to full and carry on with the normal Trans log backups.Regards,Deepak Uniyal
|
|
|
|
MuadDBA
628 Posts |
Posted - 2004-08-02 : 09:30:53
|
quote:
Originally posted by debug2k
thanks a lot mate !!But are u suggesting that , while i am reindexing the database, i should change the recovery mode to 'Simple' and after reindexing I should make it to full and carry on with the normal Trans log backups.Regards,Deepak Uniyal
NO.If you're going to do things this way, you need an intimate understanding of how backups work on SQL Server so that you don't lose data.Depeding on the criticality of your data, you need to:Set up a job to truncate the TLOG every 5 - 30 minutes (depending on how fast it grows) DO NOT ENABLE IT yet.Lock all users out of the DB.Perform a tlog backup, then disable tlog backups.Set recovery mode to SIMPLE.Enable the TLOG truncate job.Begin reindexing.Once reindexing is complete, Disable the TLOG Truncate job, enable tlog backups.Begin full DB Backup.That will give you pretty good recovery options. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-08-02 : 10:16:37
|
| You would only have to lock out all the user if you can't afford to lose data during the rebuild operations. Many people perform these things when hardly anyone is on the system, so in case of disaster they're willing to lose that data. Just something to think about.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
debug2k
Starting Member
18 Posts |
Posted - 2004-08-02 : 13:07:45
|
| Hi Derrick,We are blessed with permission to take an outage for maintenance tasks. So, when we do this nobody would be logged into the server.In this case would crazyjoe's suggestions be feasable for going ahead.Regards,Deepak Uniyal |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-08-02 : 14:04:22
|
| oops, I noticed I forgot to include the step where you let all the users back on the system....they get pretty mad when you forget that step. |
|
|
|
|
|
|
|
|
Reindexing