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.

BEGIN TRANSACTION
  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’.

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.

ALTER DATABASE TestingSuspect SET OFFLINE

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

ALTER DATABASE TestingSuspect SET ONLINE

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]
ON PRIMARY
 (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')
LOG ON
 (NAME = N'TestingSuspect_log', FILENAME =  N'D:\Develop\Databases\TestingSuspect_log.ldf')
GO
USE [TestingSuspect]
GO

CREATE TABLE Table1 (
 ID INT IDENTITY,
 SomeColumn CHAR(200)  -- filler
)
ON [Primary]

CREATE TABLE Table2 (
 ID INT IDENTITY,
 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