I have a large database that goes suspect just about every morning. Its purpose is to import data from another source and serve as a central repository; the datafile is about 2.4GB (and will continue to grow), the logfile is 7MB currently,
There's 20Gb free on the DATA volume so I don't anticipate it's a storage issue.
There are daily and weekly backups; these have failed in the past with a message of "Database 'XXX' cannot be opened due to inaccessible files or insufficient memory or disk space" -- even though there is plenty of drive space. No file access issues that I am aware of. The machine has 1GB of RAM, and in normal operation it hovers at 50% usage ... would a backup operation spike the memory to max for a large DB backup?
Where should I begin my troubleshooting? The client is decidedly not happy that the DB just "goes down" a few times every week ... thanks!
Here's some info about Suspect DB from BOL. Also, I know this is long shot but double check to see of the .mdf file is located on the disk in question.
This recovery option allows SQL Server to detect incomplete I/O operations caused by power failures or other system outages.
When set to ON, this option causes a bit to be reversed for each 512-byte sector in an 8-kilobyte (KB) database page when the page is written to disk. If a bit is in the wrong state when the page is later read by SQL Server, the page was written incorrectly; a torn page is detected. Torn pages are usually detected during recovery because any page that was written incorrectly is likely to be read by recovery.
Although SQL Server database pages are 8 KB, disks perform I/O operations using a 512-byte sector. Therefore, 16 sectors are written per database page. A torn page can occur if the system fails (for example, due to power failure) between the time the operating system writes the first 512-byte sector to disk and the completion of the 8-KB I/O operation. If the first sector of a database page is successfully written before the failure, the database page on disk will appear as updated, although it may not have succeeded.
Note Using battery-backed disk caches can ensure that data is successfully written to disk or not written at all.
If a torn page is detected, an I/O error is raised and the connection is killed. If the torn page is detected during recovery, the database is also marked suspect. The database backup should be restored, and any transaction log backups applied, because it is physically inconsistent.
By default, TORN_PAGE_DETECTION is ON.
The current setting of this option can be determined by examining the IsTornPageDetectionEnabled property of DATABASEPROPERTYEX.
Resetting the Suspect Status
Microsoft® SQL Server™ 2000 returns error 1105 and sets the status column of sysdatabases to suspect if SQL Server is unable to complete recovery on a database because the disk drive no longer has any free space. Follow these steps to resolve the problem:
Use ALTER DATABASE to add a data file or log file to the database.
Stop and restart SQL Server.
With the extra space provided by the new data file or log file, SQL Server should be able to complete recovery of the database.
Free disk space and rerun recovery.
sp_resetstatus turns off the suspect flag on a database, but leaves all other database options intact.
Caution Use sp_resetstatus only when directed by your primary support provider or as recommended in Troubleshooting. Otherwise, you might damage your database.
Because this procedure modifies the system tables, the system administrator must enable updates to the system tables before creating this procedure. To enable updates, use this procedure:
sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
After the procedure is created, immediately disable updates to the system tables:
sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
sp_resetstatus can be executed only by the system administrator. Always shut down SQL Server immediately after executing this procedure.
The syntax is:
This example turns off the suspect flag on the PRODUCTION database.