Cleaning up DesignDB Leftovers

In the "DesignDB Location" blog post I described where the design-time validation database are created. The attentive reader would have wondered when design-time validation database file get cleaned up, since we create them at project creation time and attach or re-create at project open time. So when do they get removed?

The answer: DesignDB files don't get removed!

So the question is how do I clean-up the files left behind? The following stored procedure is an attempt to help you with this task. It identifies the files, checks if the files are not currently in use by an open database and when not you can delete the files, if you explicitly ask the procedure to do so.

Usage:

There are two use cases, the first is to identify files that are left behind, the second is to explicitly delete them.

Identifying leftovers:

The following three invocations have the same result:

EXEC [master].[dbo].[CleanupDesignDB]
EXEC [master].[dbo].[CleanupDesignDB] 0
EXEC [master].[dbo].[CleanupDesignDB] @ConfirmDeletionOfFiles = 0

It will generate the following type of output:

    1:  DefaultData directory: [C:\DBS\]
    2:  File in-use by database: [AWRef_DB_c3af5090-9bac-433f-bf9b-295d3d60b2ef] - [C:\DBS\AWRef_DB_c3af5090-9bac-433f-bf9b-295d3d60b2ef.ldf]
    3:  File in-use by database: [AWRef_DB_c3af5090-9bac-433f-bf9b-295d3d60b2ef] - [C:\DBS\AWRef_DB_c3af5090-9bac-433f-bf9b-295d3d60b2ef.mdf]
    4:  File in-use by database: [AWRef_DB_c3af5090-9bac-433f-bf9b-295d3d60b2ef_0] - [C:\DBS\AWRef_DB_c3af5090-9bac-433f-bf9b-295d3d60b2ef_00_633335867071557042.mdf]
    5:  File in-use by database: [AWRef_DB_c3af5090-9bac-433f-bf9b-295d3d60b2ef_0] - [C:\DBS\AWRef_DB_c3af5090-9bac-433f-bf9b-295d3d60b2ef_01_633335867071557042.ldf]
    6:  File in-use by database: [AW_DB_389182d6-38c2-437b-bcd9-82ae8aa9b2e4] - [C:\DBS\AW_DB_389182d6-38c2-437b-bcd9-82ae8aa9b2e4.ldf]
    7:  File in-use by database: [AW_DB_389182d6-38c2-437b-bcd9-82ae8aa9b2e4] - [C:\DBS\AW_DB_389182d6-38c2-437b-bcd9-82ae8aa9b2e4.mdf]
    8:  Will be deleting: [C:\DBS\NW_DB_ad914c71-d155-451b-9607-0d122c8a274c.ldf]
    9:  Will be deleting: [C:\DBS\NW_DB_ad914c71-d155-451b-9607-0d122c8a274c.mdf]

Line 1: Returns the directory that is being scanned based on the settings detected, in this example the C:\DBS directory on my notebook.

Lines 2-7: Identify DesignDB files which are detected but are currently in use by an open database. This implies you have one or more active Visual Studio instances open which have one or more database projects open.

NOTE: If you terminated Visual Studio prematurely because you killed the devenv.exe process or crashed you can also have open design-time validation databases.

The files identified in lines 2-7 can and will not be delete because they are in use.

Line: 8-9 Identify in this example two files that are not in use and match the naming pattern of the design-time validation database and therefore are candidates to be deleted.

Cleanup leftovers

After you identified the candidate files to delete, you can use the same procedure to actually delete them by setting the @ConfirmDeletionOfFiles parameter of the CleanupDesignDB procedure to 1. This way is your explicit acknowledgement that you want to delete the files identified.

The following two invocations will have the same result:

EXEC [master].[dbo].[CleanupDesignDB] 1
EXEC [master].[dbo].[CleanupDesignDB] @ConfirmDeletionOfFiles = 1

