Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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)

    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  
        (Operation IN 
            AND so.is_ms_shipped = 0)
        ([Lock Information] like '%ACQUIRE_LOCK_SCH_M OBJECT%')
share|improve this question
So did you implement this as part of a job or??? I'ld love to have a method of daily outputting (say at 2am) all changes from the previous 24 hours to a directory so I can have a bit of a changelog for myself. – jcolebrand Feb 1 '11 at 16:39
@jcolebrand yes, I did. I my case I have to check any database activity and then to make backup (full or differential). I am checking LSN (the primary key of log record), that function fn_dblog returns. That is all. I don't think that it will work in your case. I have not investigated all features of data that can be returned by fn_dblog, but I think it does not return all information to do with. As you can see there are many other system tables joined to it. If it was easy we would have a lot of normal, cheap tools :) – garik Feb 1 '11 at 18:46
up vote 6 down vote accepted

One idea would be to make a snapshot every day and monitor the snapshot file size on the disk using a file monitor. The snapshot is increasing its size only when data is added there, so it would be a valid idea if you would find a tool to monitor the real size (reported size).

Now.. I didn't use this, so can't give you technical insights :-).

Another idea would be to verify the transaction log of each db (if you're using full recovery mode on them, of course) with some function I've seen on the forums (db_fnlog.. or something) that reads operations from the log, and see if you have any deletes/inserts/updates.

Those are no easy things to do.. but I hope you'll find them useful.

PS: found the article with the log read function (it's fndblog, by the way :-): Read the transaction log by Jens K. Suessmeyer.

share|improve this answer
+1 fn_dblog is awesome and i think, it and sp_MSforeachdb will be a good solution, i am going to test it. thank you, Marian – garik Jan 30 '11 at 10:51
by the way, monitoring of db files' size does not work because of [size] and [last modify time] change at the moment of db increasing (i have not investigated when, but it occurred at least when I had inserted a lot of records to the table and I think it was caused by database auto increment size process). – garik Jan 30 '11 at 21:13
I wasn't talking about db files sizes, but about snapshot local file, which is created with: create database xxxdb as snapshot of yyydb. See details about snapshots here: – Marian Jan 30 '11 at 21:32
resolved! thank you! – garik Jan 31 '11 at 16:47
That's great, I'm happy I could help you! – Marian Jan 31 '11 at 19:55
  • For DDL changes you may read the Default Trace.
  • For DML modifications since you find CDC to be bit heavy, you may run your own lightweight server side trace which traces only the relevant events
share|improve this answer

For DDL changes you DDL Triggers, But DML Changes you can try using 3 different options

1) Change tracking 2) CDC(Change data Capture) 3) Audit Feature

For Change tracking.. you can see the below link

this change tracking will be only used wheather the table has changed or not...but it is very difficult to find what data has changed.. if you want to find what data has changed then you can go Chnage data Capture.

For Aduit in sqlserver can check the below link

share|improve this answer
+1, but CDC shipped with Enterprise Edition – garik Dec 26 '11 at 11:31

For DML changes you can utilize any of the followinh native SQL Server auditing features:

  • SQL Server Change Tracking
  • SQL Server Change Data Capture
  • SQL Server Auditing

Each has its advantages and disadvantages, but the Auditing is the latest introduced by Microsoft, so it would be a good idea to build your current and future solutions wrapped with it.

Note that only the Auditing feature provides information about Who / When / How

share|improve this answer

You can detect any ddl changes by using trace file. below is script to get changes.

SELECT AS eventtype
FROM sys.fn_trace_gettable
        SELECT TOP 1 
        FROM sys.fn_trace_getinfo(NULL)  
        WHERE property = 2
) T 
INNER JOIN sys.trace_events as te 
    ON t.eventclass = te.trace_event_id 
WHERE eventclass=164

You can detect any modification on table and stored procedure using this script:

 FROM sys.objects AS SO
INNER JOIN sys.schemas AS SS 
    ON SS.schema_id = SO.schema_id 
WHERE DATEDIFF(D,modify_date, GETDATE()) < 50
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.