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.
LVL 13
josgoodAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BTosonCommented:
Have you tried setting context.CommandTimeout to something higher than the default of 30 seconds?
0
josgoodAuthor Commented:
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?
0
BTosonCommented:
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!
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

josgoodAuthor Commented:
>>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.
0
BTosonCommented:
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?
0
josgoodAuthor Commented:
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.
0
josgoodAuthor Commented:
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();
         }
         //
      }
0
BTosonCommented:
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...
0
josgoodAuthor Commented:
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.
0
BTosonCommented:
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);
0
josgoodAuthor Commented:
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.
0
josgoodAuthor Commented:
>>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!
0
BTosonCommented:
Sorry, it's late!  I meant INSERT statements in your DataContext log and I meant CREATEs not INSERTs!
The only thing that comes to mind with the time out is either the mdf file or the SQL server.
Is the CREATE DATABASE command showing in the log?  If so is there anything after it or does it time out on the create?

I ran the below through and it managed to get it's way through fine with no timeout issues etc.
for (int i = 0; i < 1000; i++)
{
	 var ConnectionString = string.Format(@"Data Source=MERLIN;Integrated Security=True;AttachDbFilename=W:\Temp\Test {0}.mdf;", i);
	 var db = new TestDataContext(ConnectionString);
	 db.Log = new System.IO.StringWriter();
	 if (!db.DatabaseExists()) db.CreateDatabase();
	 db.DeleteDatabase();
	 Console.WriteLine(db.Log.ToString());
	 db.Dispose();
}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
josgoodAuthor Commented:
Let me try that and I'll be back shortly.  It's late for me, too.
0
josgoodAuthor Commented:
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.
0
josgoodAuthor Commented:
Using the incrementing name, I got the same SqlException on
   db.CreateDatabase();
0
BTosonCommented:
How about on your original code?  Is the timeout occurring directly after the CREATE DATABASE command?
0
josgoodAuthor Commented:
Yes, that is correct.
0
BTosonCommented:
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.
0
josgoodAuthor Commented:
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.
0
josgoodAuthor Commented:
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!
0
BTosonCommented:
You're more than welcome.
To be honest, not sure what I've done so far :-)  I just hope it ends up working ok.

0
josgoodAuthor Commented:
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
0
josgoodAuthor Commented:
You're very cool in my book!
0
BTosonCommented:
Thanks for the positive feedback and good luck!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.