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