error enabling AWE to access >2GB of memory

hi,

I am having a problem enabling this, I am getting the error message:

Alter failed. (Microsoft.SqlServer.Smo)

Aditional information:

An exception occurred while executing a Transact-SQl Statement or batch
(Microsoft.SqlServer.ConnectionInfo)

Address Windowing Extensions (AWE) requires the 'lock pages in memory' privilege which is not currently present in the access token of the process.

Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install (Microsoft SQL Server, Error: 5845)

-- end of error.

Prior to this, I opened up gpedit.msc, and associated the Administrator on the "lock pages in memory" policy.

I rebooted the server thinking that this might require it, but it didn't. I still cannot access memory .2Gb.

Any ideas?

cheers.hi,

anyone who can help?

thanks

Error Dropping Database using SMO

I use the method shown below to drop a SQL Server database using SMO. On occasion, I'll get the following error message when this method is called. I'm not sure what I can do to prevent an error like this from happening. I don't think I have any control over it. The error message is this:

Drop all active database connections failed for Server 'SDTPLCHost1\SQLServer2005'. : An exception occurred while executing a Transact-SQL statement or batch.: Process ID 57 is not an active process ID.

Here is the method I wrote that uses SMO to drop a database:

public void DropDatabase(string serverName, string databaseName)

{

Server smoServer;

try

{

smoServer = new Server(serverName);

if (!smoServer.Databases.Contains(databaseName))

return;

smoServer.KillAllProcesses(databaseName);

smoServer.KillDatabase(databaseName);

}

catch (Exception ex)

{

throw new Exception(BuildExceptionMessage(ex));

}

}

Use the KILLDatabase method, they changed it to bahave the way you would expect to:

http://www.sqlserver2005.de/SQLServer2005/Default.aspx?tabid=56&EntryID=9

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Jens,

I don't understand. You said to use the KillDatabase method. I am using the KillDatabase method! You then include a link to an article which seems to imply that the KillDatabase method doesn't work the way it should and then you supply some sample code to make dropping a database work the way it should.

So, am I correct that in order to drop a database, I should use the code you referenced in the link? It seems I should. I've used this technique before to drop a database (without using SMO) and it's always worked.

Amos.

|||Sorry, forgot to mentioned that they kept track on that issue with the SMO connect bug, reflecting the sources they changed it to:

collection1.Add(string.Format(SmoApplication.DefaultCulture, "ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE", new object[] { SqlSmoObject.MakeSqlBraket(database) }));

Error detaching sql express database using SMO

I am using SMO to provide a backup/restore feature. The restore code looks like this:

try
{
// Initialise server object.
Server server = new Server(serverName);
server.ConnectionContext.ConnectionString = connectionString;

// Check if database is current attached to sqlexpress.
foreach (Database db in server.Databases)
{
if (String.Compare(db.Name, destinationPath, true) == 0)
{
Console.WriteLine("Detaching existing database before restore ...");
server.DetachDatabase(db.Name, false);

break;
}
}

// Configure restore.
Restore restore = new Restore();
restore.Database = destinationPath;
restore.ReplaceDatabase = true;
restore.Action = RestoreActionType.Database;
restore.Devices.Add(new BackupDeviceItem(sourcePath, DeviceType.File));

// Perform restore.
restore.SqlRestore(server);
}
catch (FailedOperationException foe)
{
Console.WriteLine("Exception - SqlRestore of {0} failed with: {1}. Detail: {2}",
sourcePath, foe.Message, foe.InnerException.ToString());
}

This work 9/10 times. However occassional the DetachDatabase() call fails with the following error even though the database is not currently in use.

Exception - SqlRestore of D:\ImlDev\Auction\Auction\bin\Debug\Databases\databaseTest.bak failed with: Detach database failed for Server 'NICKDEV\SQLEXPRESS'. . Detail: Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. > System.Data.SqlClient.SqlException: Cannot detach the database 'D:\ImlDev\Auction\Auction\bin\Debug\Databases\databaseTest.mdf' because it is currently in use.

Clearly it was in use previously and the application using it may have crashed and not closed connections etc; this is why I want to restore the database.

How can I avoid/ workaround this to ensure my restore functionality is full proof.

Thanks,

Nick

Hello

I had exactly the same problem. It took some time to figure out but here is the solution:

You need to insert the restore code somewhere in your program before you open any database associated with your program. I put a restore databases button on the main form before any databases were opened. This works fine. Backup works wherever you put it in the code.

The reason for the problem is that once your database has been opened SQL Server will report an open connection even if you close the database connection. A gift from microsoft!!! There is no way you can disconnect from the database once you have opened it except to completely exit the program! Microsoft seems to provide thousands of options but never the one you need.

I hope this helps you.

|||

Hi,

I have just tackled the same problem, and I found using server.KillAllProcess(dbName) did the trick for me.

