Un des collègues (qui se reconnaitra ;-)) a rencontré un souci chez un de nos clients qui était plutôt étrange dirons nous. Le client voulait renommer le nom logique d’un de ces fichiers de bases de données et se retrouvait avec l’erreur « Msg 5041, Level 16, State 1, Line 1 MODIFY FILE ‘FileName’ failed. File does not exist. » alors que le fichier existait bien contrairement à ce que disait SQL Server. La version de SQL Server : 2008 R2 SP1. Nous voila parti à résoudre un problème bien curieux …

Je m’étais déjà retrouvé avec ce genre d’erreurs mais je ne savais plus comment le résoudre. Finalement une recherche sur internet nous ramène rapidement à une solution de contournement que l’on peut retrouver dans le KB 2673247. Ce dernier nous explique que ce problème arrive parce que l’entrée n’est pas unique dans le catalogue système. Pourtant lorsque nous interrogeons les vues du catalogue système, nous ne constatons pas ce problème. Alors qu’est-ce qu’il nous dit SQL Server ???

En creusant un peu on arrive vite à la conclusion qu’il avait raison (heureusement ). Commençons par reproduire le problème. Petite précisions avant : je suis sur un environnement SQL Server 2008 R2 SP1 pour être aligné avec l’environnement du client mais j’avais déjà rencontré ce problème sur une plateforme SQL Server 2005.

On créé une base de données nommée bug

• On initie une sauvegarde complète
• On ajoute un fichier journal à la base de données bug mais on s’aperçoit que l’on s’est trompé et on le supprime derrière
• On sauvegarde le journal des transactions de la base de données
• On ajoute à nouveau un fichier de données cette fois-ci avec le même nom logique que le fichier précédemment supprimé
• On tente de renommer ce fichier logique avec un nouveau nom

USE [master];
GO

РCr̩ation de la base de donn̩es bug
IF DB_ID(‘bug’) IS NOT NULL
BEGIN
ALTER DATABASE bug SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE bug;
END

CREATE DATABASE bug;
GO

РSauvegarde de la base de donn̩es bug
BACKUP DATABASE bug TO DISK = ‘E:\sql_backup\bug.bak’ WITH INIT;

– Ajout d’un fichier log à la base de données bug
ALTER DATABASE bug
ADD LOG FILE (NAME = ‘bug_log3′, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\bug_log3.ldf’);

РSuppression du fichier log pr̩c̩demment cr̩̩
alter database bug
remove file bug_log3;

РSauvegarde du journal des transactions de la base de donn̩es bug
BACKUP LOG bug TO DISK = ‘E:\sql_backup\bug.trn’ WITH INIT;
GO

– Ajout d’un fichier de données supplémentaire avec le même nom logique que celui précédemment supprimé
ALTER DATABASE bug
ADD FILE (NAME = ‘bug_log3′, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\bug_log3.ndf’);
GO

On tente de renommer le fichier …

– On tente de renommer le fichier de données supplémentaire –> Erreur
ALTER DATABASE bug
MODIFY FILE (NAME = ‘bug_log3′, NEWNAME = ‘bug_3′);
GO

… et on se retrouve avec l’erreur :

Msg 5041, Level 16, State 1, Line 2
MODIFY FILE failed. File ‘bug_log3′ does not exist.

Via l’interface graphique on se retrouve également avec l’erreur suivante :

Si on revient sur ce qui a été dit tout à l’heure, l’article Microsoft nous dit ceci :

This issue occurs if the logical name of the file that you try to remove is not unique in the system catalog tables. For example, this issue occurs if the file existed in the database earlier, and then the file was removed.
When you try to remove a file that has the same logical name, SQL Server 2008 tries to remove the dropped logical file. This results in the error message.

Visiblement il semblerait que nous ayons une entrée fantôme en double dans les tables systèmes. Pourtant si nous interrogeons les vues systèmes sys.database_files ou sys.master_files on ne constate rien de particulier :

On ne voit rien mais on peut remarquer que la séquence des id de fichier n’est pas continue. Il manque le fichier dont l’id est égale à 3. Comment voir cette entrée « fantôme » dans les tables systèmes ? La solution passe par une connexion dédiée administrateur et l’interrogation de la vue système non documentée sys.sysfiles1

