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 |
|
hombrouckxp
Starting Member
1 Post |
 Posted - 2002-08-13 : 03:57:15
|
| A strange phenomenon occurs. I'm using SQL 2000 SP 2 on Win 2 K server. Via the maintenance plan wizard I let SQL perform data optimization (reorganize data and index pages). Later on, again via a maintenance plan, I do a full backup. No problem so far. But the next morning, when, again via a maintenance plan, the system performs transaction log backups, the first backup is always very large (2/3 size of the full backup). The full backup is done at night and the first transaction log backup early in the morning, it SHOULD be a small transaction log. I tested it manually, and the same thing happened. Is it not so that a full backup is supposed to clear the log ? What am I missing ? Any help is very appreciated. Greetz to you all.Patrick H |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-08-13 : 06:47:59
|
| A full backup does backup the log but doesn't free it.You can restore from any full backup then apply the following sequence of tran logs thereafter i.e. the tran log backup contains all transactions from the previous tran log backup whether or not there is a full backup in the middle.You should probably do a tran log backup befor or after the full backup.Still not keen on maint plan wizard.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-08-13 : 13:03:20
|
| Nigel is right. The reason it is getting so big is because the index rebuilding causes a lot of activity in the T-Log-Chad |
|
|
|
|
|
|
transaction log backup too big