10

If a database is detached from an instance permanently, are there any cleanup tasks that should be done?

  • 1
    Can you explain the use case for detaching a database permanently? Why not just drop it? – Joe Obbish Aug 21 '17 at 14:23
13

If you detach a database from an instance, you will need to perform an OS-level delete of the file. The safer approach is to drop the database instead.

What I suggest is taking a final backup of the database after you put it into Read Only mode (as this will ensure no activity is occurring during the backup), after which remove it from you system by way of a Drop Database command.

The full set of commands would look similar to the following:

-- Use master db to ensure you don't have an active connection to the db you wish to affect
USE [master]
GO

-- This will kill any active transactions, but will force the database into a Read-Only state
ALTER DATABASE [db_name] SET READ_ONLY WITH ROLLBACK IMMEDIATE
GO

BACKUP DATABASE [db_name] -- Fill in more options here or use the UI to take a backup if you chooose
GO

-- This will kick out all connections from the database allowing you to drop it.
ALTER DATABASE [db_name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

-- Drop the database (which automatically removes the files from the OS)
DROP DATABASE [db_name]
GO

After this, you'll want to look for any Jobs that ran scripts against the database. I would suggest you just wait to see what fails (after which you can script out/delete the job) as there are numerous ways a job can reference a database (not all of which are easy to identify).

Finally, you'll want to remove any users from the instance that only had access to this database. This script should identify whom those users are, though Max's version is much cleaner (I didn't realize he posted an approach until after I edited my answer to include this):

DECLARE @ExecString NVARCHAR (4000)

-- Create Empty Table in a very lazy manner
SELECT  name, principal_id, CAST('' AS NVARCHAR(128)) as database_name
INTO ##tmp_AllDBUsers
FROM sys.server_principals
WHERE 1 = 2

-- Declare Cursor to iterate through all DBs on the instance
DECLARE dbCursor CURSOR
FOR
        SELECT name
        FROM sys .databases


DECLARE @name NVARCHAR (128)
OPEN dbCursor
FETCH NEXT FROM dbCursor
INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @ExecString = 
    'USE [' + @name + '];
    INSERT INTO ##tmp_AllDBUsers
    SELECT sp.name, sp.principal_id, DB_NAME()
    FROM sys.server_principals sp INNER JOIN sys.database_principals dp
        ON sp.sid = dp.sid'

    EXEC(@ExecString)

    FETCH NEXT FROM dbCursor
    INTO @name
END

-- Close and deallocate the cursor because you've finished traversing all it's data
CLOSE dbCursor
DEALLOCATE dbCursor

-- Show all logins that do not belong to a server-level role nor have access to any databases
SELECT sp.*
FROM sys.server_principals sp LEFT JOIN ##tmp_AllDBUsers adu
    ON sp.principal_id = adu.principal_id
WHERE adu.principal_id IS NULL
    AND sp.principal_id NOT IN (SELECT member_principal_id
                            FROM sys.server_role_members)
    AND TYPE IN ('S', 'U', 'G')

-- cleanup
DROP TABLE ##tmp_AllDBUsers
13

I've upvoted John's answer; I'd just like to add some details about other items that you might want to clean up.

  1. SQL Server Agent jobs and alerts might reference the database. Cleaning them up will prevent unnecessary errors being reported.

  2. Remove any Logins that were created specifically for the database. The following T-SQL will identify possible candidate logins that you might investigate to see if they are being used. The code identifies logins that are not referenced by any database.

    DECLARE @cmd nvarchar(max);
    SET @cmd = '    SELECT sp.sid
        FROM master.sys.server_principals sp
    ';
    SELECT @cmd = @cmd + '  EXCEPT 
        SELECT dp.sid
        FROM ' + QUOTENAME(d.name) + '.sys.database_principals dp
    '
    FROM sys.databases d
    WHERE d.[state] <> 6; --ignore offline DBs
    
    SET @cmd = 'SELECT spr.*
    FROM (
    ' + @cmd + '
    ) src
        INNER JOIN master.sys.server_principals spr
            ON src.sid = spr.sid
    WHERE spr.type <> ''R''
        AND spr.name NOT LIKE ''%##MS_%''
        AND spr.name NOT LIKE ''NT %''
        AND NOT EXISTS (
            SELECT 1
            FROM sys.server_role_members srm
            WHERE srm.member_principal_id = spr.principal_id
                )
    ORDER BY spr.name;
    ';
    EXEC sys.sp_executesql @cmd;
    
  3. Backup devices may exist for that database. While removing them is not strictly necessary, if they are not being used, they should go to eliminate potential future confusion.

  4. Server-level triggers may reference the database.

  5. Look for maintenance plans that reference the database - these will fail if they are not updated to remove the missing database.

  • Also the OS files from the DB are still there. No impact to the SQL server environment, but they may need to be deleted or archived to free up disk space – CaM Aug 21 '17 at 15:37
  • @CaM: That has been accounted by John's answer. John's suggestion is to drop the database instead of detaching it, and dropping a DB in SQL Server means deleting the DB files from the file system. – Andriy M Aug 22 '17 at 5:53
1

All major points have been already covered. Below are my 2 cents :

Detaching a database is never a permanent solution as it was intended to be used for moving the database files within the server or to another server. Removing a database permanently can be done by Delete option in SSMS or the DROP database command as mentioned above.

Usually the databases which are intentionally kept offline and keep generating Alerts are the ones we detach and keep it till it can be permanently removed(Deleted).

Pre detach task : Run sp_helpdb dbname to know the file locations.

Cleanup tasks :

  1. Delete the mdf,ndf and ldf files of the database from the locations they reside.
  2. Old backup files for the database needs either to be Deleted or moved to another server considering your retention period.

Other than Logins,Agent Jobs,Triggers and already mentioned points by Max, these 2 can also be looked upon.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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