Hi,
I've an application, lets call it simply "A", which creates in a Microsoft Sql Database two huge tables.
Lets call them "table1" and "table2"
It safes really much data into this tables.
After application "A" has finished another application is executed which deletes this two tables.
Then application "A" is started again and it will create this two tables again, but the amount of data becomes bigger.
It can only proceed if the tables were deleted completely before and the database is empty.
This is the procedure which I repeat very often, but everytime the amount of data becomes bigger (table1 and table2 becomes bigger).
A couple if times it works fine, but once it seems data becomes too big and application "A" fails.
Mostlikely because the data wasnt removed correctly / completely.
</p><p> try
{
SqlConnectionStringBuilder builder =
new SqlConnectionStringBuilder("Server=mycomputer\dbname;Integrated Security=SSPI;" +
"Initial Catalog=testing");
builder["Server"] = "(local)\dbname";
builder["Connect Timeout"] = 10;
builder["Trusted_Connection"] = true;
builder["Initial Catalog"] = ((ComponentConfiguration)this.componentConfig).Persistency.DatabaseName;
SqlConnection sqlconnection = new SqlConnection();
sqlconnection.ConnectionString = builder.ConnectionString;
sqlconnection.Open();
SqlCommand cmd1 = new SqlCommand("DROP TABLE table1"); // TO Do delete all tables
SqlCommand cmd2 = new SqlCommand("DROP TABLE table2"); // TO Do delete all tables
cmd1.Connection = sqlconnection;
cmd2.Connection = sqlconnection;
cmd1.ExecuteNonQuery();
Thread.Sleep(7000);
cmd2.ExecuteNonQuery();
Thread.Sleep(7000);
sqlconnection.Close();
Thread.Sleep(3000);
}
catch { }</p><p> </p><p>
Thanks for help!
mulata
Rather than make 2 trips to delete the 2 tables, you could write a stored proc and delete the tables. I dont know what you mean by "huge" and "big" tables. It will be nice if you can provide some metrics as in the number of rows approximately - few hundred million rows? sample code:
CREATE PROC dbo.DeleteTablesAsBEGINSET NOCOUNT ON--Check if table existsIFEXISTS (SELECT *fromsysobjectsWHERE name ='table1'and xtype ='U')DROP TABLE table1--Check if table existsIFEXISTS (SELECT *fromsysobjectsWHERE name ='table2'and xtype ='U')DROP TABLE table2SET NOCOUNT OFFEND|||
ok so what I have to do is:
clear a database completely, so that it is empty and recent.
as my programm only creates these two tables, table1 and table2 I can also
delete this two tables.
but its important that it is done fast and reliable.
and it must be done from a .NET program, because the name of the database and server is stored in the .NET application
table1 is small, only 1 row und 0,008 MB.
table2 has about 200000 - 300000 rows and a data space of about 33 GB
thanks,
mulata
|||
Then you would need to make 2 separate connections, each time open the connection, drop the table, close the connetion, in succession.
Would you have only 2 tables or is there possibility of more objects being created?
|||well at the moment only this two tables.
what is the advantage of opening an own connection for each drop command?
how long takes the execution of a command which deletes a table with about 300000 rows and 33 GB data?
|||Deleting 300,000 rows should not take long. Generally its not advised to have multiple connections open at the same time. If you have multiple users connecting at the same time your resource pool manager will be busy managing the connection pool. Also, if you have 2 connections, you need to create 2 connection objects. If you use single connection, you can use the same connection object which is less overhead on the framework.
|||Instead of dropping and recreating the tables as was suggested, I would truncate the tables. This way your structures are left intact and since truncation is a non-logged operation it is lightning fast.