Nick

|||

You need to make sure you have the only connection to the database. You can do this by setting the database into single-user mode before detaching it, like this:

server.KillAllProcesses(db.Name);

db.DatabaseOptions.UserAccess = DatabaseUserAccess.Single;

db.Alter(TerminationClause.RollbackTransactionsImmediately);

server.DetachDatabase(db.Name, true);

Hope this helps,
Steve

|||Hi,

where exactly did you put your restore-code? On my MainForm, there's also a button, so I think it will be initialized before any connections are opened in MainForm_Load. In the button's click event I call the method that includes the "detach" code. But I receive the same error that the database is in use.

MusiMeli

Error detaching sql express database using SMO

I am using SMO to provide a backup/restore feature. The restore code looks like this:

try
{
// Initialise server object.
Server server = new Server(serverName);
server.ConnectionContext.ConnectionString = connectionString;

// Check if database is current attached to sqlexpress.
foreach (Database db in server.Databases)
{
if (String.Compare(db.Name, destinationPath, true) == 0)
{
Console.WriteLine("Detaching existing database before restore ...");
server.DetachDatabase(db.Name, false);

break;
}
}

// Configure restore.
Restore restore = new Restore();
restore.Database = destinationPath;
restore.ReplaceDatabase = true;
restore.Action = RestoreActionType.Database;
restore.Devices.Add(new BackupDeviceItem(sourcePath, DeviceType.File));

// Perform restore.
restore.SqlRestore(server);
}
catch (FailedOperationException foe)
{
Console.WriteLine("Exception - SqlRestore of {0} failed with: {1}. Detail: {2}",
sourcePath, foe.Message, foe.InnerException.ToString());
}

This work 9/10 times. However occassional the DetachDatabase() call fails with the following error even though the database is not currently in use.

Exception - SqlRestore of D:\ImlDev\Auction\Auction\bin\Debug\Databases\databaseTest.bak failed with: Detach database failed for Server 'NICKDEV\SQLEXPRESS'. . Detail: Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. > System.Data.SqlClient.SqlException: Cannot detach the database 'D:\ImlDev\Auction\Auction\bin\Debug\Databases\databaseTest.mdf' because it is currently in use.

Clearly it was in use previously and the application using it may have crashed and not closed connections etc; this is why I want to restore the database.

How can I avoid/ workaround this to ensure my restore functionality is full proof.

Thanks,

Nick

Hello

I had exactly the same problem. It took some time to figure out but here is the solution:

You need to insert the restore code somewhere in your program before you open any database associated with your program. I put a restore databases button on the main form before any databases were opened. This works fine. Backup works wherever you put it in the code.

The reason for the problem is that once your database has been opened SQL Server will report an open connection even if you close the database connection. A gift from microsoft!!! There is no way you can disconnect from the database once you have opened it except to completely exit the program! Microsoft seems to provide thousands of options but never the one you need.

I hope this helps you.

|||

Hi,

I have just tackled the same problem, and I found using server.KillAllProcess(dbName) did the trick for me.

Nick

|||

You need to make sure you have the only connection to the database. You can do this by setting the database into single-user mode before detaching it, like this:

server.KillAllProcesses(db.Name);

db.DatabaseOptions.UserAccess = DatabaseUserAccess.Single;

db.Alter(TerminationClause.RollbackTransactionsImmediately);

server.DetachDatabase(db.Name, true);

Hope this helps,
Steve

|||Hi,

where exactly did you put your restore-code? On my MainForm, there's also a button, so I think it will be initialized before any connections are opened in MainForm_Load. In the button's click event I call the method that includes the "detach" code. But I receive the same error that the database is in use.

MusiMeli

Error detaching sql express database using SMO

I am using SMO to provide a backup/restore feature. The restore code looks like this:

try
{
// Initialise server object.
Server server = new Server(serverName);
server.ConnectionContext.ConnectionString = connectionString;

// Check if database is current attached to sqlexpress.
foreach (Database db in server.Databases)
{
if (String.Compare(db.Name, destinationPath, true) == 0)
{
Console.WriteLine("Detaching existing database before restore ...");
server.DetachDatabase(db.Name, false);

break;
}
}

// Configure restore.
Restore restore = new Restore();
restore.Database = destinationPath;
restore.ReplaceDatabase = true;
restore.Action = RestoreActionType.Database;
restore.Devices.Add(new BackupDeviceItem(sourcePath, DeviceType.File));

// Perform restore.
restore.SqlRestore(server);
}
catch (FailedOperationException foe)
{
Console.WriteLine("Exception - SqlRestore of {0} failed with: {1}. Detail: {2}",
sourcePath, foe.Message, foe.InnerException.ToString());
}

This work 9/10 times. However occassional the DetachDatabase() call fails with the following error even though the database is not currently in use.

