Does a missing data file send a database suspect?

Short answer: No

I keep seeing this come up on various forums when people ask what makes a database go suspect or what could have caused their database to be marked suspect. I can almost guarantee when that question comes up, one or more people will answer ‘missing files’. That may have been true on earlier versions (I don’t have a SQL 2000 or earlier instance to play with), but it is not true in the current versions of SQL. A missing file may result in the database being inaccessible (depending what file), but it will not result in a suspect database.

Let’s prove it.

I’m going to create a database with three files, two of which are in the primary filegroup, with one user table on each filegroup. (T-SQL code at the end) Before each test I’ll begin a transaction, modify data in both tables, shut SQL down so that there’s an uncommitted transaction in the log (database cannot be cleanly shut down), then rename a file before restarting SQL and see what happens.

I’m not going to play with the transaction log. That I’ve done before. In SQL 2005/2008, if the transaction log is missing and the database was cleanly shut down, SQL will recreate it. If the the transaction log is missing and the database was not cleanly shut down, the database goes into the RECOVERY_PENDING state, so no SUSPECT here.

Let’s try the file in the secondary filegroup first.

  UPDATE dbo.Table1 SET SomeColumn = 'aaa'
  UPDATE dbo.Table2 SET SomeColumn = 'aaa'

Shut SQL down (SHUTDOWN WITH NOWAIT), then off to explorer and rename ‘Secondary.ndf’ to ‘Secondary.missing.ndf’ and restart the SQL instance.

It’s definitely not online, the plus sign is missing.

According to sys.databases (the state_desc column), the database state is ‘RECOVERY_PENDING’.

Database State

Due to the uncommitted transaction that was running at the time SQL was shut down, SQL needs to run restart recovery on this database before bringing it online. It cannot do that because of the missing file. Hence the database state is recovery pending, recovery has not started. A look at the SQL error logs gives a clear reason why.

Unable to open the physical file “D:\Develop\Databases\Secondary.ndf”. Operating system error 2: “2(failed to retrieve text for this error. Reason: 15105)”.

Operating system error 2 is “File not found”

The important thing to note about the RECOVERY_PENDING state is that it is not (always) fatal. If I can fix the underlying reason, (in this case the renamed file) I can restart the database and it will come online. To do that I’m simply going to take it offline, rename the file back to what it should be, then bring the DB back online.

Of course, if the file has been deleted or the drive has completely failed then recovery won’t be as trivial and a restore will likely be needed.


Rename the secondary data file back to what it should be.


DatabaseState 2

Back online and fully usable.

Next up, the second file in the primary filegroup. Again, begin transaction, do updates, stop SQL, rename ‘Primary2.ndf’ to ‘Primary2.missing.ndf’, restart SQL.

Again the database is inaccessible and again the state is RECOVERY_PENDING. Again taking the database offline, fixing the filename and bringing it online makes it fully available with no problems.

The final test, the primary file itself, ‘Primary.mdf’. Same process as before. Just as in the earlier two cases, the database comes up in the RECOVERY_PENDING state, not SUSPECT and the same process as before allows the DB to be brought online without any problems.

So in conclusion…

For a database to go suspect after a SQL startup, one or of the database files must be actually damaged. Just having the files missing will not result in the database being marked suspect. Missing or inaccessible files results in the database being marked recovery_pending only

Database code:

CREATE DATABASE [TestingSuspect]
 (NAME = N'Primary',  FILENAME = N'D:\Develop\Databases\Primary.mdf'),
 (NAME = N'Primary2',  FILENAME = N'D:\Develop\Databases\Primary2.ndf'),
FILEGROUP [Secondary]
 (NAME = N'Secondary', FILENAME = N'D:\Develop\Databases\Secondary.ndf')
 (NAME = N'TestingSuspect_log', FILENAME =  N'D:\Develop\Databases\TestingSuspect_log.ldf')
USE [TestingSuspect]

 SomeColumn CHAR(200)  -- filler
ON [Primary]

 SomeColumn CHAR(200)  -- filler
ON [Secondary]

INSERT INTO Table1 (SomeColumn)
SELECT TOP 50000 ' '
 FROM  master.sys.columns a CROSS JOIN master.sys.columns b
INSERT INTO Table2  (SomeColumn)
SELECT TOP 50000 ' '
 FROM master.sys.columns a CROSS  JOIN master.sys.columns b


  1. PauL Randal

    Yes, but the net effect is the same – you can’t access the database until you resolve the problem. It could actually be worse if a data file in the primary filegroup is missing rather than damaged – as the database can’t even be put in EMERGENCY mode.

  2. Gail


    It may be more semantics than anything else, but I keep seeing forum posts where someone has a suspect database (the actual SUSPECT state) and gets loads of people saying to check for missing/locked files.

    It’s just sending people off on the wrong track, wasting their time.

  3. Steve

    I tried the first test on SQL Server 2000 SP4 and got similar results. The Alter off/online did not work. I restarted SQL and the database was there. I never saw a suspect.

  4. Pingback: Loss of Files Does Not a Suspect Database Make « Voice of the DBA

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.