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?
Showing posts with label create. Show all posts
Showing posts with label create. Show all posts
Thursday, March 29, 2012
Database Standards HELP!
I would like to create a new database and follow some standard. I am hoping that there is some ANSI documentation or Microsoft documentation on a NAMING standard when creating objects in the database.
i.e Table name "tblEmployees"
Column name "txtLastName"
Is there any GOOD documentation on creating a database using a PROVEN, and ACCEPTED standard?
i.e Table name "tblEmployees"
Column name "txtLastName"
Is there any GOOD documentation on creating a database using a PROVEN, and ACCEPTED standard?
Hi HotChick -
I always rebel and do whatever I want.......but, here is a pretty decent link:http://vyaskn.tripod.com/object_naming.htm
|||The accepted standard is ISO 11179. http://metadata-standards.org/Document-library/Draft-standards/11179-Part5-Naming&Identification/Database space monitoring.
Hi
can any one please help me or guide to some good article to create a script
that has to look into the data file space and log file space for each
database on sql server and if the database id running out of space then it
should automatically increase the log file and data file size.
Thanks in Advance.
Ritesh KumarRitesh
> can any one please help me or guide to some good article to create a
> script that has to look into the data file space and log file space for
> each database on sql server and if the database id running out of space
> then it should automatically increase the log file and data file size.
If the database is running out of space that is too late. You may want to
create/modify a size of database to be increased that prevents from
auto-grow feature
Actually an idea is to compare sysfiles system table data for time to time.
I'm sure you will find on internet many examples.
"Ritesh Kumar" <mailrembersu@.gmail.com> wrote in message
news:Oo$dgfNcHHA.4616@.TK2MSFTNGP03.phx.gbl...
> Hi
> can any one please help me or guide to some good article to create a
> script that has to look into the data file space and log file space for
> each database on sql server and if the database id running out of space
> then it should automatically increase the log file and data file size.
> Thanks in Advance.
> Ritesh Kumar
>|||On Mar 28, 4:06 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Ritesh
> > can any one please help me or guide to some good article to create a
> > script that has to look into the data file space and log file space for
> > each database on sql server and if the database id running out of space
> > then it should automatically increase the log file and data file size.
> If the database is running out of space that is too late. You may want to
> create/modify a size of database to be increased that prevents from
> auto-grow feature
> Actually an idea is to compare sysfiles system table data for time to time.
> I'm sure you will find on internet many examples.
> "Ritesh Kumar" <mailrembe...@.gmail.com> wrote in message
> news:Oo$dgfNcHHA.4616@.TK2MSFTNGP03.phx.gbl...
>
> > Hi
> > can any one please help me or guide to some good article to create a
> > script that has to look into the data file space and log file space for
> > each database on sql server and if the database id running out of space
> > then it should automatically increase the log file and data file size.
> > Thanks in Advance.
> > Ritesh Kumar- Hide quoted text -
> - Show quoted text -
This stored procedure (2005) will give you the free space on all
drives in your server:
exec sys.xp_fixeddrives
This stored procedure will give you the database size and other useful
info.
exec sp_spaceused
Maybe this will help?
Kristina|||Kristina
We should run this sp with the below parameter,because of results that we
get from sp are not always accurate
sp_spaceused @.updateusage = 'TRUE'
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1175083368.213998.50830@.b75g2000hsg.googlegroups.com...
> On Mar 28, 4:06 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> Ritesh
>> > can any one please help me or guide to some good article to create a
>> > script that has to look into the data file space and log file space
>> > for
>> > each database on sql server and if the database id running out of
>> > space
>> > then it should automatically increase the log file and data file size.
>> If the database is running out of space that is too late. You may want to
>> create/modify a size of database to be increased that prevents from
>> auto-grow feature
>> Actually an idea is to compare sysfiles system table data for time to
>> time.
>> I'm sure you will find on internet many examples.
>> "Ritesh Kumar" <mailrembe...@.gmail.com> wrote in message
>> news:Oo$dgfNcHHA.4616@.TK2MSFTNGP03.phx.gbl...
>>
>> > Hi
>> > can any one please help me or guide to some good article to create a
>> > script that has to look into the data file space and log file space
>> > for
>> > each database on sql server and if the database id running out of
>> > space
>> > then it should automatically increase the log file and data file size.
>> > Thanks in Advance.
>> > Ritesh Kumar- Hide quoted text -
>> - Show quoted text -
> This stored procedure (2005) will give you the free space on all
> drives in your server:
> exec sys.xp_fixeddrives
> This stored procedure will give you the database size and other useful
> info.
> exec sp_spaceused
> Maybe this will help?
> Kristina
>|||On Mar 28, 8:09 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Kristina
> We should run this sp with the below parameter,because of results that we
> get from sp are not always accurate
> sp_spaceused @.updateusage = 'TRUE'
> "Kristina" <Kristina...@.gmail.com> wrote in message
> news:1175083368.213998.50830@.b75g2000hsg.googlegroups.com...
>
> > On Mar 28, 4:06 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> >> Ritesh
> >> > can any one please help me or guide to some good article to create a
> >> > script that has to look into the data file space and log file space
> >> > for
> >> > each database on sql server and if the database id running out of
> >> > space
> >> > then it should automatically increase the log file and data file size.
> >> If the database is running out of space that is too late. You may want to
> >> create/modify a size of database to be increased that prevents from
> >> auto-grow feature
> >> Actually an idea is to compare sysfiles system table data for time to
> >> time.
> >> I'm sure you will find on internet many examples.
> >> "Ritesh Kumar" <mailrembe...@.gmail.com> wrote in message
> >>news:Oo$dgfNcHHA.4616@.TK2MSFTNGP03.phx.gbl...
> >> > Hi
> >> > can any one please help me or guide to some good article to create a
> >> > script that has to look into the data file space and log file space
> >> > for
> >> > each database on sql server and if the database id running out of
> >> > space
> >> > then it should automatically increase the log file and data file size.
> >> > Thanks in Advance.
> >> > Ritesh Kumar- Hide quoted text -
> >> - Show quoted text -
> > This stored procedure (2005) will give you the free space on all
> > drives in your server:
> > exec sys.xp_fixeddrives
> > This stored procedure will give you the database size and other useful
> > info.
> > exec sp_spaceused
> > Maybe this will help?
> > Kristina- Hide quoted text -
> - Show quoted text -
good point! :)sql
can any one please help me or guide to some good article to create a script
that has to look into the data file space and log file space for each
database on sql server and if the database id running out of space then it
should automatically increase the log file and data file size.
Thanks in Advance.
Ritesh KumarRitesh
> can any one please help me or guide to some good article to create a
> script that has to look into the data file space and log file space for
> each database on sql server and if the database id running out of space
> then it should automatically increase the log file and data file size.
If the database is running out of space that is too late. You may want to
create/modify a size of database to be increased that prevents from
auto-grow feature
Actually an idea is to compare sysfiles system table data for time to time.
I'm sure you will find on internet many examples.
"Ritesh Kumar" <mailrembersu@.gmail.com> wrote in message
news:Oo$dgfNcHHA.4616@.TK2MSFTNGP03.phx.gbl...
> Hi
> can any one please help me or guide to some good article to create a
> script that has to look into the data file space and log file space for
> each database on sql server and if the database id running out of space
> then it should automatically increase the log file and data file size.
> Thanks in Advance.
> Ritesh Kumar
>|||On Mar 28, 4:06 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Ritesh
> > can any one please help me or guide to some good article to create a
> > script that has to look into the data file space and log file space for
> > each database on sql server and if the database id running out of space
> > then it should automatically increase the log file and data file size.
> If the database is running out of space that is too late. You may want to
> create/modify a size of database to be increased that prevents from
> auto-grow feature
> Actually an idea is to compare sysfiles system table data for time to time.
> I'm sure you will find on internet many examples.
> "Ritesh Kumar" <mailrembe...@.gmail.com> wrote in message
> news:Oo$dgfNcHHA.4616@.TK2MSFTNGP03.phx.gbl...
>
> > Hi
> > can any one please help me or guide to some good article to create a
> > script that has to look into the data file space and log file space for
> > each database on sql server and if the database id running out of space
> > then it should automatically increase the log file and data file size.
> > Thanks in Advance.
> > Ritesh Kumar- Hide quoted text -
> - Show quoted text -
This stored procedure (2005) will give you the free space on all
drives in your server:
exec sys.xp_fixeddrives
This stored procedure will give you the database size and other useful
info.
exec sp_spaceused
Maybe this will help?
Kristina|||Kristina
We should run this sp with the below parameter,because of results that we
get from sp are not always accurate
sp_spaceused @.updateusage = 'TRUE'
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1175083368.213998.50830@.b75g2000hsg.googlegroups.com...
> On Mar 28, 4:06 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> Ritesh
>> > can any one please help me or guide to some good article to create a
>> > script that has to look into the data file space and log file space
>> > for
>> > each database on sql server and if the database id running out of
>> > space
>> > then it should automatically increase the log file and data file size.
>> If the database is running out of space that is too late. You may want to
>> create/modify a size of database to be increased that prevents from
>> auto-grow feature
>> Actually an idea is to compare sysfiles system table data for time to
>> time.
>> I'm sure you will find on internet many examples.
>> "Ritesh Kumar" <mailrembe...@.gmail.com> wrote in message
>> news:Oo$dgfNcHHA.4616@.TK2MSFTNGP03.phx.gbl...
>>
>> > Hi
>> > can any one please help me or guide to some good article to create a
>> > script that has to look into the data file space and log file space
>> > for
>> > each database on sql server and if the database id running out of
>> > space
>> > then it should automatically increase the log file and data file size.
>> > Thanks in Advance.
>> > Ritesh Kumar- Hide quoted text -
>> - Show quoted text -
> This stored procedure (2005) will give you the free space on all
> drives in your server:
> exec sys.xp_fixeddrives
> This stored procedure will give you the database size and other useful
> info.
> exec sp_spaceused
> Maybe this will help?
> Kristina
>|||On Mar 28, 8:09 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Kristina
> We should run this sp with the below parameter,because of results that we
> get from sp are not always accurate
> sp_spaceused @.updateusage = 'TRUE'
> "Kristina" <Kristina...@.gmail.com> wrote in message
> news:1175083368.213998.50830@.b75g2000hsg.googlegroups.com...
>
> > On Mar 28, 4:06 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> >> Ritesh
> >> > can any one please help me or guide to some good article to create a
> >> > script that has to look into the data file space and log file space
> >> > for
> >> > each database on sql server and if the database id running out of
> >> > space
> >> > then it should automatically increase the log file and data file size.
> >> If the database is running out of space that is too late. You may want to
> >> create/modify a size of database to be increased that prevents from
> >> auto-grow feature
> >> Actually an idea is to compare sysfiles system table data for time to
> >> time.
> >> I'm sure you will find on internet many examples.
> >> "Ritesh Kumar" <mailrembe...@.gmail.com> wrote in message
> >>news:Oo$dgfNcHHA.4616@.TK2MSFTNGP03.phx.gbl...
> >> > Hi
> >> > can any one please help me or guide to some good article to create a
> >> > script that has to look into the data file space and log file space
> >> > for
> >> > each database on sql server and if the database id running out of
> >> > space
> >> > then it should automatically increase the log file and data file size.
> >> > Thanks in Advance.
> >> > Ritesh Kumar- Hide quoted text -
> >> - Show quoted text -
> > This stored procedure (2005) will give you the free space on all
> > drives in your server:
> > exec sys.xp_fixeddrives
> > This stored procedure will give you the database size and other useful
> > info.
> > exec sp_spaceused
> > Maybe this will help?
> > Kristina- Hide quoted text -
> - Show quoted text -
good point! :)sql
Database space monitoring.
Hi
can any one please help me or guide to some good article to create a script
that has to look into the data file space and log file space for each
database on sql server and if the database id running out of space then it
should automatically increase the log file and data file size.
Thanks in Advance.
Ritesh KumarRitesh
> can any one please help me or guide to some good article to create a
> script that has to look into the data file space and log file space for
> each database on sql server and if the database id running out of space
> then it should automatically increase the log file and data file size.
If the database is running out of space that is too late. You may want to
create/modify a size of database to be increased that prevents from
auto-grow feature
Actually an idea is to compare sysfiles system table data for time to time.
I'm sure you will find on internet many examples.
"Ritesh Kumar" <mailrembersu@.gmail.com> wrote in message
news:Oo$dgfNcHHA.4616@.TK2MSFTNGP03.phx.gbl...
> Hi
> can any one please help me or guide to some good article to create a
> script that has to look into the data file space and log file space for
> each database on sql server and if the database id running out of space
> then it should automatically increase the log file and data file size.
> Thanks in Advance.
> Ritesh Kumar
>|||On Mar 28, 4:06 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Ritesh
>
> If the database is running out of space that is too late. You may want to
> create/modify a size of database to be increased that prevents from
> auto-grow feature
> Actually an idea is to compare sysfiles system table data for time to tim
e.
> I'm sure you will find on internet many examples.
> "Ritesh Kumar" <mailrembe...@.gmail.com> wrote in message
> news:Oo$dgfNcHHA.4616@.TK2MSFTNGP03.phx.gbl...
>
>
>
>
>
> - Show quoted text -
This stored procedure (2005) will give you the free space on all
drives in your server:
exec sys.xp_fixeddrives
This stored procedure will give you the database size and other useful
info.
exec sp_spaceused
Maybe this will help?
Kristina|||Kristina
We should run this sp with the below parameter,because of results that we
get from sp are not always accurate
sp_spaceused @.updateusage = 'TRUE'
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1175083368.213998.50830@.b75g2000hsg.googlegroups.com...
> On Mar 28, 4:06 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> This stored procedure (2005) will give you the free space on all
> drives in your server:
> exec sys.xp_fixeddrives
> This stored procedure will give you the database size and other useful
> info.
> exec sp_spaceused
> Maybe this will help?
> Kristina
>|||On Mar 28, 8:09 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Kristina
> We should run this sp with the below parameter,because of results that w
e
> get from sp are not always accurate
> sp_spaceused @.updateusage = 'TRUE'
> "Kristina" <Kristina...@.gmail.com> wrote in message
> news:1175083368.213998.50830@.b75g2000hsg.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -
good point!
can any one please help me or guide to some good article to create a script
that has to look into the data file space and log file space for each
database on sql server and if the database id running out of space then it
should automatically increase the log file and data file size.
Thanks in Advance.
Ritesh KumarRitesh
> can any one please help me or guide to some good article to create a
> script that has to look into the data file space and log file space for
> each database on sql server and if the database id running out of space
> then it should automatically increase the log file and data file size.
If the database is running out of space that is too late. You may want to
create/modify a size of database to be increased that prevents from
auto-grow feature
Actually an idea is to compare sysfiles system table data for time to time.
I'm sure you will find on internet many examples.
"Ritesh Kumar" <mailrembersu@.gmail.com> wrote in message
news:Oo$dgfNcHHA.4616@.TK2MSFTNGP03.phx.gbl...
> Hi
> can any one please help me or guide to some good article to create a
> script that has to look into the data file space and log file space for
> each database on sql server and if the database id running out of space
> then it should automatically increase the log file and data file size.
> Thanks in Advance.
> Ritesh Kumar
>|||On Mar 28, 4:06 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Ritesh
>
> If the database is running out of space that is too late. You may want to
> create/modify a size of database to be increased that prevents from
> auto-grow feature
> Actually an idea is to compare sysfiles system table data for time to tim
e.
> I'm sure you will find on internet many examples.
> "Ritesh Kumar" <mailrembe...@.gmail.com> wrote in message
> news:Oo$dgfNcHHA.4616@.TK2MSFTNGP03.phx.gbl...
>
>
>
>
>
> - Show quoted text -
This stored procedure (2005) will give you the free space on all
drives in your server:
exec sys.xp_fixeddrives
This stored procedure will give you the database size and other useful
info.
exec sp_spaceused
Maybe this will help?
Kristina|||Kristina
We should run this sp with the below parameter,because of results that we
get from sp are not always accurate
sp_spaceused @.updateusage = 'TRUE'
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1175083368.213998.50830@.b75g2000hsg.googlegroups.com...
> On Mar 28, 4:06 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> This stored procedure (2005) will give you the free space on all
> drives in your server:
> exec sys.xp_fixeddrives
> This stored procedure will give you the database size and other useful
> info.
> exec sp_spaceused
> Maybe this will help?
> Kristina
>|||On Mar 28, 8:09 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Kristina
> We should run this sp with the below parameter,because of results that w
e
> get from sp are not always accurate
> sp_spaceused @.updateusage = 'TRUE'
> "Kristina" <Kristina...@.gmail.com> wrote in message
> news:1175083368.213998.50830@.b75g2000hsg.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -
good point!
Database space monitoring.
Hi
can any one please help me or guide to some good article to create a script
that has to look into the data file space and log file space for each
database on sql server and if the database id running out of space then it
should automatically increase the log file and data file size.
Thanks in Advance.
Ritesh Kumar
Ritesh
> can any one please help me or guide to some good article to create a
> script that has to look into the data file space and log file space for
> each database on sql server and if the database id running out of space
> then it should automatically increase the log file and data file size.
If the database is running out of space that is too late. You may want to
create/modify a size of database to be increased that prevents from
auto-grow feature
Actually an idea is to compare sysfiles system table data for time to time.
I'm sure you will find on internet many examples.
"Ritesh Kumar" <mailrembersu@.gmail.com> wrote in message
news:Oo$dgfNcHHA.4616@.TK2MSFTNGP03.phx.gbl...
> Hi
> can any one please help me or guide to some good article to create a
> script that has to look into the data file space and log file space for
> each database on sql server and if the database id running out of space
> then it should automatically increase the log file and data file size.
> Thanks in Advance.
> Ritesh Kumar
>
|||On Mar 28, 4:06 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Ritesh
>
> If the database is running out of space that is too late. You may want to
> create/modify a size of database to be increased that prevents from
> auto-grow feature
> Actually an idea is to compare sysfiles system table data for time to time.
> I'm sure you will find on internet many examples.
> "Ritesh Kumar" <mailrembe...@.gmail.com> wrote in message
> news:Oo$dgfNcHHA.4616@.TK2MSFTNGP03.phx.gbl...
>
>
>
> - Show quoted text -
This stored procedure (2005) will give you the free space on all
drives in your server:
exec sys.xp_fixeddrives
This stored procedure will give you the database size and other useful
info.
exec sp_spaceused
Maybe this will help?
Kristina
|||Kristina
We should run this sp with the below parameter,because of results that we
get from sp are not always accurate
sp_spaceused @.updateusage = 'TRUE'
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1175083368.213998.50830@.b75g2000hsg.googlegro ups.com...
> On Mar 28, 4:06 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> This stored procedure (2005) will give you the free space on all
> drives in your server:
> exec sys.xp_fixeddrives
> This stored procedure will give you the database size and other useful
> info.
> exec sp_spaceused
> Maybe this will help?
> Kristina
>
|||On Mar 28, 8:09 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Kristina
> We should run this sp with the below parameter,because of results that we
> get from sp are not always accurate
> sp_spaceused @.updateusage = 'TRUE'
> "Kristina" <Kristina...@.gmail.com> wrote in message
> news:1175083368.213998.50830@.b75g2000hsg.googlegro ups.com...
>
>
>
>
>
>
>
>
>
> - Show quoted text -
good point!
can any one please help me or guide to some good article to create a script
that has to look into the data file space and log file space for each
database on sql server and if the database id running out of space then it
should automatically increase the log file and data file size.
Thanks in Advance.
Ritesh Kumar
Ritesh
> can any one please help me or guide to some good article to create a
> script that has to look into the data file space and log file space for
> each database on sql server and if the database id running out of space
> then it should automatically increase the log file and data file size.
If the database is running out of space that is too late. You may want to
create/modify a size of database to be increased that prevents from
auto-grow feature
Actually an idea is to compare sysfiles system table data for time to time.
I'm sure you will find on internet many examples.
"Ritesh Kumar" <mailrembersu@.gmail.com> wrote in message
news:Oo$dgfNcHHA.4616@.TK2MSFTNGP03.phx.gbl...
> Hi
> can any one please help me or guide to some good article to create a
> script that has to look into the data file space and log file space for
> each database on sql server and if the database id running out of space
> then it should automatically increase the log file and data file size.
> Thanks in Advance.
> Ritesh Kumar
>
|||On Mar 28, 4:06 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Ritesh
>
> If the database is running out of space that is too late. You may want to
> create/modify a size of database to be increased that prevents from
> auto-grow feature
> Actually an idea is to compare sysfiles system table data for time to time.
> I'm sure you will find on internet many examples.
> "Ritesh Kumar" <mailrembe...@.gmail.com> wrote in message
> news:Oo$dgfNcHHA.4616@.TK2MSFTNGP03.phx.gbl...
>
>
>
> - Show quoted text -
This stored procedure (2005) will give you the free space on all
drives in your server:
exec sys.xp_fixeddrives
This stored procedure will give you the database size and other useful
info.
exec sp_spaceused
Maybe this will help?
Kristina
|||Kristina
We should run this sp with the below parameter,because of results that we
get from sp are not always accurate
sp_spaceused @.updateusage = 'TRUE'
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1175083368.213998.50830@.b75g2000hsg.googlegro ups.com...
> On Mar 28, 4:06 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> This stored procedure (2005) will give you the free space on all
> drives in your server:
> exec sys.xp_fixeddrives
> This stored procedure will give you the database size and other useful
> info.
> exec sp_spaceused
> Maybe this will help?
> Kristina
>
|||On Mar 28, 8:09 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Kristina
> We should run this sp with the below parameter,because of results that we
> get from sp are not always accurate
> sp_spaceused @.updateusage = 'TRUE'
> "Kristina" <Kristina...@.gmail.com> wrote in message
> news:1175083368.213998.50830@.b75g2000hsg.googlegro ups.com...
>
>
>
>
>
>
>
>
>
> - Show quoted text -
good point!
Tuesday, March 27, 2012
Database Snapshots, Mirroring, and Log Shipping
Can someone explain to me how/why you can create a database snapshot
against a mirrored database, but not a log shipped database?Some extra logic is required to keep the snapshot active during recovery.
This is built into the mirroring logic. Log shipping just uses normal log
restore logic.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<jalbenberg@.yahoo.com> wrote in message
news:1169249796.950379.163120@.q2g2000cwa.googlegroups.com...
> Can someone explain to me how/why you can create a database snapshot
> against a mirrored database, but not a log shipped database?
>
against a mirrored database, but not a log shipped database?Some extra logic is required to keep the snapshot active during recovery.
This is built into the mirroring logic. Log shipping just uses normal log
restore logic.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<jalbenberg@.yahoo.com> wrote in message
news:1169249796.950379.163120@.q2g2000cwa.googlegroups.com...
> Can someone explain to me how/why you can create a database snapshot
> against a mirrored database, but not a log shipped database?
>
Database Snapshots, Mirroring, and Log Shipping
Can someone explain to me how/why you can create a database snapshot
against a mirrored database, but not a log shipped database?Some extra logic is required to keep the snapshot active during recovery.
This is built into the mirroring logic. Log shipping just uses normal log
restore logic.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<jalbenberg@.yahoo.com> wrote in message
news:1169249796.950379.163120@.q2g2000cwa.googlegroups.com...
> Can someone explain to me how/why you can create a database snapshot
> against a mirrored database, but not a log shipped database?
>
against a mirrored database, but not a log shipped database?Some extra logic is required to keep the snapshot active during recovery.
This is built into the mirroring logic. Log shipping just uses normal log
restore logic.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<jalbenberg@.yahoo.com> wrote in message
news:1169249796.950379.163120@.q2g2000cwa.googlegroups.com...
> Can someone explain to me how/why you can create a database snapshot
> against a mirrored database, but not a log shipped database?
>
Database Snapshots, Mirroring, and Log Shipping
Can someone explain to me how/why you can create a database snapshot
against a mirrored database, but not a log shipped database?
Some extra logic is required to keep the snapshot active during recovery.
This is built into the mirroring logic. Log shipping just uses normal log
restore logic.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<jalbenberg@.yahoo.com> wrote in message
news:1169249796.950379.163120@.q2g2000cwa.googlegro ups.com...
> Can someone explain to me how/why you can create a database snapshot
> against a mirrored database, but not a log shipped database?
>
against a mirrored database, but not a log shipped database?
Some extra logic is required to keep the snapshot active during recovery.
This is built into the mirroring logic. Log shipping just uses normal log
restore logic.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<jalbenberg@.yahoo.com> wrote in message
news:1169249796.950379.163120@.q2g2000cwa.googlegro ups.com...
> Can someone explain to me how/why you can create a database snapshot
> against a mirrored database, but not a log shipped database?
>
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
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
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
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.
|||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 SQL Server 2005
Hello Every
I have a problem withASP.NET connect toDatabase Snapshot SQL Server 2005.
Step One: I create database snapshot.
Step Two: I want to use ASP.NET to Database Snapshot that create already. But I don't know connection string to database snapshot.
Please help me .....................
Thank You,
I also have the same problem as follows:
The first execute of SQL Server 2005 tasks to creates the snapshot as follows:
USE Master;
GO
IF EXITS (SELECT name FROM sys.database WHERE name = "N'source_system_snapshot_extract')
BEGIN
DROP DATABASE [source_system_snapshot_extract]
END
GO
CREATE DATABASE source_system_snapshot_extract ON
('C:\Test\source_system_snapshot_extract.ss)
AS SNAPSHOT OF source_system
GO
The second I want code in the ASP.NET to connect to the database snapshot replication above
Please post your example code if anyone can solve this problem.
|||In the ASP.NET from the code design , i write the code as follow:
Imports Microsoft.SqlServer.Dts.RuntimePublic Sub Main()
Dim Constr$=Dts.connections("Source_System").connectionstring
If not Constr.contain("Initial Catalog=Source_System") then
Dts.taskresult=dts.results.failure
return
Endif
Dts.connections("Source_System").connectionstring=Dts.connections("Source_System").connectionstring.replace("Initial Catalog=Source_System", ("Initial Catalog=Source_System_Snapshot_Extract")
Dts.taskresult=dts.results.Success
End sub
The code above does not response correctly to have the database snapshot connect to asp.net
However ,DTS does not show up in code.
Please held me to resolve this .
|||
With your above code, I have write the same code using the Visual Studion IDE, then I have added a reference as follows:
Microsoft.SqlServer.ManagedDTS.DLL
To add the reference above, it is in order to create using statement as discussed in the above code, but it is not possible to display the right statement. I think someone in Microsoft with SQL Server 2005 Specialist will help us to code this. The crucial help from Microsoft will be usefull the programmers over the world.
|||
Thanks alot that you try to help me.
but i still got a problem i can't connect to database snapshot with VB.NET.
Microsoft said that we can do database with database snapshot . but try to find any sample on the internet websit i can't found about that problem , How to connect to database snapshot with VB.NET.
i don't belive in that The Microsoft speak a lie all of customers that support his product, but i am not sure untill he solve this problem , to indicate
me to know or all of programmer in the world.
i hope that someone in Microsoft with SQL Server 2005 Specialist will help this.
|||I try helping to solve this problem, but i have still not yet find the solution. I think that there will be another specialist like moderator, contributor or commentator of this web site will help you to solve this problem. I have read some technical notes regarding to your needs I hope SQL Server Specialist from Microsoft will assist you to resolve your problem.
If Database Snapshot Replication has no connection with VB.NET, I believe that the SQL Server 2005 programmer within Microsoft was not hosted this project in SQL Server 2005 Project.
|||I understand your concepts related to the database snapshot to create just the read-only databases for viewing the reports. This is no need to use the original database with SQL Server 2005 install to come along with your backage, but I have some link to talk about database snapshot, but this web is not talking the database snapshot connecting with VB.NET (http://articles.techrepublic.com.com/5100-9592_11-6146916.html), (http://www.code-magazine.com/article.aspx?quickid=0311101&page=4), also see this link:http://sqljunkies.com/WebLog/marathonsqlguy/archive/2006/05.aspx. I think there is no way to code from VB.NET to connect the database snapshot out of the original database.
I probably am poor with this coding, I hope someone in this site will save you to code this and attached with nice example project.
|||?????????????????? ?????????????????????????????????????http://www.c-sharpcorner.com/UploadFile/paulyau/DBOpsInADOPA11302005071252AM/DBOpsInADOPA.aspx
|||
Please read this link:https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1149179&SiteID=1 , I think it is useful for you.
|||Also see this link:http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=63581&SiteID=17
|||
This is also useful link:http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1458445&SiteID=1
|||
See this help you:http://msdn2.microsoft.com/zh-cn/library/microsoft.sqlserver.management.smo.database.isdatabasesnapshot.aspx
|||
Is this link help you:http://support.microsoft.com/kb/319649 orhttp://support.microsoft.com/kb/319648/
|||
This topic is still error. Please help me
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.
>
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.
>
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.
>
Database Snapshot (SQL Server 2005)
Hi,
--
Sorry for posting my question in this group. Isn't Micsrosoft going to
create new forums for SQL2K5?
--
BOL states that the snapshot file(sparse file) is small when it is created,
and gradually grows. But I tried on my databases (even big ones) and its
size is the same as original data files. For example on AdventureWorks, the
sparse file I created took 223mb which is even bigger than the db itself!
Any help would be greatly appreciated.
LeilaRight-click the file in explorer, properties, check "size on disk".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leila" <Leilas@.hotpop.com> wrote in message news:usZ5Tdr5FHA.2576@.TK2MSFTNGP10.phx.gbl...
> Hi,
> --
> Sorry for posting my question in this group. Isn't Micsrosoft going to create new forums for
> SQL2K5?
> --
> BOL states that the snapshot file(sparse file) is small when it is created, and gradually grows.
> But I tried on my databases (even big ones) and its size is the same as original data files. For
> example on AdventureWorks, the sparse file I created took 223mb which is even bigger than the db
> itself!
> Any help would be greatly appreciated.
> Leila
>|||That is the way a sparse file works. It appears as large as it can be but
in reality it is only a few bytes to begin with and will grow as it gets
populated. Right click on the file in Explorer and choose properties. You
will see both sizes.
--
Andrew J. Kelly SQL MVP
"Leila" <Leilas@.hotpop.com> wrote in message
news:usZ5Tdr5FHA.2576@.TK2MSFTNGP10.phx.gbl...
> Hi,
> --
> Sorry for posting my question in this group. Isn't Micsrosoft going to
> create new forums for SQL2K5?
> --
> BOL states that the snapshot file(sparse file) is small when it is
> created, and gradually grows. But I tried on my databases (even big ones)
> and its size is the same as original data files. For example on
> AdventureWorks, the sparse file I created took 223mb which is even bigger
> than the db itself!
> Any help would be greatly appreciated.
> Leila
>|||"Leila" <Leilas@.hotpop.com> wrote in message
news:usZ5Tdr5FHA.2576@.TK2MSFTNGP10.phx.gbl...
> Hi,
> --
> Sorry for posting my question in this group. Isn't Micsrosoft going to
> create new forums for SQL2K5?
> --
I hope not. It's just SQL Server.
> BOL states that the snapshot file(sparse file) is small when it is
> created, and gradually grows. But I tried on my databases (even big ones)
> and its size is the same as original data files. For example on
> AdventureWorks, the sparse file I created took 223mb which is even bigger
> than the db itself!
>
Sparse files have a logical size and a smaller physical size. You are just
seeing the logical size of the file.
http://msdn.microsoft.com/en-us/library/ms175823.aspx
Look at the available space on your drive before and after creating the
snapshot. You will find that although the file is reported as being 223mb,
the available space on your drive has hardly diminished at all.
David|||And just to add, within SQL you can use fn_virtualfilestats to get the
actual size on disk of a snapshot e.g.
select db_name(DbId) as [Database],
sum(cast(((BytesOnDisk/1024.0)/1024.0) as numeric(25,2))) as [SizeOnDisk_MB]
from fn_virtualfilestats(-1,-1)
group by db_name(DbId)
You should see your snapshot database is a lot smaller than the database
it's based on (initially at least!)
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Leila" <Leilas@.hotpop.com> wrote in message
news:usZ5Tdr5FHA.2576@.TK2MSFTNGP10.phx.gbl...
> Hi,
> --
> Sorry for posting my question in this group. Isn't Micsrosoft going to
> create new forums for SQL2K5?
> --
> BOL states that the snapshot file(sparse file) is small when it is
> created, and gradually grows. But I tried on my databases (even big ones)
> and its size is the same as original data files. For example on
> AdventureWorks, the sparse file I created took 223mb which is even bigger
> than the db itself!
> Any help would be greatly appreciated.
> Leila
>sql
--
Sorry for posting my question in this group. Isn't Micsrosoft going to
create new forums for SQL2K5?
--
BOL states that the snapshot file(sparse file) is small when it is created,
and gradually grows. But I tried on my databases (even big ones) and its
size is the same as original data files. For example on AdventureWorks, the
sparse file I created took 223mb which is even bigger than the db itself!
Any help would be greatly appreciated.
LeilaRight-click the file in explorer, properties, check "size on disk".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leila" <Leilas@.hotpop.com> wrote in message news:usZ5Tdr5FHA.2576@.TK2MSFTNGP10.phx.gbl...
> Hi,
> --
> Sorry for posting my question in this group. Isn't Micsrosoft going to create new forums for
> SQL2K5?
> --
> BOL states that the snapshot file(sparse file) is small when it is created, and gradually grows.
> But I tried on my databases (even big ones) and its size is the same as original data files. For
> example on AdventureWorks, the sparse file I created took 223mb which is even bigger than the db
> itself!
> Any help would be greatly appreciated.
> Leila
>|||That is the way a sparse file works. It appears as large as it can be but
in reality it is only a few bytes to begin with and will grow as it gets
populated. Right click on the file in Explorer and choose properties. You
will see both sizes.
--
Andrew J. Kelly SQL MVP
"Leila" <Leilas@.hotpop.com> wrote in message
news:usZ5Tdr5FHA.2576@.TK2MSFTNGP10.phx.gbl...
> Hi,
> --
> Sorry for posting my question in this group. Isn't Micsrosoft going to
> create new forums for SQL2K5?
> --
> BOL states that the snapshot file(sparse file) is small when it is
> created, and gradually grows. But I tried on my databases (even big ones)
> and its size is the same as original data files. For example on
> AdventureWorks, the sparse file I created took 223mb which is even bigger
> than the db itself!
> Any help would be greatly appreciated.
> Leila
>|||"Leila" <Leilas@.hotpop.com> wrote in message
news:usZ5Tdr5FHA.2576@.TK2MSFTNGP10.phx.gbl...
> Hi,
> --
> Sorry for posting my question in this group. Isn't Micsrosoft going to
> create new forums for SQL2K5?
> --
I hope not. It's just SQL Server.
> BOL states that the snapshot file(sparse file) is small when it is
> created, and gradually grows. But I tried on my databases (even big ones)
> and its size is the same as original data files. For example on
> AdventureWorks, the sparse file I created took 223mb which is even bigger
> than the db itself!
>
Sparse files have a logical size and a smaller physical size. You are just
seeing the logical size of the file.
http://msdn.microsoft.com/en-us/library/ms175823.aspx
Look at the available space on your drive before and after creating the
snapshot. You will find that although the file is reported as being 223mb,
the available space on your drive has hardly diminished at all.
David|||And just to add, within SQL you can use fn_virtualfilestats to get the
actual size on disk of a snapshot e.g.
select db_name(DbId) as [Database],
sum(cast(((BytesOnDisk/1024.0)/1024.0) as numeric(25,2))) as [SizeOnDisk_MB]
from fn_virtualfilestats(-1,-1)
group by db_name(DbId)
You should see your snapshot database is a lot smaller than the database
it's based on (initially at least!)
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Leila" <Leilas@.hotpop.com> wrote in message
news:usZ5Tdr5FHA.2576@.TK2MSFTNGP10.phx.gbl...
> Hi,
> --
> Sorry for posting my question in this group. Isn't Micsrosoft going to
> create new forums for SQL2K5?
> --
> BOL states that the snapshot file(sparse file) is small when it is
> created, and gradually grows. But I tried on my databases (even big ones)
> and its size is the same as original data files. For example on
> AdventureWorks, the sparse file I created took 223mb which is even bigger
> than the db itself!
> Any help would be greatly appreciated.
> Leila
>sql
Database snapshot
I drop and recreate a database snapshot for reporting purposes at the end of a DW loading process.
I need to create some indexes to improve query performance.
Where I should create the index, on the originale db or on the snapshot db ?
Cosimo
If I remember from an article, you can not do this on a snapshot database. So I guess you should do this on the source DB. Please check it though.|||SOLVED
I create the index on the source db.
Thursday, March 22, 2012
database size and design questions
I have a question regarding database design and size .
I am migrating from several DB2 databases to SQL server. I was going to
create different databases based on application dept or business units
(the way it has been in db2). But my application folks says, they
cannot connect to multiple database or join tables accross databases,
so have all the tables in one database.
If I do that( i hate to do it), the database size will easily be 200 -
250 GB.
1. Is having all the tables in 1 database a good idea, what are the
pros and cons ?
2. If I create this huge database, how can i do maintainance on it ? Is
there a way, I can backup quickly. My guestimate for backing up a 250
GB database is around 1-2 hrs, which is not feasible.
Any input is greatly appreciated.
Thanks
Roger1st of all, your application folks dont know what they'r talking about.
You CAN do multi-db joins, and they should be able to connect to multiple
DBs. (are they writing in VB, C++, C#, VB.NET, etc or what ?)
If they dont know how to do that, then they might want to go take a class or
something as it's pretty "101" stuff.
Multiple databases on the same server is not a bad option at all.
further, you should look at this site and learn about large Databases and
maintenance, etc:
Cheers
Greg Jackson
PDX, Oregon|||man I was so aggravated, I forgot to paste my link
http://www.microsoft.com/sql/techinfo/administration/2000/scalability.asp
GAJ|||Thanks Greg, Those guys are coding in COBOL, using ES-MTO (a
microfocus engine)- this is a mainframe conversion project. I showed
them that you can add the database name in front of the table name to
do multi database queries (am i right) . But they keep saying they
cannot do it. ES-MTO uses ODBC , ADO.NET to connect to sql server.
Thanks for link Greg...i appreciate it|||> cannot do it. ES-MTO uses ODBC , ADO.NET to connect to sql server.
Ideally, their external code would call stored procedures. Then they don't
have to know how you implement the database side. It could be one database
or 200, and you could have a job switch it back and forth between the two
architectures every other Thursday.
Let the developers write the code. This is why you have database people on
staff. :-)|||AMEN My Brother...!
Furthermore if they use ADO.NET and SQL Providers, they can do all the joins
they need.
but I'm not going to even gonna go down that road.
I like Aarons solution much better anyway.
GAJ|||"sql rookie" <anytasks@.gmail.com> wrote in message
news:1112294686.285175.58040@.o13g2000cwo.googlegroups.com...
> 2. If I create this huge database, how can i do maintainance on it ? Is
> there a way, I can backup quickly. My guestimate for backing up a 250
> GB database is around 1-2 hrs, which is not feasible.
Since no on addressed this:
Backup time should not generally be the criteria here. Recovery time should
be.
As you can do online backups, you can do backups w/o downtime.
Moreover, you can do other things to help with recovery.
Look at filegroup backups... i.e. backup only parts of the DB and recover
parts as required. (BTW, SQL 2005 Enterprise handles this in a BEAUTIFUL
manner...)
Also look at perhaps a weekly full backup and then daily differentials with
transaction log backups as required.
> Any input is greatly appreciated.
> Thanks
> Roger
>
I am migrating from several DB2 databases to SQL server. I was going to
create different databases based on application dept or business units
(the way it has been in db2). But my application folks says, they
cannot connect to multiple database or join tables accross databases,
so have all the tables in one database.
If I do that( i hate to do it), the database size will easily be 200 -
250 GB.
1. Is having all the tables in 1 database a good idea, what are the
pros and cons ?
2. If I create this huge database, how can i do maintainance on it ? Is
there a way, I can backup quickly. My guestimate for backing up a 250
GB database is around 1-2 hrs, which is not feasible.
Any input is greatly appreciated.
Thanks
Roger1st of all, your application folks dont know what they'r talking about.
You CAN do multi-db joins, and they should be able to connect to multiple
DBs. (are they writing in VB, C++, C#, VB.NET, etc or what ?)
If they dont know how to do that, then they might want to go take a class or
something as it's pretty "101" stuff.
Multiple databases on the same server is not a bad option at all.
further, you should look at this site and learn about large Databases and
maintenance, etc:
Cheers
Greg Jackson
PDX, Oregon|||man I was so aggravated, I forgot to paste my link
http://www.microsoft.com/sql/techinfo/administration/2000/scalability.asp
GAJ|||Thanks Greg, Those guys are coding in COBOL, using ES-MTO (a
microfocus engine)- this is a mainframe conversion project. I showed
them that you can add the database name in front of the table name to
do multi database queries (am i right) . But they keep saying they
cannot do it. ES-MTO uses ODBC , ADO.NET to connect to sql server.
Thanks for link Greg...i appreciate it|||> cannot do it. ES-MTO uses ODBC , ADO.NET to connect to sql server.
Ideally, their external code would call stored procedures. Then they don't
have to know how you implement the database side. It could be one database
or 200, and you could have a job switch it back and forth between the two
architectures every other Thursday.
Let the developers write the code. This is why you have database people on
staff. :-)|||AMEN My Brother...!
Furthermore if they use ADO.NET and SQL Providers, they can do all the joins
they need.
but I'm not going to even gonna go down that road.
I like Aarons solution much better anyway.
GAJ|||"sql rookie" <anytasks@.gmail.com> wrote in message
news:1112294686.285175.58040@.o13g2000cwo.googlegroups.com...
> 2. If I create this huge database, how can i do maintainance on it ? Is
> there a way, I can backup quickly. My guestimate for backing up a 250
> GB database is around 1-2 hrs, which is not feasible.
Since no on addressed this:
Backup time should not generally be the criteria here. Recovery time should
be.
As you can do online backups, you can do backups w/o downtime.
Moreover, you can do other things to help with recovery.
Look at filegroup backups... i.e. backup only parts of the DB and recover
parts as required. (BTW, SQL 2005 Enterprise handles this in a BEAUTIFUL
manner...)
Also look at perhaps a weekly full backup and then daily differentials with
transaction log backups as required.
> Any input is greatly appreciated.
> Thanks
> Roger
>
Database Size
If I'm creating a db from an inport, and I know it will be large (400 gig),
is it faster to create the DB that size, or create a 1 gig DB and let it
auto grow by 1 gig at a time when it needs it during the import?
Or would it take the same amount of time?Steve wrote:
> If I'm creating a db from an inport, and I know it will be large (400 gig)
,
> is it faster to create the DB that size, or create a 1 gig DB and let it
> auto grow by 1 gig at a time when it needs it during the import?
> Or would it take the same amount of time?
>
>
If you plan to keep this database for a while, I would create it all at
one time. Your import will run faster, and, assuming you can create the
initial file as one contiguous file, you won't have disk fragmentation
to worry about later.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I know the import would run faster, as it wouldn't have to pause to claim
more disk space. But would I be saving time over all or would it take the
same amount of time?
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:e8zyPUopGHA.4196@.TK2MSFTNGP04.phx.gbl...
> Steve wrote:
> If you plan to keep this database for a while, I would create it all at
> one time. Your import will run faster, and, assuming you can create the
> initial file as one contiguous file, you won't have disk fragmentation to
> worry about later.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||would creating the db with a 200 gig mdf and 200 gif ndf file be faster than
creating a single 400 gig mdf file?
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:e8zyPUopGHA.4196@.TK2MSFTNGP04.phx.gbl...
> Steve wrote:
> If you plan to keep this database for a while, I would create it all at
> one time. Your import will run faster, and, assuming you can create the
> initial file as one contiguous file, you won't have disk fragmentation to
> worry about later.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Steve wrote:
> I know the import would run faster, as it wouldn't have to pause to claim
> more disk space. But would I be saving time over all or would it take the
> same amount of time?
>
Overall, the entire growth + import process will probably take as long
as just initially creating the database that size. Fragmentation
resulting from the constant addition of 1GB chunks would be your concern
if you let it auto-grow. You'll end up with chunks of the database
scattered all over the disk, causing additional disk overhead when
retrieving data.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Steve wrote:
> would creating the db with a 200 gig mdf and 200 gif ndf file be faster th
an
> creating a single 400 gig mdf file?
>
Probably not, because you're still initializing the same amount of
space. If you could create them simultaneously, on seperate I/O
channels, then yes.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks for your help.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:OD$9QhopGHA.4424@.TK2MSFTNGP05.phx.gbl...
> Steve wrote:
> Probably not, because you're still initializing the same amount of space.
> If you could create them simultaneously, on seperate I/O channels, then
> yes.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||One more thing to consider about autogrow: you have no control over when the
growth happens - so a random insert/update could cause growth and IO delays
at a busy period for your server. Far better for you to manage the growth of
the server yourself.
On SQL 2005, you can setup the system such that file growth is instantaneous
(i.e. the file is not zeroed when its created or grown)
Thanks
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Steve" <ss@.Mailinator.com> wrote in message
news:ee26VkopGHA.4760@.TK2MSFTNGP05.phx.gbl...
> Thanks for your help.
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:OD$9QhopGHA.4424@.TK2MSFTNGP05.phx.gbl...
>
is it faster to create the DB that size, or create a 1 gig DB and let it
auto grow by 1 gig at a time when it needs it during the import?
Or would it take the same amount of time?Steve wrote:
> If I'm creating a db from an inport, and I know it will be large (400 gig)
,
> is it faster to create the DB that size, or create a 1 gig DB and let it
> auto grow by 1 gig at a time when it needs it during the import?
> Or would it take the same amount of time?
>
>
If you plan to keep this database for a while, I would create it all at
one time. Your import will run faster, and, assuming you can create the
initial file as one contiguous file, you won't have disk fragmentation
to worry about later.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I know the import would run faster, as it wouldn't have to pause to claim
more disk space. But would I be saving time over all or would it take the
same amount of time?
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:e8zyPUopGHA.4196@.TK2MSFTNGP04.phx.gbl...
> Steve wrote:
> If you plan to keep this database for a while, I would create it all at
> one time. Your import will run faster, and, assuming you can create the
> initial file as one contiguous file, you won't have disk fragmentation to
> worry about later.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||would creating the db with a 200 gig mdf and 200 gif ndf file be faster than
creating a single 400 gig mdf file?
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:e8zyPUopGHA.4196@.TK2MSFTNGP04.phx.gbl...
> Steve wrote:
> If you plan to keep this database for a while, I would create it all at
> one time. Your import will run faster, and, assuming you can create the
> initial file as one contiguous file, you won't have disk fragmentation to
> worry about later.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Steve wrote:
> I know the import would run faster, as it wouldn't have to pause to claim
> more disk space. But would I be saving time over all or would it take the
> same amount of time?
>
Overall, the entire growth + import process will probably take as long
as just initially creating the database that size. Fragmentation
resulting from the constant addition of 1GB chunks would be your concern
if you let it auto-grow. You'll end up with chunks of the database
scattered all over the disk, causing additional disk overhead when
retrieving data.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Steve wrote:
> would creating the db with a 200 gig mdf and 200 gif ndf file be faster th
an
> creating a single 400 gig mdf file?
>
Probably not, because you're still initializing the same amount of
space. If you could create them simultaneously, on seperate I/O
channels, then yes.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks for your help.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:OD$9QhopGHA.4424@.TK2MSFTNGP05.phx.gbl...
> Steve wrote:
> Probably not, because you're still initializing the same amount of space.
> If you could create them simultaneously, on seperate I/O channels, then
> yes.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||One more thing to consider about autogrow: you have no control over when the
growth happens - so a random insert/update could cause growth and IO delays
at a busy period for your server. Far better for you to manage the growth of
the server yourself.
On SQL 2005, you can setup the system such that file growth is instantaneous
(i.e. the file is not zeroed when its created or grown)
Thanks
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Steve" <ss@.Mailinator.com> wrote in message
news:ee26VkopGHA.4760@.TK2MSFTNGP05.phx.gbl...
> Thanks for your help.
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:OD$9QhopGHA.4424@.TK2MSFTNGP05.phx.gbl...
>
Database Size
I am attempting to create a new database with a size of 7gig. I have 16 gig
available but Enterprise Manager errors with not enough disk space with any
attempt greater than 4gig. any suggestions are very much welcomedAll I can think of is that the file system is FAT rather thjan NTFS.=20
It is always recommended to use NTFS for SQL Server
Mike John
"needing help" <anonymous@.discussions.microsoft.com> wrote in message =
news:737D769F-71B3-40F0-A2FD-DB8C609273F3@.microsoft.com...
16 gig available but Enterprise Manager errors with not enough disk =
space with any attempt greater than 4gig. any suggestions are very much =
welcomed|||Hi,
From Query analyzer , execute the below Extended procedure and identify the
hard disk availability,
xp_fixeddrives
Thanks
Hari
MCDBA
"needing help" <anonymous@.discussions.microsoft.com> wrote in message
news:737D769F-71B3-40F0-A2FD-DB8C609273F3@.microsoft.com...
gig available but Enterprise Manager errors with not enough disk space with
any attempt greater than 4gig. any suggestions are very much welcomed|||I execute the produre and it confirms the drive has 16515MB free. The drive
is formatted to FAT32 much to my suprise
available but Enterprise Manager errors with not enough disk space with any
attempt greater than 4gig. any suggestions are very much welcomedAll I can think of is that the file system is FAT rather thjan NTFS.=20
It is always recommended to use NTFS for SQL Server
Mike John
"needing help" <anonymous@.discussions.microsoft.com> wrote in message =
news:737D769F-71B3-40F0-A2FD-DB8C609273F3@.microsoft.com...
quote:
> I am attempting to create a new database with a size of 7gig. I have =
16 gig available but Enterprise Manager errors with not enough disk =
space with any attempt greater than 4gig. any suggestions are very much =
welcomed|||Hi,
From Query analyzer , execute the below Extended procedure and identify the
hard disk availability,
xp_fixeddrives
Thanks
Hari
MCDBA
"needing help" <anonymous@.discussions.microsoft.com> wrote in message
news:737D769F-71B3-40F0-A2FD-DB8C609273F3@.microsoft.com...
quote:
> I am attempting to create a new database with a size of 7gig. I have 16
gig available but Enterprise Manager errors with not enough disk space with
any attempt greater than 4gig. any suggestions are very much welcomed|||I execute the produre and it confirms the drive has 16515MB free. The drive
is formatted to FAT32 much to my suprise
Database Size
Hi,
I have create Database with size 200 md and 40 mb for mdf and ldf
respectively (Fixed Size - not go grow or shrink) while creating database,
using wizard, little later i checked database size it became with default
size automatically.
How can I create database with Fixed size
Thanks
Mothi KannanIn sql Enterprise Manager right click your database and use the data and log
tabs to set the sizes. be sure to uncheck the autogrow stuff..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mothi" <Mothi@.discussions.microsoft.com> wrote in message
news:2AAF530A-5462-4E60-8D28-D76D7429DFC4@.microsoft.com...
> Hi,
> I have create Database with size 200 md and 40 mb for mdf and ldf
> respectively (Fixed Size - not go grow or shrink) while creating database,
> using wizard, little later i checked database size it became with default
> size automatically.
> How can I create database with Fixed size
> Thanks
> Mothi Kannan
I have create Database with size 200 md and 40 mb for mdf and ldf
respectively (Fixed Size - not go grow or shrink) while creating database,
using wizard, little later i checked database size it became with default
size automatically.
How can I create database with Fixed size
Thanks
Mothi KannanIn sql Enterprise Manager right click your database and use the data and log
tabs to set the sizes. be sure to uncheck the autogrow stuff..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mothi" <Mothi@.discussions.microsoft.com> wrote in message
news:2AAF530A-5462-4E60-8D28-D76D7429DFC4@.microsoft.com...
> Hi,
> I have create Database with size 200 md and 40 mb for mdf and ldf
> respectively (Fixed Size - not go grow or shrink) while creating database,
> using wizard, little later i checked database size it became with default
> size automatically.
> How can I create database with Fixed size
> Thanks
> Mothi Kannan
Subscribe to:
Posts (Atom)