question

Felipe avatar image
Felipe asked

Maintenance Plan x Transaction Log

Friends,

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?

t-sqlsql-server-2000
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

·
Grant Fritchey avatar image
Grant Fritchey answered

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.

1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Felipe avatar image Felipe commented ·
I backup transactions every half hour. The problem is only to run the maintenance plan.
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered

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?

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.