MSSQL Decom Database

Check

List databases not used since last restart

To get an idea of whether a database is unused (and, therefore, safe to decommission) use:

select [name] from sys.databases 
where database_id > 4 AND [name] NOT IN 
    (select DB_NAME(database_id) 
    from sys.dm_db_index_usage_stats
    where coalesce(last_user_seek, last_user_scan, last_user_lookup,'1/1/1970') > 
    (select login_time from sysprocesses where spid = 1)
)

The above query show DBs which have had no usage since the last restart, without relying on query plans being held in the cache, as it shows user IO against the indexes (and heaps). This is sort of along the lines of using virtual file stats, but the DMV used here excludes IO activity from backups. No need to keep a profiler trace running, no triggers or auditing required. Of course, if you restart your SQL server frequently (or you attach/shutdown databases often) this might not be the way to go :-)

http://dba.stackexchange.com/questions/2050/how-can-i-tell-if-a-sql-server-database-is-still-being-used

Last DML date

SELECT d.name as  dbName, max(u.last_user_update) as [LastUpdate]--, max(user_updates)
FROM sys.databases as d
  left outer join sys.dm_db_index_usage_stats as u on d.database_id = u.database_id 
where d.database_id >4
group by d.name
order by 1

Last DDL

select isnull(SCHEMA_NAME(schema_id), ' ') + '.' + name as name, type_desc ,create_date , modify_date
from sys.objects 
order by modify_date desc

Even if above queries seems to confirm that a DB can be dropped, definitely do the OFFLINE/detach (see later) or deny user access for some time, plus any due diligence of asking around before actually dropping!

Disable Jobs

Find and disable jobs working in context of decommissioned database and change login's default database if needed

use [msdb]
go
select distinct j.name as NAME_OF_JOB, s.step_name, s.database_name, s.command
from sysjobs as j, sysjobsteps as s
where 
j.job_id = s.job_id
and j.enabled = 1
and (
  s.database_name like 'DB_TO_DROP'
  or s.command like '%DB_TO_DROP%'
)
order by 1, 2
go

EXEC msdb.dbo.sp_update_job @job_name=N'NAME_OF_JOB', @enabled=0

Default Database

Change Login's default database if needed

use [master]
go
select name as LOGIN_NAME, dbname from syslogins where dbname = 'DB_TO_DROP'
go

ALTER LOGIN [LOGIN_NAME] WITH DEFAULT_DATABASE=[master]

Backup


NOTE: If the database has been backed up using a scheduled backup plan and there has been no recent database activity then you may not a specific backup. i.e. you should be able to restore from the scheduled backup if necessary. Until you know you do not need to restore you should not delete the backup history.
use [master]
go
BACKUP DATABASE [DB_TO_DROP]
TO DISK = N'G:\SQLBackup\DB_TO_DROP\FULL\DB_TO_DROP_FULL_2017_10_24_075700_0000000.bak.CT17-0349086' 
WITH NOFORMAT, NOINIT,  NAME = N'DB_TO_DROP-LAST Full Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
RESTORE VERIFYONLY 
FROM DISK = N'G:\SQLBackup\DB_TO_DROP\FULL\DB_TO_DROP_FULL_2017_10_24_075700_0000000.bak.CT17-0349086' 
WITH FILE = 1,  NOUNLOAD,  NOREWIND
GO

Monitoring

TODO ** THIS MONITORING STILL TO BE DOCUMENTED **

Turn off monitoring for off-lined database

use [DBAUtils]
go
insert into [DBToSkip_X20aT] values('DB_TO_DROP', getdate())
go

Offline Database

use [master]
go
ALTER DATABASE [DB_TO_DROP] SET OFFLINE WITH ROLLBACK IMMEDIATE

Update MDBdb

TODO ** MDBdb STILL TO BE DOCUMENTED **

update mdbdb.databases 
set decom = sysdate, decom_change='Change#', dbactive = 'Decom' 
where hostname='hostname' and INSTANCE_NAME='instance_name' and database_name = 'database_name';

DROP Database

After leaving database offline for a week or two and if no other errors have been reported you can drop that database.

Make database online (doing that will allow us to drop database files from disk automatically)
use [master]
go
ALTER DATABASE [DB_TO_DROP] SET ONLINE
go

Delete backup history (optional)

--if select COUNT(*) from msdb..backupset returns >= 100K execute in loop
declare @CleanupDate datetime 
set @CleanupDate= (select min(backup_start_date) from msdb..backupset)
set @CleanupDate = DATEADD(day, 30, @CleanupDate)
select @CleanupDate
execute msdb.dbo.sp_delete_backuphistory @oldest_date = @CleanupDate
--if select COUNT(*) from msdb..backupset returns < 100K execute
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'DB_TO_DROP'
GO

Drop Database

USE [master]
GO
DROP DATABASE [DB_TO_DROP]

--Clear monitoring table
use [DBAUtils]
go