Recovering DBs With A Suspect State
Aug 28, 2007Hello
(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.
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.
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
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?
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.
We have mirroring setup for 5 dbs, 4 of which are synchronized and 1 which is in "synchronizing" state on the principal and "restoring" state on the mirror. Mirroring for all dbs has been working fine for the past several months and we have a witness that has allowed automatic failover in the past without problems.
This database has several bulk inserts performed throughout the day and am sure there is some latency due to the size of these transactions.
Not sure as to why this is happening all of a sudden, but the db in question has been in this state for the past 12 hrs. I checked the mirroring status on the principal and it states that it is "synchronizing: data is being transferred from principal to mirror", but the mirror server states that db is in "restoring" state. Can anyone suggest as to how I can get the database on the mirroring server to get back to "mirror, synchronizing/restoring..." state? Or suggest on how I can troubleshoot this?
Thanks in advance.
Can anyone give me assistance on trying to restore my master file. I purposely corrupted my dabase on my test box so that I could work on my recovery process. However, during one of the steps I used the:
restore database Master From
disk= 'c:mssql7ackupmaster.bak'
Command. However, I continue to recieve:
Server:Msg3201, Level 16, State 2, Line 1
'Cannont open backup device 'c:mssql7ackupmaster.bak. Device error or device off-line. See the SQL Server error log for more details.
Note I am in single user mode and I have not been able to find anything specific in the error log file.
Can some tell me what possible could be the problem. Thanks.
Any ideas on how long it should take SQL Server 6.5 running on a 8 Gig RAID 0 array to "recover" a 4 GIG database. I rebooted the machine this morning (seldom ever have any problems with it) and it seems to be taking an awfully long time (15 min so far). I'll let you know what happens in any case.
View 2 Replies View Relatedis it possible to recover an existing SQL server? Here is what happened. WNT server 4.0 was corrupted, had no backup, repair did not work. consequently, i had to reformat the C drive and do a clean install of the OS.
my SQL files and user databases were on other drives. can i do a install of SQL server and tell is were the SQL server files (ie., the MSSQLDatamaster.db) are and have it use those files? given that this problem occurred as i tried to backup my SQL server database.
hope you can help or my butt is burned!
We are having major hardware replaced on our W2000 Adv Server and we
need to be prepared to restore the entire system.
If the hardware fix goes badly, worst case, the plan is to
rebuild the server and then REINSTALL SS7 and
then restore all the databases from tape backups.
My question is, which database(s) should we restore first?
Should we restore master and then proceed to restore the user
databases or restore the user databases first and restore master last?
Thank you in advance.
Help !!!!
How would you fix these two problems.
1. On the window of Enterprise Manger and under the databases, the database shows one of the databases like this manag_dat(recovering)rather than manag_dat. (manag_dat is the name of the database). What could cause the probem and how to fix it?
2. Same position as above, but instead it shows manag_dat (loading). I tried to delete its device and the database. it hang. How would it happen and how to fix it?
Thank you ahead of time
I need to recover a database, but unfortunatelly i donīt have any backup... Well, it was not my fault. Anyway, i only have the physical .DAT files of the data and log devices of the database.
Will it work if I create two devices with the same name as the old devices, create a database with the same name as the old database within these devices and then run a full script of my old database, then change the 'phy_name' on the sysdevices to point to where my old dats were, or replacing the current ones with the old ones? If not, will anything else work?
Thanks in advance.
My server crashed i had full back up my .BAK file for every database and i reinstalled a fresh verion of sql server 2000 enterprise and when i comme in enterprise manger to restor the master its telling me i need to be in single user mode what do i do.
View 2 Replies View RelatedAny ideas how I can recover a job that has been deleted (?) from SQL7.0 ? We had one which was used on a regular basis which has nowceased to exists. I've spoken to the people responsible for the serverand was met by blank faces (as usual). They have no idea when it wasdeleted, by whom or when. To make matters worse, they don't think theycan get it off the backup set without a major bit of work to theserver. Suprisingly, none of the users noticed that things had stoppedworking.Unfortunately, I needed to do some work on this in preparation for mymonthly meeting with my boss on Monday. Whilst I'm not to blame, I'dlike to understand a little more about any options available to us forrecovery as he will undoubtedly ask when he finds out.I have an old back up on another server, so it's not the end of theworld re-creating it, but I was under the impression it was backed upproperly. Is this normal or have they missed something ?Are there any ways I can recover this ? What can I get done to thebackup to make sure this is backed up properly ?Thanks in advance.Ryan
View 1 Replies View RelatedHi,
I am working on SQL Server Business Intelligence Development Studio - 2005. While working on the same, the studio got closed automatically and I was not able to save my existing file.
When I try to open the last saved file, it is thowing the following error:
Deserialization failed: The element 'QueryParameter' in namespace 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition' has incomplete content. List of possible elements expected: 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition:Value ##other:*'. Line 277, position 12.
EDIT CODE
Now I am not able to open the file. Please let me know how can I recover the lost file.
Regards
Syed
I saw the other thread that is related, but let me give you our circumstances. A drive failed and that drive had the SQL installation and the *.ldf files on it. The MDF files were unscathed.
The drive has been rebuilt and I have reinstalled SQL 2000 SP3A on there (that is what was there before). I have tried attaching the MDF through the GUI and letting it create the log for me. This failed.
I tried sp_attach_single_file_db, but this failed since the db was never dettached.
I tried creating the db again, dettaching it, copying the old (good) MDF file over the newly created one and then attaching (using multiple attach approaches). It knows that the log file and mdf are not matched and won't let me do the attach.
I also found DBCC REBUILD_LOG, but the article in SQL Mag supposes that your instance is still in working order and that there is an entry for the db in sysdatabases. Such is not true in my case.
Can you help? We are trying to get old backup tapes shipped to the site, looking up our Gold Partner contact info, etc., but in the meantime, I am trying to get the data restored even if we lose some transactions.
Thanks for any help!
1) How do I reinstall a fresh copy of the MSDB database (since my current
one is corrupt). Is there any script available?
2) What is the purpose of the MSDB database? Backups and jobs/ Wizards?
Thanks for your help.
Ziggy
Our database -SQL Server 7.0 sp1 (NT 4.0 sp5)- is growing at a very fast rate despite the fact that we are deleting old record. It doesn't seem to be recovering disk space for the deleted records. Please let me know if there is a specific setting that can help us recover disk space. )
View 1 Replies View RelatedI 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.
(By the way this is a restored DB)
Thanks.
LC
Hi,
Bit of a panic here.. Someone accidentally deleted a database device (and
consequentially the associated databases) from MS Sequel 6.5. I don't have a
backup dump of the databases.
Does anyone know how I restore the device? I have the DAT file and I can
see the data in it if I use a text editor.
Thanks
Jonathan
Is there any way to recreate SQL2k cluster recources after someone removed them, which is is quite easy to do in Clustermanager?
We are running testscenarios at the moment with SQL2000Adv on Win2kadv servers. We assume a situation where someone at a client site accidentaly removes one or more of the SQL cluster receources in clustermanager. We can easily re-create the recources manually, but they will not come online. Of course it is always possible to re-install SQL2k on the nodes, but after "accidentaly" removing recources even that seems to be a pain, as the sql2k installer will not uninstall properly or reinstall/overwrite. Usually we get both nodes back up and running after running the uninstaller, cleaning up the registry and reinstalling SQL2k with the previous virtual (sql)server name and all the previous settings.
There must be a more efficient and reliable way to recover from this type of "usererror", but neither technet nor any other site seems to mention anything on a situation like this.
Any thoughts?
Alexander
What is the most effecient way to recover a SQL 2000/2005 database to a different server.
View 1 Replies View RelatedIs there any way to get the deleted records from LDF or MDF file. Today by accidentally I executed one query and that dropped my table Inbox(table name only) from database. And in that table there was more 4,000 records, all these lost. So anybody can help me how to recover this table records?. Is there any way to get these?
I don't have any backup for this table.
shaji
Hi all
I'm a newbie with SQl Server But somewhere i read that SQL server Data can be Recovered through *.LDF files
now i want to know if i open a table in Enterprise-Manager and delete some records through DELETE statement or
manually, is it possible to recover those deleted records or not? if so how?
Thanks in advance.
Regards.
I was trying to relocate my transaction log to a bigger drive usingsp_movedevice but I made a mistake in the syntax of the second parameterand put only the path, not the path and the file name.Now my database is marked as "suspect" and I get an error message in my logupon database start up saying that the log file cannot be open.Is there a way to have MS SQL 6.5 "forget" all the logs of this database,create new ones and restart the database? The logs contained nothingimportant, I had truncated them an hour or so before I made my mistake. Ijust want to make sure the data are still usable.When I look at the devices with sp_helpdevice, I can see a log that existand is hopefully in pristine condition and the one that doesn't existanymore.I looked in the archives of various newsgroups but couldn't find somethingthat correspond closely to my situation. I saw something similar but withMS SQL 7.0(http://groups.google.com/groups?hl=...om %26rnum%3D4)using sp_attach_db/sp_detach_db. What would be the equivalent with version6.5?Thanks!Charles--Charles-E. Nadeau Ph.Dhttp://radio.weblogs.com/0111823/
View 8 Replies View RelatedI am in a big trouble ,
accidently i have issued a DROP DATABASE XXX command in the sql client, thinking its a local server....... The whole 4 months of database database is now dropped.
Please help me , how can i recover the database ....
thanks in advance
I accidentally deleted at SQL 2005 database. Is there any way I can get it back? No, there is no backup.
We occasionally use TruncateTable to delete the contents of a number of tables on our SQL Server 7 database. We then build them anew from an outside source. I am finding that the size of the database (and corresponding backup file) has grown significantly. As I understand it, the space representing the deleted records is retained by the database. I have investigated using various DBCC commands, to no avail. Does anyone have a clue as to how I can recover this lost space? ... Thanking you in advance.
Mike Rosen - Amalgamated Bank
What should I have done? Is there anything that can be done other than restoring from backup?
How does one know if the database is really recovering or is EM just joken? I can wait 2 hours
before starting the restore
I was BCPing 12 million rows into a staging table. II used the '-b' option every 20K which I thought
would do a commit and clear the log in batches. After the process EM appeared to show the transaction log
as empty. Upon inspecting the Bcp output file I discovered the message that the BCP did not complete
because syslogs was full. I could not do a truncate transaction log or a dump database. I tried to
do a truncate transaction with no_log and it appeared to just hang. I stopped the SQL Server thinking
I could dump the transaction log, but could not start the Sql Server again. I then stopped the NT Server
because 'if all else fails'. The SQL Server started but the user database if marked as recovering.
Just upgraded a users computer from 7.0 to 2000. All previous SQL Server Registrations are gone IP Addresses, User Names and Passwords arent readily availiable. Are the old connections stored in a DLL or Ini that I can place in the SQL 2000 folder to regain those or do I need to recreate some DSNs?
Help
Matt
We currently have a company program in foxpro which inputs all of our information into its database. We are trying to shift from this to new updated software and a SQL database. There is a program we created which converts all the data from our old database into our new SQL database in preparation for our final move to the newer software. However, some of the tables in SQL were not taken directly from our old database but rather added in directly after our initial use of SQL, so when we ran our conversion software, a couple of specific tables were wiped because there was no equivilant within our old database. We already altered our conversion program to not touch those tables again, but is there any way to do a roll back or to find a backup of specific tables?
View 4 Replies View RelatedI'm having some deadlocking issues in my ASP.Net 2.0 web application with SQL Server 2000. One solution I've come across involves catching the the deadlock error (at the application level) and then retrying the query again, in the hopes that whatever was causing the deadlock is no longer locked.
I've tried to implement such a solution, where I catch the deadlock exception, then try to execute the same query again. Only now I'm getting a "The transaction is in doubt" exception.
So am I going about this wrong? Am I supposed to retry the entire transaction, or is valid to just try to redo the one query that it failed on? Is there some way around the transaction being in doubt?
I have a mirred database and have snapshot created from the mirroed database. I can do data selecting from the snapshot. But when the pricipal server is down , I can re-active the mirroed database.( I did succesfully upto this). Then I need to restore the database from snapshot and which failing with following error message.
Code Snippet
Msg 5123, Level 16, State 1, Line 3
CREATE FILE encountered operating system error 3(error not found) while attempting to open or create the physical file 'E:sql_datadevitst_mirrorlog st_mirror_log.ldf'.
Msg 5024, Level 16, State 2, Line 3
No entry found for the primary log file in sysfiles1. Could not rebuild the log.
Msg 5028, Level 16, State 2, Line 3
The system could not activate enough of the database to rebuild the log.
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.