SQL 2000: How To Recover Database In SUSPECT State.
Jan 10, 2005
Hi,
I've my database, testdb, ended up in Suspect state. The SQL log shows " I/O error 38(Reached the end of the file.) detected during read at offset xxxxxxxxxx in file '<path> estdb_Data.MDF'" during recovery. I do not have backup to restore the database from. So to run DBCC CHECKDB, I tried to put the database in emergency(bypass recovery) mode using
update sysdatabases set status = 32768 where name = 'testdb'
DBCC CHECKDB showed some allocation and consistency errors and suggested "repair_allow_data_loss" as minimum repair level.
Now to run
DBCC CHECKDB('testdb', repair_allow_data_loss)
I've to put database in SINGLE USER mode. For that I started SQL server by command
sqlservr.exe -c -m
Now when I try to run DBCC CHECKDB with repair option it says "Attempt to BEGIN TRANSACTION in database 'testdb' failed because database is in BYPASS RECOVERY mode."
So it seems I need to change the status of database such that it will allow me to repair it. If I try to reset the status, the database again goes in Suspect state and it seems DBCC commands don't run on database in Suspect state. Does anybody know how to recover the database in this state? Is there any other way to repair it?
Thanks in advance,
Yojana
View 3 Replies
ADVERTISEMENT
May 24, 2007
One of the database in our SQL Server 2000 environment is in the suspect status. We need to bring it back to the normal status.
The problem occurred because the disk on which the data file and log file for this database were placed ran out of space.
Pls note other databases in the same server are working fine.
Later on more space was made available on this disk. We tried the following options but with no success.
1. Reset the status of database and restarted the SQL Server. After restarting the SQL Server, the database once again was showing the suspect status.
2. Used the same data and log file in another SQL Server and attached with the database in this another SQL Server.
3. Tried dbcc chkdb with repair_allow_data_loss.
Since the database is in suspect status, we are neither able to export the data nor able to back up the database.
Please suggest some options to recover the database from the suspect status. Also it would be great if we can get the commands, scripts to find if the data/log file is corrupt and a way to correct it (even with data loss is fine).
View 9 Replies
View Related
Apr 24, 2015
As I know the database should be restored from backup and logs reapplied. That’s the first and generally recommended approach any time a DB is suspect, whether it’s from log or data file corruption.But if I have no backups then we follow below steps ( after Googling I found these steps on most of the websites ):
1.EXEC sp_resetstatus ‘yourDBname’;
2.ALTER DATABASE yourDBname SET EMERGENCY
3.DBCC checkdb(’yourDBname’)
4.ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
5.DBCC CheckDB (’yourDBname’, REPAIR_ALLOW_DATA_LOSS)
6.ALTER DATABASE yourDBname SET MULTI_USER
Now my question is,
1. If after executing DBCC CHECKDB at 3rd step, it shows inconsistency.Then I will execute SET SINGLE USER WITH ROLLBACK & if after executing DBCC CHECKDB at 3rd step, no errors found. Then what will be the steps?
2. When we execute DBCC CHECKDB (' DBNAME ', REPAIR_ALLOW_DATA_LOSS) how can we recover lost data during process.As far as I know some data will loss and if it will happen then business will get suffered.
3. Why run DBCC CHECKDB() WITH REPAIR_ALLOW_DATA_LOSS if there are no consistency errors in the database?
View 8 Replies
View Related
Nov 17, 2007
I have the MS SQL2000 database failed to recover at computer restart. Now the database is marked suspect. How can I manage to recover the data?
Thank you.
View 5 Replies
View Related
Apr 29, 2008
Hi
I want to suspect database
stop server first
I try to rename C:Program Files (x86)Microsoft SQL ServerMSSQL.1MSSQLDatamsdbdata.mdf to msdbdata.sav
and then start the server
use command to check:
SELECT status & 256 FROM master.dbo.sysdatabases WHERE name = database_name
if the result is 256,it means the msdb is suspect,but the result is 0,it same as the normal status
do you know how to set database suspect with this way, or do you know other way to suspect databse.
absolutely,I could re-back my server noraml with your way
Thanks
View 6 Replies
View Related
Aug 28, 2007
Hello
(SQL Server 2000)
I have databases that appear as "Suspect" in the Enteprise Manager. What can I do to recover them or put them in a normal state?
Thanks a lot.
View 2 Replies
View Related
Apr 17, 2007
Guys,
Got an issue with an DB where it turn itself to suspect with just some normal queries. Its SQL 2000 8.00.2175 on Win2K3 sp1.
From SQL log, it got the following:
ex_raise2: Exception raised, major=79, minor=87, severity=22, attempting to create symptom dump
Error: 7987, Severity: 22, State: 1
SQL Server Assertion: File: <logscan.cpp>, line=3187
Failed Assertion = '(m_lastLSN == NullLSN) || (m_lastLSN > m_curLSN)'.
Any idea on what will be causing it and what can be done to fix it? We tried rebooting the instance with no luck and we finally restore the DB from last good backup. But we would like to find out the cause of it.
Thanks
View 11 Replies
View Related
Apr 25, 2007
hi all,
one of my sql database went in suspect mode can any one advice why it has happened is it the problem with sql server . This has happend for the second time in 4 months .
thanks and regards
jk
View 3 Replies
View Related
Jul 23, 2005
Hello,I am trying to recover a SQL Server 7 database from another hard diskdrive that has a corrupted Windows 2000 Advanced Server installation. Iam not able to repair the corrupted Windows 2000 Advanced Serverinstallation but the file system is intact. I have installed a new copyof SQL Server 7 onto a new hard disk and have used the sp_attach_dbsystem stored procedure to attach the database from the old hard driveinto the new installation of SQL Server 7 on the new hard drive. Thedatabase shows up in the Enterprise Manager View and all the data andstructure are there, but when I try to run a Web site that uses ODBCconnections to the database I get the following error:ODBC Error Code = 08001 (Unable to connect to data source)[Microsoft][ODBC SQL Server Driver][Named Pipes]Specified SQL servernot found.I have double-checked that all the NTFS permissions, ODBC DSNs, and WebServer settings are correct and when I run the SQL Server ODBC drivertest at the completion of each ODBC DSN setup, it is successfull.1) Why am I getting this ODBC error?2) What steps have I missed in the recovery process?3) If I have done something wrong recovering my database, can I stillrecover it since I still have: a) The old master database file and logfile; b) The old database (non-master) file and log file; c)A backed-up(Enterprise Manager -> Right Click A Database -> All Tasks -> BackupDatabase) copy of the old database (non-master) file; ?My System Specifications:-Windows 2000 Advanced Server-SQL Server 7-IIS 5.0-Cold Fusion Server 4.5 Professional Full EditionThanks For Your Time,Nathan
View 2 Replies
View Related
Aug 3, 2006
The 2.0 version of ASPSTATE is slightly different than the 1.1 version in that one table has one additional column and another table uses a different data type and size for the key. The 2.0 version also has a couple additional stored procedures.
We'd like to manage just one session state database if possible so we're trying to figure out if Microsoft supports using the new schema for 1.1 session state access (it seems to work, but our testing has been very light).
Is there any official support line on this? If not, can anyone comment on whether or not you'd expect it to work and why?
Thanks.
View 1 Replies
View Related
Dec 22, 2006
Hi,
My database corrupted because when I was running an update query, there is a power failure. After the computer booted, I cannot open the database anymore, it just not responding. Then I stop the sql server service, and tried to rename the .mdf and .ldf. After that it worked normally, but I need the data from the corrupted mdf file, I tried to attach the database but it just hanged. I even tried to attach without the .ldf file but it didn't work either, so I concluded that the problem is with the mdf file.
Is there any way to recover my data ??
Thanks in advance
Regards,
Edwin
View 8 Replies
View Related
Apr 20, 2007
Hi,
I have two servers, one production server and one backup server which have transactional replication with a pull subscription.
When I configure replication, it works fine during our test weekends testing production load. After tests, replication looks fine for a random number of days. Then, all of a sudden, an error message is displayed on one of the agents: "The agent is suspect, no response within 10 minutes." This has happened a number o times. If I remove replication and configures it again, it always works. Sometimes it works by just updating one of the tables and the error message disappears. The last time (today) that did not work. Updating the database did not replicate and the error message remained.
Has anyone experienced this same problem and has a god solution. One thing that is common is that the error message appears after long times of inactivity on the servers, or perhaps after a restart but that I am not sure about.
Question 1: How can I prevent this error message?
Question 2: Are there any special things to think about when I need to restart the servers and replication is configured, e.g. after installing updates from Windows Update.
I would be very grateful for any answers regarding this.
Best,
/M
View 1 Replies
View Related
Mar 31, 2007
I created the db with the attached script and I am able to access ituntil I reboot the server. I've tried enabling flag 1807 via the SQLserver service and the startup parameters of the instance. In allcases the database always come up suspect after a reboot. There wasone instance where I was able to recover, but I am not sure how thathappened.Does anyone have an idea of how I can reboot the server without thedatabase becomming suspect?USE MASTERGODBCC TRACEON(1807)GO--DBCC TRACEOFF(1807)--DBCC TRACESTATUS(1807)GOCREATE DATABASE ReadyNAS ON( NAME = ReadyNAS_Data,FILENAME = '\NAS1NASDiskSQL ServerReadyNASReadyNAS_Data.mdf',SIZE = 100MB,MAXSIZE = 20GB,FILEGROWTH = 20MB)LOG ON ( NAME = ReadyNAS_Log,FILENAME = '\NAS1NASDiskSQL ServerReadyNASReadyNAS_Log.ldf',SIZE = 20MB,MAXSIZE = 100MB,FILEGROWTH = 10MB)
View 5 Replies
View Related
Jun 13, 2007
My database is marked as suspectI am using SQL Server 2000,I ran the command sp_resetstatus it executes succesfully. but still the database is in suspect state.can any one tell me how i recover my data.thanks in advance
View 1 Replies
View Related
Nov 23, 1999
Im running SQL 7.0 with a large heavily used 4Gb database. After a while the database is marked suspect. What can be the cause to this ? Please email me and I can send you additional information about hardware and the system.
View 3 Replies
View Related
Dec 9, 1999
Hi everyone,
I really need help on this, one of my databases is marked suspect. I did tried to execute this command below (scripts):
use master
go
sp_configure 'allow updates',1
go
reconfigure with override
go
update sysdatabases set status=4 where name='security'
go
sp_configure 'allow updates',0
go
reconfigure with override
go
and it release the suspect from the 'Security' database, but it Won't let me access the table,data or nothing. The worse thing is when I stop & start the server again, the database marked suspect again.
Could anybody out the help me on this. Thank in advance, Vu
View 1 Replies
View Related
Apr 8, 1999
I am using SQL server 6.5 sp3 and NT 4.0 sp4.
My SMS database has been marked suspect.
I do not have a backup.
I have read several articles about trying to us sp_resetstatus.
I can not find this sp on my server.
When I try the following:
UPDATE master..sysdatabases status=256 WHERE name = ('sms')
I get the following back:
Ad-hoc updates to system catalog not enabled. System Adminstrator must recongifure system to allow this.
I have searched Microsoft KB and everywhere I can think of. If anyone can help I would really appreciate it.
I even tried reapplying sp3 and rebooting...this did not help either.
Can someone please tell me how to change the status back to normal.....I think this is a it will take..
thanks in advance,
nathan
View 1 Replies
View Related
Mar 5, 1999
I have the original dat files ( 2 seperate log / data) of a database. I have created new devices (same size as originals) and a new database. I replace the new dat files with the originals and the database always comes up as a suspect. I have done this with the other 7 databases on my server and none have similar problems. This database was created different then others using new database and new devices routine rather than creating the devices first. Any Ideas?
Thanks in Advance.
View 1 Replies
View Related
Nov 18, 1998
Hi... I have this problem with my database (dunno how this thing happen). Today, 2 of my databases have been marked as 'suspect' (grey color in SQL Enterprise Manager). In ISQL, when I try 2 connect 2 the database (use), the message '
Msg 926, Level 14, State 1
Database 'MYUAT' cannot be opened - it has been marked SUSPECT by recovery. The SA can drop the database with DBCC.'
appeared.
Don't understand Y this occurred...
Thanks in advance for any help...
~Cheers~
View 1 Replies
View Related
Jun 15, 2004
my database is in suspect,how to correct it?
thx in adance..
View 2 Replies
View Related
Sep 9, 2004
Hi all,
We recently had some kind of error on one of the server's hard drives. The network guys, reinintialized (or something) and were able to bring everything back. However, two of my databases now is "Suspect" and can't be used. Both the MDF & LDF exist, and there are no indications of actual data loss. What can I do with a "suspect" database ? SQL 2K
What should I do to recover these two databases?
Thank you for all your suggestions.
View 2 Replies
View Related
Sep 29, 2004
I could not open my database.It says its Suspect and could not open and not displaying any of the objects
What should be the reason and what is steps i can do make it up and running.
Thanks
View 10 Replies
View Related
Oct 3, 2005
:mad:
One of my prodn databases went into suspect mode today..
I was able to recover it.
The tech support guyz had rebooted the server couple of times
(out of ignorance) cos they thought if wud fix it.
I lost the sql errpor log file which had the error details as to why db when into suspect....so my questions are..
1. Probable reasons why db when into suspect mode.
2. Any other way to find out about the error details.
3. When I ran dbcc checkdb with repair_rebuild option
for some tables it gave error
"Parent node for page (1:13075) was not encountered"
How can i resolve the consistency errors.
Is DBCC CHECKTABLE sufficient.???
View 12 Replies
View Related
Jan 5, 2004
We have an SQL Server 2000 DB server with several databases on it. Frequently, one of the DBs will have a state of "Suspect." This appears to occur after our 3rd party backup software runs a full backup on the weekends.
How can I begin to troubleshoot the cause of this issue? Has anyone seen anything like this?
thanks,
Jeff
View 5 Replies
View Related
Nov 1, 2005
Why do i keep getting this error ?
Mary Mary
Yes O !
View 3 Replies
View Related
Apr 12, 2007
Hi everyone,
I hope you could help me.
I have a database that was marked as suspect, i change is state but now i want to extract the information of the database and a couldn't because de database have several erros include 2 allocation erros.
How could i solve this problem? I don't have a recent database backup.
thanks to everyone.
Pedro Silva
View 7 Replies
View Related
Apr 16, 2007
Hi all,
I have a suspect database caused by a disk error on a Raid5 disk. I'm a bit of a novice on SQL and am using SQL 2005. I have tried the following and can't get the database status to reset which means the DBCC checkdb command does not run. The client I am fixing this for has no backup of the data at all.
USE master
GO
sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_resetstatus 'nhd2007';
GO
sp_configure 'allow updates', 0
GO
RECONFIGURE WITH OVERRIDE
GO
DBCC checkdb ('nhd2007',NOINDEX);
GO
I get the following error back:-
Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install.
The suspect flag on the database "nhd2007" is already reset.
Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install.
Msg 926, Level 14, State 1, Line 3
Database 'nhd2007' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.
The error in the SQL Log which is causing all the problems is:-
Message
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:3425329; actual 25600:1744858624). It occurred during a read of page (1:3425329) in database ID 24 at offset 0x00000688862000 in file 'E:hd2007hd2007.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Any advice on what I can do to get some access to the data would be great.
Thanks
View 9 Replies
View Related
May 9, 2007
Okay, so i have a tabase named "TEST" that i set up, everything in it is for a website i've been designing slowly for the last month, i come in today, and notice the database is marked suspect. look at the log and i see all of this:
Recovery of database 'Test' (7) is 20% complete (approximately 305 more seconds) (Phase 2 of 3).
ForwardLogBlockReadAheadAsync: Operating system error 23(Data error (cyclic redundancy check).) encountered.
Operating system error 23(Data error (cyclic redundancy check).) on device 'C:Program FilesMicrosoft SQL ServerMSSQLdataTest_Log.LDF' during ForwardLogBlockReadAheadAsync.
Error: 3313, Severity: 21, State: 2
Error while redoing logged operation in database 'Test'. Error at log record ID (8440:42526:1)..
Error: 3414, Severity: 21, State: 1
Database 'Test' (database ID 7) could not recover. Contact Technical Support..
Now, honestly, i could care less about the data, the thing the matters most to me, is the couple of stored procedures i've developed.
what can i do?
View 11 Replies
View Related
Sep 8, 2007
Hi all , i am facing a problem and don't know how to handle the issue. I created a database in sql server 2000 , gave it to a friend who made certain changes to the database and mailed me back.
Now that i try to open the database , in the enterprise manager the icon of the my database is gray with "suspected" written in brackets after the database name. On expandin it , it shows ("No Items"). Kindly help me out guys , i am totally stuck for the moment.
Regards
View 12 Replies
View Related
Mar 3, 2008
i have a database which has a log file size of 10 Mb. it goes into single user mode automatically . i tried to increase the size of file size of log file from 10 mb to 50 mb... but i want to make it only 20 mb ... i am unable to change since it gives a message .cannot decrease the size of the file .. is there another way to decrease the size of log file .....also the database goes into suspect stage automatically sometimes
View 2 Replies
View Related
Jul 20, 2005
I have two questions:(1) How can we diagnose the reason of database suspect? The error logdoesn't tell me much.(2) After restart the server, the suspect is gone, but how can Iensure everthing is fine? DBCC check?Thanks!Saiyou
View 2 Replies
View Related
Feb 25, 2008
I had a "suspect" database. I detached successfully and now I'm trying to attach it. For some reason the LDF file is missing. The "detach" was successfull but the "attach" would not succeed. Whenever I try to attach the database, it would give me an error that says:
"Device Activation Error. The physical filename "xyz...LDF" may be incorrect."
What can I do to save our data from total loss?
Please help
Joel
View 1 Replies
View Related
Apr 17, 2007
I'm currently storing session state in a sql 2000 server for my vs.net 2002 projects. I'm currently developing a new project using vs.net 2005 and was wondering if I could store the session state in the same sql server. Does anyone know if this is possible? If so can someone point me in to an article or code example. All of the examples I have come across are for sqlExpress.
Thanks in advanceJason
View 1 Replies
View Related
