There are a lot of databases on my client's sql server. In common, these databases is under development, so developers can design, refactor, do data modifications and so on. There are some databases that change rarely. My client has to keep all them safe (backup) and spend a little time to manage this environment (these is no db administrator position in the company). After long discussion client has decided to use every day full backup strategy, because of easiest of restoring its (developers saw so - no problem). OK.
So here is the summary of the situation:
- number of databases can vary every day.
- databases that were changed (it means data or/and structure have been changed) shall be backed up
- databases that were not changed shall NOT be backed up.
- solution shall not impact on database structure (it's not restricted requirement)
- this "backup engine" shall work automatically.
The main problem: how to detect that database has been changed. The first part of the problem (DDL changes) can be resolved by using DDL triggers. It's ok. But the data changes(DML changes) is a problem. It is impossible to apply DML triggers to all tables of all databases to track changes (performance, management of extended objects...). Backup engine has to track any change (at least one) to mark database as ready to backup. Change Data Capture is a solution but it seems to heavy from the first sight (it requires SQL Server Enterprise Edition as well). Other way to track database files changes(size or last change time), but it does not work correctly: database can changes its size after it exceed all reserved free space and sp_spaceused is not a solution. Tracing is a solution but it causes the performance issues and additional management action.
Are there any solution to calculate the actual database usage size without impact of other database management objects (like statistics..)? I agree that any table's data change to the same size value would not triggered table's size change (I think), but it's something than nothing. Really I am looking for direct or indirect solution for sql server 2008.
Thank you for any comments, solutions, thoughts.
Here is the solution (thanks to Marian)
Select NextLSN = MAX(fn.[Current LSN]) ,Databasename = DB_NAME() from fn_dblog(NULL, NULL) fn LEFT JOIN sys.allocation_units au ON fn.AllocUnitId = au.allocation_unit_id LEFT JOIN sys.partitions p ON p.partition_id = au.container_id LEFT JOIN sys.objects so ON so.object_id = p.object_id WHERE ( (Operation IN ('LOP_INSERT_ROWS','LOP_MODIFY_ROW', 'LOP_DELETE_ROWS','LOP_BEGIN_XACT','LOP_COMMIT_XACT') AND so.is_ms_shipped = 0) OR ([Lock Information] like '%ACQUIRE_LOCK_SCH_M OBJECT%') )