SQL 2000 Suspect Database
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
ADVERTISEMENT
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
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
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
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
Mar 14, 2002
Hello Gurus,
After the power failure my database was marked suspect. According to my associate we will execute SQL command sp_resetstatus. My question is what are the things we have to do first before we execute the command? We are using NT 4.
thanks
Claudio
View 1 Replies
View Related
May 12, 2000
I have a database in suspect mode. There is no backup of this database.
I've followed the troubleshooting and it isn't working out.
There are 3 steps:
1. execute sp_resetstatus
2. use alter database to add data file or log file to the database
3. stop and restart SQL Server
4. free disk space and rerun recovery
I've tried to make it, but the step # 2 haven't worked.
Then I tried step 3, however the database is still in suspect mode.
What should I do?
Is there something I am missing?
Thanks,
Fábio
View 1 Replies
View Related
Jan 25, 2001
Dear friends,
I had a huge problem with my SQL database and I had it restored by using my back up tapes from the days before. I tried to back it up although it allowed me to restore it...it now says that the database is "suspect"= not found,,lost?... How can I get rid of this suspect & open my database,,, Does this mean that my database cannot be restored from a prior day (using my backup tapes from past days through the server?)
Can someone please help me concerning "suspect database names in SQL Serbver 7.0?
Any ideas ...please help me ...more detail?
Thanks in advance,
Brad Isaacs
Junior VB Developer / SQL Server 7.0 databases
View 4 Replies
View Related
Feb 3, 2000
Hi,
One of my most used databases has been marked by the Server as SUSPECT. No, i was not trying to recover the Db. The story goes like this..
We are using MTS for a VB, SQL distributed application. One of the MTS transactions was blocking a lot of my other transactions. I tried to kill that transaction to avail nothing. I stopped the SQL Server and restarted it again ( as suggested in BOL, coz the transaction was open and was awaiting command, so server restart was the only way-BOL )
When the server came back up, and i tried to restore the db, it said that the database has been marked suspect. When i tried dbcc dbrecover, it said, could not recover because it could not connect to MSDTC to check the completion status of the transaction xxxx. sp_resetstaus did not help as well.
I do not have a usable backup of that db.
Help. This very urgent.
Deepak.
View 1 Replies
View Related
Jan 27, 1999
The case:
MS Sql Server 6.5
A Database has expanded and use of 2 data devices and 2 log devices.
(Data1.dat and Log1.dat located on drive c:
Data2.dat and Log2.dat located on drive d:)
The backup that was taken was only a filebackup of the
devicefiles on drive c:
So, I have a database with the devicefiles out of sync.
I have tried several approaches, but the server always tries to make a recovery and marks the database as suspect.
I have used the sp_resetstatus procedure and tried to start the database with traceflag 3608
Sqlservr –c –m –T3608
Every time I try to use any traceflag, I just get the error 615 and it says it can not find database table id 2, name tempdb.
Do anyone know why Sqlserver do not start with the traceflag?
Is their any option I have overlooked?
View 1 Replies
View Related
Sep 29, 2005
Hi Folk,
I am bit new in Database administration. We have a MS SQL 2000 Server. This have database of huge sizes. till yesterday everything was okay. Today I saw in the Enterprise manager that one of the Huge databse is grayed out and withing bracket (Suspect ) is written jaust after the database name.
When I tried to expand the database to see the tables. It did not show anything. (No Items).
I dont know what has gone wrong with the database.
Is ther any way to bring this database back to normal.
Please suggest.
Vijay
viju_vijay@hotmail.com, viprasad@in.ibm.com
View 2 Replies
View Related
Jan 22, 2004
Hi
I got the following error
Error: 823, Severity: 24, State: 4
I/O error 33(The process cannot access the file because another process has locked a portion of the file.) detected during write at offset
0x0000000a796000 in file xxxxxxxxx.ndf'.
and the respective database could not be brought online - this was just due to a problem with a .ndf file containing only indexes...is there any way to connect to/alter a database while it is in this transitional state? (it would be no loss if i could just remove the file & its filegroup)
(i tried starting with -f -c, but no go)
thanks in advance
des
View 3 Replies
View Related
Feb 9, 2004
I moved a database file (.mdf) from the Microsoft SQL Server data directory to another location, and moved it back into the original location. After which the database staus changed to suspect.
How do I make it live? Thanks
View 1 Replies
View Related
