March 30, 2006 at 2:35 am
"If the database recovery model is set to either bulk-logged or simple, some INDEX DDL operations are minimally logged whether the operation is executed offline or online. The minimally logged index operations are as follows:
CREATE INDEX operations (including indexed views).
ALTER INDEX REBUILD or DBCC DBREINDEX operations.
"
Note that as mentioned earlier, DBCC DBREINDEX is old school now.
Have you got a copy of SQL 2005 BOL?
Basically you want to switch to bulk-logged before your nightly maintenance tasks, then switch back and your shipped logs will be much smaller.
March 30, 2006 at 4:29 pm
Last night we implemented log shipping on one of our smaller databases. We set the recovery mode to FULL and then ran a snippet of T-SQL before and after the nightly index rebuilding to switch it to BULK-LOGGED and back. We also removed the reorganizing altogether.
The initial database backup was about 1 gig, and the 15-minute transaction log chunks are generally around a meg or so. Just after the rebuilding of the indexes, the transaction log chunk size was 300 meg. This is significantly less than before (0.3 vs 2.0+ times the size of the initial backup). However, if it scales linearly with database size, this will still be too big to transfer without a third-party tool to compress the files. Also, unlike some of our other databases, no home-grown, custom maintenance or procedures are running at this time, so all of this activity must be from the maintenance plan-- and it corresponds in timing to the rebuilding.
There is a chance that the database was not successfully placed in BULK-LOGGED mode. The logging for the nightly maintenance plan is so poor I can't tell. I just enabled a server-level DDL trigger so I can see whether the ALTER DATABASE even runs.
I did briefly verify, however, that the log shipping is working with respect to the destination having up-to-date data.
(Regarding the maintenance plan logging, we seem to have fallen victim to some bug which others have noted:
http://www.mcse.ms/archive83-2006-1-2095336.html
)
(As a side note... I remember reading somewhere that reorganizing before rebuilding can lead to a decreased time spent rebuilding. The total time is often greater, but the time the tables are locked is lower because reorganizing is an online process. Well, our reorganizing and rebuilding were both about a half hour, and after eliminating the reorganizing, the rebuild time did not change.)
March 31, 2006 at 9:13 am
Our log from the DDL trigger showed that the ALTER DATABASE statements were issued before and after the rebuild, as intended. And the log file right after the rebuild was exactly the same size as before, around 370 meg. Though I can't tell for sure whether the ALTER DATABASE statements failed and the recovery mode wasn't changed, I think it was, which indicates to me that the BULK-LOGGED recovery mode doesn't eliminate log bloat due to reindexing when it comes to BACKUPS, but it does seem to reduce it a bit. This confirms what I read elsewhere that although the original log only contains a note about what happened, when it is backed up, it extracts the actual changed information, which greatly increases the size of the backup compared to the actual transaction log.
So it looks like the third-party product will in fact be necessary.
Unless anyone has any other ideas or quarrels with this conclusion?
March 31, 2006 at 11:38 am
No, snapshots aren't available in standard edition. See "SQL Server 2005 Database Engine Features" in BOL. I personally don't like the idea of setting up database mirroring and then a snapshot just to have a reporting database. If you have a very large database then you're looking at lots of disk space. Having 3 copies of the same database seems a bit much. Just my opinion 🙂
April 21, 2006 at 8:56 am
The third-party tool (Red-Gate's SQL Backup) was able to achieve basically 90% compression. This makes the solution theoretically possible, but still not desireable.
We are going to try additional options:
1. Less Frequent Re-Indexing (though we lament the potential trade-off in performance).
1. Database Mirroring (though we will miss the ability to verify the functionality of the mirror by direct query).
2. Replication (though we fear the constraints of modifying the schema of the source when it's part of replication).
April 21, 2006 at 5:09 pm
A number of SQL backup tool developers are saying this is pretty much just how it is.
We're proceeding with testing Replication for now.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply