MS SQL Server - Troubleshooting a "suspect" database

Author

Troubleshooting a "suspect" database

Marcel van Brakel

2005-06-30, 7:23 am

Hi,

Were using SQL Server 2000 on a Windows 2000 Server machine and one (and
only one out of about 40) of our databases periodically (say twice a month)
gets set to the "suspect" state. I've already looked through the log files
and searched the internet/newsgroups but they don't help me much in
pinpointing the reason to why this is happening. Here are some lines from
the log files (maintenance plan logfiles and errorlog):

2005-06-30 01:00:58.45 spid90 udopen: Operating system error 32(The
process cannot access the file because it is being used by another process.)
during the creation/opening of physical device
d:\sql\mssql\data\DM
_app_Data.MDF.
2005-06-30 01:00:58.46 spid90 FCB::Open failed: Could not open device
d:\sql\mssql\data\DM
_app_Data.MDF for virtual device number (VDN) 1.

[14] Database DMapp: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 945: [Microsoft][ODBC SQL
Server Driver][SQL Server]Database 'DMapp' cannot be opened due to
inaccessible files or insufficient memory or disk space. See the SQL Server
errorlog for details.

The following errors were found:

[Microsoft][ODBC SQL Server Driver][SQL Server]Database 'DMapp' cannot be
opened due to inaccessible files or insufficient memory or disk space. See
the SQL Server errorlog for details.
** Execution Time: 0 hrs, 0 mins, 1 secs **

[14] Database DMapp: Index Rebuild (leaving 10%% free space)...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5105: [Microsoft][ODBC SQL
Server Driver][SQL Server]Device activation error. The physical file name
'd:\sql\mssql\data\D
M_app_Data.MDF' may be incorrect.
[Microsoft][ODBC SQL Server Driver][SQL Server]Database 'DMapp' cannot be
opened due to inaccessible files or insufficient memory or disk space. See
the SQL Server errorlog for details.

** Execution Time: 0 hrs, 0 mins, 1 secs **

Note that we do have sufficient disk space. The server has 1 GB of RAM,
which is always used fully, but I can't imagin that being the problem
considering other (even larger) databases have no problems. No virus scanner
is running, neither is the indexing service. The "being used by another
process" troubled me, but I've used tools from SysInternals to periodically
check which processes had this database file open, and never found any
process except sqlserver itself.

Does anybody have a suggestion how we can go about finding and correcting
the source of this problem?

best regards,
Marcel van Brakel

Tibor Karaszi

2005-06-30, 7:23 am

It certainly sounds like some other process uses this file from time to time. Could it be an
anti-virus software? Also, do you have autoclose turned on for the database?

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/

"Marcel van Brakel" <brakelm@newsgroup.nospam> wrote in message
news:eJxGT%23VfFHA.1204@TK2MSFTNGP12.phx.gbl...
> Hi,
>
> Were using SQL Server 2000 on a Windows 2000 Server machine and one (and only one out of about 40)
> of our databases periodically (say twice a month) gets set to the "suspect" state. I've already
> looked through the log files and searched the internet/newsgroups but they don't help me much in
> pinpointing the reason to why this is happening. Here are some lines from the log files
> (maintenance plan logfiles and errorlog):
>
> 2005-06-30 01:00:58.45 spid90 udopen: Operating system error 32(The process cannot access the
> file because it is being used by another process.) during the creation/opening of physical device
> d:\sql\mssql\data\DM
_app_Data.MDF.
> 2005-06-30 01:00:58.46 spid90 FCB::Open failed: Could not open device
> d:\sql\mssql\data\DM
_app_Data.MDF for virtual device number (VDN) 1.
>
> [14] Database DMapp: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 945: [Microsoft][ODBC SQL Server Driver][SQL
> Server]Database 'DMapp' cannot be opened due to inaccessible files or insufficient memory or disk
> space. See the SQL Server errorlog for details.
>
> The following errors were found:
>
> [Microsoft][ODBC SQL Server Driver][SQL Server]Database 'DMapp' cannot be opened due to
> inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>
>
> [14] Database DMapp: Index Rebuild (leaving 10%% free space)...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5105: [Microsoft][ODBC SQL Server Driver][SQL
> Server]Device activation error. The physical file name 'd:\sql\mssql\data\D
M_app_Data.MDF' may be
> incorrect.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Database 'DMapp' cannot be opened due to
> inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
>
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>
> Note that we do have sufficient disk space. The server has 1 GB of RAM, which is always used
> fully, but I can't imagin that being the problem considering other (even larger) databases have no
> problems. No virus scanner is running, neither is the indexing service. The "being used by another
> process" troubled me, but I've used tools from SysInternals to periodically check which processes
> had this database file open, and never found any process except sqlserver itself.
>
> Does anybody have a suggestion how we can go about finding and correcting the source of this
> problem?
>
> best regards,
> Marcel van Brakel
>

John Bell

2005-06-30, 7:23 am

Hi

A lack of space is one of the most common causes for a database being marked
as suspect. This is mentioned in the log, therefore you may want to monitor
your d drive usage (it could be a temporary issue that you subsequently don't
see!) Autoshrinking may mask the issue so check to see if it is on.

Check that if you have automatic file growth that it is a fixed amount and
not a percentage that takes you over the limits. You may also want to check
if quotas are being used.

If you have disc caching you may want to try turning it off as this may be
causing the issue.

HTH

John

"Marcel van Brakel" wrote:

> Hi,
>
> Were using SQL Server 2000 on a Windows 2000 Server machine and one (and
> only one out of about 40) of our databases periodically (say twice a month)
> gets set to the "suspect" state. I've already looked through the log files
> and searched the internet/newsgroups but they don't help me much in
> pinpointing the reason to why this is happening. Here are some lines from
> the log files (maintenance plan logfiles and errorlog):
>
> 2005-06-30 01:00:58.45 spid90 udopen: Operating system error 32(The
> process cannot access the file because it is being used by another process.)
> during the creation/opening of physical device
> d:\sql\mssql\data\DM
_app_Data.MDF.
> 2005-06-30 01:00:58.46 spid90 FCB::Open failed: Could not open device
> d:\sql\mssql\data\DM
_app_Data.MDF for virtual device number (VDN) 1.
>
> [14] Database DMapp: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 945: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Database 'DMapp' cannot be opened due to
> inaccessible files or insufficient memory or disk space. See the SQL Server
> errorlog for details.
>
> The following errors were found:
>
> [Microsoft][ODBC SQL Server Driver][SQL Server]Database 'DMapp' cannot be
> opened due to inaccessible files or insufficient memory or disk space. See
> the SQL Server errorlog for details.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>
>
> [14] Database DMapp: Index Rebuild (leaving 10%% free space)...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5105: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Device activation error. The physical file name
> 'd:\sql\mssql\data\D
M_app_Data.MDF' may be incorrect.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Database 'DMapp' cannot be
> opened due to inaccessible files or insufficient memory or disk space. See
> the SQL Server errorlog for details.
>
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>
> Note that we do have sufficient disk space. The server has 1 GB of RAM,
> which is always used fully, but I can't imagin that being the problem
> considering other (even larger) databases have no problems. No virus scanner
> is running, neither is the indexing service. The "being used by another
> process" troubled me, but I've used tools from SysInternals to periodically
> check which processes had this database file open, and never found any
> process except sqlserver itself.
>
> Does anybody have a suggestion how we can go about finding and correcting
> the source of this problem?
>
> best regards,
> Marcel van Brakel
>
>
>

Marcel van Brakel

2005-06-30, 9:23 am

> It certainly sounds like some other process uses this file from time to
> time. Could it be an anti-virus software? Also, do you have autoclose
> turned on for the database?

We don't run anti-virus software on this server, and periodic monitoring
didn't show any other process aside from sqlserver touching the database
file. I do have autoclose turned on.

best regards,
Marcel van Brakel

Marcel van Brakel

2005-06-30, 9:23 am

> A lack of space is one of the most common causes for a database being

> marked
> as suspect. This is mentioned in the log, therefore you may want to
> monitor
> your d drive usage (it could be a temporary issue that you subsequently
> don't
> see!) Autoshrinking may mask the issue so check to see if it is on.

Autoshrinking is indeed set to on. I wil monitor disk usage more closely for
a few days (especially during the backup/maintenance plan execution) to see
if this reveals anything.

> Check that if you have automatic file growth that it is a fixed amount and
> not a percentage that takes you over the limits. You may also want to
> check
> if quotas are being used.

I do have automatic file growth set to a percentage (10%), but the database
file is only about 130 MB (with about 2 GB of free disk space). Quotas are
not enabled.

> If you have disc caching you may want to try turning it off as this may be
> causing the issue.

I'm not sure about this one. Where can I check this/turn it off? What is the
effect of turning it off?

best regards,
Marcel van Brakel

Daniel Crichton

2005-06-30, 9:23 am

Marcel wrote on Thu, 30 Jun 2005 15:54:35 +0200:

>
> We don't run anti-virus software on this server, and periodic monitoring
> didn't show any other process aside from sqlserver touching the database
> file. I do have autoclose turned on.

Any particular reason why you use autoclose? It leaves you open to problems
like this. Autoclose is fine in a test environment, or a desktop database
system, but I'd never risk it in a live system.

Periodic monitoring isn't going to pick up the processes that only hold a
lock on the files briefly.

Are you running backups at the times that there are connections to the
database so SQL would be attempting to reopen the files? Backup software
might be preventing SQL Server getting the read/write lock on the files that
it requires.

Dan

John Bell

2005-06-30, 1:23 pm

Hi

You may want to look in the tools/utilities for the cache control or
possibly the bios for the controller. Turning it off will mean that
everything should pass through and writes/reads are not delayed or
pre-fetched.

John

Sponsored Links