Database going suspect...

I'm having trouble interpreting the errorlog for a
database which is behaving strangely.
Basically, after startup, it comes to start up
database 'JDE_Transactions' and the following appears in
the errorlog...
2003-08-04 12:00:07.51 spid10 Starting up
database 'JDE_Transactions'.
2003-08-04 12:00:07.51 spid10 Opening file e:\mssql7
\data\JDE_Transactions_data.mdf.
2003-08-04 12:00:07.61 spid10 Opening file e:\MSSQL7
\data\JDE_Transactions_Log.LDF.
2003-08-04 12:00:11.54 spid10 Closing file e:\mssql7
\data\JDE_Transactions_data.mdf.
2003-08-04 12:00:11.56 spid10 Closing file e:\MSSQL7
\data\JDE_Transactions_Log.LDF.
2003-08-04 15:55:26.95 spid9 Starting up
database 'JDE_Transactions'.
2003-08-04 15:55:26.95 spid9 Opening file e:\mssql7
\data\JDE_Transactions_data.mdf.
2003-08-04 15:55:26.98 spid9 Opening file e:\MSSQL7
\data\JDE_Transactions_Log.LDF.
2003-08-04 15:55:30.86 spid9 Closing file e:\mssql7
\data\JDE_Transactions_data.mdf.
2003-08-04 15:55:30.87 spid9 Closing file e:\MSSQL7
\data\JDE_Transactions_Log.LDF.
2003-08-04 15:56:53.54 spid9 Starting up
database 'JDE_Transactions'.
2003-08-04 15:56:53.54 spid9 Opening file e:\mssql7
\data\JDE_Transactions_data.mdf.
2003-08-04 15:56:53.57 spid9 Opening file e:\MSSQL7
\data\JDE_Transactions_Log.LDF.
2003-08-04 15:56:57.46 spid9 Closing file e:\mssql7
\data\JDE_Transactions_data.mdf.
2003-08-04 15:56:57.48 spid9 Closing file e:\MSSQL7
\data\JDE_Transactions_Log.LDF.
2003-08-04 15:57:03.22 spid9 Starting up
database 'JDE_Transactions'.
2003-08-04 15:57:03.22 spid9 Opening file e:\mssql7
\data\JDE_Transactions_data.mdf.
2003-08-04 15:57:03.25 spid9 Opening file e:\MSSQL7
\data\JDE_Transactions_Log.LDF.
2003-08-04 15:57:07.16 spid9 Closing file e:\mssql7
\data\JDE_Transactions_data.mdf.
2003-08-04 15:57:07.17 spid9 Closing file e:\MSSQL7
\data\JDE_Transactions_Log.LDF.
....
This continues, with bursts of activity at different
intervals, until eventually...
2003-08-05 18:02:19.22 spid9 Starting up
database 'JDE_Transactions'.
2003-08-05 18:02:19.22 spid9 Opening file e:\mssql7
\data\JDE_Transactions_data.mdf.
2003-08-05 18:02:19.26 spid9 Opening file e:\MSSQL7
\data\JDE_Transactions_Log.LDF.
2003-08-05 18:02:24.43 spid9 Closing file e:\mssql7
\data\JDE_Transactions_data.mdf.
2003-08-05 18:02:24.44 spid9 Closing file e:\MSSQL7
\data\JDE_Transactions_Log.LDF.
2003-08-05 20:00:00.54 spid10 Starting up
database 'JDE_Transactions'.
2003-08-05 20:00:00.54 spid10 Opening file e:\mssql7
\data\JDE_Transactions_data.mdf.
2003-08-05 20:00:00.55 kernel udopen: Operating system
error 32(error not found) during the creation/opening of
physical device e:\mssql7\data\JDE_Transactions_data.mdf.
2003-08-05 20:00:00.55 kernel FCB::Open failed: Could
not open device e:\mssql7\data\JDE_Transactions_data.mdf
for virtual device number (VDN) 1.
At this point (I think), the database is marked as suspect.
Now, I have very little experience with SQL Server, but
this at first sight is a file access problem. What I don't
understand is all the open/close activity in the errorlog
over an extended period, during which the database is
accessible, and data can be retreived, seemingly as
normal - until the final errors re. failure to open
device.
Any ideas? I'd thought about doing some sort of check on
the files involved, but don't understand what the errorlog
activity is indicating. There can't be a permanent or
critical file/device access problem, otherwise the data
would never be accessible, and surely the db would fail to
open immediately.
Any suggestions gratefully received.
I should add that I'm running SQL Server 7.00.699 on NT 4.0
(1381).
Paul.It sounds like autoclose option is on for the database. Maybe when it's
closed, another process such as a virus checker is getting a handle on it
and confusing SQL when it tries to open it. You can switch off autoclose
using
exec sp_dboption 'JDE_Transactions','autoclose','false'
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Paul M. Filby" <paulmfilby@.hotmail.com> wrote in message
news:037d01c35b9f$b3b001d0$a501280a@.phx.gbl...
I'm having trouble interpreting the errorlog for a
database which is behaving strangely.
Basically, after startup, it comes to start up
database 'JDE_Transactions' and the following appears in
the errorlog...
2003-08-04 12:00:07.51 spid10 Starting up
database 'JDE_Transactions'.
2003-08-04 12:00:07.51 spid10 Opening file e:\mssql7
\data\JDE_Transactions_data.mdf.
2003-08-04 12:00:07.61 spid10 Opening file e:\MSSQL7
\data\JDE_Transactions_Log.LDF.
2003-08-04 12:00:11.54 spid10 Closing file e:\mssql7
\data\JDE_Transactions_data.mdf.
2003-08-04 12:00:11.56 spid10 Closing file e:\MSSQL7
\data\JDE_Transactions_Log.LDF.
2003-08-04 15:55:26.95 spid9 Starting up
database 'JDE_Transactions'.
2003-08-04 15:55:26.95 spid9 Opening file e:\mssql7
\data\JDE_Transactions_data.mdf.
2003-08-04 15:55:26.98 spid9 Opening file e:\MSSQL7
\data\JDE_Transactions_Log.LDF.
2003-08-04 15:55:30.86 spid9 Closing file e:\mssql7
\data\JDE_Transactions_data.mdf.
2003-08-04 15:55:30.87 spid9 Closing file e:\MSSQL7
\data\JDE_Transactions_Log.LDF.
2003-08-04 15:56:53.54 spid9 Starting up
database 'JDE_Transactions'.
2003-08-04 15:56:53.54 spid9 Opening file e:\mssql7
\data\JDE_Transactions_data.mdf.
2003-08-04 15:56:53.57 spid9 Opening file e:\MSSQL7
\data\JDE_Transactions_Log.LDF.
2003-08-04 15:56:57.46 spid9 Closing file e:\mssql7
\data\JDE_Transactions_data.mdf.
2003-08-04 15:56:57.48 spid9 Closing file e:\MSSQL7
\data\JDE_Transactions_Log.LDF.
2003-08-04 15:57:03.22 spid9 Starting up
database 'JDE_Transactions'.
2003-08-04 15:57:03.22 spid9 Opening file e:\mssql7
\data\JDE_Transactions_data.mdf.
2003-08-04 15:57:03.25 spid9 Opening file e:\MSSQL7
\data\JDE_Transactions_Log.LDF.
2003-08-04 15:57:07.16 spid9 Closing file e:\mssql7
\data\JDE_Transactions_data.mdf.
2003-08-04 15:57:07.17 spid9 Closing file e:\MSSQL7
\data\JDE_Transactions_Log.LDF.
....
This continues, with bursts of activity at different
intervals, until eventually...
2003-08-05 18:02:19.22 spid9 Starting up
database 'JDE_Transactions'.
2003-08-05 18:02:19.22 spid9 Opening file e:\mssql7
\data\JDE_Transactions_data.mdf.
2003-08-05 18:02:19.26 spid9 Opening file e:\MSSQL7
\data\JDE_Transactions_Log.LDF.
2003-08-05 18:02:24.43 spid9 Closing file e:\mssql7
\data\JDE_Transactions_data.mdf.
2003-08-05 18:02:24.44 spid9 Closing file e:\MSSQL7
\data\JDE_Transactions_Log.LDF.
2003-08-05 20:00:00.54 spid10 Starting up
database 'JDE_Transactions'.
2003-08-05 20:00:00.54 spid10 Opening file e:\mssql7
\data\JDE_Transactions_data.mdf.
2003-08-05 20:00:00.55 kernel udopen: Operating system
error 32(error not found) during the creation/opening of
physical device e:\mssql7\data\JDE_Transactions_data.mdf.
2003-08-05 20:00:00.55 kernel FCB::Open failed: Could
not open device e:\mssql7\data\JDE_Transactions_data.mdf
for virtual device number (VDN) 1.
At this point (I think), the database is marked as suspect.
Now, I have very little experience with SQL Server, but
this at first sight is a file access problem. What I don't
understand is all the open/close activity in the errorlog
over an extended period, during which the database is
accessible, and data can be retreived, seemingly as
normal - until the final errors re. failure to open
device.
Any ideas? I'd thought about doing some sort of check on
the files involved, but don't understand what the errorlog
activity is indicating. There can't be a permanent or
critical file/device access problem, otherwise the data
would never be accessible, and surely the db would fail to
open immediately.
Any suggestions gratefully received.
I should add that I'm running SQL Server 7.00.699 on NT 4.0
(1381).
Paul.|||Thanks very much for that Jasper. That's exactly what was
going on. I've switched off this option now, and hopefully
that's the problem resolved!
Cheers,
Paul.
>--Original Message--
>It sounds like autoclose option is on for the database.
Maybe when it's
>closed, another process such as a virus checker is
getting a handle on it
>and confusing SQL when it tries to open it. You can
switch off autoclose
>using
>exec sp_dboption 'JDE_Transactions','autoclose','false'
>--
>HTH
>Jasper Smith (SQL Server MVP)
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>"Paul M. Filby" <paulmfilby@.hotmail.com> wrote in message
>news:037d01c35b9f$b3b001d0$a501280a@.phx.gbl...
>I'm having trouble interpreting the errorlog for a
>database which is behaving strangely.
>Basically, after startup, it comes to start up
>database 'JDE_Transactions' and the following appears in
>the errorlog...
>2003-08-04 12:00:07.51 spid10 Starting up
>database 'JDE_Transactions'.
>2003-08-04 12:00:07.51 spid10 Opening file e:\mssql7
>\data\JDE_Transactions_data.mdf.
>2003-08-04 12:00:07.61 spid10 Opening file e:\MSSQL7
>\data\JDE_Transactions_Log.LDF.
>2003-08-04 12:00:11.54 spid10 Closing file e:\mssql7
>\data\JDE_Transactions_data.mdf.
>2003-08-04 12:00:11.56 spid10 Closing file e:\MSSQL7
>\data\JDE_Transactions_Log.LDF.
>2003-08-04 15:55:26.95 spid9 Starting up
>database 'JDE_Transactions'.
>2003-08-04 15:55:26.95 spid9 Opening file e:\mssql7
>\data\JDE_Transactions_data.mdf.
>2003-08-04 15:55:26.98 spid9 Opening file e:\MSSQL7
>\data\JDE_Transactions_Log.LDF.
>2003-08-04 15:55:30.86 spid9 Closing file e:\mssql7
>\data\JDE_Transactions_data.mdf.
>2003-08-04 15:55:30.87 spid9 Closing file e:\MSSQL7
>\data\JDE_Transactions_Log.LDF.
>2003-08-04 15:56:53.54 spid9 Starting up
>database 'JDE_Transactions'.
>2003-08-04 15:56:53.54 spid9 Opening file e:\mssql7
>\data\JDE_Transactions_data.mdf.
>2003-08-04 15:56:53.57 spid9 Opening file e:\MSSQL7
>\data\JDE_Transactions_Log.LDF.
>2003-08-04 15:56:57.46 spid9 Closing file e:\mssql7
>\data\JDE_Transactions_data.mdf.
>2003-08-04 15:56:57.48 spid9 Closing file e:\MSSQL7
>\data\JDE_Transactions_Log.LDF.
>2003-08-04 15:57:03.22 spid9 Starting up
>database 'JDE_Transactions'.
>2003-08-04 15:57:03.22 spid9 Opening file e:\mssql7
>\data\JDE_Transactions_data.mdf.
>2003-08-04 15:57:03.25 spid9 Opening file e:\MSSQL7
>\data\JDE_Transactions_Log.LDF.
>2003-08-04 15:57:07.16 spid9 Closing file e:\mssql7
>\data\JDE_Transactions_data.mdf.
>2003-08-04 15:57:07.17 spid9 Closing file e:\MSSQL7
>\data\JDE_Transactions_Log.LDF.
>.....
>This continues, with bursts of activity at different
>intervals, until eventually...
>2003-08-05 18:02:19.22 spid9 Starting up
>database 'JDE_Transactions'.
>2003-08-05 18:02:19.22 spid9 Opening file e:\mssql7
>\data\JDE_Transactions_data.mdf.
>2003-08-05 18:02:19.26 spid9 Opening file e:\MSSQL7
>\data\JDE_Transactions_Log.LDF.
>2003-08-05 18:02:24.43 spid9 Closing file e:\mssql7
>\data\JDE_Transactions_data.mdf.
>2003-08-05 18:02:24.44 spid9 Closing file e:\MSSQL7
>\data\JDE_Transactions_Log.LDF.
>2003-08-05 20:00:00.54 spid10 Starting up
>database 'JDE_Transactions'.
>2003-08-05 20:00:00.54 spid10 Opening file e:\mssql7
>\data\JDE_Transactions_data.mdf.
>2003-08-05 20:00:00.55 kernel udopen: Operating system
>error 32(error not found) during the creation/opening of
>physical device e:\mssql7\data\JDE_Transactions_data.mdf.
>2003-08-05 20:00:00.55 kernel FCB::Open failed: Could
>not open device e:\mssql7\data\JDE_Transactions_data.mdf
>for virtual device number (VDN) 1.
>At this point (I think), the database is marked as
suspect.
>Now, I have very little experience with SQL Server, but
>this at first sight is a file access problem. What I don't
>understand is all the open/close activity in the errorlog
>over an extended period, during which the database is
>accessible, and data can be retreived, seemingly as
>normal - until the final errors re. failure to open
>device.
>Any ideas? I'd thought about doing some sort of check on
>the files involved, but don't understand what the errorlog
>activity is indicating. There can't be a permanent or
>critical file/device access problem, otherwise the data
>would never be accessible, and surely the db would fail to
>open immediately.
>Any suggestions gratefully received.
>I should add that I'm running SQL Server 7.00.699 on NT
4.0
>(1381).
>Paul.
>
>.
>|||I'm sure Jasper is spot on. Win32 error number 32 is "the process
cannot access the file because it is being used by another process."
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Paul M. Filby" <paulmfilby@.hotmail.com> wrote in message
news:040201c35ba9$8ba43120$a501280a@.phx.gbl...
> Thanks very much for that Jasper. That's exactly what was
> going on. I've switched off this option now, and hopefully
> that's the problem resolved!
> Cheers,
> Paul.
>
> >--Original Message--
> >It sounds like autoclose option is on for the database.
> Maybe when it's
> >closed, another process such as a virus checker is
> getting a handle on it
> >and confusing SQL when it tries to open it. You can
> switch off autoclose
> >using
> >
> >exec sp_dboption 'JDE_Transactions','autoclose','false'
> >
> >--
> >HTH
> >
> >Jasper Smith (SQL Server MVP)
> >
> >I support PASS - the definitive, global
> >community for SQL Server professionals -
> >http://www.sqlpass.org
> >
> >"Paul M. Filby" <paulmfilby@.hotmail.com> wrote in message
> >news:037d01c35b9f$b3b001d0$a501280a@.phx.gbl...
> >I'm having trouble interpreting the errorlog for a
> >database which is behaving strangely.
> >Basically, after startup, it comes to start up
> >database 'JDE_Transactions' and the following appears in
> >the errorlog...
> >
> >2003-08-04 12:00:07.51 spid10 Starting up
> >database 'JDE_Transactions'.
> >2003-08-04 12:00:07.51 spid10 Opening file e:\mssql7
> >\data\JDE_Transactions_data.mdf.
> >2003-08-04 12:00:07.61 spid10 Opening file e:\MSSQL7
> >\data\JDE_Transactions_Log.LDF.
> >2003-08-04 12:00:11.54 spid10 Closing file e:\mssql7
> >\data\JDE_Transactions_data.mdf.
> >2003-08-04 12:00:11.56 spid10 Closing file e:\MSSQL7
> >\data\JDE_Transactions_Log.LDF.
> >2003-08-04 15:55:26.95 spid9 Starting up
> >database 'JDE_Transactions'.
> >2003-08-04 15:55:26.95 spid9 Opening file e:\mssql7
> >\data\JDE_Transactions_data.mdf.
> >2003-08-04 15:55:26.98 spid9 Opening file e:\MSSQL7
> >\data\JDE_Transactions_Log.LDF.
> >2003-08-04 15:55:30.86 spid9 Closing file e:\mssql7
> >\data\JDE_Transactions_data.mdf.
> >2003-08-04 15:55:30.87 spid9 Closing file e:\MSSQL7
> >\data\JDE_Transactions_Log.LDF.
> >2003-08-04 15:56:53.54 spid9 Starting up
> >database 'JDE_Transactions'.
> >2003-08-04 15:56:53.54 spid9 Opening file e:\mssql7
> >\data\JDE_Transactions_data.mdf.
> >2003-08-04 15:56:53.57 spid9 Opening file e:\MSSQL7
> >\data\JDE_Transactions_Log.LDF.
> >2003-08-04 15:56:57.46 spid9 Closing file e:\mssql7
> >\data\JDE_Transactions_data.mdf.
> >2003-08-04 15:56:57.48 spid9 Closing file e:\MSSQL7
> >\data\JDE_Transactions_Log.LDF.
> >2003-08-04 15:57:03.22 spid9 Starting up
> >database 'JDE_Transactions'.
> >2003-08-04 15:57:03.22 spid9 Opening file e:\mssql7
> >\data\JDE_Transactions_data.mdf.
> >2003-08-04 15:57:03.25 spid9 Opening file e:\MSSQL7
> >\data\JDE_Transactions_Log.LDF.
> >2003-08-04 15:57:07.16 spid9 Closing file e:\mssql7
> >\data\JDE_Transactions_data.mdf.
> >2003-08-04 15:57:07.17 spid9 Closing file e:\MSSQL7
> >\data\JDE_Transactions_Log.LDF.
> >.....
> >
> >This continues, with bursts of activity at different
> >intervals, until eventually...
> >
> >2003-08-05 18:02:19.22 spid9 Starting up
> >database 'JDE_Transactions'.
> >2003-08-05 18:02:19.22 spid9 Opening file e:\mssql7
> >\data\JDE_Transactions_data.mdf.
> >2003-08-05 18:02:19.26 spid9 Opening file e:\MSSQL7
> >\data\JDE_Transactions_Log.LDF.
> >2003-08-05 18:02:24.43 spid9 Closing file e:\mssql7
> >\data\JDE_Transactions_data.mdf.
> >2003-08-05 18:02:24.44 spid9 Closing file e:\MSSQL7
> >\data\JDE_Transactions_Log.LDF.
> >2003-08-05 20:00:00.54 spid10 Starting up
> >database 'JDE_Transactions'.
> >2003-08-05 20:00:00.54 spid10 Opening file e:\mssql7
> >\data\JDE_Transactions_data.mdf.
> >2003-08-05 20:00:00.55 kernel udopen: Operating system
> >error 32(error not found) during the creation/opening of
> >physical device e:\mssql7\data\JDE_Transactions_data.mdf.
> >2003-08-05 20:00:00.55 kernel FCB::Open failed: Could
> >not open device e:\mssql7\data\JDE_Transactions_data.mdf
> >for virtual device number (VDN) 1.
> >
> >At this point (I think), the database is marked as
> suspect.
> >
> >Now, I have very little experience with SQL Server, but
> >this at first sight is a file access problem. What I don't
> >understand is all the open/close activity in the errorlog
> >over an extended period, during which the database is
> >accessible, and data can be retreived, seemingly as
> >normal - until the final errors re. failure to open
> >device.
> >
> >Any ideas? I'd thought about doing some sort of check on
> >the files involved, but don't understand what the errorlog
> >activity is indicating. There can't be a permanent or
> >critical file/device access problem, otherwise the data
> >would never be accessible, and surely the db would fail to
> >open immediately.
> >
> >Any suggestions gratefully received.
> >I should add that I'm running SQL Server 7.00.699 on NT
> 4.0
> >(1381).
> >Paul.
> >
> >
> >.
> >sql

