Torus
asked on
database in suspect mode
I separated the database into 3 files, mydb.mdf, mydb.ndf, mydb2.ndf. Today the database suddenly hanged and then I rebooted the OS. But after rebooting, the database was in suspect mode. Firstly, I thought the log file is corrupted so that I renmaed the files to mydb.mdf.bak, mydb.ndf.bak, mydb2.ndf.bak, removed the suspect database and then created the same database name mydb and database files. I stopped the service, deleted the database files and copy back the bak fiels to the database files. However, after I started the service, the database was still in suspect mode.
Finally, I checked the event log and found the following error
Error: 823, Severity: 24, State: 3
I/O error 33 (the system cannot access the file because another process already locked some part of the file) detected during write at offset 0x000006931760000 in the file 'c:\data\mydb3.ndf'
What's the problem? the ndf is corrupted?? how can I recover the file?
Finally, I checked the event log and found the following error
Error: 823, Severity: 24, State: 3
I/O error 33 (the system cannot access the file because another process already locked some part of the file) detected during write at offset 0x000006931760000 in the file 'c:\data\mydb3.ndf'
What's the problem? the ndf is corrupted?? how can I recover the file?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
can you give more detail on the step?
As I said , the error is
Error: 823, Severity: 24, State: 3
I/O error 33 (The process cannot access the file because it is being used by another
process.) detected during write at offset 0x000006931760000 in the file 'c:\data\mydb3.ndf'
So now, I have detached the database. I need to create the a new database with the same name first and let it have suspect database mydb first?
As I said , the error is
Error: 823, Severity: 24, State: 3
I/O error 33 (The process cannot access the file because it is being used by another
process.) detected during write at offset 0x000006931760000 in the file 'c:\data\mydb3.ndf'
So now, I have detached the database. I need to create the a new database with the same name first and let it have suspect database mydb first?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Torus -
Also to your question. You have detached the database and need to create a new one with the same name. I am confused here. Do you not need the data in that other database right now? If that is the case you can create a new database and give it the same name. If you have a good backup that gets you to an acceptable point you can also restore that.
You also need to be proactive here and look at the cause. You could have this same problem again. Look at your event logs and error logs (windows event logs as well as Imran indicated), analyze your disks, talk to your hardware folks about how write caching is happening and if there is a battery backup, look to see if you have a virus scanning program that uses the scan on demand feature (this is definitely not a good situation and should be disabled you don't want to be scaning your mdf/ldf/ndf files while SQL is using them)
Also to your question. You have detached the database and need to create a new one with the same name. I am confused here. Do you not need the data in that other database right now? If that is the case you can create a new database and give it the same name. If you have a good backup that gets you to an acceptable point you can also restore that.
You also need to be proactive here and look at the cause. You could have this same problem again. Look at your event logs and error logs (windows event logs as well as Imran indicated), analyze your disks, talk to your hardware folks about how write caching is happening and if there is a battery backup, look to see if you have a virus scanning program that uses the scan on demand feature (this is definitely not a good situation and should be disabled you don't want to be scaning your mdf/ldf/ndf files while SQL is using them)
ASKER
MikeWalsh, Thanks for your comment.
Actually the system is not monitored by me. It has no backup and very critical. When I heard that, I was very surprised about that. But anyway, I just try my best to see if the db can be recoverd in any methods.
I checked the logged and system and knew what caused the system crashed. One of the reason as you said is autoprotect of the virus scanning program is enabled. Another is no harddisk space so that the problem occurred.
I created a new database before I backup the old db files first. That really works if a log file is corrupted. (deleted the new db files after created and move the old db files back to previous names) However, if the mdf or ndf is corrupted, the method cannot work in this case.
Anyway, I'm trying imran method and will get back to him if work or not.
Thanks all your comments
Actually the system is not monitored by me. It has no backup and very critical. When I heard that, I was very surprised about that. But anyway, I just try my best to see if the db can be recoverd in any methods.
I checked the logged and system and knew what caused the system crashed. One of the reason as you said is autoprotect of the virus scanning program is enabled. Another is no harddisk space so that the problem occurred.
I created a new database before I backup the old db files first. That really works if a log file is corrupted. (deleted the new db files after created and move the old db files back to previous names) However, if the mdf or ndf is corrupted, the method cannot work in this case.
Anyway, I'm trying imran method and will get back to him if work or not.
Thanks all your comments
<virus scanning program is enabled>
exclude sql server mdf; ndf,etc files from Antivirus - for recovery just stop Antivirus
--keep in mind plan B - Microsoft Pay-Per-Call option if something did not work ( also after you stop SQL Server - copy the DB files - as backup somewhere in safe harbor - just in case)
---For recovery:
Try --exec sp_resetstatus --: from http://support.microsoft.com/default.aspx?scid=kb;en-us;180500
|
\/
To work around this problem, perform the steps below. Note that the final step is critical.
1. Ensure that the device file is actually available.
2. Use the supplemental stored procedure sp_resetstatus to reset the status of a suspect database. If you have not already done so, create this procedure by executing the Instsupl.sql script, found in the Mssql\Install directory. For more information on sp_resetstatus, see the "Resetting the Suspect Status" topic in the SQL Server Books Online.
3. Execute sp_resetstatus in the master database for the suspect database: use master
go
exec sp_resetstatus msdb -- replace msdb with your database name
You will see the following output: Prior to Update sysdatabases attempt for DBName='msdb', the mode=0
and status=328 (status suspect_bit=256). For DBName='msdb' in
sysdatabases, status bit 256 was forced Off and mode was forced to
0. WARNING: You MUST stop/restart SQL Server prior to accessing this
database!
4. Stop and restart SQL Server.
5. Verify that the database was recovered and is available.
6. Run DBCC NEWALLOC, DBCC TEXTALL, and DBCC CHECKDB.
exclude sql server mdf; ndf,etc files from Antivirus - for recovery just stop Antivirus
--keep in mind plan B - Microsoft Pay-Per-Call option if something did not work ( also after you stop SQL Server - copy the DB files - as backup somewhere in safe harbor - just in case)
---For recovery:
Try --exec sp_resetstatus --: from http://support.microsoft.com/default.aspx?scid=kb;en-us;180500
|
\/
To work around this problem, perform the steps below. Note that the final step is critical.
1. Ensure that the device file is actually available.
2. Use the supplemental stored procedure sp_resetstatus to reset the status of a suspect database. If you have not already done so, create this procedure by executing the Instsupl.sql script, found in the Mssql\Install directory. For more information on sp_resetstatus, see the "Resetting the Suspect Status" topic in the SQL Server Books Online.
3. Execute sp_resetstatus in the master database for the suspect database: use master
go
exec sp_resetstatus msdb -- replace msdb with your database name
You will see the following output: Prior to Update sysdatabases attempt for DBName='msdb', the mode=0
and status=328 (status suspect_bit=256). For DBName='msdb' in
sysdatabases, status bit 256 was forced Off and mode was forced to
0. WARNING: You MUST stop/restart SQL Server prior to accessing this
database!
4. Stop and restart SQL Server.
5. Verify that the database was recovered and is available.
6. Run DBCC NEWALLOC, DBCC TEXTALL, and DBCC CHECKDB.
Torus -
Well as I said the virus scan is definitely not a great idea with default setup on a database server. As Eugene said definitely disabling those file extensions is a start. Personally I like to see that a database server is locked down tight and protected from virus through access controls, proper change management and a proper security policy then nothing but the necessary OS services and the Database Server needs to run.
The suggestion Eugene gave is basically the same thing Imran gave. This carries dangers but if you have no other way to get your data you can follow this. Eugene also suggested what I suggested (call for support) and I stand by that one, especially if this is critical. This does a couple things:
1.) It brings expertise in for you with the full weight and support of Microsoft's support organization
2.) It also gives you a bit of CYA in that you are doing your homework, you are making suggestions from the vendor of the program affected
and
3.) You can weigh all of your options.
It will cost money up front but could save you time and money in the long run.
Definitely move all of your mdf,ndf and ldf files someplace safe before trying the steps Imran and then Eugene suggested and beware of potential issues here.
Then you obviously learned that you need to work with the folks who maintain this and get it backed up. When you said someone else maintains this, were you talking about the database backups (like another team of DBAs?) or were you talking about the file system backups? If you are responsible for fixing something like this you either need to getthe folks who are responsible for DBA activities involved or you are the DBA and you need to become aware of if not responsible for the backups. Obviously I am teaching to the converted anyway here but just a point.
Are there any maintenance plans on the server? It is possible that you may have a backup and not even realize it.
Also just out of curiosity. If you kill your virus scan program with SQL Server stopped and try and start the server what happens? Being out of space does not mean you have corruption, it can mean other issues and may require other steps but if your files are not corrupt and you just have a process holding locks you may not have to go through so many superflous steps besides just a restart, stop of virus scan autostarts , active scans and any autoprotect/scan on demand features and restart SQL.
Well as I said the virus scan is definitely not a great idea with default setup on a database server. As Eugene said definitely disabling those file extensions is a start. Personally I like to see that a database server is locked down tight and protected from virus through access controls, proper change management and a proper security policy then nothing but the necessary OS services and the Database Server needs to run.
The suggestion Eugene gave is basically the same thing Imran gave. This carries dangers but if you have no other way to get your data you can follow this. Eugene also suggested what I suggested (call for support) and I stand by that one, especially if this is critical. This does a couple things:
1.) It brings expertise in for you with the full weight and support of Microsoft's support organization
2.) It also gives you a bit of CYA in that you are doing your homework, you are making suggestions from the vendor of the program affected
and
3.) You can weigh all of your options.
It will cost money up front but could save you time and money in the long run.
Definitely move all of your mdf,ndf and ldf files someplace safe before trying the steps Imran and then Eugene suggested and beware of potential issues here.
Then you obviously learned that you need to work with the folks who maintain this and get it backed up. When you said someone else maintains this, were you talking about the database backups (like another team of DBAs?) or were you talking about the file system backups? If you are responsible for fixing something like this you either need to getthe folks who are responsible for DBA activities involved or you are the DBA and you need to become aware of if not responsible for the backups. Obviously I am teaching to the converted anyway here but just a point.
Are there any maintenance plans on the server? It is possible that you may have a backup and not even realize it.
Also just out of curiosity. If you kill your virus scan program with SQL Server stopped and try and start the server what happens? Being out of space does not mean you have corruption, it can mean other issues and may require other steps but if your files are not corrupt and you just have a process holding locks you may not have to go through so many superflous steps besides just a restart, stop of virus scan autostarts , active scans and any autoprotect/scan on demand features and restart SQL.
ASKER
I solved the problem. But I haven't used Mike methods. I tried to scan the harddisk using chkdsk and found that the chkdsk was stunned in some position. I think that the harddisk has some bad sector or other problem. I tried make another copy of the file and then attached the file. It seems ok. But before I turned off the virus scanning program. I am not sure if this is the main reason that make it work or the file can be copied to other harddisk area to avoid the bad sectors or physical damage of the harddisk so that it works.
check this similar post
https://www.experts-exchange.com/questions/21159643/Suspect-Database-recovery.html