database still exists after deletion

hi

Basically, I create a database with sql, then I delete it manually(not via sql statment. This is a problem which I realise. In fact, you can't delete the database because the VS 2005 still is using it) I run the same code again,
then it says the database still exists, even it is physically destroied.

--Here is the errors:
System.Data.SqlClient.SqlException: Database 'riskDatabase' already exists.
at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception, Boolea
n breakConnection)

--The evidence that the database doesn't exist physically:
Unhandled Exception: System.Data.SqlClient.SqlException: Cannot open database "riskDatabase" requested by the login. The login failed.

--The code:
/*
* C# code to programmically create
* database and table. It also inserts
* data into the table.
*/

using System;
using System.Collections.Generic;
using System.Text;

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace riskWizard
{
public class RiskWizard
{
// Sql
private string connectionString;
private SqlConnection connection;
private SqlCommand command;

// Database
private string databaseName;
private string currDatabasePath;
private string database_mdf;
private string database_ldf;

public RiskWizard(string databaseName, string currDatabasePath, string database_mdf, string database_ldf)
{
this.databaseName = databaseName;
this.currDatabasePath = currDatabasePath;
this.database_mdf = database_mdf;
this.database_ldf = database_ldf;
}

private void executeSql(string sql)
{
// Create a connection
connection = new SqlConnection(connectionString);

// Open the connection.
if (connection.State == ConnectionState.Open)
connection.Close();

connection.ConnectionString = connectionString;
connection.Open();

command = new SqlCommand(sql, connection);
try
{
command.ExecuteNonQuery();
}
catch (SqlException e)
{
Console.WriteLine(e.ToString());
}
}

public void createDatabase()
{
string database_data = databaseName + "_data";
string database_log = databaseName + "_log";

connectionString
= "Data Source=.\\SQLExpress;Initial Catalog=;Integrated Security=SSPI;";

string sql = "CREATE DATABASE " + databaseName + " ON PRIMARY"
+ "(name=" + database_data + ",filename=" + database_mdf + ",size=3,"
+ "maxsize=5,filegrowth=10%)log on"
+ "(name=" + database_log + ",filename=" + database_ldf + ",size=3,"
+ "maxsize=20,filegrowth=1)";

executeSql(sql);
}

public void dropDatabase()
{
connectionString
= "Data Source=.\\SQLExpress;Initial Catalog=" + databaseName + ";Integrated Security=SSPI;";

string sql = "DROP DATABASE " + databaseName;

executeSql(sql);
}

// Create table.
public void createTable(string tableName)
{
connectionString
= "Data Source=.\\SQLExpress;Initial Catalog=" + databaseName + ";Integrated Security=SSPI;";

string sql = "CREATE TABLE " + tableName +
"(userId INTEGER IDENTITY(1, 1) CONSTRAINT PK_userID PRIMARY KEY," +
"name CHAR(50) NOT NULL, address CHAR(255) NOT NULL, employmentTitle TEXT NOT NULL)";

executeSql(sql);
}

// Insert data
public void insertData(string tableName)
{
string sql;

connectionString
= "Data Source=.\\SQLExpress;Initial Catalog=" + databaseName + ";Integrated Security=SSPI;";

sql = "INSERT INTO " + tableName + "(userId, name, address, employmentTitle) " +
"VALUES (1001, 'Puneet Nehra', 'A 449 Sect 19, DELHI', 'project manager') ";
executeSql(sql);

sql = "INSERT INTO " + tableName + "(userId, name, address, employmentTitle) " +
"VALUES (1002, 'Anoop Singh', 'Lodi Road, DELHI', 'software admin') ";
executeSql(sql);

sql = "INSERT INTO " + tableName + "(userId, name, address, employmentTitle) " +
"VALUES (1003, 'Rakesh M', 'Nag Chowk, Jabalpur M.P.', 'tester') ";
executeSql(sql);

sql = "INSERT INTO " + tableName + "(userId, name, address, employmentTitle) " +
"VALUES (1004, 'Madan Kesh', '4th Street, Lane 3, DELHI', 'quality insurance mamager') ";
executeSql(sql);
}

public static void Main(String[] argv)
{
string databaseName = "riskDatabase";
string currDatabasePath = "E:\\liveProgrammes\\cSharpWorkplace\\riskWizard\\A pp_Data";
// Need to be more flexible.
string database_mdf = "'E:\\liveProgrammes\\cSharpWorkplace\\riskWizard\\ App_Data\\riskDatabase.mdf'";
string database_ldf = "'E:\\liveProgrammes\\cSharpWorkplace\\riskWizard\\ App_Data\\riskDatabase.ldf'";

RiskWizard riskWizard = new RiskWizard(databaseName, currDatabasePath, database_mdf, database_ldf);
riskWizard.createDatabase();
riskWizard.createTable("userTable");
riskWizard.insertData("userTable");
//riskWizard.dropDatabase();
}
}
}Have someone tried to create a database then drop it in runtime?

Database Snapshots Performance

Hi,
I'm developing a Data Mart and i'm experiencing a performance gap between my fact table and its snapshot.
I create snapshot with the istruction:

CREATE DATABASE DB_SNAP ON

( NAME = DB_SNAP_Data, FILENAME =

'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DB_SNAP_Data.ss' )

AS SNAPSHOT OF DB;

And it works. But executing queries on the snapshot result very "low".
Can anyone tell me why?

Tanks.

F.