When you choose to delete the files the output of the procedure will reflect this as such:

    1:  DefaultData directory: [C:\DBS\]
    2:  File in-use by database: [AWRef_DB_c3af5090-9bac-433f-bf9b-295d3d60b2ef] - [C:\DBS\AWRef_DB_c3af5090-9bac-433f-bf9b-295d3d60b2ef.ldf]
    3:  File in-use by database: [AWRef_DB_c3af5090-9bac-433f-bf9b-295d3d60b2ef] - [C:\DBS\AWRef_DB_c3af5090-9bac-433f-bf9b-295d3d60b2ef.mdf]
    4:  File in-use by database: [AWRef_DB_c3af5090-9bac-433f-bf9b-295d3d60b2ef_0] - [C:\DBS\AWRef_DB_c3af5090-9bac-433f-bf9b-295d3d60b2ef_00_633335867071557042.mdf]
    5:  File in-use by database: [AWRef_DB_c3af5090-9bac-433f-bf9b-295d3d60b2ef_0] - [C:\DBS\AWRef_DB_c3af5090-9bac-433f-bf9b-295d3d60b2ef_01_633335867071557042.ldf]
    6:  File in-use by database: [AW_DB_389182d6-38c2-437b-bcd9-82ae8aa9b2e4] - [C:\DBS\AW_DB_389182d6-38c2-437b-bcd9-82ae8aa9b2e4.ldf]
    7:  File in-use by database: [AW_DB_389182d6-38c2-437b-bcd9-82ae8aa9b2e4] - [C:\DBS\AW_DB_389182d6-38c2-437b-bcd9-82ae8aa9b2e4.mdf]
    8:  Deleting: [C:\DBS\NW_DB_ad914c71-d155-451b-9607-0d122c8a274c.ldf]
    9:  Deleting: [C:\DBS\NW_DB_ad914c71-d155-451b-9607-0d122c8a274c.mdf]

As you can see lines 7-8 indicate the file is actually being deleted. When you run the procedure again the files will be gone!

The code:

