Hi all,
I have a big problem with a Database on SQL-Server7.0 running on windows 2000 advanced server. The msdb database is marked suspect. I have tried a few suggestions in the SQL BOL but no luck.
We have a new installation on Friday which was running fine. Unfortunately today, the msdb is marked suspect. Do I uninstall and reload SQL server or change the status. We only have a training data on the server presently. Does anybody has any other suggestions? Please help. Thanks for your assistance.
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.
Usman Masood writes "we were using our database named db1. when suddenly it stopped working and upon checking it gave an error that db1 and db2 are marked as suspected. while we were only using db1,,,,what is the cause of this error?"
DBCC CHECKDB (A2PD,REPAIR_ALLOW_DATA_LOSS) The above command was not executed as there was some long rollback was going from a user...
I stopped and re started the MSSQLSERVR service...
Then i found the follwing error..
how can i work with the database A2PD marked as suspect
Server: Msg 926, Level 14, State 1, Line 1 Database 'A2PD' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.
Hi,I restored my Windows XP system and discovered that I cannot access aMSDE DB that was saved in D: drive (I restored my primary partition inC). How can I restore it?Thanks a lot.
Hi there, I need urgent help regarding a database that has been marked as suspect. I need to get the data from the database!!
I have a copy of the structure of the database but I need to get cerain very recent data from the database. I have tried bcp and it doesn't work. The backup that I have won't sufice
If a database is marked as suspect, Will it recover?> And if so how long will it take. If it won't recover is there any other way of getting the data, Thanks in advance, Fin
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.
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.
Dear Experts, in one of our servers, the MSDB database is in suspect mode, and i've used the command sp_resetstatus and i restarted the service also but still it is in suspect mode.
how can i change that to normal state? it is asking to check the errorlog.
Vinod Even you learn 1%, Learn it with 100% confidence.
My msdb database is marked as suspect. This means that my maintenance plandoesn't work. I'm a novice when it comes to sql server so now I'm wonderingif anyone could give a hint on how to fix the suspect msdb database? Doesthis database contain any vital information? What could be the reason thatthis database become suspect?Appreciate any suggestions in this matter.Thanks!--mvhHallgeir
I have mssql 2005 installed and i see that MSDB database is marked as suspect. I almost tried all the steps which are submitted in the forums.
1) I shut down the server, navigating to the directory 'd:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinn' and doing the following: start sqlservr.exe -c -T3608 2) renamed the damaged msdb files (msdbdata.mdf and msdblog.ldf in the 'd:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData' directory) 3) Run the instmsdb.sql script in the 'd:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLInstall' directory
--x--- When I executed third step it returned me below error: Msg 926, Level 14, State 1, Line 63 Database 'msdb' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.
A client improperly shutdown a PC running MSDE and it corrupted the MSDB database which contained several DTS packages. I have tried fixing the problem on my own but have exhausted all the simple options like restarting SQL Server.
I googled the problem and found the following SQL script:
USE MasterGO-- Determine the original database statusSELECT [Name], DBID, StatusFROM master.dbo.sysdatabasesGO-- Enable system changessp_configure 'allow updates',1GORECONFIGURE WITH OVERRIDEGO-- Update the database statusUPDATE master.dbo.sysdatabasesSET Status = 24WHERE [Name] = 'msdb'GO-- Disable system changessp_configure 'allow updates',0GORECONFIGURE WITH OVERRIDEGO-- Determine the final database statusSELECT [Name], DBID, StatusFROM master.dbo.sysdatabasesGO I ran it and and it generated the log file below. I restarted MS SQL but the database was still marked as suspect (as I would expect from the log file).
Any help in interpreting and recovering from this problem would be greatly appreciated.
Mu MSDB database is in suspect mode. Anyone have any info to help get it out. It tells me that it cannot find the path specified for the backups, yet the path exists and there is more than enough disk space even though it says that there is not? I am confused. First time getting this error and doesn't make sense why.
Dear Experts, I'm using sql server 2005, and now i'm trying to restore with one backup. then i'm getting error saying that MSDB database in suspect mode.
how to make the DB to the normal mode.... please help me in this regard
Vinod Even you learn 1%, Learn it with 100% confidence.
The problem is, that this user database only has one data file and file_id 2 is the log file.I understand that the event_type shows checksum and torn page error but in the log file? DBCC CHECKDB shows no errors and the database is part of an AOAG in synchronized state.We have log backups in every 15 minutes.
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?
Hi! maybe this question is very common, but i can resolve that. I have a DB marked 'loading', and nothing seems to change this state. Is any way to revert to a normal state, or the only way is destroy and re-create? Thanks in advance
I want to know if the System.Xml.XmlTextReader is marked with Host Protection Attributes(HPA) in the SQL Server 2005 CLR. I ask this because when I add a custom class that inherits from System.Xml.XmlTextReader, I get a code access security error at runtime. I am able to register the assembly just fine. I have tested it with all the cases and pretty much sure my code breaks in this class.
My code snippet is shown below:
public class CustomXmlTextReader : XmlTextReader { ...... ...... }
Doesn't really matter what is in this class. It gives an expection even when there is no code inside.
I canceled a restore job, and now the database is marked (Loading). When I go to propertied it tells me "database can't be opened, it's in a middle of a restore" Yet from a Query window I can connect and run queries against that database. Should I just wait until it finishes "Loading" or should I re-start the server? Thanks
I have a 3GB DB that I was running an update script on. The sript failed and ran out of transaction log space. I expanded it and stopped and restarted SQL. The user DB is now marked as recovering. How long should I wait for it to recover? I don't want to have to restore again and start all over with my script. Please help.
using the following syntax in SQL 2005 (win 2003) to restore a log to a marked transaction. Code runs ok but I'm never getting back to the point in time where id 13 still exists , help !
kind regards
Simon
/*backup and overwrite */ BACKUP DATABASE AdventureWorks TO DISK = N'C:SQL Backupsadw.bak' WITH FORMAT, INIT, NAME = N'AdventureWorks-Full Database Backup' GO
/* verify data */ restore verifyonly from disk = 'C:SQL Backupsadw.bak' with file = 1 go
-- with MARK to place a marker in the tranny log BEGIN TRANSACTION CandidateDelete13a WITH MARK N'Deleting a Job Candidate'; GO USE AdventureWorks; GO DELETE FROM AdventureWorks.HumanResources.JobCandidate WHERE JobCandidateID = 13; GO COMMIT TRANSACTION CandidateDelete13a; GO
BACKUP LOG AdventureWorks TO DISK = N'C:SQL Backupsadw_log.bak' with format, NAME = N'AdventureWorks-Trans Log Backup' GO
/* RESTORE DATABASE AdventureWorks FROM DISK = N'C:SQL Backupsadw.bak' WITH FILE = 1, RECOVERY, NOUNLOAD, REPLACE,STATS = 10 GO */
use master RESTORE DATABASE AdventureWorks FROM DISK = N'C:SQL Backupsadw.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE,STATS = 10 GO
use master RESTORE LOG AdventureWorks FROM DISK = N'C:SQL Backupsadw_log.bak' WITH recovery, STOPatMARK = 'CandidateDelete13a' GO"
We've got a Sharepoint 3.0 SQL database that was partially corrupted during an ill executed BackupExec restore. Now the database will not start up.
Error log reports:
2008-05-07 13:01:50.85 spid8s Starting up database 'model'. 2008-05-07 13:01:50.89 spid8s The database 'model' is marked RESTORING and is in a state that does not allow recovery to be run. 2008-05-07 13:01:50.89 spid5s The database 'msdb' is marked RESTORING and is in a state that does not allow recovery to be run. 2008-05-07 13:01:50.89 spid8s Error: 927, Severity: 14, State: 2. 2008-05-07 13:01:50.89 spid8s Database 'model' cannot be opened. It is in the middle of a restore. 2008-05-07 13:01:50.89 spid8s Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized. 2008-05-07 13:01:50.89 spid8s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
I'm looking for suggestions on how to get the database back up and running with minimal SQL Server knowledge. If anyone has a quick and dirty how-to that would be appreciated of course any detailed documents specific to this configuration would also be greatly appreciated.
i have a scheduled job which shrinks the database every day once at 12 am
today the job got failed
In the view Job history its showing the below contents ------------------------------------------------------- Database 'ecatalog' is already open and can only have one user at a time. [SQLSTATE 42000] (Error 924) DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528). The step failed.
And in Application Log of event viewer its showing the below contents ------------------------------------------------------------------------------ The description for Event ID ( 208 ) in Source ( SQLServerAgent$ABCSQL ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. The following information is part of the event: Shrink Database - ecatalog, 0xA0C4F8157A744244A61A4ECABE8C6056, Failed, 4/19/2004 12:00:03 AM, The job failed. The Job was invoked by Schedule 27 (Shrink Database - ecatalog). The last step to run was step 1 (Shrink Database - ecatalog)..
I ran the job manually it worked fine
but now when i go and see the database ecatalog in my Enterprise Manager it is showing ecatalog(Single User) What is the meaning of this, will this make any problem to my database
I read in Microsoft SQL Server 2005 Integration Services by Kirk Haselden that the BULK INSERT task was provided for backward compatibility and its use is disrecommended.
But after looking on the web I cannot find information supporting this.
Do you think the BULK INSERT task should be used for new development?
After I restore a database with SQL the database I cannot access it. However, after restoring using Enterprise Manager I do not have the same problem. The message I get is:
Database 'les_test' cannot be opened. It is in the middle of a restore.
The code I am trying to use for the restore is:
RESTORE DATABASE les_test FROM DISK = 'c:MSSQL7BACKUPiztrobeta_db_200012011201.BAK' WITH NORECOVERY, REPLACE, MOVE 'biztrobeta_PRI' TO 'c:mssql7DATAiztrobeta_PRI.ndf', MOVE 'biztrobeta_FGE_Dat1' TO 'c:mssql7DATAiztrobeta_FGE_Dat1.ndf', MOVE 'biztrobeta_FGX_Idx1 ' TO 'c:mssql7DATAiztrobeta_FGX_Idx1.ndf', MOVE 'biztrobeta_LOG' TO 'c:mssql7Dataiztrobeta_LOG.ldf' GO
I tried changing the status in the master..sysdatabases tables, but the restored database was still not accessible. I tried to do this using the following statment:
UPDATE sysdatabases SET STATUS = 4194316 WHERE name = 'les_test' GO