November 7, 2005 at 1:17 am
I have difficulties backing up the transaction log during a database reoganization.
The database is reorganized every week using a datbase maintanance plan (Reorganize data and index pages in optimizations tab). The database size is 25GB. Due to the reorganization there is also 25GB of transaction log being generated during the reorg process.
I want to backup the transaction log during the reorg process to limit the growth of the transaction logfile but the backup command seems to be hanging. It seems that the reorg is taking up all CPU or other resources. (4 CPUs are present in the machine and can be used by SQL Server)
Therefore I wonder if there are any solutions for this situation:
- Can I limit the resources used by the reorg process?
- Can I "force" a transaction log backup during the reorg? (by giving it a higher priority than the backup?
- Can the reorg be split up?
- ...
Any advise is welcome.
Thanks,
Christophe
November 7, 2005 at 6:25 am
Hi there,
I 've had the same thing. from my undestanding the reson the backup log is hanging us that there are open transactions while the optimization is working. note that even if you change to recovery mode to simple, still the Transaction log will grow.
My advise is not to use the Maintnance plan optimization but rather create a script that issues dbcc dbreindex commands for each DB in your server. In that way you can "split" the optimization process to table level even.
I don't Know a way to limit the optimization process by limiting it's CPU, only limiting the whole SQL Server CPU usage.
Hope this helps,
Yoav S.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply