Crashed db recovery

Hello,

i've got MS Windows Server 2003 OS (RAID 10) and MS SQL 2005, however server crashed with "blue death" screen. After forced reset, SQL server marked my db as a SUSPECT. Tried DBCC CHECKDB with no luck, what can i do to bring it back to life, please? (have no backup).

Msg 926, Level 14, State 1, Line 1
Database 'IRIX' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:182; actual 32:7602287). It occurred during a read of page (1:182) in database ID 5 at offset 0x0000000016c000 in file 'D:\MSSQL\IRIX.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.
Msg 3313, Level 21, State 2, Line 1
During redoing of a logged operation in database 'IRIX', an error occurred at log record ID (844:9770:2). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
Msg 3414, Level 21, State 1, Line 1
An error occurred during recovery, preventing the database 'IRIX' (database ID 5) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.

SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:182; actual 32:7602287). It occurred during a read of page (1:182) in database ID 5 at offset 0x0000000016c000 in file 'D:\MSSQL\IRIX.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.
During redoing of a logged operation in database 'IRIX', an error occurred at log record ID (844:9770:2). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
An error occurred during recovery, preventing the database 'IRIX' (database ID 5) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.
Cannot drop the table '#tmp_sp_db_vardecimal_storage_format', because it does not exist or you do not have permission. (Microsoft SQL Server, Error: 824)

you make use of sp_resetstatus sp....just execute it and you need to restart sql services i believe...if its not working you can try,

just bring the db into emergency mode and you can extract the data alone to another db....
alter database DBNAME set emergency

and export the datas using ssis

|||

Oh dear, it's so easy... i've reset the status, bring it to emergency and exported the data...ALL! Amazing, thank you for your kind help.

crash recovery (huge rollback) going on over 24 hours! How can I stop it?

Crash recovey is going on over 24 hours!

I want to stop it!

One of the databases in our server is in recovery mode after a huge

delete job (started as a job) did not complete ( I suspect due to

lack of transaction log sapce.) the log file size is 120 GB.

Whenever the server restarted the recovery for this database starts.

After 20 hours, analysis done 100% and recovery done up to

76%. Then after “the percentage of recovery done” went down to

16% and it is still running.

How can I get rid of this database without damaging the master database and other database in the same instance?

I don’t mind to lose this database.

There are 4 user databases in this instance and msdb and the recovery mode database are not operational.(I cannot connect)

Your best option is to let recovery complete. Anything else you try to do is not supported and could end up causing you bigger problems.

I also recommend batching up your delete job in some way to avoid having a single, huge transaction that could cause the log file to run out of space.

Thanks

Crash Recovery

I am trying to restore a crashed server. The server has an SQL2K
installation with a small database (say) DATA. The file DATA.MDF has gone
missing during the crash. The file DATA.LDF has been recovered. There is
also a backup file DATA.BAK. This backup is about 4 MB and I suspect it was
used to install the database in the first place, as it is several months
old. The log file LDF has been recently updated and is 47 Mb.
Is there any way to recover this database to a reasonable up to date version
using DATA.BAK and DATA.LDF. All suggestions are welcome?
BobHi,
Since the MDF file is not avaible you will not able be use the LDF.
But using DATA.BAK file you can recover the database back to the time in
whcih backup is created.
How to restore the BAK file.
From Query analyzer execute the below command
1. Copy the DATA.BAK to c:\backup folder
Restore database <dbname> from disk='c:\backup\data.bak'
You may need to use WITH Move option along with restore statement.
Thanks
Hari
MCDBA
"Bob Morris" <bmorris@.kenyaonline.com> wrote in message
news:#fVLgrcBEHA.3348@.TK2MSFTNGP11.phx.gbl...
> I am trying to restore a crashed server. The server has an SQL2K
> installation with a small database (say) DATA. The file DATA.MDF has gone
> missing during the crash. The file DATA.LDF has been recovered. There is
> also a backup file DATA.BAK. This backup is about 4 MB and I suspect it
was
> used to install the database in the first place, as it is several months
> old. The log file LDF has been recently updated and is 47 Mb.
> Is there any way to recover this database to a reasonable up to date
version
> using DATA.BAK and DATA.LDF. All suggestions are welcome?
> Bob
>
>|||Thanks,
But I need to know if there's any way to apply the LDF. The MDB file was
probably created on this machine from the BAK file. (i.e. the database was
created elsewhere and "restored" to this machine with the force restore
option). The LDF will reflect changes since that was done, and that's what I
want to try and do.
Regards
Bob
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:eAF4v9cBEHA.3568@.tk2msftngp13.phx.gbl...
> Hi,
> Since the MDF file is not avaible you will not able be use the LDF.
> But using DATA.BAK file you can recover the database back to the time in
> whcih backup is created.
> How to restore the BAK file.
> From Query analyzer execute the below command
> 1. Copy the DATA.BAK to c:\backup folder
> Restore database <dbname> from disk='c:\backup\data.bak'
> You may need to use WITH Move option along with restore statement.
> Thanks
> Hari
> MCDBA
>
>
>
> "Bob Morris" <bmorris@.kenyaonline.com> wrote in message
> news:#fVLgrcBEHA.3348@.TK2MSFTNGP11.phx.gbl...
gone
> was
> version
>|||Thanks, but what I need is to try and apply the LDF after I restore the
backup.
Regards
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:eAF4v9cBEHA.3568@.tk2msftngp13.phx.gbl...
> Hi,
> Since the MDF file is not avaible you will not able be use the LDF.
> But using DATA.BAK file you can recover the database back to the time in
> whcih backup is created.
> How to restore the BAK file.
> From Query analyzer execute the below command
> 1. Copy the DATA.BAK to c:\backup folder
> Restore database <dbname> from disk='c:\backup\data.bak'
> You may need to use WITH Move option along with restore statement.
> Thanks
> Hari
> MCDBA
>
>
>
> "Bob Morris" <bmorris@.kenyaonline.com> wrote in message
> news:#fVLgrcBEHA.3348@.TK2MSFTNGP11.phx.gbl...
gone
> was
> version
>|||You can't apply an LDF file to a restored database backup. If you're lucky,
you might be able to dig something out of the LDF file using some log reader
program. See my signature, the links.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Bob Morris" <bmorris@.kenyaonline.com> wrote in message
news:eiZLHtfBEHA.2768@.tk2msftngp13.phx.gbl...
> Thanks, but what I need is to try and apply the LDF after I restore the
> backup.
> Regards
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:eAF4v9cBEHA.3568@.tk2msftngp13.phx.gbl...
> gone
is
it
months
>