USE msdb;
GO

IF OBJECT_ID('dbo.P_MOVE_TEMPDB_FILES') IS NOT NULL
   EXEC ('DROP PROCEDURE dbo.P_MOVE_TEMPDB_FILES ');
GO    

CREATE PROCEDURE dbo.P_MOVE_TEMPDB_FILES
       @NBR_FICHIER  TINYINT,       -- nombre de fichiers de données à créer
       @DEST_DATA    NVARCHAR(256), -- répertoire destination des données
       @SIZE_DATA_GB SMALLINT,      -- taille des fichiers de données en Go
       @DEST_TRAN    NVARCHAR(128), -- répertoire destination des transactions
       @SIZE_TRAN_GB SMALLINT,      -- taille du journal de transaction en Go
       @GROWTH_MB    TINYINT        -- pas d'incrément des fichiers en Mo
AS
/******************************************************************************
* Procédure modifiant le stockage des fichiers de la base système tempdb      *
*******************************************************************************
* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2013-08-16 *
*******************************************************************************
* Cette procédure permet de redessiner le stockage de la BD système tempdb    *
* FONCTIONNEMENT :                                                            *
* après avoir vérifié les paramètres, la procédure modifie l'organisation du  *
* stockage de la base tempdb et fournit un script pour supprimer les fichiers *
* obsolètes.                                                                  *
*******************************************************************************
* PARAMÈTRES :                                                                *
* @NBR_FICHIER  : nombre de fichiers de données à créer                       *
* @DEST_DATA    : répertoire de destination des fichiers de données           *
* @SIZE_DATA_GB : taille des fichiers de données en Go                        *              
* @DEST_TRAN    : répertoire de destination du journal de transaction         *
* @SIZE_DATA_GB : taille du journal de transaction en Go                      *
* @GROWTH_MB    : taile en Mo du pas d'incrément de tous les fichiers         *
*******************************************************************************
* PROBLÈMES POSSIBLES :                                                       *
* - l'utilisateur qui lance cette procédure n'a pas les privilèges adéquats   *
* - le compte de service de l'instance SQL Server n'a pas les droits en       *
*   lecture/ecriture sur le répertoire de destination                         *
* - le répertoire de destination n'a pas une capacité suffisante pour y       *
*   stocker les fichiers à déplacer                                           *
* - des fichiers possédant le même nom sont déjà en exploitation dans la      *
*   destination
* REMÈDE :                                                                    *
* en cas d'erreur, le résultat (paneau messages de SSMS) fournit la commande  *
* qui est partie en exception, mais aussi toute la partie déjà exécutée du    *
* script de migration.                                                        *
******************************************************************************/
BEGIN
-- variables locales
DECLARE @N TABLE (N TINYINT);
DECLARE @T TABLE (existe BIT, repertoire BIT, parent BIT);
DECLARE @F TABLE (nom_logique  sysname,
                   nom_physique NVARCHAR(384),
                   nature       CHAR(4),
                   "action"     CHAR(6),
                   destination  NVARCHAR(384),
                   taille_GO    SMALLINT);
DECLARE @U TABLE (unite CHAR(1), capacite INT); -- capacité de stockage actuelle  
DECLARE @D TABLE (unite CHAR(1), capacite INT); -- volume à stocké
               
DECLARE @SQL NVARCHAR(max), @SCRIPT NVARCHAR(max), @ERROR NVARCHAR(102);

-- initialisations diverses
SELECT @SIZE_DATA_GB = COALESCE(@SIZE_DATA_GB, 0),
       @SIZE_TRAN_GB = COALESCE(@SIZE_TRAN_GB, 0);
INSERT INTO @N VALUES (0), (1), (2), (3);
INSERT INTO @N
SELECT T1.N + T2.N * 4 +T3.N * 16
FROM   @N AS T1
       CROSS JOIN @N AS T2
       CROSS JOIN @N AS T3
WHERE  T1.N + T2.N * 4 +T3.N * 16 > 3;
UPDATE @N SET N = N + 1;

