August 4, 2008 at 9:22 pm
Hi ,
i have made the log shipping setup with the data base recovery mode as 'Full' .it is running fine . After some time i have changed the data base recovery mode as 'Bulk-logged' because i don't to grow log file when i do the rebuild index . albeit log file increase the size when i do the rebuild index for data base recovery mode bulk logged . can any explain about it . why its happening .(sql server 2005)
Thanks in advance .
August 4, 2008 at 10:03 pm
The rebuild operation is logged. Bulk mode deals with bulk loading, it doesn't stop logging.
August 5, 2008 at 4:47 am
Under the bulk-logged recovery model, the modified pages arising from the index rebuild are backed up when you back up the transaction log. So, even though your transaction log file is say 1 MB, you could end up with a transaction log backup file of 30 MB, if 30 MB worth of pages were modified as a result of the index rebuild.
You can verify this by looking at the output from the backup of a transaction log that contains minimally logged transactions e.g.
Processed 480 pages for database 'indextest', file 'indextest' on file 28.
Processed 66 pages for database 'indextest', file 'indextest_log' on file 28.
BACKUP LOG successfully processed 546 pages in 0.737 seconds (6.064 MB/sec).
Note the first line that says pages from the database's data file were backed up. These were the pages that were modified as the result of a minimally logged operation.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply