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