Cleaning up DesignDB Leftovers

Artikel
12/18/2007

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

Comments

Anonymous
December 21, 2007
Maor David on Custom Build Numbers in Team Build. GertD on Cleaning up DesignDB Leftovers and Is DesignDB...

Freigeben über