-- vérifications diverses :
IF @NBR_FICHIER = 0 OR @NBR_FICHIER IS NULL
BEGIN
   RAISERROR('Le nombre de fichiers à spécifier pour les données doit être > 0.',
             16, 1);
   RETURN;
END;
IF @NBR_FICHIER > 64
BEGIN
   RAISERROR('Le nombre de fichiers pour les données (%d) est trop élevé.',
             16, 1);
   RETURN;
END;
IF @SIZE_DATA_GB < 1
BEGIN
   RAISERROR('la taille spécifieé %d pour les données est incorrecte.',
             16, 1, @SIZE_DATA_GB);
   RETURN;
END;
IF @SIZE_TRAN_GB < 1
BEGIN
   RAISERROR('la taille spécifieé %d pour les transactions est incorrecte.',
             16, 1, @SIZE_TRAN_GB);
   RETURN;
END;

-- les destinations sont-elles bien des répertoires ?
IF RIGHT(@DEST_DATA, 1)  '\'
   SET @DEST_DATA = @DEST_DATA + N'\';
SET @SQL = 'EXEC sys.xp_fileexist ''' + @DEST_DATA +''';'
INSERT INTO @T EXEC (@SQL);
IF NOT EXISTS(SELECT * FROM @T WHERE repertoire = 1)
BEGIN
   RAISERROR('L''emplacement %s n''est pas un répertoire de données.',
             16, 1, @DEST_DATA);
   RETURN;
END;
DELETE FROM @T;
IF RIGHT(@DEST_TRAN, 1)  '\'
   SET @DEST_TRAN = @DEST_TRAN + N'\';
SET @SQL = 'EXEC sys.xp_fileexist ''' + @DEST_TRAN +''';'
INSERT INTO @T EXEC (@SQL);
IF NOT EXISTS(SELECT * FROM @T WHERE repertoire = 1)
BEGIN
   RAISERROR('L''emplacement %s n''est pas un répertoire de données.',
             16, 1, @DEST_TRAN);
   RETURN;
END;  
-- y a t-il la capacité de stockage ?
INSERT INTO @U
EXEC ('EXEC xp_fixeddrives;');
INSERT INTO @D SELECT LEFT(@DEST_DATA, 1), @NBR_FICHIER * @SIZE_DATA_GB * 1024;
IF LEFT(@DEST_DATA, 1) = LEFT(@DEST_TRAN, 1)
   UPDATE @D
   SET capacite = capacite + @SIZE_TRAN_GB * 1024
   WHERE unite = LEFT(@DEST_TRAN, 1)
ELSE
   INSERT INTO @D SELECT LEFT(@DEST_TRAN, 1), @SIZE_TRAN_GB * 1024;
SET @ERROR = N'';
SELECT @ERROR = @ERROR + N' '
              + CAST((D.capacite - U.capacite) / 1024.0 AS NVARCHAR(32))
              + N' Go dans l''unité ' + U.unite + N', '
FROM   @U AS U
       INNER JOIN @D AS D
             ON U.unite = D.unite
WHERE  U.capacite < D.capacite;
IF @ERROR  N''
BEGIN            
   SET @ERROR = N'Il manque ' + SUBSTRING(@ERROR, 1, LEN(@ERROR) - 1)
              + N' pour assurer la capacité de stockage demandée.';
   RAISERROR(@ERROR, 16, 1, @DEST_TRAN);
   RETURN;              
END;
-- la procédure xp_cmdshell est-elle utilisable ?
IF NOT EXISTS(SELECT * FROM sys.configurations
              WHERE name = 'xp_cmdshell' AND value_in_use = 1)
BEGIN
   RAISERROR('La procédure xp_cmdshell nécessaire à l''utilisation
             de cette procédure est désactivée. Veuillez contacter
             votre adminisrateur de bases de données', 16, 1);
   RETURN;
END;              
   
-- on stocke les matadonnées de redéfinition du stockage dans @F
-- fichier à modifier :                  
INSERT INTO @F
SELECT name, physical_name, type_desc, 'MODIFY',
       CASE
          WHEN "type" = 0 THEN @DEST_DATA
          WHEN "type" = 1 THEN @DEST_TRAN
       END + REVERSE(SUBSTRING(REVERSE(physical_name), 1 ,  
                     CHARINDEX('\', REVERSE(physical_name))  -1)),
       CASE
          WHEN "type" = 0 THEN @SIZE_DATA_GB
          WHEN "type" = 1 THEN @SIZE_TRAN_GB
       END  
FROM   sys.master_files AS f
WHERE  DB_NAME(database_id) = 'tempdb'
  AND  file_id  2;                        

-- fichiers de données à créer :
INSERT INTO @F
SELECT 'tempdata' + CAST(N AS VARCHAR(2)),  
       NULL,
       'ROWS', 'ADD',
       @DEST_DATA + 'tempdata' + CAST(N AS VARCHAR(2)) + '.ndf',
       @SIZE_DATA_GB
FROM   @N
WHERE  N BETWEEN 1 AND @NBR_FICHIER - 1;

SET @SCRIPT = N'';
-- construction du script de modification du stockage de tempdb
BEGIN TRY
   -- fichiers à modifier
   SET @SQL = N'';
   SELECT @SQL = @SQL + N'ALTER DATABASE tempdb MODIFY FILE ( NAME = '''
               + nom_logique + N''', FILENAME = '''
               + destination + N''', SIZE = '
               + CAST(taille_GO AS VARCHAR(16)) + N' GB, FILEGROWTH = '
               + CAST(@GROWTH_MB AS VARCHAR(16)) + N' MB);'
   FROM   @F
   WHERE  "action" = 'MODIFY';              
   EXEC (@SQL);                
   SET @SCRIPT = @SCRIPT + @SQL + NCHAR(13) + NCHAR(10)
               + N'GO' + NCHAR(13) + NCHAR(10);
               
   -- fichiers à supprimer
   SET @SQL = N'';
   SELECT @SQL = @SQL + N'ALTER DATABASE tempdb REMOVE FILE ['
               + nom_logique + N'];'
   FROM   @F
   WHERE  "action" = 'REMOVE';              
   EXEC (@SQL);                
   SET @SCRIPT = @SCRIPT + @SQL + NCHAR(13) + NCHAR(10)
               + N'GO' + NCHAR(13) + NCHAR(10);
   EXEC (@SQL);
   SET @SCRIPT = @SCRIPT + @SQL + NCHAR(13) + NCHAR(10)
               + N'GO' + NCHAR(13) + NCHAR(10);
               

   -- fichiers à ajouter
   SET @SQL = N'';
   SELECT @SQL = @SQL + N'ALTER DATABASE tempdb ADD FILE ( NAME = '''
               + nom_logique + N''', FILENAME = '''
               + destination + N''', SIZE = '
               + CAST(taille_GO AS VARCHAR(16)) + N' GB, FILEGROWTH = '
               + CAST(@GROWTH_MB AS VARCHAR(16)) + N' MB);'
   FROM   @F
   WHERE  "action" = 'ADD';              
   EXEC (@SQL);                
   SET @SCRIPT = @SCRIPT + @SQL + NCHAR(13) + NCHAR(10)
               + N'GO' + NCHAR(13) + NCHAR(10);
   

   -- tout s'est bien passé, on informe des fichiers à nettoyer
   SELECT nom_physique AS FICHIER_A_SUPPRIMER,
          'EXEC xp_cmdshell ''DEL "' + nom_physique +'"'';'
   FROM   @F
   WHERE  nom_physique IS NOT NULL
     AND  nom_physique  destination;    
END TRY
-- en cas d'ereur...
BEGIN CATCH
   SET @ERROR = ERROR_MESSAGE();
   RAISERROR('Une erreur est survenur lors de la procédure dbo.P_MOVE_TEMPDB_FILES .', 16, 1);
-- renseigne sur la commande ayant levée l'exception
   RAISERROR('L''ereur est survenue lors de la commande : %s', 16, 1, @SQL);
-- renvoie la partie du script déjà exécutée
   RAISERROR(@ERROR, 16, 1);
   PRINT '--- PARTIE DU SCRIPT DÉJÀ EXÉCUTÉE ---';
   PRINT @SCRIPT;  
END CATCH
END;
GO