Effectivement nous avons bien notre doublon. Lorsque l’on essaie de renommer notre fichier bug_log3 c’est la première entrée dans l’ordre des id de fichiers qui est pris en compte. Comme cette entrée n’existe pas en réalité on se retrouve donc avec notre erreur du début. La solution de contournement proposée dans l’article consiste à ajouter une entrée dans cette table qui viendra mettre à jour notre ligne fantôme avec un nouveau nom comme ceci :

ALTER DATABASE bug
ADD LOG FILE (NAME = ‘bug_log3_remove’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\bug_log3_remove.ldf’);
GO

Ce qui a pour effet de remplacer l’entrée fantôme par notre nouvelle entrée :

A ce moment précis il est possible de changer le nom du fichier qui nous posait problème.

ALTER DATABASE bug
MODIFY FILE (NAME = ‘bug_log3′, NEWNAME = ‘bug_3′);
GO

Ce qui donne :

The file name ‘bug_3′ has been set.

Ok tout est rentré dans l’ordre mais maintenant il faut supprimer le fichier que nous avons créé pour pouvoir écrasé notre entrée fantôme. D’ailleurs dans le KB Microsoft c’est ce qui est demandé.

ALTER DATABASE bug
REMOVE FILE bug_log3_remove;                                                                                 ;
GO

BACKUP LOG [bug] TO DISK = ‘E:\sql_backup\bug_remove.trn’ WITH INIT;
GO

ce qui donne :

The file ‘bug_log3_remove’ has been removed.

Si l’on regarde la situation avec la vue système sys.master_files :

SELECT
[file_id],
type_desc,
name AS logical_name,
physical_name
FROM sys.master_files
WHERE database_id = DB_ID(‘bug’);

Ce qui donne :

Cependant si l’on regarde à nouveau la table système sys.sysfiles1 via une connexion DAC :

On remarque notre entrée n’est pas effacée par SQL Server. On peut pousser le test plus loin dans ce cas. On tente de renommer à nouveau le nom logique de notre fichier avec le nom suivant : bug_log3_remove

ALTER DATABASE [bug]
MODIFY FILE (NAME = ‘bug_3′, NEWNAME = ‘bug_log3_remove’);
GO  

Un message d’erreur apparait …

Msg 1828, Level 16, State 3, Line 1
The logical file name « bug_log3_remove » is already in use. Choose a different name.

… alors que celui-ci n’est pas utilisé si l’on regarde nos vues systèmes sys.master_files ou éventuellement sys.database_files. Donc il va falloir changer de nom ou encore mettre à jour le nom logique de notre entrée fantôme.

Un dernier test enfin : On ajoute un fichier d’un type différent avec le même nom logique

ALTER DATABASE [bug]
ADD FILE ( NAME = N’bug_log3_remove’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\bug_log3_remove.ndf’, SIZE = 1MB)  

L’opération doit pouvoir fonctionner avec un nouveau id de fichier puisque les id sont réutilisés en fonction de leur type. Bingo !!

Et si maintenant on essayait de supprimer notre fichier. La commande ALTER DATABASE REMOVE FILE va demander le nom logique du fichier et nous avons 2 fichiers avec le même logique. Quid de la suppression : Va-t-il supprimer le 1er fichier alors que nous voulons supprimer le dernier ? (la première ligne est une ligne fantôme. Nous ne voyons que la deuxième avec les vues systèmes classiques)

ALTER DATABASE [bug]
REMOVE FILE bug_log3_remove

Eh bien non. Un message d’erreur apparait à nouveau :

Msg 5009, Level 16, State 9, Line 1
One or more files listed in the statement could not be found or could not be initialized.

La suppression ne fonctionne pas à nouveau. Heureusement dans un sens car SQL Server est bien incapable de savoir quel type de fichier supprimé. La commande utilisée ne nous permet pas de préciser le type. On se retrouve donc avec notre problématique de départ. Il va falloir ajouter un fichier du même type qui existait pour notre ligne fantôme en donnant un nouveau nom de fichier etc. J’avoue ne pas avoir essayé avec un SQL Server 2012 mais je ferai un update quand j?aurai un petit moment.

CQFD

David BARBARIN (Mikedavem)
MVP SQL Server