This is the T-SQL code for the CleanDesignDB stored procedure.

    1:  -- ---------------------------------------------------------------------------------------
    2:  -- CleanupDesignDB.sql
    3:  -- Written by Gert E.R. Drapers (GertD@SQLDev.Net) - All Rights Reserved.
    4:  --
    5:  -- Disclaimer - Thoroughly test this script, execute at your own risk.
    6:  -- ---------------------------------------------------------------------------------------
    7:  USE master
    8:  GO
    9:   
   10:  IF (OBJECT_ID('[dbo].[CleanupDesignDB]') IS NOT NULL)
   11:      DROP PROC [dbo].[CleanupDesignDB]
   12:  GO
   13:   
   14:  CREATE PROC [dbo].[CleanupDesignDB]
   15:  @ConfirmDeletionOfFiles int = 0
   16:  AS
   17:  BEGIN
   18:   
   19:  SET NOCOUNT ON
   20:   
   21:  DECLARE @datadir nvarchar(260),
   22:          @rc      int
   23:   
   24:  -- We want explicit conformation if you want the proc to delete files
   25:  --
   26:  IF NOT (@ConfirmDeletionOfFiles = 0 OR @ConfirmDeletionOfFiles = 1)
   27:  BEGIN
   28:      RAISERROR(N'Incorrect value for parameter @ConfirmDeletionOfFiles possible values [0|1]', 1, 16, 1) WITH NOWAIT
   29:  END
   30:   
   31:  -- Check if xp_cmdshell usage has been enabled, by default this is turned off in SQL Server 2005
   32:  --
   33:  IF NOT EXISTS (
   34:      SELECT     * 
   35:      FROM     [sys].[configurations]
   36:      WHERE    [name] = 'xp_cmdshell'
   37:      AND        [value_in_use] = 1)
   38:  BEGIN
   39:      RAISERROR(N'xp_cmdshell is not enabled, run exec sp_configure ''xp_cmdshell'', 1', 16, 1) WITH NOWAIT
   40:  END
   41:   
   42:  -- Get the DefaultData directory
   43:  --
   44:  EXEC @rc = [master].[dbo].[xp_instance_regread] 
   45:          N'HKEY_LOCAL_MACHINE', 
   46:          N'Software\Microsoft\MSSQLServer\MSSQLServer', 
   47:          N'DefaultData', 
   48:          @datadir output, 
   49:          'no_output'
   50:   
   51:  -- Add slash to the end of the path if not present
   52:  --
   53:  IF ((@datadir IS NOT NULL) AND (CHARINDEX(N'\', @datadir, len(@datadir)) = 0))
   54:  BEGIN
   55:      SELECT @datadir = @datadir + N'\'
   56:  END
   57:   
   58:  -- Use same path as master if DefaultData path is not set (NULL)
   59:  --
   60:  IF (@datadir IS NULL)
   61:  BEGIN
   62:      SELECT    @datadir = SUBSTRING([physical_name], 0, CHARINDEX(N'master.mdf', [physical_name]))
   63:      FROM    [master].[sys].[database_files]
   64:      WHERE    [file_id] = 1
   65:  END
   66:   
   67:  PRINT 'DefaultData directory: [' + @datadir + ']'
   68:   
   69:  -- Temporary table to hold files inside directory
   70:  --
   71:  CREATE TABLE #f
   72:  (
   73:      [subdirectory]    nvarchar(260) not null,
   74:      [depth]         int not null,
   75:      [file]            int not null
   76:  )
   77:   
   78:  -- Insert all filenames in @datadir path in to temp table 
   79:  -- 
   80:  INSERT INTO #f EXEC [master].[dbo].[xp_dirtree] @datadir, 1, 1  
   81:   
   82:  -- Pattern match on distinct pattern of DesignDB naming, if no files found bail
   83:  --
   84:  IF NOT EXISTS (
   85:      SELECT    [subdirectory] AS [filename]
   86:      FROM    #f
   87:      WHERE     [file] = 1 
   88:      AND       ([subdirectory] LIKE '%[_]DB[_]________[-]____[-]____[-]____[-]____________[.]_[dD][fF]' 
   89:      OR        [subdirectory] LIKE '%[_]DB[_]________[-]____[-]____[-]____[-]____________[_]__[_]__________________[.]_[dD][fF]'
   90:             ))
   91:  BEGIN
   92:      PRINT N'No files found'
   93:      RETURN
   94:  END
   95:    
   96:  DECLARE filename_cursor CURSOR
   97:  READ_ONLY
   98:  FOR 
   99:  SELECT    [subdirectory] AS [filename]
  100:  FROM    #f
  101:  WHERE     [file] = 1 
  102:  AND       ([subdirectory] LIKE '%[_]DB[_]________[-]____[-]____[-]____[-]____________[.]_[dD][fF]' 
  103:  OR        [subdirectory] LIKE '%[_]DB[_]________[-]____[-]____[-]____[-]____________[_]__[_]__________________[.]_[dD][fF]'
  104:         )  
  105:   
  106:  DECLARE @filename nvarchar(260),
  107:          @fullpath nvarchar(260),
  108:          @cmdstmt nvarchar(4000),
  109:          @fileexist int,
  110:          @db_id int
  111:   
  112:  OPEN filename_cursor
  113:   
  114:  FETCH NEXT FROM filename_cursor INTO @filename
  115:  WHILE (@@fetch_status <> -1)
  116:  BEGIN
  117:      IF (@@fetch_status <> -2)
  118:      BEGIN
  119:          SELECT @fullpath = @datadir + @filename
  120:   
  121:          EXEC [master].[dbo].[xp_fileexist] @fullpath, @fileexist OUTPUT
  122:          IF (@fileexist = 1)
  123:          BEGIN         
  124:              IF NOT EXISTS (
  125:                  SELECT  *
  126:                  FROM     [dbo].[sysaltfiles]
  127:                  WHERE     UPPER([filename]) = UPPER(@fullpath))
  128:              BEGIN
  129:                  IF (@ConfirmDeletionOfFiles = 0)
  130:                  BEGIN
  131:                      PRINT 'Will be deleting: [' + @fullpath +']'
  132:                  END
  133:                  IF (@ConfirmDeletionOfFiles = 1)
  134:                  BEGIN
  135:                      PRINT 'Deleting: [' + @fullpath +']'
  136:                      SELECT @cmdstmt = 'DEL "' + @fullpath +'"'
  137:                      EXEC [master].[dbo].[xp_cmdshell] @cmdstmt, no_output
  138:                  END
  139:              END
  140:              ELSE
  141:              BEGIN
  142:                  SELECT  @db_id = [dbid]
  143:                  FROM     [dbo].[sysaltfiles]
  144:                  WHERE     UPPER([filename]) = UPPER(@fullpath)
  145:                  
  146:                  PRINT 'File in-use by database: [' + db_name(@db_id) + '] - [' + @fullpath +']'
  147:              END    
  148:          END
  149:          ELSE
  150:          BEGIN
  151:              PRINT 'File does not exist: [' + @fullpath +']'
  152:          END
  153:      END
  154:      FETCH NEXT FROM filename_cursor INTO @filename
  155:  END
  156:   
  157:  CLOSE filename_cursor
  158:  DEALLOCATE filename_cursor
  159:   
  160:  DROP TABLE #f
  161:   
  162:  END
  163:  GO

Download: CleanDesign.sql

-GertD