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 corrupted?

Hi all,

Had a disk problem here, and all apps connected to our database hung. Nothing was responding, rebooted, and mssqlserver hangs on startup.

If i move the mdf and ldf files of the database in question to another folder, mssqlserver starts up ok, with the db marked as suspect.

It would seem the the mdf and ldf are corrupted.

I am restoring a backup from yesterday, but this will take hours.

Any suggestions trying to recover it as it sits?

Thanks in advance for any ideas.I had the same problem just last week. The only fix was to restore from last good backup and bring the db forward as far as you can with transaction logs.|||Odds are near certain that you aren't hung, it is just doing a truly massive recovery operation when the server restarts. The only answer I know is to let it recover, then you can go merrily on your way with your data intact.

-PatP|||Let it recover, it that complete, and run a dbcc checkdb to verify.|||Odds are near certain that you aren't hung, it is just doing a truly massive recovery operation when the server restarts. The only answer I know is to let it recover, then you can go merrily on your way with your data intact.

-PatP

The restore completed....now its time for the post-mortem.

The problem I was having was that I could not access the database using Query Analyzer, any application software, or Enterprise Manager. Any attempt to connect would result in a time-out, or just hang when trying to connect.

If the DB is not responding, how can one determine if a recovery operation is in progress?|||I use sp_who. Check for a low numbered (usually 10) spid doing a recovery in the database in question.

-PatP|||In answer to your next question "How do I run sp_who when I can't connect through Query Analyzer?", the answer is to try the OSQL command line utility, which sometimes works when all others fail.|||Good anticipation blindman :)

Thanks for the suggestions. I will file this information away for future reference.