Exception - SqlRestore of D:\ImlDev\Auction\Auction\bin\Debug\Databases\databaseTest.bak failed with: Detach database failed for Server 'NICKDEV\SQLEXPRESS'. . Detail: Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. > System.Data.SqlClient.SqlException: Cannot detach the database 'D:\ImlDev\Auction\Auction\bin\Debug\Databases\databaseTest.mdf' because it is currently in use.

Clearly it was in use previously and the application using it may have crashed and not closed connections etc; this is why I want to restore the database.

How can I avoid/ workaround this to ensure my restore functionality is full proof.

Thanks,

Nick

Hello

I had exactly the same problem. It took some time to figure out but here is the solution:

You need to insert the restore code somewhere in your program before you open any database associated with your program. I put a restore databases button on the main form before any databases were opened. This works fine. Backup works wherever you put it in the code.

The reason for the problem is that once your database has been opened SQL Server will report an open connection even if you close the database connection. A gift from microsoft!!! There is no way you can disconnect from the database once you have opened it except to completely exit the program! Microsoft seems to provide thousands of options but never the one you need.

I hope this helps you.

|||

Hi,

I have just tackled the same problem, and I found using server.KillAllProcess(dbName) did the trick for me.

Nick

|||

You need to make sure you have the only connection to the database. You can do this by setting the database into single-user mode before detaching it, like this:

server.KillAllProcesses(db.Name);

db.DatabaseOptions.UserAccess = DatabaseUserAccess.Single;

db.Alter(TerminationClause.RollbackTransactionsImmediately);

server.DetachDatabase(db.Name, true);

Hope this helps,
Steve

|||Hi,

where exactly did you put your restore-code? On my MainForm, there's also a button, so I think it will be initialized before any connections are opened in MainForm_Load. In the button's click event I call the method that includes the "detach" code. But I receive the same error that the database is in use.

MusiMeli
sql

Error detaching sql express database using SMO

I am using SMO to provide a backup/restore feature. The restore code looks like this:

try
{
// Initialise server object.
Server server = new Server(serverName);
server.ConnectionContext.ConnectionString = connectionString;

// Check if database is current attached to sqlexpress.
foreach (Database db in server.Databases)
{
if (String.Compare(db.Name, destinationPath, true) == 0)
{
Console.WriteLine("Detaching existing database before restore ...");
server.DetachDatabase(db.Name, false);

break;
}
}

// Configure restore.
Restore restore = new Restore();
restore.Database = destinationPath;
restore.ReplaceDatabase = true;
restore.Action = RestoreActionType.Database;
restore.Devices.Add(new BackupDeviceItem(sourcePath, DeviceType.File));

// Perform restore.
restore.SqlRestore(server);
}
catch (FailedOperationException foe)
{
Console.WriteLine("Exception - SqlRestore of {0} failed with: {1}. Detail: {2}",
sourcePath, foe.Message, foe.InnerException.ToString());
}

This work 9/10 times. However occassional the DetachDatabase() call fails with the following error even though the database is not currently in use.

Exception - SqlRestore of D:\ImlDev\Auction\Auction\bin\Debug\Databases\databaseTest.bak failed with: Detach database failed for Server 'NICKDEV\SQLEXPRESS'. . Detail: Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. > System.Data.SqlClient.SqlException: Cannot detach the database 'D:\ImlDev\Auction\Auction\bin\Debug\Databases\databaseTest.mdf' because it is currently in use.

Clearly it was in use previously and the application using it may have crashed and not closed connections etc; this is why I want to restore the database.

How can I avoid/ workaround this to ensure my restore functionality is full proof.

Thanks,

Nick

Hello

I had exactly the same problem. It took some time to figure out but here is the solution:

You need to insert the restore code somewhere in your program before you open any database associated with your program. I put a restore databases button on the main form before any databases were opened. This works fine. Backup works wherever you put it in the code.

The reason for the problem is that once your database has been opened SQL Server will report an open connection even if you close the database connection. A gift from microsoft!!! There is no way you can disconnect from the database once you have opened it except to completely exit the program! Microsoft seems to provide thousands of options but never the one you need.

I hope this helps you.

|||

Hi,

I have just tackled the same problem, and I found using server.KillAllProcess(dbName) did the trick for me.

Nick

|||

You need to make sure you have the only connection to the database. You can do this by setting the database into single-user mode before detaching it, like this:

server.KillAllProcesses(db.Name);

db.DatabaseOptions.UserAccess = DatabaseUserAccess.Single;

db.Alter(TerminationClause.RollbackTransactionsImmediately);

server.DetachDatabase(db.Name, true);

Hope this helps,
Steve

|||Hi,

where exactly did you put your restore-code? On my MainForm, there's also a button, so I think it will be initialized before any connections are opened in MainForm_Load. In the button's click event I call the method that includes the "detach" code. But I receive the same error that the database is in use.

MusiMeli