KillAllProcesses, KillDatabase ,SET SINGLE_USER none is working all the time

Question

My SQL server version is Express 2008 R2.

I use the following code to restore the database. I would estimate this code works 60% of the time and other times it throws the error:

Exclusive access could not be obtained because the database is in use

Even if I add KillDatabase method it still can throw the error

using (SqlConnection conn = new
SqlConnection(DatabaseProperties.ConnectionString))

{

conn.Open();

ServerConnection sc = new ServerConnection(conn);

conn.ChangeDatabase("master");

Server srvr = new Server(sc);

Restore rstDatabase = new Restore();

rstDatabase.Action = RestoreActionType.Database;

rstDatabase.ReplaceDatabase = true;

rstDatabase.Database = DatabaseProperties.InitialCatalog;

BackupDeviceItem
bkpDevice = new BackupDeviceItem(DatabasePath,
DeviceType.File);

rstDatabase.Devices.Add(bkpDevice);

rstDatabase.ReplaceDatabase = true;

srvr.KillAllProcesses(DatabaseProperties.InitialCatalog);

srvr.ConnectionContext.ExecuteNonQuery("ALTER DATABASE "+
DatabaseProperties.InitialCatalog + " SET
SINGLE_USER WITH ROLLBACK IMMEDIATE");

rstDatabase.Wait();

rstDatabase.SqlRestore(srvr);

SqlConnection.ClearAllPools();

return true;

}

I am at loss   on  what else I can do.

Answer 1

Hello,

Have you tried , just before the launch of the restore, to test the value of Database.ActiveConnections ?

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.database.activeconnections.aspx 

You could use also

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.server.getactivedbconnectioncount.aspx    ( you must provide the name of the database to test as parameter )

Else you can use the sys.dm_exec_requests in a query

SELECT * FROM sys.dm_exec_requests where database_id = srv.Databases[DatabaseProperties.InitialCatalog.ToString()

http://msdn.microsoft.com/en-us/library/ms177648.aspx

if the count of returned rows is > 1 , then you have at least one connection to your database to restore, so the restore will fail.

I have got a look at http://msdn.microsoft.com/en-us/library/bb522682.aspx and i have seen that there is a possibility of problem 

"verify the AUTO_UPDATE_STATISTICS_ASYNC option is set to OFF. When set to ON, the background thread used to update statistics takes a connection against the database, and you will be unable to access the database in single-user mode" .

But for more possible  and full explanations, i would suggest to post on the Transact-SQL Forum or , with your agreement, a moderator may move your thread in this forum

Please, could you tell us what is the "definition" of  DatabaseProperties ? I have understood that you get the name of the database to restore from the InitialCataLog property, but i am not sure to understand that it is an "own" class .

Don't hesitate to post again for more help or explanations.

Have a nice day.

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 2

I've seen issues with applications that will reconnect faster than you can kill the connections. Its usually best if you can stop the application. That said, one thing I've done on non-clustered servers is to pause the SQL Server service. If you obtain a connection to SQL Server then pause the service, this will keep existing connections and not allow new connections to the SQL Server instance. You can then kill off the unneeded connections and they won't reconnect. After you restore the database you'll want to resume the service. Of course this effects the entire SQL instance, so you may or may not be able to do this. Here's Powershell code to pause and resume the SQL Server service:

try {add-type -AssemblyName "Microsoft.SqlServer.SqlWmiManagement, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop}
catch {add-type -AssemblyName "Microsoft.SqlServer.SqlWmiManagement"}

$computer = "$env:computername"
$wmi = new-object "Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer" $Computer -ErrorAction 'Stop'
$wmi.Services| where {$_.Type -eq 'SqlServer'}  | foreach {$_.Pause()}

#DO STUFF

$wmi.Services| where {$_.Type -eq 'SqlServer'}  | foreach {$_.Resume()}

Answer 3

hi - what is the wait for before the restore?   Could be sql agent is grabbing the connection at that time.  Put a try catch block and in the catch print out the connections.

from bol: http://msdn.microsoft.com/en-us/library/ms188236(v=sql.105).aspx

Starting SQL Server in Single-User Mode

SQL Server 2008 R2

Other Versions

1 out of 3 rated this helpful Rate this topic

Under certain circumstances, you may have to start an instance of SQL Server in single-user mode by using the startup option -m. For example, you may want to change server configuration options or recover a damaged master database or other system database. Both actions require starting an instance of SQL Server in single-user mode.

Starting SQL Server in single-user mode enables any member of the computer's local Administrators group to connect to the instance of SQL Server as a member of the sysadmin fixed server role. For more information, seeTroubleshooting: Connecting to SQL Server When System Administrators Are Locked Out.

When you start an instance of SQL Server in single-user mode, note the following:

• Only one user can connect to the server.

• The CHECKPOINT process is not executed. By default, it is executed automatically at startup.

Note
Stop the SQL Server Agent service before connecting to an instance of SQL Server in single-user mode; otherwise, the SQL Server Agent service uses the connection, thereby blocking it.

When you start an instance of SQL Server in single-user mode, SQL Server Management Studio can connect to SQL Server. Object Explorer in Management Studio might fail because it requires more than one connection for some operations. To manage SQL Server in single-user mode, execute Transact-SQL statements by connecting only through the Query Editor in Management Studio, or use the sqlcmd utility.

When you use the -m option with sqlcmd or Management Studio, you can limit the connections to a specified client application. For example, -m"sqlcmd" limits connections to a single connection and that connection must identify itself as the sqlcmd client program. Use this option when you are starting SQL Server in single-user mode and an unknown client application is taking the only available connection. To connect through the Query Editor in Management Studio, use -m"Microsoft SQL Server Management Studio - Query".

Important
Do not use this option as a security feature. The client application provides the client application name, and can provide a false name as part of the connection string.

Answer 4

Hello gao.seng,

The Op ( original poster ) is using a SQL Server Express 2005. But for SQL Server Express 2005, there is no SQL Agent .For SQL Server Express 2008, a SQL Agent service is installed but it is left in the Disabled state ( done by the SQL Server Express Team to make easier the upgrade Express ==> Standard or Entreprise, during the upgrade the SQL Agent service is enabled... explained by Mike Wachal on the SQL Server Express Weblog ).

the Wait is a method of the Restore class. According to

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.backuprestorebase.wait(v=sql.90).aspx , it means "Waits for the current backup or restore operation to complete before moving on to the next process"

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 5

Thanks Pappy - I missed that is is SQLExpresS.  My question on the wait is why is it before the restore?  Because there may be another backup or restore op in process?  Wouldn't it be better then to check the database state?  Anyway I will post back with the try catch logic I mentioned.

Edit: FWIW with try catch

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;
namespace SMO_Restore
{
    class Program
    {
        static void Main(string[] args)
        {
            Boolean result = false;

            if (args.Length == 0)
                result = DoRestore("c:\\temp\\mydatabase.bak", "mydatabase");
            else
                result = DoRestore(args[0].ToString(),args[1].ToString());

            if (result == true)
                Console.WriteLine("restore succeeded. Press any key to continue...");
            else
                Console.WriteLine("restore failed. Press any key to continue...");
            Console.ReadKey(true);
        }

        static Boolean DoRestore(string DatabasePath, string DBName)
        {
            Boolean blnResult = false;

            SqlConnectionStringBuilder DatabaseProperties = new SqlConnectionStringBuilder();
            DatabaseProperties.DataSource = @".\SQLEXPRESS";
            DatabaseProperties.InitialCatalog = DBName;
            DatabaseProperties.IntegratedSecurity = true;

            using (SqlConnection conn = new SqlConnection(DatabaseProperties.ConnectionString))
            {
                conn.Open();
                ServerConnection sc = new ServerConnection(conn);
                conn.ChangeDatabase("master");
                Server srvr = new Server(sc);
                Restore rstDatabase = new Restore();
                rstDatabase.Action = RestoreActionType.Database;
                rstDatabase.ReplaceDatabase = true;
                rstDatabase.Database = DatabaseProperties.InitialCatalog;
                BackupDeviceItem bkpDevice = new BackupDeviceItem(DatabasePath, DeviceType.File);
                rstDatabase.Devices.Add(bkpDevice);
                rstDatabase.ReplaceDatabase = true;
                srvr.KillAllProcesses(DatabaseProperties.InitialCatalog);
                string q = "ALTER DATABASE " + DatabaseProperties.InitialCatalog.ToString() + " SET SINGLE_USER WITH ROLLBACK IMMEDIATE";
                srvr.ConnectionContext.ExecuteNonQuery(q.ToString());
                rstDatabase.Wait();
                try
                {
                    rstDatabase.SqlRestore(srvr);
                    blnResult = true;
                }
                catch (Exception e)
                {
                    q = "select p.spid, p.hostname, p.program_name, p.nt_username, t.text from sys.sysprocesses p " +
                        " outer apply sys.dm_exec_sql_text(sql_handle) t where p.dbid = DB_id('" + DatabaseProperties.InitialCatalog.ToString() + "')";
                    string o = string.Empty;
                    Console.WriteLine(e.InnerException.InnerException.Message.ToString());
                    //Console.WriteLine(e.Message.ToString());
                    DataSet dSet = srvr.ConnectionContext.ExecuteWithResults(q);
                    foreach (DataTable dTable in dSet.Tables)
                       foreach (DataRow dRow in dTable.Rows)
                            foreach (DataColumn dCol in dRow.Table.Columns)
                            {
                                o += dCol.ColumnName.ToString() + "==" + dRow[dCol].ToString() + "\r\n";
                            }
                    //MessageBox.Show(o.ToString());
                    Console.WriteLine(o.ToString());
                }
                finally
                {
                    SqlConnection.ClearAllPools();
                }
                return blnResult;
            }
        }
    }
}

Answer 6

Thanks everyone for responses.

I implemented cimille19 in regard to pause the data base first and then kill all processes and it seems to be working

ManagedComputer mc = new ManagedComputer();

Service service = mc.Services[@"MSSQL$SQLEXPRESS"];

service.Pause();

srvr.KillAllProcesses(DatabaseProperties.InitialCatalog);

rstDatabase.SqlRestore(srvr);

rstDatabase.Wait();

service.Resume()

SqlConnection.ClearAllPools();

Now I only I could figure how not to hardcode service name, becuase SMO just provides me with Display Name of the services SQLEXPRESS vs MSSQL$SQLEXPRESS.

Answer 7

the servicename is either mssqlserver for default or MSSQL$ & instance name.