Database Design

This may not be the right place to ask this, but I'm not sure where the discussion forum for Database Design is. Basically I need to turn like a regular book for instance into a database design. It's a regular book with table of contents with titles and sub-titles. How would I make that into a database?

Here is a quick and very basic way to approach at the problem:

Tables:

Books

BookID

Title

ISBN

PublisherID

Date

{Other Base publication info}

BookFormat

BookID

Format (hard/soft/electronic/cd/tape, etc.)

DateAvailable

{Format specific Info}

Chapters

BookID

chNumber

chTitle

chSubTitle

chContents

Authors

AuthorID

LastName

FirstName

{contact info}

BooksAuthors

BookID

AuthorID

TOC and Index are often an artifact of the print-prep process. Most TOC information can be 'pulled' from the other tables. Page numbers are assiged depending upon the format/size.

Here is a good source for data models. While there isn't one for a Publisher, perhaps some amalgamation of 'Books and Libraries' and 'CD Collection' will get you thinking in a direction that will serves your needs.

http://www.databaseanswers.org/data_models/index.htm

|||Oh Wow! That site has so many designs, it will become very valuable to me. Thanks for pointing me to that site, I'm sure it will point me in the right direction.|||

It is an okay site, but it never seems to go nearly deep enough. I once started trying to put together a book of database design blueprints, but situations are all so different. You really need to take every situation differently.

Like for a book design, in addition to what Arnie noted, you might want Genre, Sections, Author of Section (or Chapter). It really depends on context. So you have to study what you are trying to convey in a database, determine everything you want to store about it, normalize it so that it works well with a relational database, and then you will have what you want..

And for any questions, yes, this is the place on the MSDN forums where I would take design questions. The first step to writing good SQL is having a good foundation to query