SQL Server - Decommissioning a database part 1

Today we will go through some tips for decommissioning a database. It can be very difficult to decommission a database, the people who set it up fifteen years ago are no longer there, there is a service running from an old NT4 application server somewhere keeping connections to the database, but is it really being used?

Unfortunately there is no 100% method to find out but we can do a lot technically and I will go through some different methods below. If you have any good tips yourself, please let us know at addprodba@addpro.se.

Check when the index was last used
With the help of DMV index_usage_stats we can find out when when an index was last used, below lists the usage of indexes in the current database and shows objects and index names. Note that it is since the last restart as well as when you are in and pillaging you can get the date updated. It is also possible to get the information via the standard reports, right click on your database, select Reports, Standard Reports, Index Usage Statistics. Note that statistics for HEAP tables are not shown in the report.

SELECT so.name AS ObjectName, ISNULL(si.name,'[HEAP]') AS IndexName, si.type_desc As IndexType, last_user_lookup, last_user_scan, last_user_seek, last_user_update FROM sys.dm_db_index_usage_stats ius
RIGHT JOIN sys.objects so ON so.object_id = ius.object_id
RIGHT JOIN sys.indexes si ON si.object_id = ius.object_id AND si.index_id = ius.index_id
WHERE database_id = DB_ID()

Check dates in tables
Below queries look through all tables for date columns "datetime" (modify if you also want to include modernities such as datetime2, datetimeoffset, date etc), copy the lastDateQuery rows to query no 2. This gives some indication of the extent to which data in tables is used, however it needs to be linked to what the table is doing, if it is a current order table the information is interesting, but a history or future project table makes no sense at all as the data may have been entered years ago but refer to something happening in the future.

Run this first!

SELECT table_schema, table_name, column_name,
'INSERT INTO @LastDates SELECT TOP 1 ' + column_name + ',' + '''' + table_name + '''' + ',' + '''' + column_name + '''' + ' FROM ' + table_schema + '.' + table_name + ' ORDER BY ' + column_name + ' DESC' AS lastDateQuery,column_name,ordinal_position,data_type,character_maximum_length
from INFORMATION_SCHEMA.COLUMNS where data_type = 'datetime'

Copy all lines of lastDateQuery from above result and paste below.

DECLARE @LastDates table(
            xlastrow datetime,
    xtablename varchar(100),
    xrowname varchar(100));
-- { KLISTRA IN lastDateQuery rader here }
SELECT * FROM @LastDates

And run to display last date in each table containing date columns.

Enable AUTO CLOSE on the database
If we are reasonably sure that the database is not in use, we can turn on AUTO CLOSE. The database will then shut down when not in use and will automatically start up when someone tries to use it. This means that we will have some performance degradation due to shutdown/startup but at this point we don't expect it to happen very often. The advantage of this method is that at every startup this is logged in the Error log so we can wait a few weeks and then check the Error log if something happened.

Start a trace
Start SQL Server Profiler, run a trace on e.g. Audit Login, ExistingConnection, RPC:Completed, SQL:BatchCompleted. Use filters on database and set up the database(s) you intend to decommission, you will probably need to set up additional filters to remove various jobs and other things that go against your databases. Keep in mind that a user may be logging in against a different database but asking questions that include your database. If a user has selected a different database (USE OtherDatabase) and is running a query against OtherDatabase but which includes, for example, a JOIN against the database we are going to decommission, that will not be included in the trace. So when the database we intend to decommission is used indirectly, we may not catch it in our trace, but one advantage is that what we do catch, we know exactly where it comes from, which machine, the application name and the username.