steps to recovery suspect database
Log showed a 614 error and probably is a result of a hardware 0/s problem or ?. The database was not
reloaded before the error.
I was recently was called in to help some people who had a SQL Server database marked as suspect.
I have never had to deal with this situation before. I got the database operational by doing the
following steps. Was there an easier or better way?
Executed statement per BOL procedure to allow updates to system tables.
Removed suspect flag per BOL (BOL says to use sp_resetstatus which I did not find in master or msdb,
however the BOL had the code listed so I typed in the pertinent statement). I do not know what the '^ 256
meant in BOL, I used the status code that the nonsuspect databases had '16400'.
SQL Server would not let me restore into a suspect database so I reset the suspect flag. Could have dropped
the database, however I did not have the information to recreate the database so the restore function would
work.
Put database in single user mode.
restored database.
Removed the ability to update system tables per BOL
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
One of my database (name XYZ) shows suspect status in EM but when i try to dig further by running below query i get only "OK" ( see query)
SELECT [name],status, case status when (status & 32) then 'Loading' when (status & 32) then 'Loading' when (status & 64) then 'Pre Recovery' when (status & 128) then 'Recovering' when (status & 256) then 'Not recoverd' when (status & 512) then ' Offline' when (status & 1024) then ' Single user' when (status & 1024) then ' Read only' when (status & 32768) then ' Emergency Mode' else 'OK' end as status FROM master.dbo.sysdatabases WHERE [name] NOT IN ('distribution', 'tempdb', 'model','Pubs','Northwind')
also i run this
select * from sysdatabases where databaseproperty(name,N'IsSuspect') <> 1
and here also i get all the database including "XYZ"...i guess if "XYZ" is suspect the resultset should not be including "XYZ"
Why if the EM shows suspect status FOR "XYZ" DATABASE it should come up when i check status column in sysdatabases table?
We have a requirement to build SQL environment which will give us local high availability and disaster recovery to second site. We have two sites- Site A & Site B. We are planning to have two nodes at Site A and 2 nodes at Site B. All four nodes will be part of same Windows failover cluster. We will build two SQL Cluster, InstanceA will be clustered between the nodes at Site A Server and InstanceB will be clustered between the nodes at Site B, we will enable Always On Between the InstanceA and InstanceB and will be primary owner where data will be written on InstanceA and will be replicated to InstaceB. URL....Now we want we will have instanceC on the Site B and data will be writen from the application available on Site B, will be replicated to the instance on the Site A as replica.
Pages on a full recovery model database corrupted, need to ensure data loss is minimal for restore operation am thinking about restoring the latest full backup.
in the process of migrating a big db from server 1 to server 2, we had to roll back the change. I started with taking a full db backup and restoring it on server 2 with norecovery, and then a couple logs with norecovery, and then the last log with recovery.
Is there some way to continue this chain now, I mean to change the db to norecovery, or other way to restore logs.
I dont want to do a new full backup.
If I try to do a log restore now i get the message:
Msg 3117, Level 16, State 4, Line 1
The log or differential backup cannot be restored because no files are ready to rollforward.
We have 3 replica AG setup. 2 replicas are in sync/automatic failover, the other(DR Server, different subnet) in asynchronous/manual mode…All these replicas were on sql server 2012, Recently we upgraded DR server to 2014. Since then we have a problem, the AG databases in 2014 instance went into ‘Synchronizing/ in recovery’ state…The SQL server error log has message, the recovery couldn’t start for the database ‘XYZ’…We tried to create a new database and add it to AG , it works for fine for other two 2012 replicas, but on 2014 we see the same issue
We have an issue with a 3 node SQL 2012 Always on availability group. Normal operation is node 1 (primary replica) with node 2 and node 3 as secondary replicas.After some patching, SQL wasn't running on node 1 hence the AG flipped over to node 2. This went unnoticed for some time and the transaction log for one of the AG databases became full on node 2 and node 3. (I think this is because it couldn't commit the transactions on node 1 so couldn't truncate it's t-log?) The DB is using synchronous replication btw.So I started SQL on node 1 and flipped the AG back to node 1 (with a data loss warning but I accepted this).Now the issue is that on node 2 and 3, the DB in question is stuck in a "Reverting / In Recovery" State. I've tried various commands such as ALTER DATABASE SET ONLINE, RESTORE DATABASE WITH RECOVERY etc but these fail stating unable to obtain a lock on the DB.
The weird thing is that on node 1 the state of the DB is "synchronised".how to resolve this issue on node 2 and 3? I've left them overnight (in case they were rolling back transactions, the DB is fairly large) but nothing seems to have happened. remove the DB from the AG in node 2 and 3 and add it back in again, ie recreate the replication?
If you are doing a disaster recovery of an entire SQL 2005 cluster, can you just install SQL server and restore the system database to get the configuration?
Hi all, What does this mean? SUSPECT, in SQL server 7? I dont remember what caused one of my database to be SUSPECTed. Can someone let me informed what causes a database to be SUSPECT and what are its implications and how to make the SUSPECTed database work
I had a database (give it a name A) that was marked suspect. I ran the following query
use master go sp_resetstatus <db_name>
I then stopped and restarted SQL Server. This fixed the problem for database A but I got two more that were suspect. After running the same query for both B & C, C got fixed but I still cannot fix B. My question is why did fixing problem A cause a problem for DB B & C?
I tried running the following query on B after the first one failed.
Update sysdatabases set status = -32768 where name='B'
This gave me the following error: Ad hoc updates to system catalogs are not enabled. The system administrator must reconfigure SQL Server to allow this.
Hi all, I have had problems lately with my standby server,when i am trying to do a load from my production it goes into suspect mode .the load is just fine as per my error log after 5/10 minutes it goes into suspect mode the error is "Error 3305,Severity 21,State 1" "Page 559396 in databse "xyz' read in during runtime or load xact was uninitilised" "Idxact:Dbid:6 is in IN_LOAD state with the following exception raised:major 43,minor 3,pstat:0X400"
Now i have checkd error 3305 everywhere but no answers..please help
I have a db in suspect. I want to try to run sp_reset_status and reset the status bit. I believe there is a script that I can run to create sp_reset_status. Where can I find that?
Every once in a while one the DB on SQL Server 2000 goes suspect. I was thinking it was because there was no back up plan. The problem is usually resolved after a simple start and stop of the SQL Server Agents. Anyone have similar situations that could shed some light?
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
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.
I tried "sp_resetstatus" procedure. But it says, the following mesg, " Prior to updating sysdatabases entry for database 'DELPHI3', mode = 0 and status = 1077936141 (status suspect_bit = 0). No row in sysdatabases was updated because mode and status are already correctly reset. No error and no changes made. ".
Can anybody please help me how to make this into normal status.
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
Today we found a suspect database and after consulting this website went and ran sp_resetstatus on one database with success. After which we stopped and started the server and ran dbcc newalloc, dbcc textall, dbcc checkdb on that database. It successfully changed from its suspect status and random queries showed that the data was still intact.
Exactly the same process was run on another database and it failed to move from the suspect status after restarting the server. We then went through books online, which advised to update sysdatabases setting the database's status to 0. This also failed.
HELP PLEASE.
Does anyone know what else to try? This was a development database with no backups. This would save a weeks work. Thanks for you time.
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..
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?
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.
I'm running Win2000/SQL2000 on a Compaq CL380 Cluster Server. After a powerdown to do a physical move, the MSDB is now marked SUSPECT. I have tried the stored procedure SP_RESETSTATUS, a DBCC consistency check, and also have restored the database from backup. The database still shows MSDB is suspect. What can I do? We can't get a good backup as long as it is still this way as it depends on the SQL Agent.
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.
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.
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.???
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?
