Maintenance Plan x Transaction Log


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?

more ▼

asked Feb 10, 2010 at 02:03 PM in Default

avatar image

262 23 23 26

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Feb 10, 2010 at 02:18 PM

avatar image

Grant Fritchey ♦♦
137k 20 47 81

I backup transactions every half hour. The problem is only to run the maintenance plan.

Feb 10, 2010 at 02:35 PM Felipe
(comments are locked)
10|1200 characters needed characters left

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?

more ▼

answered Feb 10, 2010 at 05:45 PM

avatar image

Fatherjack ♦♦
43.8k 79 102 118

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Feb 10, 2010 at 02:03 PM

Seen: 1024 times

Last Updated: Feb 10, 2010 at 02:03 PM

Copyright 2018 Redgate Software. Privacy Policy