SQL Server 2005: What percentage of changes happened within the database after the last successful backup?
No doubt that the blog subject will be a most wanted requirement on their databases, that is most responsible for a SQL Server database then arguably the most critical task that you absolutely MUST get right is your database backup procedures.
Where the backup sequence comes the database RECOVERY model comes as another important step, choosing best model is highlight for the transaction log too and this TransactionLog blog post refers best practices. To go through the history of transaction log importance we must refer through the FULL recovery model role, such as back up the Transaction Log regularly as well as the database. Obviously if you fail to do this the Transaction Log will grow and grow, that will cause disk free space issue on the drive where this log file is located. It is a general mis-conception of the users that when you choose SIMPLE recovery model it will take of the size, but its not! By default when you backup the Transaction Log it is 'truncated' at which point all completed transactions will be wiped from the log file and the space that they occupied will be made available for future transactions. The size of the Transaction Log is not changed by the backup process however, just the amount of 'free' space contained within it, shrinking the log size is different subject here.
So to cover up what you want to know changes happened within the database after the last good known 'successful' backup then our own SQL Storage Engine guru Paul Randal has given useful script to obtain information in a database to report the percentage of extents that have changed since last backup, refer to DatabaseChange-since-LastBackup post from his blog.