Start Free Trial

asked on

LINQ CreateDatabase Timeout

I am creating about 9000 .mdf files. This process works great for a while and then an SqlException is thrown -- "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

My question is "Why am I getting this timeout"?

At the moment, the process iterates over 241 databases, which have all been created, so it doesn't create them a second time. When it gets to the 242nd database, it tries to create it (since it isn't present) and then gets the SqlException. This same code created the first 241 databases, although the exception was encountered earlier in the iteration. Restarting the loop gets you further...or at least it did until now.

I'm not sure what data to give...I have a DataContext defined
public class StockDataContext : DataContext {
public Table<StockPriceData> data;
public StockDataContext(string connection) : base(connection) {}
}
with
[Table(Name = "StockData")]
public class StockPriceData {
[Column(DbType = "int not null", IsPrimaryKey=true)] public int IntDate;
[Column(DbType = "nvarchar(10) not null")] public string Ticker;
}
used to create a database
string mdfPath = full path to the database file
context = new StockDataContext(mdfPath);
if (!context.DatabaseExists()) {
try {
context.CreateDatabase();
} catch (System.Data.SqlClient.SqlException ex) {
context.CreateDatabase();
}
This code is in a class named MDF that implements IDisposable. The calling loop calls Dispose on the instance at an appropriate time. MDF's Dispose calls StockDataContext's Dispose. Each instance is used to investigate one stock.

Have you tried setting context.CommandTimeout to something higher than the default of 30 seconds?

ASKER

Good idea. I set
context.CommandTimeout = 120;
and still hit the breakpoint. I checked the value and it really is set to 120.

Here's the clue, though. The timeout still happened in 30 seconds.

Someone else is timing out. Who might that be?

Where did you set the timeout? Make sure you set it before new StockDataContext as it will be lost on disposal.
Not sure if it would help but have you tried setting/changing the connection timeout in the connection string?

On another topic, couldn't you get away with using one table for them all instead of seperate databases!

ASKER

>>using one table for them all instead of seperate databases
The actual table for all 9000 stocks will be over 10 million rows. My poor Wintel doesn't have the horsepower for that

Here's the code that sets the 120 sec timeout
context = new StockDataContext(mdfPath);
context.CommandTimeout = 120;
DateTime now = DateTime.Now;
if (!context.DatabaseExists()) {
try {
context.CreateDatabase();
} catch (System.Data.SqlClient.SqlException ex) {
TimeSpan ts = DateTime.Now - now;
}
I'm using the TimeSpan to confirm the 30 seconds. On four successive runs it was 30 sec and a few hundredths.

I'll try changing the timeout in the connection string.

Seems really strange. Maybe CreateDatabase uses a seperate timeout.
Have you tried setting context.Log to a TextWriter and dumping the output on the exception to see what commands are executing?

ASKER

Another really good idea and another clue.

I have
context = new StockDataContext(mdfPath);
FileStream fs = new FileStream(@"C:\scratch\log.txt",FileMode.Append);
context.Log = new StreamWriter(fs,Encoding.UTF8);;
On the second database, I get an IOException - "The process cannot access the file 'C:\\scratch\\log.txt' because it is being used by another process."

fs goes out of scope when the function exits, which it will when we finish the first database. That should dispose the FileStream. The StreamWriter will get disposed when I call dispose on the context.

At least that's my thinking. That obviously isn't happening. I clearly don't understand something.

ASKER

Here is the method in full...might make things clearer

//
// ------------------------------------------------------------------------
// | Bring the Database File up to date -- in line with the AIQ data file |
// ------------------------------------------------------------------------
/// Ensure that the ticker's database file is as up-to-date as the AIQ data file.
/// This method is responsible for \n
/// 1) Setting the current ticker in AIQ \n
/// 2) Ensuring the context database file exists (creating one if not)
/// 3) Adding any new later data to the MDF file for the ticker \n
///
/// This method is *not* responsible for entering data into the MDF file that is earlier than the earliest current data in the MDF file.
///
/// /arg mdfPath - full path to the ticker's MDF file
//
void BringUpToDate(string mdfPath) {
//
// Find the earliest date in the AIQ file
//
ticker = Path.GetFileNameWithoutExtension(mdfPath);
AIQ.EstablishCurrentTicker(ticker); // AIQ needs to know which ticker we're talking about
DateTime latestAIQ = AIQDataFile.LatestDate(); // Latest date in the AIQ data file
//
// Ensure the database file exists.
// Note: If you use Explorer to delete a database file, someone remembers that the file exists and someone forgets.
// So DatabaseExists() will return false, but CreateDatabase() will fail, thinking that the files already exists.
//
context = new StockDataContext(mdfPath);
FileStream fs = new FileStream(@"C:\scratch\log.txt",FileMode.Append);
context.Log = new StreamWriter(fs,Encoding.UTF8);;
context.CommandTimeout = 120;
DateTime latestDB = DateTime.MinValue;
DateTime now = DateTime.Now;
if (!context.DatabaseExists()) {
try {
context.CreateDatabase();
} catch (System.Data.SqlClient.SqlException ex) {
TimeSpan ts = DateTime.Now - now;
context.CreateDatabase();
}
//
// If the database file already exists, get the date of the latest entry in the database.
// If the database is empty, a date of 0 is returned. In that case, start with the earliest AIQ date.
//
} else {
// Select latest date in the database
int date = (from rows in context.data orderby rows.AIQDate descending select rows.AIQDate).FirstOrDefault();
if (date != 0) {
latestDB = AIQ.AIQDateToCalendar(date);
} else {
StringBuilder aDate = new StringBuilder(64);
AIQ.GetTickerFirstDataDate(aDate);
latestDB = DateTime.Parse(aDate.ToString());
}
}
//
// Bring the database up to date. Read each new AIQ file entry and add it to the database.
//
if (latestAIQ > latestDB) {
Int16 fromThisDate = AIQ.DateToAIQ(latestDB);
Int16 throughThisDate = AIQ.DateToAIQ(latestAIQ);
StockPriceData data = new StockPriceData();
data.Ticker = ticker;
for (; fromThisDate <= throughThisDate; ++fromThisDate) {
data.AIQDate = fromThisDate;
data.Date = AIQ.AIQDateToCalendar(data.AIQDate);
AIQDataFile.DailyData(ref data);
context.data.InsertOnSubmit(data);
}
context.SubmitChanges();
}
//
}

For the Log try soemthing like:
context.Log == new System.IO.StringWriter();
then just dump context.Log.ToString somewhere!

Just reading through your code now...

ASKER

Last contents of the log file are
.Net SqlClient Data Provider: Changed database context to 'F:\Projects\Dawn\Database\AGIL.mdf'..
SELECT TOP (1) [t0].[AIQDate]
FROM [StockData] AS [t0]
ORDER BY [t0].[AIQDate] DESC
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.1

Current file is AGIX.mdf. The context.Log is of zero length.

Strange! Would expect to see some INSERT statements. Is it only that file that is causing the issues?
Have you tried dropping and deleting it and retrying?

If that fails, have you tried declaring context deeper within the scope? It might not make a difference but you never know!
var context = new StockDataContext(mdfPath);

ASKER

In this code snip...
if (!context.DatabaseExists()) {
try {
context.CreateDatabase();
} catch (System.Data.SqlClient.SqlException ex) {
LogThis(context.Log.ToString());
TimeSpan ts = DateTime.Now - now;
}
breakpoint was set on the
TimeSpan ts = DateTime.Now - now;
On the normal path, a
LogThis(context.Log.ToString());
is the last line in the method.

ASKER

>>Would expect to see some INSERT statements
I'm doing one thing at a time...getting the databases built.

>>Is it only that file that is causing the issues
Currently, yes. Earlier today, other files were having this problem. On the next run, more files got created and then this problem came up. Now we always stop on the same file.

>>tried declaring context deeper within the scope
If it worked I would be even more confused than I am now!

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Let me try that and I'll be back shortly. It's late for me, too.

ASKER

Using this connection string
var ConnectionString = string.Format(@"F:\Projects\Dawn\Database\{0}.mdf",i);

I got
.Net SqlClient Data Provider: Changed database context to 'master'..
Attempting to delete the database 'F:\Projects\Dawn\Database\120.mdf'.
DROP DATABASE [F:\Projects\Dawn\Database\120.mdf]

I tried it first using the same name over and over. That ran all the way through.

ASKER

Using the incrementing name, I got the same SqlException on
db.CreateDatabase();

How about on your original code? Is the timeout occurring directly after the CREATE DATABASE command?

ASKER

Yes, that is correct.

And is there anything in the SQL server log? From what we have discussed that's where the issue seems to lie! As I said, it looks liek the file might be the issue in some way.

ASKER

This connection string seems to be the solution...
var ConnectionString = string.Format(@"Server=(local)\SQLEXPRESS;Initial Catalog=;Integrated Security=SSPI;AttachDbFilename=F:\Projects\Dawn\Database\{0}.mdf",i);

Currently it is over 400 and running fine.

ASKER

It ran all the way through. I will have to try this with my real file names, but having a working similar bit of code is a key factor to success.

If you have an explanation for why that connection string makes a difference, I would love to hear it.

Either way, I'm happy and I *REALLY* appreciate your help!

You're more than welcome.
To be honest, not sure what I've done so far :-) I just hope it ends up working ok.

ASKER

You provided ideas and questions that broke the logjam.

It *will* end up working OK. I make a habit of succeeding.

(It's OK, I knocked on wood)

Thanks.

Joe

ASKER

You're very cool in my book!

Thanks for the positive feedback and good luck!