I have a db (with replication enabled) which was marked as suspect. As per the logs it is the Hardware crash that caused this db to become suspect.
Tried with sp_resetstatus to recover the db. But it is still in suspect mode. Changed the db into emercengy mode to recover the data thru' bcp which was succesful. Since there is no other way to recover the db, now I want to drop the db and create / restore the db from backup.
I am unable to drop the db becasue it is having a publication. I am also unable to drop publication becasue now the db is in emergency mode.
Kindly help and inform how I can drop the db and create it again from the backup without reinstalling SQL Server again.
Hi, I have more than 80 databases on my publisher (SQL Server 2000 SP4), I tried to enable Transaction Replication on all of those databases instantly through some T-SQL programming and DTS Packages. Every thing works fine until the snapshot agents starts to take sanpshot from the publisher databases. As soon as their snapshot agents start for those 80 databases, they start giving the deadlock error. All 80 snapshot agents starts at the same time.
Error Message: Transaction (Process ID xxx) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Error Detail: Transaction (Process ID xxx) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. (Source: Server_Distribution (Data source); Error number: 1205) ---------------------------------------------------------------------------------------------------------------
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
We had transactional replication set up by a consultant with remote distributer, push subscription to the publisher (main production database). However, the subscriber machine had fatal crash and is unrecoverable. Now, replication jobs have been failing on distributor and we immediately need to disable and remove replication as it has started causing problems with transaction log backups on production database.
How can I completely remove this whole replication from publisher and distributor?
Does anyone know how to remove 'Replication Triggers'?
I have a database created by export of data object. This process does not export any system tables to do with replication but there still seems to be something in the database which references them. There is no poblem if I backup and restore the dataabse.
Several years ago I set up Merge Replication on a SQL 2000 database and an MSDE Client on a Laptop. The Laptop frequently travelled out of the country and would "synch" up when it came back in.
Well the Laptop received some Viruses that we cannot get to remove so putting it on the network is not possible. Also, the owner of the laptop received a new one. He wants the database set up on the new laptop but it is not a priority at the moment and we plan to wait till the "Yemen" project is finished.
In the mean time, I want to remove the old laptop out of the Merge Replication scenario. We are not concerned about losing the data in the database on the laptop.
Does anyone know the steps I need to perform to "unreplicate" my database without being able to connect them?
SQL Server 2005 Standard replicating to an identical server.
My issue:
When we go into the database to remove a large number of rows, it says we are unable to delete since the database is in replication. Is there a way to pause, or stop replication temporarily? I stopped the log reader but it still gave me the same error.
The only way I have found around this is to delete the publication and re-create it when I'm ready. Now though, I'm unable to delete the publication. SQL Server Management Studio will freeze if I either try to delete something from the UI or command line. This happens with objects in SQL Server as well as rows and such. It will just freeze and I have to kill the process.
I'm trying to write a create database script for a test database and I need to check if the database exists before dropping and recreating it. The problem I'm having is that the test database is also merge replicating so if the db exists I need to remove the replication and then drop the database. I'm running the script using osql from a NAnt build script. The script goes something like this:
USE master GO if exists (select * from sysdatabases where name = N'@INITIAL_CATALOG@') BEGIN USE [N'@INITIAL_CATALOG@'] EXEC sp_dropmergepublication @publication = N'@PUBLICATION@' USE master EXEC sp_replicationdboption @dbname = N'@INITIAL_CATALOG@', @optname = N'merge publish', @value = N'false' DROP DATABASE @INITIAL_CATALOG@ END GO CREATE DATABASE ...
Where the NAnt script replaces the @PARAMETER@ fields before running the script. Running the script using osql when the database doesn't exists appears to fail on parsing the line "USE [N'@INITIAL_CATALOG@']" with the error "Could not locate entry in sysdatabases for database '[DatabaseName]' ..." and does not create the database. If I run the same script in SQL Management Studio I get the same error but it does create the database.
The script should only execute the USE [N'@INITIAL_CATALOG@'] statement if the database exists, but the parser isn't able to see this and returns an error. If anyone can see an error in this script syntax, or see a different way of doing the same function without getting errors I would greatly appreciate it.
Hi, i restored a backup from a database thas has replication configured.When i restored it the system tables that the merge replication createsare restored too. I was investigating on internet and I found that i candelete it using this query:sp_configure 'allow updates', 1goreconfigure with overridegoDROP TABLE aonflict_SiacDataEEC_security_info...sp_configure 'allow updates', 0goreconfigure with overridegoDo somebody know if i use this queries to delete this tables i candamage the database or is correct to use it.Thanks a lot for your help.*** Sent via Developersdex http://www.developersdex.com ***
Hi guys does anyone know of a quick solution to remove the replication monitor icon within Enterprise Manager please I have tried all sorts but it is still appearing and starting to irritate me as I don't use replication on this server. cheers
I'm trying to remove replication from my database so that I can run some update scripts on it but SQL Enterprise Manager just keeps hanging on me. I've searched the web an no-one seems to have had this problem.
Does anyone know of this problem or have they experienced it? If so, can you help?
Am I doing something wrong trying to remove the replication (deleting the publication)?
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?
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.
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
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.
