Search code examples
sql-serverrdbms

SQL Server 2008 SSIS Export Wizard - find out how long it took

We ran a SQL Server 2008 Export wizard overnight to copy 100+ tables from one server to another. The process worked fine (if a little slow over our network)

The report produced at the end does not show a start and end time for the operation for some unknown reason

I know the names of all the tables created (they are brand new on the target server) - is there any SQL I can run against sys.tables or a similar table that can show the last write time against the table?

The create_date value on sys.tables seems to imply that the export wizard creates empty copies of each table before starting the data insert rather than doing each one in turn

Solution

  • You can get an estimation by looking at the index usage statistics

    SELECT Object_Name(object_id) As object
     , Max(last_user_update) As last_update
FROM   sys.dm_db_index_usage_stats
WHERE  database_id = DB_ID()
GROUP
    BY Object_Name(object_id)
ORDER
    BY object