Click here to Skip to main content
15,890,825 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a windows service for backup sql databases
it works good but after any database backup the service keep the connection open for along time which slowing down the database when I backup many databases
what is the problem?

What I have tried:

private void BackupSqlServerDB(string BackupPath, string DBName, string DBCon)
 {

     SqlConnection Con = new SqlConnection();
     try
     {
         System.DateTime TheTime = DateTime.Now;

         string fileName = DBName + ".bak";
         BackupPath = Path.Combine(BackupPath, fileName);
         if (System.IO.File.Exists(BackupPath))
             System.IO.File.Delete(BackupPath);
         if (System.IO.File.Exists(BackupPath + ".zip"))
             System.IO.File.Delete(BackupPath + ".zip");
         Con.ConnectionString = DBCon;
         Con.Open();
         SqlCommand Cmd = new SqlCommand();
         Cmd.Connection = Con;
         Cmd.CommandText = "BACKUP DATABASE " + DBName + " TO DISK ='" + BackupPath + "';";
         Cmd.CommandTimeout = 360;
         Cmd.ExecuteNonQuery();
         Cmd.Dispose();
         Con.Close();
         Con.Dispose();
         GC.Collect();


     }
     catch (Exception ex)
     {

         AddLog(ex.Message, "Preparing Sql Backup", true);
     }
     finally
     {
         Con.Close();
         Con.Dispose();
         GC.Collect();
     }
 }
Posted
Updated 14-May-17 10:01am

You can try to use:
SQL
SqlConnection.ClearAllPools();
 
Share this answer
 
Comments
M. Daban 16-May-17 16:10pm    
good it works, but does it just close my service connections or all connections?
RickZeeland 16-May-17 16:38pm    
It affects all connections, quote:
ClearAllPools resets (or empties) the connection pool. If there are connections in use at the time of the call, they are marked appropriately and will be discarded (instead of being returned to the pool) when Close is called on them.
https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.clearallpools(v=vs.110).aspx
M. Daban 16-May-17 16:42pm    
I have just tested it, it closes only my service's connections
that what I want!
Thank you
You're already doing it with Con.Close and Con.Dispose. Just having the connection open will not slow down the database for any other operations.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900