My database server has a maintenance plan to reindex all tables. After the maintenance plan, the transaction log is too large.
I set the following scenario to try to solve the problem:
1 - Run database backup full to be able to test later.
2 - Before starting the maintenance plan, change the recovery model to SIMPLE.
3 - Run Maintenance Plan.
4 - Manipulate some data.
5 - Back the recovery model to FULL.
6 - Backup transaction log.
I had this idea to check whether it was possible to restore the changed information at the time the database was with the recovery model SIMPLE. If so, I could leave the simple recovery model as it was executed the maintenance plan and solve the problem of enlargement of the transaction log.
To test, I did the following:
1 - Restored the full backup
2 - Tryed to restore the transction log, to verify that the information changed (while the recovery model of the database was as simple) had been restored correctly, but gave error of LSN.
Can anyone help me?
asked Feb 10 '10 at 02:03 PM in Default
First, you'll have to define the phrase "too large." If that much log space is needed to process the data inside your database, then it's not too large. If your database is in FULL recovery mode, how often are you running log backups? Maybe then need to be run more often.
answered Feb 10 '10 at 02:18 PM
Grant Fritchey ♦♦
Do you really need to reindex all tables every time? Are you trying to deal with fragmentation or out of date statistics? Can you split your maintenance plan up or alter the steps so that only indexes that need rebuiding are worked on?
answered Feb 10 '10 at 05:45 PM