There is a section in Books Online titled "How Database Snapshots Work" which shows the extra level of redirection for snapshots. For a newly created snapshot, the data will not be in cache so it might take a little while to pull the data into the buffer pool. Once it is "warmed up" though, the performance should not be that different.

You can look at the sys.dm_db_index_operational_stats and sys.dm_io_virtual_file_stats DMVs to try to determine where the issues are.

|||Yes, but I always have a "newly created snapshot". Infact, to update the snapshot, i need to drop and re-create. And i do this every day at least.|||

So, do the performance problems persist, or are they temporary until the cache is populated?

This will help narrow down where the problem may be.

Database Snapshots Performance

Hi,
I'm developing a Data Mart and i'm experiencing a performance gap between my fact table and its snapshot.
I create snapshot with the istruction:

CREATE DATABASE DB_SNAP ON

( NAME = DB_SNAP_Data, FILENAME =

'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DB_SNAP_Data.ss' )

AS SNAPSHOT OF DB;

And it works. But executing queries on the snapshot result very "low".
Can anyone tell me why?

Tanks.

F.

There is a section in Books Online titled "How Database Snapshots Work" which shows the extra level of redirection for snapshots. For a newly created snapshot, the data will not be in cache so it might take a little while to pull the data into the buffer pool. Once it is "warmed up" though, the performance should not be that different.

You can look at the sys.dm_db_index_operational_stats and sys.dm_io_virtual_file_stats DMVs to try to determine where the issues are.

|||Yes, but I always have a "newly created snapshot". Infact, to update the snapshot, i need to drop and re-create. And i do this every day at least.|||

So, do the performance problems persist, or are they temporary until the cache is populated?

This will help narrow down where the problem may be.

Database Snapshots

Hi,
I'm developing a Data Mart and i'm experiencing a performance gap between my fact table and its snapshot.
I create snapshot with the istruction:

CREATE DATABASE DB_SNAP ON

( NAME = DB_SNAP_Data, FILENAME =

'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DB_SNAP_Data.ss' )

AS SNAPSHOT OF DB;

And it works. But executing queries on the snapshot result very "low".
Can anyone tell me why?

Tanks.

F.Can you please explain the term about 'low', is it the data size or data?|||

Satya SKJ wrote:

Can you please explain the term about 'low', is it the data size or data?

It was "Slow" and not "low". Sorry.
I mean the time to execute a query.|||

It is by design@.

Performance is reduced, due to increased I/O on the source database resulting from a copy-on-write operation to the snapshot every time a page is updated.

|||

Satya SKJ wrote:

It is by design@.

Performance is reduced, due to increased I/O on the source database resulting from a copy-on-write operation to the snapshot every time a page is updated.

Ok. I read that note.
But what does it mean "Performance is reduced"?
Query on the normal table executed in about 40 seconds; on the snapshot it's over 8 minutes...

Database Snapshot Performance

Hi,
I'm developing a Data Mart and i'm experiencing a performance gap
between my fact table and its snapshot.
I create snapshot with the istruction:
CREATE DATABASE DB_SNAP ON ( NAME = DB_SNAP_Data, FILENAME = 'C:
\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
\DB_SNAP_Data.ss' ) AS SNAPSHOT OF DB;
And it works. But executing queries on the snapshot result very
"Slow".
Can anyone tell me why?
Tanks.
F.
A snapshot is slow because it makes a copy of of modified data in tempdb.
Thus reads are scattered all over. The real question is why would you NEED
a snapshot of a fact table? This is non-standard DW practice AFAIK.
TheSQLGuru
President
Indicium Resources, Inc.
"Johnny" <xxx.johnny@.gmail.com> wrote in message
news:1171290125.043815.129030@.v33g2000cwv.googlegr oups.com...
> Hi,
> I'm developing a Data Mart and i'm experiencing a performance gap
> between my fact table and its snapshot.
> I create snapshot with the istruction:
> CREATE DATABASE DB_SNAP ON ( NAME = DB_SNAP_Data, FILENAME = 'C:
> \Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
> \DB_SNAP_Data.ss' ) AS SNAPSHOT OF DB;
> And it works. But executing queries on the snapshot result very
> "Slow".
> Can anyone tell me why?
> Tanks.
> F.
>

Database Snapshot Performance

Hi,
I'm developing a Data Mart and i'm experiencing a performance gap
between my fact table and its snapshot.
I create snapshot with the istruction:
CREATE DATABASE DB_SNAP ON ( NAME = DB_SNAP_Data, FILENAME = 'C:
\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
\DB_SNAP_Data.ss' ) AS SNAPSHOT OF DB;
And it works. But executing queries on the snapshot result very
"Slow".
Can anyone tell me why?
Tanks.
F.A snapshot is slow because it makes a copy of of modified data in tempdb.
Thus reads are scattered all over. The real question is why would you NEED
a snapshot of a fact table? This is non-standard DW practice AFAIK.
TheSQLGuru
President
Indicium Resources, Inc.
"Johnny" <xxx.johnny@.gmail.com> wrote in message
news:1171290125.043815.129030@.v33g2000cwv.googlegroups.com...
> Hi,
> I'm developing a Data Mart and i'm experiencing a performance gap
> between my fact table and its snapshot.
> I create snapshot with the istruction:
> CREATE DATABASE DB_SNAP ON ( NAME = DB_SNAP_Data, FILENAME = 'C:
> \Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
> \DB_SNAP_Data.ss' ) AS SNAPSHOT OF DB;
> And it works. But executing queries on the snapshot result very
> "Slow".
> Can anyone tell me why?
> Tanks.
> F.
>