Log file growing big with log backups

[mebenz]

We have a 10Gb database that is backed up each morning to backup.bak. The logs are then backed up each half hour to the backup.bak.

As a test, I shrunk the log to 500mb. After a week, the log is 3Gb. Since the log is backed up each half hour, how can it possibly grow this large? That is 1/3 of the database size.

 

[mrdenny]

When did the log file grow? Did it grows slowly through out the week, or did it grow as you were doing your database maintenance (re-indexing, etc)?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[mebenz]

It appears that it must be while doing maintenance. Is it possible to NOT log during maintenance? I used the maintenance wizard, but there doesn't seem to be options. If we backup the database prior to performing the maintenace, why would it need to log all the changes for the maintenance?

 

[mrdenny]

Is it possible to NOT log during maintenance?

No it is not.

If we backup the database prior to performing the maintenace, why would it need to log all the changes for the maintenance?

Because as the maintenance runs objects within the database are changed. Indexes are rebuild, data within the indexes is moved, statistic data is updated, etc. All of this must be logged so that if the database crashes during the maintenance the changes can be rolled back. Also when you restore the logs these changes must be rolled forward.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[mebenz]

WE backup just prior to the maintenance so that if a rollback was required, we would just restore the database backup, not the logs.

I guess I can add another step to shrink the logs after it completes.

 

[mrdenny]

WE backup just prior to the maintenance so that if a rollback was required, we would just restore the database backup, not the logs.

Yes but not everyone does that, and it's not required that it's done that way. The developers of the database engine have to account for everyone.

Not to mention that you can have user transactions happening within the database during the database maintenance. In order for their transactions to be replayed correctly all transactions would need to be logged.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[mebenz]

The database can't be placed into single-user mode if users are accessing it. Is it normal to be running maintenance with users actively using the database?

We run these on the weekend when no users are any of the applications which access the database, as the maintenance will fail if there are active connections to it.

 

[mrdenny]

If the maintenance plan is trying to put the database into single user mode it's because you've got the plan set to fix errors automatically. To get the maintenance plan to stop failing uncheck the automatically fix minor problems (or what ever the check box is labeled) check box. Then when it fails review the log and take corrective action manually.

It's typically best practice to have SQL tell you about problems so that you can then take corrective action rather than let the app make decisions for you.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog