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 |
|
pauer
Starting Member
2 Posts |
 Posted - 2008-08-28 : 14:14:16
|
| I have found several posts which answer parts of my question but none which address it completely so I unfortunately have to add yet another post on this topic.I am running SQL Server 2000 SP4.I have a production database of approx. 100 GB.When I run the DBCC REINDEX function via a Maintenance Plan, the transactionb log file expands to approx. the size of the database.Eventually, I will run out of space on the device on which the transaction log resides.I would like to continue to run the reorg. process unattended and do not want to mess with changing the recovery model.The reorg. section of the Maintenance Plan runs for approx. 4 hours.My Question:If I schedule log file backups to run every 30 minutes during this time period, will this cause space in the log file to be re-used, keeping the physical log file size in, say, the 25-50 GB range?I backup log files directly to tape so disk space for the backup is not an issue. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-08-28 : 14:41:20
|
| Points to be noted:1) In SQL 2000, when you rebuild non-unique clustered index ,all non clustered index are rebuilt as well2)Changing Clustered index structure will rebuilt non clustered indexesReorganize will reorganizen the leaf level of index and doesn't update statistics while Rebuild does both.Also,if you don't have issue with disk issue for backup then, why don't you do these:1)Backup log before changing Recovery model to Bulk-Logged2)Rebuild index it with job3)Change Recovery model to Full and take log backup to tape which will be huge in size. |
 |
|
|
pauer
Starting Member
2 Posts |
Posted - 2008-08-29 : 13:48:50
|
| I have posted this question on a couple of forums and none of the replies have been on point.Let me try again.If, over the 4 hour time period the Maintenance Plan (which executes the DBCC REINDEX directives), I periodically (once an hour) perform a concurrent transaction log backup, will this free space in the physical log file to be re-used, thus preventing it from growing to the approximate size of the database itself? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-08-29 : 14:20:09
|
quote:
Originally posted by pauer
I have posted this question on a couple of forums and none of the replies have been on point.Let me try again.If, over the 4 hour time period the Maintenance Plan (which executes the DBCC REINDEX directives), I periodically (once an hour) perform a concurrent transaction log backup, will this free space in the physical log file to be re-used, thus preventing it from growing to the approximate size of the database itself?
It really depends on the size of your tables. DBCC REINDEX for a table happens in a single transaction, so it the table is large, the size of the transaction will be large. The transaction log backup can only backup the committed transactions.It is not uncommon for one table to be most of the space used in the database. It that is the case, your transaction log will get very large, and frequent transaction log backups will not help much.CODO ERGO SUM |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-08-29 : 14:30:49
|
| Why are you using Maintenance plan for Index rebuild? You can use script to Rebuild or Reorganize according to Fragmentation level so it is done quicker? ALso MVJ is right,taking transaction log backup during index rebuild will truncates only inactive portion(Committed transaction from transaction log). |
|
|
|
|
|
|
|
|
Reorganization with a Maintenance Plan