SQL Server Performance Forum – Threads Archive

Database suspect

Hi I have a database under suspect mode in one of the prod servers. The below is the error after digging the large error log 2004-04-28 18:17:53.89 kernel 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 E

ataSQLDataMSSQL7DataOneSight_log.ldf. 2004-04-28 18:17:53.89 kernel FCB:<img src=’/community/emoticons/emotion-3.gif’ alt=’:O’ />pen failed: Could not open device E

ataSQLDataMSSQL7DataOneSight_log.ldf for virtual device number (VDN) 2. 2004-04-28 18:17:53.91 spid12 Attempting to rebuild primary log file for database OneSight. 2004-04-28 18:17:53.91 spid12 FCB::CreateFile() failed with error 80 for file E

ataSQLDataMSSQL7DataOneSight_log.LDF. I search the whole of net to find the straight solution and the possible problem of the error and I see that nothing straightforward. Can any body tell me how can this be troubleshooted in time pl. Thanks in advance. RoyalSher. ********* The world is the great gymnasium where we come to make ourselves strong.

Try to follow the procedure listed in books online to recover the suspect database:
Execute sp_resetstatus.
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. 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: USE master
GO
sp_configure ‘allow updates’, 1
GO
RECONFIGURE WITH OVERRIDE
GO After the procedure is created, immediately disable updates to the system tables: sp_configure ‘allow updates’, 0
GO
RECONFIGURE WITH OVERRIDE
GO sp_resetstatus can be executed only by the system administrator. Always shut down SQL Server immediately after executing this procedure.
Also findout what are causes for the suspect, check event viewer logs for any h/w malfunction information.
HTH
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

The first message in the log gives you a clue. It looks like another process (probably another database) is using this log file. Free up the file first. Then detach the database and re-attach specifying the correct data and log files. You can confirm if this file free or not by trying to rename or copy this file. If you get an error, then the file is not free.

If the database is in suspect mode then you cannot detach the database, you must run SP_RESET_STATUS to set the status. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>