Sql Server 2008 : fermeture des connexions après l'abandon de la base de données

Question

J'ai des problèmes avec SQL Server qui abandonne une connexion après avoir abandonné et recréé une base de données donnée et la prochaine fois que j'essaie d'exécuter une commande avec une nouvelle connexion sur cette même base de données, j'obtiens :

Une erreur au niveau du transport s'est produite lors de l'envoi de la demande au serveur. (fournisseur : Shared Memory Provider, error : 0 - Aucun processus n'est à l'autre bout du tuyau).

Voici la version TCP (si j'essaie de me connecter à un autre serveur)

Une erreur au niveau du transport s'est produite lors de l'envoi de la demande au serveur. (fournisseur : TCP Provider, error : 0 - Une connexion existante a été fermée de force par l'hôte distant).

Voici les étapes à suivre pour reproduire le problème :

1. Ouvrez une connexion à une base de données et exécutez une commande sql.
2. Abandonner la base de données
3. Recréer la base de données
4. Ouvrez une nouvelle connexion à la même base de données et essayez d'exécuter une commande sur celle-ci.

Résultat : Je reçois une exception

Voici le code :

using (var conn = new System.Data.SqlClient.SqlConnection("Data Source=.;Initial Catalog=DBNAME;Integrated Security=True"))
{
    conn.Open();
    var cmd = conn.CreateCommand();
    cmd.CommandText = "UPDATE ...";
    cmd.ExecuteNonQuery();
}

string sql = "Alter Database DBNAME set single_user with rollback immediate drop database DBNAME";
var server = new Microsoft.SqlServer.Management.Smo.Server(".");
server.ConnectionContext.ExecuteNonQuery(sql);
server.ConnectionContext.Disconnect();

sql = File.ReadAllText("PathToDotSqlFile..."));
server = new Microsoft.SqlServer.Management.Smo.Server(".");
server.ConnectionContext.ExecuteNonQuery(sql);
server.ConnectionContext.Disconnect();

using (var conn = new System.Data.SqlClient.SqlConnection("Data Source=.;Initial Catalog=WER_CONFIG;Integrated Security=True"))
{
    conn.Open();
    var cmd = conn.CreateCommand();
    cmd.CommandText = "UPDATE ...";
    cmd.ExecuteNonQuery();
}

L'erreur se produit à la toute fin de la ligne "cmd.ExecuteNonQuery()". Il semble que même si je crée une nouvelle connexion à chaque fois que je me connecte, le serveur SQL garde une trace de quelque chose (ou peut-être le code ADO.net) qui fait que la prochaine fois que je demande une connexion, il m'en donne une qui est déjà utilisée ou qui a été fermée du côté du serveur. Il ne se rend pas compte qu'elle a été fermée par le serveur (vraisemblablement parce que la base de données à laquelle il est connecté a été abandonnée) jusqu'à ce que vous essayiez d'exécuter une autre commande contre elle.

Notez que si je ne fais pas la première étape d'exécution de la requête initiale, et que je me contente d'abandonner la base de données, de la recréer et d'exécuter une commande, je ne reçois pas cette erreur. Je pense que l'établissement de cette connexion initiale avant l'abandon de la base de données est une partie importante de cette erreur.

J'ai également essayé d'utiliser un processus externe pour supprimer et recréer la base de données comme suit :

ProcessStartInfo info = new ProcessStartInfo("sqlcmd.exe", " -e -E -S . -Q \"Alter Database DBNAME set single_user with rollback immediate drop database DBNAME\"");
var p = Process.Start(info);
p.WaitForExit();

info = new ProcessStartInfo("sqlcmd.exe", " -i " + PathToDotSqlFile);
p = Process.Start(info);
p.WaitForExit();

Et cela n'a pas aidé.

Existe-t-il un moyen de créer une nouvelle SqlConnection et de s'assurer qu'elle est propre et ne provient pas d'un pool ? Avez-vous d'autres suggestions pour résoudre ce problème ?

MISE À JOUR : L'utilisation de SqlConnection.ClearPool() a résolu le problème mais j'ai choisi de modifier ma chaîne de connexion avec pooling=false, ce qui a également fonctionné.

Answer 1

ADO.NET gère automatiquement un pool de connexion. Lorsque vous "fermez" une connexion dans votre application, elle est renvoyée dans le pool et maintenue en vie, au cas où vous demanderiez une connexion avec la même chaîne de connexion. Cela pourrait être la raison pour laquelle votre "nouvelle" connexion est périmée.

Vous pouvez essayer de désactiver ce comportement en ajoutant pooling=false comme paramètre de votre chaîne de connexion.

Answer 2

Je ne connais pas SQL2008, mais cela ressemble à un problème de pooling de connexion du côté de l'application. Autrefois, nous ajoutions "OLEDB Services=-1" à la chaîne de connexion pour désactiver la mise en commun des connexions. Il existe probablement une façon plus élégante de le faire maintenant.

edit : ADO.Net 2.0 semble avoir ajouté une fonction ClearPool à l'objet SQLConnection. ( http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.clearpool(VS.80).aspx ) Je suis très intéressé de savoir si cela fonctionne.