Record Locks: March 2012

Friday, March 30, 2012

Hello everybody,
I'd like to get some help about Recovery model.
My scenario:
MS SQL Server 2K SP3
Backup with C.A. Brighstor Enterprise Backup
If i choose the simple recovery model this will impact me if i use a 3rd
Paty backup tool?
Thanks
Leandro Loureiro dos Santos
Vitoria/ES
BrazilDepends on what the 3:rd party backup tool does. Use simple recovery model
if you don't do transaction log backups. If you do transaction log backups,
use full recovery model.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Leandro Loureiro dos Santos" <leandro@.email.com> wrote in message
news:%232M0lqK8DHA.2540@.TK2MSFTNGP11.phx.gbl...
> Hello everybody,
> I'd like to get some help about Recovery model.
> My scenario:
> MS SQL Server 2K SP3
> Backup with C.A. Brighstor Enterprise Backup
> If i choose the simple recovery model this will impact me if i use a 3rd
> Paty backup tool?
> Thanks
> Leandro Loureiro dos Santos
> Vitoria/ES
> Brazil
>
>

recovery question

I have a full backup of a sql database on 4/27
I have a full backup of a sql datbase on 6/27
Is it possible to recover the data on 6/26 without seeing the changes on
6/27?On Tue, 24 Jul 2007 10:51:33 -0700, "nobody" <nobody@.nobody.com>
wrote:
>I have a full backup of a sql database on 4/27
>I have a full backup of a sql datbase on 6/27
>Is it possible to recover the data on 6/26 without seeing the changes on
>6/27?
If the database is in Full recovery mode, and you have all the log
backups from 4/27 through 6/26, then you can restore the 4/27 database
backup and apply all the log up to the point you want to stop
(point-in-time recover).
Otherwise, no.
Roy Harvey
Beacon Falls, CT|||In article <#JjvGthzHHA.5160@.TK2MSFTNGP05.phx.gbl>, nobody@.nobody.com
says...
> I have a full backup of a sql database on 4/27
> I have a full backup of a sql datbase on 6/27
> Is it possible to recover the data on 6/26 without seeing the changes on
> 6/27?
>
>
if you have full recovery turned on, and all the required LOG backups,
you can use "STOPAT" to do a point-in-time restore. If you have not run
a backup of the TLOG that should be the first thing you do.
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu|||I have no log backups. So No?
"Pete Berry" <PeteBerry@.Caltech.edu> wrote in message
news:MPG.210fe9254040420a98969a@.msnews.microsoft.com...
> In article <#JjvGthzHHA.5160@.TK2MSFTNGP05.phx.gbl>, nobody@.nobody.com
> says...
>> I have a full backup of a sql database on 4/27
>> I have a full backup of a sql datbase on 6/27
>> Is it possible to recover the data on 6/26 without seeing the changes on
>> 6/27?
>>
> if you have full recovery turned on, and all the required LOG backups,
> you can use "STOPAT" to do a point-in-time restore. If you have not run
> a backup of the TLOG that should be the first thing you do.
> --
> Graham (Pete) Berry
> PeteBerry@.Caltech.edu|||actually i found out its only a table within a database that needs to be
fixed or restored. how can this be done?
"nobody" <nobody@.nobody.com> wrote in message
news:u3qP2bjzHHA.5980@.TK2MSFTNGP04.phx.gbl...
>I have no log backups. So No?
> "Pete Berry" <PeteBerry@.Caltech.edu> wrote in message
> news:MPG.210fe9254040420a98969a@.msnews.microsoft.com...
>> In article <#JjvGthzHHA.5160@.TK2MSFTNGP05.phx.gbl>, nobody@.nobody.com
>> says...
>> I have a full backup of a sql database on 4/27
>> I have a full backup of a sql datbase on 6/27
>> Is it possible to recover the data on 6/26 without seeing the changes on
>> 6/27?
>>
>> if you have full recovery turned on, and all the required LOG backups,
>> you can use "STOPAT" to do a point-in-time restore. If you have not run
>> a backup of the TLOG that should be the first thing you do.
>> --
>> Graham (Pete) Berry
>> PeteBerry@.Caltech.edu
>|||In article <u3qP2bjzHHA.5980@.TK2MSFTNGP04.phx.gbl>, nobody@.nobody.com
says...
> I have no log backups. So No?
> "Pete Berry" <PeteBerry@.Caltech.edu> wrote in message
> news:MPG.210fe9254040420a98969a@.msnews.microsoft.com...
> > In article <#JjvGthzHHA.5160@.TK2MSFTNGP05.phx.gbl>, nobody@.nobody.com
> > says...
> >> I have a full backup of a sql database on 4/27
> >>
> >> I have a full backup of a sql datbase on 6/27
> >>
> >> Is it possible to recover the data on 6/26 without seeing the changes on
> >> 6/27?
> >>
> >>
> >>
> > if you have full recovery turned on, and all the required LOG backups,
> > you can use "STOPAT" to do a point-in-time restore. If you have not run
> > a backup of the TLOG that should be the first thing you do.
> > --
> > Graham (Pete) Berry
> > PeteBerry@.Caltech.edu
>
Again depends on the recovery model
simple => NO
full => yes if you do a backup of the tail of the log first
--
Graham (Pete) Berry
PeteBerry@.Caltech.edusql

Recovery question

Hello everybody,
I'd like to get some help about Recovery model.
My scenario:
MS SQL Server 2K SP3
Backup with C.A. Brighstor Enterprise Backup
If i choose the simple recovery model this will impact me if i use a 3rd
Paty backup tool?
Thanks
Leandro Loureiro dos Santos
Vitoria/ES
BrazilDepends on what the 3:rd party backup tool does. Use simple recovery model
if you don't do transaction log backups. If you do transaction log backups,
use full recovery model.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Leandro Loureiro dos Santos" <leandro@.email.com> wrote in message
news:%232M0lqK8DHA.2540@.TK2MSFTNGP11.phx.gbl...
> Hello everybody,
> I'd like to get some help about Recovery model.
> My scenario:
> MS SQL Server 2K SP3
> Backup with C.A. Brighstor Enterprise Backup
> If i choose the simple recovery model this will impact me if i use a 3rd
> Paty backup tool?
> Thanks
> Leandro Loureiro dos Santos
> Vitoria/ES
> Brazil
>
>

Recovery problem

In my production database i have lost data in three tables.so i just want to restore the data from my backup which was created.

I am connecting over remote desktop connetion to the server.

With in the databases i right click the database which has my three tables within them.

Then select all tasks->restore database

In restore i select files or file groups

then i select the day i want to restore the ok

It comes up with this error

Cannot open the back up device 'C:\programs\......'

device error or device online

Restore database is terminating abnormally

Is it because i am not doing at the server as i am connecting over remote desktop....

And my database has several tables apart from these three tables.is it possible for me just to restore these three tables.

i am using sql server 2000 database

Please let me know..

Are you using the GUI on the remote server or the GUI on your local machine? The GUI you use will need to be on the machine that the file resides if you are using localfile paths.

Also, you will only be able to restore those 3 tables using a PARTIAL restore if they exist in a separate filegroup to the rest of the data.If you only have one filegroup then you will have to restore the whole thing.

Either way, i think your only option is to do the restore to a separate database and then copy the data to your "live" db.

Check out the RESTORE information in Books Online as this gives a fairly thorough rundown of all the options.

HTH!

|||

Hi,

YOU CAN RETRIEVE THE BACKUP DATA FROM THE DELETED DATA..

I AM WORKING WITH SQL SERVER 2005.

JUST TRY ONCE AFTER THAT YOU CAN!!!!!!!!!!

Recovery Plan -

I want to make sure that what I'm thinking can actually be
done.
We currently have our production server with the datafiles
(including systems) on one removeable drive (E) and the
logs on another (F).We maintain a backup server that hold
all the data/log files on the D drive. Both server's have
the same server configurations.
The backup server is kept up to date via log shipping. If
we had disk failure, we would be able to bring the
databases out of standby mode and rename the server (with
a few other steps in there)...
HERE"S MY REAL QUESTION -
Now if we had a different failure where we had our disk
drives, but could no longer use the server, would the
following be possible.
Could we add the E and F drive to the backup server.
leaving the exsisting database in place. Modify the SQL
Server startup paramenters to use the master database on
the E drive instead of the D drive.
The master on the E drive would then point to all the
databases we have on the E and F drive and we would be
ready to go'
I hope that makes sense... It just seems a little too
easy...
Please advise and poke holes.
ThanksDefine removable drive here ... I'm not sure what you mean.
If you had a disk failure, why would you bother even
attempting an operation that may not work if your log
shipped secondary is brought online? I assume you'd want
to do some sort of attach, but that makes no sense. You
still have issues of DB users, etc.
You would also be changing the drive signatures, etc. I
wouldn't go there. And renaming everything (server, IP,
etc.) is always IMHO not the way to go. Your DR plans
should be able to tolerate the name change of the server
in a log shipping situation. Too many manual steps, too
many moving parts ... too many things that could go wrong.
Log shipping just works.|||By removable drives I mean external drives that can be
taken from one server and added to another. We do this
right now when we have large backups (150gig)that we need
to move to different servers. You haven't heard of this
before?
Since our log shipping server can be up to an hour behind,
if we have our production data we would prefer to use
that. The log shipping server is to be used only when we
cannot recovery the data from production.
Our current failover method does have attaching involved,
but I was thinking if my system databases are on the drive
we just moved over, why couldn't I point my SQL startup
paraments to the new master database, which in turn would
already have the databases/logins already setup.
>--Original Message--
>Define removable drive here ... I'm not sure what you
mean.
>If you had a disk failure, why would you bother even
>attempting an operation that may not work if your log
>shipped secondary is brought online? I assume you'd want
>to do some sort of attach, but that makes no sense. You
>still have issues of DB users, etc.
>You would also be changing the drive signatures, etc. I
>wouldn't go there. And renaming everything (server, IP,
>etc.) is always IMHO not the way to go. Your DR plans
>should be able to tolerate the name change of the server
>in a log shipping situation. Too many manual steps, too
>many moving parts ... too many things that could go wrong.
>Log shipping just works.
>
>.
>

Recovery phases

I have seen this message in Windows event log after restarting database.
3450:
Recovery of database 'IMAX_INV_Mgmt' (5) is 0% complete.
(approximately 2152 more seconds) (Phase 2 of 3)
Question is:
What are these three phases.
Thx,
MohI'm not entirely certain about all 3 but there are at least 2 steps in a
recovery (whether a restore or a DB startup) and they are the
roll-forward and the roll-back. The roll-forward is "redoing" committed
transactions (they may have been written to the transaction log, as this
always happens first, but not yet written to the data file(s)). If the
server is shutdown properly, then this phase will should be very short,
if not non-existent. The roll-back phase is "undoing" transactions that
had not been committed at the recovery point (so at the point the server
was shut down there may have been some transactions in progress that
hadn't been committed yet). These 2 phases will put the data in a
consistent state as at the recovery point.
I'm guessing the 3rd stage in the startup recovery would be bringing the
database "online" so it will accept user connections (in a RESTORE
process the very 1st phase (before roll-forward and then roll-back) is
the data copy phase but this wouldn't be necessary for a startup
recovery because the data would already be in the data files). There's
a fairly good page about it in BOL: Understanding How Restore and
Recovery of Backups Work
<http://msdn2.microsoft.com/en-us/li...8SQL.90%29.aspx>
With SQL 2000 the database is unusable until the end of the roll-back
phase. With SQL 2005 the database is now usable after the end of the
roll-forward phase. From memory they coined this "fast recovery" or
something like that (Database Engine Availability Enhancements
<http://msdn2.microsoft.com/en-us/li...8SQL.90%29.aspx> ).
Hope this helps.
*mike hodgson*
http://sqlnerd.blogspot.com
Moh wrote:

>I have seen this message in Windows event log after restarting database.
>3450:
>Recovery of database 'IMAX_INV_Mgmt' (5) is 0% complete.
>(approximately 2152 more seconds) (Phase 2 of 3)
>Question is:
>What are these three phases.
>Thx,
>Moh
>|||Just a note that in SQL 2005 its only Enterprise edition that allows the dat
abase to be accessed before the rol back phase is complete.
--
Simon Sabin
SQL Server MVP
http://sqljunkies.com/weblog/simons
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message news:OJ64mwqYGHA.1016@.
TK2MSFTNGP03.phx.gbl...
I'm not entirely certain about all 3 but there are at least 2 steps in a rec
overy (whether a restore or a DB startup) and they are the roll-forward and
the roll-back. The roll-forward is "redoing" committed transactions (they m
ay have been written to the transaction log, as this always happens first, b
ut not yet written to the data file(s)). If the server is shutdown properly
, then this phase will should be very short, if not non-existent. The roll-
back phase is "undoing" transactions that had not been committed at the reco
very point (so at the point the server was shut down there may have been som
e transactions in progress that hadn't been committed yet). These 2 phases
will put the data in a consistent state as at the recovery point.
I'm guessing the 3rd stage in the startup recovery would be bringing the dat
abase "online" so it will accept user connections (in a RESTORE process the
very 1st phase (before roll-forward and then roll-back) is the data copy pha
se but this wouldn't be necessary for a startup recovery because the data wo
uld already be in the data files). There's a fairly good page about it in B
OL: Understanding How Restore and Recovery of Backups Work
With SQL 2000 the database is unusable until the end of the roll-back phase.
With SQL 2005 the database is now usable after the end of the roll-forward
phase. From memory they coined this "fast recovery" or something like that
(Database Engine Availability Enhancements).
Hope this helps.
mike hodgson
http://sqlnerd.blogspot.com
Moh wrote:
I have seen this message in Windows event log after restarting database.
3450:
Recovery of database 'IMAX_INV_Mgmt' (5) is 0% complete.
(approximately 2152 more seconds) (Phase 2 of 3)
Question is:
What are these three phases.
Thx,
Moh

Recovery phases

I have seen this message in Windows event log after restarting database.
3450:
Recovery of database 'IMAX_INV_Mgmt' (5) is 0% complete.
(approximately 2152 more seconds) (Phase 2 of 3)
Question is:
What are these three phases.
Thx,
MohThis is a multi-part message in MIME format.
--080209040300000504020608
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
I'm not entirely certain about all 3 but there are at least 2 steps in a
recovery (whether a restore or a DB startup) and they are the
roll-forward and the roll-back. The roll-forward is "redoing" committed
transactions (they may have been written to the transaction log, as this
always happens first, but not yet written to the data file(s)). If the
server is shutdown properly, then this phase will should be very short,
if not non-existent. The roll-back phase is "undoing" transactions that
had not been committed at the recovery point (so at the point the server
was shut down there may have been some transactions in progress that
hadn't been committed yet). These 2 phases will put the data in a
consistent state as at the recovery point.
I'm guessing the 3rd stage in the startup recovery would be bringing the
database "online" so it will accept user connections (in a RESTORE
process the very 1st phase (before roll-forward and then roll-back) is
the data copy phase but this wouldn't be necessary for a startup
recovery because the data would already be in the data files). There's
a fairly good page about it in BOL: Understanding How Restore and
Recovery of Backups Work
<http://msdn2.microsoft.com/en-us/library/ms191455%28SQL.90%29.aspx>
With SQL 2000 the database is unusable until the end of the roll-back
phase. With SQL 2005 the database is now usable after the end of the
roll-forward phase. From memory they coined this "fast recovery" or
something like that (Database Engine Availability Enhancements
<http://msdn2.microsoft.com/en-us/library/ms171001%28SQL.90%29.aspx>).
Hope this helps.
--
*mike hodgson*
http://sqlnerd.blogspot.com
Moh wrote:
>I have seen this message in Windows event log after restarting database.
>3450:
>Recovery of database 'IMAX_INV_Mgmt' (5) is 0% complete.
>(approximately 2152 more seconds) (Phase 2 of 3)
>Question is:
>What are these three phases.
>Thx,
>Moh
>
--080209040300000504020608
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>I'm not entirely certain about all 3 but there are at least 2 steps
in a recovery (whether a restore or a DB startup) and they are the
roll-forward and the roll-back.Â The roll-forward is "redoing"
committed transactions (they may have been written to the transaction
log, as this always happens first, but not yet written to the data
file(s)).Â If the server is shutdown properly, then this phase will
should be very short, if not non-existent.Â The roll-back phase is
"undoing" transactions that had not been committed at the recovery
point (so at the point the server was shut down there may have been
some transactions in progress that hadn't been committed yet).Â These 2
phases will put the data in a consistent state as at the recovery point. 
 
I'm guessing the 3rd stage in the startup recovery would be bringing
the database "online" so it will accept user connections (in a RESTORE
process the very 1st phase (before roll-forward and then roll-back) is
the data copy phase but this wouldn't be necessary for a startup
recovery because the data would already be in the data files).Â There's
a fairly good page about it in BOL: <a
href="http://links.10026.com/?link=Understanding">http://msdn2.microsoft.com/en-us/library/ms191455%28SQL.90%29.aspx">Understanding
How Restore and Recovery of Backups Work</a> 
 
With SQL 2000 the database is unusable until the end of the roll-back
phase.Â With SQL 2005 the database is now usable after the end of the
roll-forward phase.Â From memory they coined this "fast recovery" or
something like that (<a
href="http://links.10026.com/?link=Database">http://msdn2.microsoft.com/en-us/library/ms171001%28SQL.90%29.aspx">Database
Engine Availability Enhancements</a>). 
 
Hope this helps. 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
<a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
Moh wrote:
<blockquote cite="mid8C953261-676E-41D5-80E7-4A2064EB9823@.microsoft.com"
type="cite">
<pre wrap="">I have seen this message in Windows event log after restarting database.
3450:
Recovery of database 'IMAX_INV_Mgmt' (5) is 0% complete.
(approximately 2152 more seconds) (Phase 2 of 3)
Question is:
What are these three phases.
Thx,
Moh
</pre>
</blockquote>
</body>
</html>
--080209040300000504020608--|||This is a multi-part message in MIME format.
--=_NextPart_000_00F1_01C662E8.12DAC620
Content-Type: text/plain;
charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Just a note that in SQL 2005 its only Enterprise edition that allows the =database to be accessed before the rol back phase is complete.
-- Simon Sabin
SQL Server MVP
http://sqljunkies.com/weblog/simons
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message =news:OJ64mwqYGHA.1016@.TK2MSFTNGP03.phx.gbl...
I'm not entirely certain about all 3 but there are at least 2 steps in =a recovery (whether a restore or a DB startup) and they are the =roll-forward and the roll-back. The roll-forward is "redoing" committed =transactions (they may have been written to the transaction log, as this =always happens first, but not yet written to the data file(s)). If the =server is shutdown properly, then this phase will should be very short, =if not non-existent. The roll-back phase is "undoing" transactions that =had not been committed at the recovery point (so at the point the server =was shut down there may have been some transactions in progress that =hadn't been committed yet). These 2 phases will put the data in a =consistent state as at the recovery point.
I'm guessing the 3rd stage in the startup recovery would be bringing =the database "online" so it will accept user connections (in a RESTORE =process the very 1st phase (before roll-forward and then roll-back) is =the data copy phase but this wouldn't be necessary for a startup =recovery because the data would already be in the data files). There's =a fairly good page about it in BOL: Understanding How Restore and =Recovery of Backups Work
With SQL 2000 the database is unusable until the end of the roll-back =phase. With SQL 2005 the database is now usable after the end of the =roll-forward phase. From memory they coined this "fast recovery" or =something like that (Database Engine Availability Enhancements).
Hope this helps.
--
mike hodgson
http://sqlnerd.blogspot.com=20
Moh wrote: I have seen this message in Windows event log after restarting database.
3450:
Recovery of database 'IMAX_INV_Mgmt' (5) is 0% complete.
(approximately 2152 more seconds) (Phase 2 of 3)
Question is:
What are these three phases.
Thx,
Moh
--=_NextPart_000_00F1_01C662E8.12DAC620
Content-Type: text/html;
charset="utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Just a note that in SQL 2005 its only =Enterprise edition that allows the database to be accessed before the rol back =phase is complete.
-- Simon SabinSQL Server MVPhttp://sqljunkies.com/weblog/simons">http://sqljunkies.com/weblog=/simons
"Mike Hodgson" wrote =in message news:OJ64mwqYGHA.1016=@.TK2MSFTNGP03.phx.gbl...I'm not entirely certain about all 3 but there are at least 2 steps in a =recovery (whether a restore or a DB startup) and they are the roll-forward and =the roll-back. The roll-forward is "redoing" committed transactions =(they may have been written to the transaction log, as this always happens =first, but not yet written to the data file(s)). If the server is =shutdown properly, then this phase will should be very short, if not non-existent. The roll-back phase is "undoing" transactions that =had not been committed at the recovery point (so at the point the server was =shut down there may have been some transactions in progress that hadn't been =committed yet). These 2 phases will put the data in a consistent state as =at the recovery point.I'm guessing the 3rd stage in the startup =recovery would be bringing the database "online" so it will accept user =connections (in a RESTORE process the very 1st phase (before roll-forward and then =roll-back) is the data copy phase but this wouldn't be necessary for a startup =recovery because the data would already be in the data files). There's a =fairly good page about it in BOL: Understanding How Restore and Recovery of Backups WorkWith SQL 2000 the =database is unusable until the end of the roll-back phase. With SQL 2005 =the database is now usable after the end of the roll-forward phase. =From memory they coined this "fast recovery" or something like that (Database Engine Availability Enhancements).Hope this =helps.
--mike =hodgsonhttp://sqlnerd.blogspot.com Moh wrote: I have seen this message in Windows event =log after restarting database.
3450:
Recovery of database 'IMAX_INV_Mgmt' (5) is 0% complete.
(approximately 2152 more seconds) (Phase 2 of 3)
Question is:
What are these three phases.
Thx,
Moh

--=_NextPart_000_00F1_01C662E8.12DAC620--sql

Recovery options when the transaction log is unavailable

Hi,
On MS SQL Server 2000 SP3, what are my recovery options when only the
database transaction log becomes unavailable and when there are active
transactions in the database?
Sp_detach_db/sp_attach_db/sp_attach_single_file_db doesn't work in this
scenario, I have tried that.
Are there any other options and how much of data loss to expect in each case?
-- Many thanks, OskarIs the DB currenlty in Suspect state, have you tried...
sp_add_log_file_recover_suspect_db , Read BOL to get more information
regarding this.
For Active transactions on a damaged / corrupted database you can do a
transacgion log backup using NO_TRUNCATE option. This will backup all the
active transaction in the database. This command will be used only when your
database is damaged.
BACKUP LOG DBNAME TO DISK='C:\db_corr.trn' WITH NO_TRUNCATE
So once the DB is up..
Restorethe last TX log backup taken with NO_TRUNCATE. During this time
mention 'RECOVERY' during RESTORE
Please do share once you restore the DB or let us know if you have any other
queries...
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Oskar" wrote:
> Hi,
> On MS SQL Server 2000 SP3, what are my recovery options when only the
> database transaction log becomes unavailable and when there are active
> transactions in the database?
> Sp_detach_db/sp_attach_db/sp_attach_single_file_db doesn't work in this
> scenario, I have tried that.
> Are there any other options and how much of data loss to expect in each case?
> -- Many thanks, Oskar
>|||Sorry, but neither of your suggestions work:
sp_add_log_file_recover_suspect_db 'TestDB1', 'TestDB1_Log',
'C:\TEMP\TestDB1_Log.LDF'
returned this error
Server: Msg 5004, Level 16, State 2, Line 1
To use ALTER DATABASE, the database must be in a writable state in which a
checkpoint can be executed.
ALTER DATABASE TestDB1 ADD LOG FILE(NAME = [TestDB1_Log], FILENAME ='C:\TEMP\TestDB1_Log.LDF' )
Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may be
incorrect.
Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may be
incorrect.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
and I can't backup the transaction log because it is not accessible:
backup tran TestDB1 to disk = 'C:\BACKUP\TestDB1_backup' with no_truncate
returns
Server: Msg 3447, Level 16, State 1, Line 1
Could not activate or scan all of the log files for database 'TestDB1'.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may be
incorrect.
So it seems, that only option I have in this case is to restore from backup.
-- Thanks, Oskar
"Sreejith G" wrote:
> Is the DB currenlty in Suspect state, have you tried...
> sp_add_log_file_recover_suspect_db , Read BOL to get more information
> regarding this.
> For Active transactions on a damaged / corrupted database you can do a
> transacgion log backup using NO_TRUNCATE option. This will backup all the
> active transaction in the database. This command will be used only when your
> database is damaged.
> BACKUP LOG DBNAME TO DISK='C:\db_corr.trn' WITH NO_TRUNCATE
> So once the DB is up..
> Restorethe last TX log backup taken with NO_TRUNCATE. During this time
> mention 'RECOVERY' during RESTORE
> Please do share once you restore the DB or let us know if you have any other
> queries...
>
>
> --
> Thanks,
> Sree
> [Please specify the version of Sql Server as we can save one thread and time
> asking back if its 2000 or 2005]
>
> "Oskar" wrote:
> > Hi,
> >
> > On MS SQL Server 2000 SP3, what are my recovery options when only the
> > database transaction log becomes unavailable and when there are active
> > transactions in the database?
> > Sp_detach_db/sp_attach_db/sp_attach_single_file_db doesn't work in this
> > scenario, I have tried that.
> > Are there any other options and how much of data loss to expect in each case?
> >
> > -- Many thanks, Oskar
> >|||In your first thread you said u tried detach/attach/sp_attach_single_file_db,
Below is the syntax and after detach you need not have to execute sp_attach
EXEC sp_detach_db @.dbname = 'pubs'
EXEC sp_attach_single_file_db @.dbname = 'pubs',
@.physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'
Can you answer the below questions.
Is the DB in SUSPECT state?
Do your DB have multiple data file?
--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Oskar" wrote:
> Sorry, but neither of your suggestions work:
> sp_add_log_file_recover_suspect_db 'TestDB1', 'TestDB1_Log',
> 'C:\TEMP\TestDB1_Log.LDF'
> returned this error
> Server: Msg 5004, Level 16, State 2, Line 1
> To use ALTER DATABASE, the database must be in a writable state in which a
> checkpoint can be executed.
> ALTER DATABASE TestDB1 ADD LOG FILE(NAME = [TestDB1_Log], FILENAME => 'C:\TEMP\TestDB1_Log.LDF' )
> Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may be
> incorrect.
> Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may be
> incorrect.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> and I can't backup the transaction log because it is not accessible:
> backup tran TestDB1 to disk = 'C:\BACKUP\TestDB1_backup' with no_truncate
> returns
> Server: Msg 3447, Level 16, State 1, Line 1
> Could not activate or scan all of the log files for database 'TestDB1'.
> Server: Msg 3013, Level 16, State 1, Line 1
> BACKUP LOG is terminating abnormally.
> Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may be
> incorrect.
> So it seems, that only option I have in this case is to restore from backup.
> -- Thanks, Oskar
> "Sreejith G" wrote:
> > Is the DB currenlty in Suspect state, have you tried...
> >
> > sp_add_log_file_recover_suspect_db , Read BOL to get more information
> > regarding this.
> >
> > For Active transactions on a damaged / corrupted database you can do a
> > transacgion log backup using NO_TRUNCATE option. This will backup all the
> > active transaction in the database. This command will be used only when your
> > database is damaged.
> >
> > BACKUP LOG DBNAME TO DISK='C:\db_corr.trn' WITH NO_TRUNCATE
> >
> > So once the DB is up..
> > Restorethe last TX log backup taken with NO_TRUNCATE. During this time
> > mention 'RECOVERY' during RESTORE
> >
> > Please do share once you restore the DB or let us know if you have any other
> > queries...
> >
> >
> >
> >
> >
> > --
> > Thanks,
> > Sree
> > [Please specify the version of Sql Server as we can save one thread and time
> > asking back if its 2000 or 2005]
> >
> >
> >
> > "Oskar" wrote:
> >
> > > Hi,
> > >
> > > On MS SQL Server 2000 SP3, what are my recovery options when only the
> > > database transaction log becomes unavailable and when there are active
> > > transactions in the database?
> > > Sp_detach_db/sp_attach_db/sp_attach_single_file_db doesn't work in this
> > > scenario, I have tried that.
> > > Are there any other options and how much of data loss to expect in each case?
> > >
> > > -- Many thanks, Oskar
> > >|||1) Yes, it is suspect as you would expect it to be with inaccessible tran.
log file.
2) No, single data file.
-- Thanks, Oskar
"Sreejith G" wrote:
> In your first thread you said u tried detach/attach/sp_attach_single_file_db,
> Below is the syntax and after detach you need not have to execute sp_attach
> EXEC sp_detach_db @.dbname = 'pubs'
> EXEC sp_attach_single_file_db @.dbname = 'pubs',
> @.physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'
> Can you answer the below questions.
> Is the DB in SUSPECT state?
> Do your DB have multiple data file?
> --
> Thanks,
> Sree
> [Please specify the version of Sql Server as we can save one thread and time
> asking back if its 2000 or 2005]
>
> "Oskar" wrote:
> > Sorry, but neither of your suggestions work:
> >
> > sp_add_log_file_recover_suspect_db 'TestDB1', 'TestDB1_Log',
> > 'C:\TEMP\TestDB1_Log.LDF'
> >
> > returned this error
> >
> > Server: Msg 5004, Level 16, State 2, Line 1
> > To use ALTER DATABASE, the database must be in a writable state in which a
> > checkpoint can be executed.
> > ALTER DATABASE TestDB1 ADD LOG FILE(NAME = [TestDB1_Log], FILENAME => > 'C:\TEMP\TestDB1_Log.LDF' )
> > Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may be
> > incorrect.
> > Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may be
> > incorrect.
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> >
> > and I can't backup the transaction log because it is not accessible:
> >
> > backup tran TestDB1 to disk = 'C:\BACKUP\TestDB1_backup' with no_truncate
> >
> > returns
> >
> > Server: Msg 3447, Level 16, State 1, Line 1
> > Could not activate or scan all of the log files for database 'TestDB1'.
> > Server: Msg 3013, Level 16, State 1, Line 1
> > BACKUP LOG is terminating abnormally.
> > Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may be
> > incorrect.
> >
> > So it seems, that only option I have in this case is to restore from backup.
> >
> > -- Thanks, Oskar
> >
> > "Sreejith G" wrote:
> >
> > > Is the DB currenlty in Suspect state, have you tried...
> > >
> > > sp_add_log_file_recover_suspect_db , Read BOL to get more information
> > > regarding this.
> > >
> > > For Active transactions on a damaged / corrupted database you can do a
> > > transacgion log backup using NO_TRUNCATE option. This will backup all the
> > > active transaction in the database. This command will be used only when your
> > > database is damaged.
> > >
> > > BACKUP LOG DBNAME TO DISK='C:\db_corr.trn' WITH NO_TRUNCATE
> > >
> > > So once the DB is up..
> > > Restorethe last TX log backup taken with NO_TRUNCATE. During this time
> > > mention 'RECOVERY' during RESTORE
> > >
> > > Please do share once you restore the DB or let us know if you have any other
> > > queries...
> > >
> > >
> > >
> > >
> > >
> > > --
> > > Thanks,
> > > Sree
> > > [Please specify the version of Sql Server as we can save one thread and time
> > > asking back if its 2000 or 2005]
> > >
> > >
> > >
> > > "Oskar" wrote:
> > >
> > > > Hi,
> > > >
> > > > On MS SQL Server 2000 SP3, what are my recovery options when only the
> > > > database transaction log becomes unavailable and when there are active
> > > > transactions in the database?
> > > > Sp_detach_db/sp_attach_db/sp_attach_single_file_db doesn't work in this
> > > > scenario, I have tried that.
> > > > Are there any other options and how much of data loss to expect in each case?
> > > >
> > > > -- Many thanks, Oskar
> > > >

Recovery options when the transaction log is unavailable

Recovery of the SQL Server 2000 Database.

Situation:
SQL S2000 DB with some 6 mil. records, 100 fields for each record - was indexed at first on just few fields, then some more and when everything seemed to be OK and we had partially indexed good DB we stetted up indexing (single for each field) for the remaining 60 or so fields and left for the weekend.

Upon returning 2 days later we found SQL Server unusually busy and non-responding to Enterprise Manager at all.

Finally we have stopped SQL Server, with proper and uneventful shutdown - restarted the machine and at the moment all that is happening after lots of the activity at first (from current activity log) is that: "Recovery of database 'OurDB' (10) is 64% complete (approximately 240 more seconds) (Phase 2 of 3)."
No clue in previous messages in the current logs, no clue on previous logs.
All databases are inaccessible from Enterprise Manager.

What could be happening and now what?
:confused: :confused:When you say, "proper and uneventful shutdown " do you mean, sp_who showed that nothing was running? Could it be that indexes were still being build? And are you really creating (100) indexes for each field in every table?!?

-jfp|||jfp,
after some additional investigation I have discovered, that indeed, SQL Server was shut down with Windows "End Task" which unfortunately means, that - yes, it could very well still have been in the middle of indexing - and therefore ultimate price was paid for not having DB backed up BEFORE indexing.

BTW indexing of this sort on DB on just dual Xeon 500 box with 1 GB of Ram could go on for days.

Yes, we are indexing all the fields in order to query them and get the idea what the heck we need to do with all this wonderful data.
This is a boring trial run only to help prepare criteria for the real app with less junk and therefore less indexed junk.

Recovery of Suspect Database

I have a large database (>150 million records) that is marked "Suspect" and I would like to recover this if possible. Our IT group did an unexpected reboot of the server during a data import process implemented in multiple nested DTS packages. After the
reboot, the database was marked as Suspect.
SQL Server won't let me dbcc checkdb, restore datbase with recovery or even back up the log file (DB recovery mode is SIMPLE). After running sp_resetstatus and restarting SQL Server, a new error log and dump file are generated and the database is reset to
Suspect. A two-day-old backup is available and I can replace all the data by reimporting the missing days, but this will take days and I'm hoping to be able to recover the existing DB.
A couple of snippets from the error log are below. I also have a 18+ MB dump file. What's the best approach to fixing this?
<snip />
2004-05-04 13:51:46.07 spid5 Clearing tempdb database.
2004-05-04 13:51:46.38 spid5 Starting up database 'tempdb'.
2004-05-04 13:51:46.41 spid5 Analysis of database 'tempdb' (2) is 100% complete (approximately 0 more seconds)
2004-05-04 13:51:46.54 server SQL server listening on TCP, Shared Memory, Named Pipes.
2004-05-04 13:51:46.54 server SQL Server is ready for client connections
2004-05-04 13:52:06.49 spid11 Using 'dbghelp.dll' version '4.0.5'
*Dump thread - spid = 11, PSS = 0x42dc2098, EC = 0x42dc23c0
*Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL\log\SQLDump0005.txt
* ************************************************** *****************************
*
* BEGIN STACK DUMP:
* 05/04/04 13:52:06 spid 11
*
<snip />
* ----
2004-05-04 13:52:07.80 spid11 Stack Signature for the dump is 0x37EAFC8F
2004-05-04 13:52:07.82 spid11 SQL Server Assertion: File: <pageref.cpp>, line=4454
Failed Assertion = 'rowLog.RowCount () == 1 || pPage->IsEmpty ()'.
2004-05-04 13:52:07.82 spid11 Location: pageref.cpp:4454
Expression: rowLog.RowCount () == 1 || pPage->IsEmpty ()
SPID: 11
Process ID: 2180
2004-05-04 13:52:07.82 spid11 Error: 3313, Severity: 21, State: 2
2004-05-04 13:52:07.82 spid11 Error while redoing logged operation in database 'IDD'. Error at log record ID (7480:19322:383)..
2004-05-04 13:52:22.91 spid11 Error: 3414, Severity: 21, State: 1
2004-05-04 13:52:22.91 spid11 Database 'IDD' (database ID 7) could not recover. Contact Technical Support..
2004-05-04 13:52:42.95 spid3 Recovery complete.
Some good info can be found here.
http://www.karaszi.com/sqlserver/inf...suspect_db.asp
You can set the status of your DB to -32768 which is emergency mode. This
should allow you to see the database unless the mdf file is actually gone.
Jeff Duncan
MCDBA, MCSE+I
"Scott" <anonymous@.discussions.microsoft.com> wrote in message
news:B2C25106-BA97-42D1-910B-3E76CF72EDEB@.microsoft.com...
> I have a large database (>150 million records) that is marked "Suspect"
and I would like to recover this if possible. Our IT group did an
unexpected reboot of the server during a data import process implemented in
multiple nested DTS packages. After the reboot, the database was marked as
Suspect.
> SQL Server won't let me dbcc checkdb, restore datbase with recovery or
even back up the log file (DB recovery mode is SIMPLE). After running
sp_resetstatus and restarting SQL Server, a new error log and dump file are
generated and the database is reset to Suspect. A two-day-old backup is
available and I can replace all the data by reimporting the missing days,
but this will take days and I'm hoping to be able to recover the existing
DB.
> A couple of snippets from the error log are below. I also have a 18+ MB
dump file. What's the best approach to fixing this?
> <snip />
> 2004-05-04 13:51:46.07 spid5 Clearing tempdb database.
> 2004-05-04 13:51:46.38 spid5 Starting up database 'tempdb'.
> 2004-05-04 13:51:46.41 spid5 Analysis of database 'tempdb' (2) is 100%
complete (approximately 0 more seconds)
> 2004-05-04 13:51:46.54 server SQL server listening on TCP, Shared
Memory, Named Pipes.
> 2004-05-04 13:51:46.54 server SQL Server is ready for client
connections
> 2004-05-04 13:52:06.49 spid11 Using 'dbghelp.dll' version '4.0.5'
> *Dump thread - spid = 11, PSS = 0x42dc2098, EC = 0x42dc23c0
> *Stack Dump being sent to C:\Program Files\Microsoft SQL
Server\MSSQL\log\SQLDump0005.txt
> *
************************************************** **************************
***
> *
> * BEGIN STACK DUMP:
> * 05/04/04 13:52:06 spid 11
> *
> <snip />
>
* ----
--
> 2004-05-04 13:52:07.80 spid11 Stack Signature for the dump is
0x37EAFC8F
> 2004-05-04 13:52:07.82 spid11 SQL Server Assertion: File:
<pageref.cpp>, line=4454
> Failed Assertion = 'rowLog.RowCount () == 1 || pPage->IsEmpty ()'.
> 2004-05-04 13:52:07.82 spid11 Location: pageref.cpp:4454
> Expression: rowLog.RowCount () == 1 || pPage->IsEmpty ()
> SPID: 11
> Process ID: 2180
> 2004-05-04 13:52:07.82 spid11 Error: 3313, Severity: 21, State: 2
> 2004-05-04 13:52:07.82 spid11 Error while redoing logged operation in
database 'IDD'. Error at log record ID (7480:19322:383)..
> 2004-05-04 13:52:22.91 spid11 Error: 3414, Severity: 21, State: 1
> 2004-05-04 13:52:22.91 spid11 Database 'IDD' (database ID 7) could not
recover. Contact Technical Support..
> 2004-05-04 13:52:42.95 spid3 Recovery complete.
>
|||Yeah, I saw that site. Those steps aren't working. If I can't find a way to recover otherwise, I may use emergency mode and bcc the data out as a fail-safe before attempting a restore from backup. Not being able to even force it out of Suspect mode preclu
des a number of things I might try. Thanks Jeff! -- Scott
-- Jeff Duncan wrote: --
Some good info can be found here.
http://www.karaszi.com/sqlserver/inf...suspect_db.asp
You can set the status of your DB to -32768 which is emergency mode. This
should allow you to see the database unless the mdf file is actually gone.
Jeff Duncan
MCDBA, MCSE+I
"Scott" <anonymous@.discussions.microsoft.com> wrote in message
news:B2C25106-BA97-42D1-910B-3E76CF72EDEB@.microsoft.com...
> I have a large database (>150 million records) that is marked "Suspect"
and I would like to recover this if possible. Our IT group did an
unexpected reboot of the server during a data import process implemented in
multiple nested DTS packages. After the reboot, the database was marked as
Suspect.[vbcol=seagreen]
even back up the log file (DB recovery mode is SIMPLE). After running
sp_resetstatus and restarting SQL Server, a new error log and dump file are
generated and the database is reset to Suspect. A two-day-old backup is
available and I can replace all the data by reimporting the missing days,
but this will take days and I'm hoping to be able to recover the existing
DB.[vbcol=seagreen]
dump file. What's the best approach to fixing this?
> 2004-05-04 13:51:46.38 spid5 Starting up database 'tempdb'.
> 2004-05-04 13:51:46.41 spid5 Analysis of database 'tempdb' (2) is 100%
complete (approximately 0 more seconds)
> 2004-05-04 13:51:46.54 server SQL server listening on TCP, Shared
Memory, Named Pipes.
> 2004-05-04 13:51:46.54 server SQL Server is ready for client
connections
> 2004-05-04 13:52:06.49 spid11 Using 'dbghelp.dll' version '4.0.5'
> *Dump thread - spid = 11, PSS = 0x42dc2098, EC = 0x42dc23c0
> *Stack Dump being sent to C:\Program Files\Microsoft SQL
Server\MSSQL\log\SQLDump0005.txt
> *
************************************************** **************************
***
> *
> * BEGIN STACK DUMP:
> * 05/04/04 13:52:06 spid 11
> *
><snip />>
* ----
--
> 2004-05-04 13:52:07.80 spid11 Stack Signature for the dump is
0x37EAFC8F
> 2004-05-04 13:52:07.82 spid11 SQL Server Assertion: File:
<pageref.cpp>, line=4454
> Failed Assertion = 'rowLog.RowCount () == 1 || pPage->IsEmpty ()'.
> 2004-05-04 13:52:07.82 spid11 Location: pageref.cpp:4454
> Expression: rowLog.RowCount () == 1 || pPage->IsEmpty ()
> SPID: 11
> Process ID: 2180
> 2004-05-04 13:52:07.82 spid11 Error: 3313, Severity: 21, State: 2
> 2004-05-04 13:52:07.82 spid11 Error while redoing logged operation in
database 'IDD'. Error at log record ID (7480:19322:383)..
> 2004-05-04 13:52:22.91 spid11 Error: 3414, Severity: 21, State: 1
> 2004-05-04 13:52:22.91 spid11 Database 'IDD' (database ID 7) could not
recover. Contact Technical Support..
> 2004-05-04 13:52:42.95 spid3 Recovery complete.
>
|||You should open a case with PSS. This definitely sounds like it is worth
more than $250.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Scott" <anonymous@.discussions.microsoft.com> wrote in message
news:B2C25106-BA97-42D1-910B-3E76CF72EDEB@.microsoft.com...
> I have a large database (>150 million records) that is marked "Suspect"
and I would like to recover this if possible. Our IT group did an
unexpected reboot of the server during a data import process implemented in
multiple nested DTS packages. After the reboot, the database was marked as
Suspect.
> SQL Server won't let me dbcc checkdb, restore datbase with recovery or
even back up the log file (DB recovery mode is SIMPLE). After running
sp_resetstatus and restarting SQL Server, a new error log and dump file are
generated and the database is reset to Suspect. A two-day-old backup is
available and I can replace all the data by reimporting the missing days,
but this will take days and I'm hoping to be able to recover the existing
DB.
> A couple of snippets from the error log are below. I also have a 18+ MB
dump file. What's the best approach to fixing this?
> <snip />
> 2004-05-04 13:51:46.07 spid5 Clearing tempdb database.
> 2004-05-04 13:51:46.38 spid5 Starting up database 'tempdb'.
> 2004-05-04 13:51:46.41 spid5 Analysis of database 'tempdb' (2) is 100%
complete (approximately 0 more seconds)
> 2004-05-04 13:51:46.54 server SQL server listening on TCP, Shared
Memory, Named Pipes.
> 2004-05-04 13:51:46.54 server SQL Server is ready for client
connections
> 2004-05-04 13:52:06.49 spid11 Using 'dbghelp.dll' version '4.0.5'
> *Dump thread - spid = 11, PSS = 0x42dc2098, EC = 0x42dc23c0
> *Stack Dump being sent to C:\Program Files\Microsoft SQL
Server\MSSQL\log\SQLDump0005.txt
> *
************************************************** **************************
***
> *
> * BEGIN STACK DUMP:
> * 05/04/04 13:52:06 spid 11
> *
> <snip />
>
* ----
--
> 2004-05-04 13:52:07.80 spid11 Stack Signature for the dump is
0x37EAFC8F
> 2004-05-04 13:52:07.82 spid11 SQL Server Assertion: File:
<pageref.cpp>, line=4454
> Failed Assertion = 'rowLog.RowCount () == 1 || pPage->IsEmpty ()'.
> 2004-05-04 13:52:07.82 spid11 Location: pageref.cpp:4454
> Expression: rowLog.RowCount () == 1 || pPage->IsEmpty ()
> SPID: 11
> Process ID: 2180
> 2004-05-04 13:52:07.82 spid11 Error: 3313, Severity: 21, State: 2
> 2004-05-04 13:52:07.82 spid11 Error while redoing logged operation in
database 'IDD'. Error at log record ID (7480:19322:383)..
> 2004-05-04 13:52:22.91 spid11 Error: 3414, Severity: 21, State: 1
> 2004-05-04 13:52:22.91 spid11 Database 'IDD' (database ID 7) could not
recover. Contact Technical Support..
> 2004-05-04 13:52:42.95 spid3 Recovery complete.
>

Recovery of Suspect Database

I have a large database (>150 million records) that is marked "Suspect" and
I would like to recover this if possible. Our IT group did an unexpected re
boot of the server during a data import process implemented in multiple nest
ed DTS packages. After the
reboot, the database was marked as Suspect.
SQL Server won't let me dbcc checkdb, restore datbase with recovery or even
back up the log file (DB recovery mode is SIMPLE). After running sp_resetsta
tus and restarting SQL Server, a new error log and dump file are generated a
nd the database is reset to
Suspect. A two-day-old backup is available and I can replace all the data by
reimporting the missing days, but this will take days and I'm hoping to be
able to recover the existing DB.
A couple of snippets from the error log are below. I also have a 18+ MB dump
file. What's the best approach to fixing this?
<snip />
2004-05-04 13:51:46.07 spid5 Clearing tempdb database.
2004-05-04 13:51:46.38 spid5 Starting up database 'tempdb'.
2004-05-04 13:51:46.41 spid5 Analysis of database 'tempdb' (2) is 100% c
omplete (approximately 0 more seconds)
2004-05-04 13:51:46.54 server SQL server listening on TCP, Shared Memory,
Named Pipes.
2004-05-04 13:51:46.54 server SQL Server is ready for client connections
2004-05-04 13:52:06.49 spid11 Using 'dbghelp.dll' version '4.0.5'
*Dump thread - spid = 11, PSS = 0x42dc2098, EC = 0x42dc23c0
*Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL\log\SQ
LDump0005.txt
* ****************************************
**********************************
*****
*
* BEGIN STACK DUMP:
* 05/04/04 13:52:06 spid 11
*
<snip />
* ----
--
2004-05-04 13:52:07.80 spid11 Stack Signature for the dump is 0x37EAFC8F
2004-05-04 13:52:07.82 spid11 SQL Server Assertion: File: <pageref.cpp>,
line=4454
Failed Assertion = 'rowLog.RowCount () == 1 || pPage->IsEmpty ()'.
2004-05-04 13:52:07.82 spid11 Location: pageref.cpp:4454
Expression: rowLog.RowCount () == 1 || pPage->IsEmpty ()
SPID: 11
Process ID: 2180
2004-05-04 13:52:07.82 spid11 Error: 3313, Severity: 21, State: 2
2004-05-04 13:52:07.82 spid11 Error while redoing logged operation in dat
abase 'IDD'. Error at log record ID (7480:19322:383)..
2004-05-04 13:52:22.91 spid11 Error: 3414, Severity: 21, State: 1
2004-05-04 13:52:22.91 spid11 Database 'IDD' (database ID 7) could not re
cover. Contact Technical Support..
2004-05-04 13:52:42.95 spid3 Recovery complete.Some good info can be found here.
http://www.karaszi.com/sqlserver/in..._suspect_db.asp
You can set the status of your DB to -32768 which is emergency mode. This
should allow you to see the database unless the mdf file is actually gone.
Jeff Duncan
MCDBA, MCSE+I
"Scott" <anonymous@.discussions.microsoft.com> wrote in message
news:B2C25106-BA97-42D1-910B-3E76CF72EDEB@.microsoft.com...
> I have a large database (>150 million records) that is marked "Suspect"
and I would like to recover this if possible. Our IT group did an
unexpected reboot of the server during a data import process implemented in
multiple nested DTS packages. After the reboot, the database was marked as
Suspect.
> SQL Server won't let me dbcc checkdb, restore datbase with recovery or
even back up the log file (DB recovery mode is SIMPLE). After running
sp_resetstatus and restarting SQL Server, a new error log and dump file are
generated and the database is reset to Suspect. A two-day-old backup is
available and I can replace all the data by reimporting the missing days,
but this will take days and I'm hoping to be able to recover the existing
DB.
> A couple of snippets from the error log are below. I also have a 18+ MB
dump file. What's the best approach to fixing this?
> <snip />
> 2004-05-04 13:51:46.07 spid5 Clearing tempdb database.
> 2004-05-04 13:51:46.38 spid5 Starting up database 'tempdb'.
> 2004-05-04 13:51:46.41 spid5 Analysis of database 'tempdb' (2) is 100%
complete (approximately 0 more seconds)
> 2004-05-04 13:51:46.54 server SQL server listening on TCP, Shared
Memory, Named Pipes.
> 2004-05-04 13:51:46.54 server SQL Server is ready for client
connections
> 2004-05-04 13:52:06.49 spid11 Using 'dbghelp.dll' version '4.0.5'
> *Dump thread - spid = 11, PSS = 0x42dc2098, EC = 0x42dc23c0
> *Stack Dump being sent to C:\Program Files\Microsoft SQL
Server\MSSQL\log\SQLDump0005.txt
> *
****************************************
************************************
***
> *
> * BEGIN STACK DUMP:
> * 05/04/04 13:52:06 spid 11
> *
> <snip />
>
* ----
--
> 2004-05-04 13:52:07.80 spid11 Stack Signature for the dump is
0x37EAFC8F
> 2004-05-04 13:52:07.82 spid11 SQL Server Assertion: File:
<pageref.cpp>, line=4454
> Failed Assertion = 'rowLog.RowCount () == 1 || pPage->IsEmpty ()'.
> 2004-05-04 13:52:07.82 spid11 Location: pageref.cpp:4454
> Expression: rowLog.RowCount () == 1 || pPage->IsEmpty ()
> SPID: 11
> Process ID: 2180
> 2004-05-04 13:52:07.82 spid11 Error: 3313, Severity: 21, State: 2
> 2004-05-04 13:52:07.82 spid11 Error while redoing logged operation in
database 'IDD'. Error at log record ID (7480:19322:383)..
> 2004-05-04 13:52:22.91 spid11 Error: 3414, Severity: 21, State: 1
> 2004-05-04 13:52:22.91 spid11 Database 'IDD' (database ID 7) could not
recover. Contact Technical Support..
> 2004-05-04 13:52:42.95 spid3 Recovery complete.
>|||Yeah, I saw that site. Those steps aren't working. If I can't find a way to
recover otherwise, I may use emergency mode and bcc the data out as a fail-s
afe before attempting a restore from backup. Not being able to even force it
out of Suspect mode preclu
des a number of things I might try. Thanks Jeff! -- Scott
-- Jeff Duncan wrote: --
Some good info can be found here.
http://www.karaszi.com/sqlserver/in..._suspect_db.asp
You can set the status of your DB to -32768 which is emergency mode. This
should allow you to see the database unless the mdf file is actually gone.
Jeff Duncan
MCDBA, MCSE+I
"Scott" <anonymous@.discussions.microsoft.com> wrote in message
news:B2C25106-BA97-42D1-910B-3E76CF72EDEB@.microsoft.com...
> I have a large database (>150 million records) that is marked "Suspect"
and I would like to recover this if possible. Our IT group did an
unexpected reboot of the server during a data import process implemented in
multiple nested DTS packages. After the reboot, the database was marked as
Suspect.
even back up the log file (DB recovery mode is SIMPLE). After running
sp_resetstatus and restarting SQL Server, a new error log and dump file are
generated and the database is reset to Suspect. A two-day-old backup is
available and I can replace all the data by reimporting the missing days,
but this will take days and I'm hoping to be able to recover the existing
DB.[vbcol=seagreen]
dump file. What's the best approach to fixing this?[vbcol=seagreen]
> 2004-05-04 13:51:46.38 spid5 Starting up database 'tempdb'.
> 2004-05-04 13:51:46.41 spid5 Analysis of database 'tempdb' (2) is 100%
complete (approximately 0 more seconds)
> 2004-05-04 13:51:46.54 server SQL server listening on TCP, Shared
Memory, Named Pipes.
> 2004-05-04 13:51:46.54 server SQL Server is ready for client
connections
> 2004-05-04 13:52:06.49 spid11 Using 'dbghelp.dll' version '4.0.5'
> *Dump thread - spid = 11, PSS = 0x42dc2098, EC = 0x42dc23c0
> *Stack Dump being sent to C:\Program Files\Microsoft SQL
Server\MSSQL\log\SQLDump0005.txt
> *
****************************************
************************************
***
> *
> * BEGIN STACK DUMP:
> * 05/04/04 13:52:06 spid 11
> *
><snip />>
* ----
--
> 2004-05-04 13:52:07.80 spid11 Stack Signature for the dump is
0x37EAFC8F
> 2004-05-04 13:52:07.82 spid11 SQL Server Assertion: File:
<pageref.cpp>, line=4454
> Failed Assertion = 'rowLog.RowCount () == 1 || pPage->IsEmpty ()'.
> 2004-05-04 13:52:07.82 spid11 Location: pageref.cpp:4454
> Expression: rowLog.RowCount () == 1 || pPage->IsEmpty ()
> SPID: 11
> Process ID: 2180
> 2004-05-04 13:52:07.82 spid11 Error: 3313, Severity: 21, State: 2
> 2004-05-04 13:52:07.82 spid11 Error while redoing logged operation in
database 'IDD'. Error at log record ID (7480:19322:383)..
> 2004-05-04 13:52:22.91 spid11 Error: 3414, Severity: 21, State: 1
> 2004-05-04 13:52:22.91 spid11 Database 'IDD' (database ID 7) could not
recover. Contact Technical Support..
> 2004-05-04 13:52:42.95 spid3 Recovery complete.
>|||You should open a case with PSS. This definitely sounds like it is worth
more than $250.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Scott" <anonymous@.discussions.microsoft.com> wrote in message
news:B2C25106-BA97-42D1-910B-3E76CF72EDEB@.microsoft.com...
> I have a large database (>150 million records) that is marked "Suspect"
and I would like to recover this if possible. Our IT group did an
unexpected reboot of the server during a data import process implemented in
multiple nested DTS packages. After the reboot, the database was marked as
Suspect.
> SQL Server won't let me dbcc checkdb, restore datbase with recovery or
even back up the log file (DB recovery mode is SIMPLE). After running
sp_resetstatus and restarting SQL Server, a new error log and dump file are
generated and the database is reset to Suspect. A two-day-old backup is
available and I can replace all the data by reimporting the missing days,
but this will take days and I'm hoping to be able to recover the existing
DB.
> A couple of snippets from the error log are below. I also have a 18+ MB
dump file. What's the best approach to fixing this?
> <snip />
> 2004-05-04 13:51:46.07 spid5 Clearing tempdb database.
> 2004-05-04 13:51:46.38 spid5 Starting up database 'tempdb'.
> 2004-05-04 13:51:46.41 spid5 Analysis of database 'tempdb' (2) is 100%
complete (approximately 0 more seconds)
> 2004-05-04 13:51:46.54 server SQL server listening on TCP, Shared
Memory, Named Pipes.
> 2004-05-04 13:51:46.54 server SQL Server is ready for client
connections
> 2004-05-04 13:52:06.49 spid11 Using 'dbghelp.dll' version '4.0.5'
> *Dump thread - spid = 11, PSS = 0x42dc2098, EC = 0x42dc23c0
> *Stack Dump being sent to C:\Program Files\Microsoft SQL
Server\MSSQL\log\SQLDump0005.txt
> *
****************************************
************************************
***
> *
> * BEGIN STACK DUMP:
> * 05/04/04 13:52:06 spid 11
> *
> <snip />
>
* ----
--
> 2004-05-04 13:52:07.80 spid11 Stack Signature for the dump is
0x37EAFC8F
> 2004-05-04 13:52:07.82 spid11 SQL Server Assertion: File:
<pageref.cpp>, line=4454
> Failed Assertion = 'rowLog.RowCount () == 1 || pPage->IsEmpty ()'.
> 2004-05-04 13:52:07.82 spid11 Location: pageref.cpp:4454
> Expression: rowLog.RowCount () == 1 || pPage->IsEmpty ()
> SPID: 11
> Process ID: 2180
> 2004-05-04 13:52:07.82 spid11 Error: 3313, Severity: 21, State: 2
> 2004-05-04 13:52:07.82 spid11 Error while redoing logged operation in
database 'IDD'. Error at log record ID (7480:19322:383)..
> 2004-05-04 13:52:22.91 spid11 Error: 3414, Severity: 21, State: 1
> 2004-05-04 13:52:22.91 spid11 Database 'IDD' (database ID 7) could not
recover. Contact Technical Support..
> 2004-05-04 13:52:42.95 spid3 Recovery complete.
>|||Hi Scott
Did you manage to sort this problem out, because I have the same problem.
SQL Error:
2004-05-10 08:41:31.07 spid10 Error: 3313, Severity: 21, State: 2
2004-05-10 08:41:31.07 spid10 Error while redoing logged operation in data
base 'CBPRD'. Error at log record ID (90312:130277:138)..
2004-05-10 08:41:35.21 spid7 Using 'xpstar.dll' version '2000.28.09' to e
xecute extended stored procedure 'xp_regread'.
2004-05-10 08:42:01.78 spid10 Your transaction (process ID #10) was deadlo
cked with another process and has been chosen as the deadlock victim. Rerun
your transaction.
2004-05-10 08:42:01.78 spid10 Your transaction (process ID #10) was deadlo
cked with another process and has been chosen as the deadlock victim. Rerun
your transaction.
2004-05-10 08:42:01.78 spid10 Error: 3413, Severity: 21, State: 1
2004-05-10 08:42:01.78 spid10 Database ID 8. Could not mark database as su
spect. Getnext NC scan on sysdatabases.dbid failed..
2004-05-10 08:42:01.78 spid10 Process 10 unlocking unowned resource: KEY:
1:30:2 (df0082738c65)
2004-05-10 08:42:01.78 spid10 Error: 1203, Severity: 20, State: 1
2004-05-10 08:42:01.78 spid10 Process ID 10 attempting to unlock unowned r
esource KEY: 1:30:2 (df0082738c65)..
2004-05-10 08:42:01.78 server Using 'sqlimage.dll' version '4.0.5'
Stack Dump being sent to F:\MSSQL7\log\SQL00012.dmp
****************************************
************************************
***
*
* BEGIN STACK DUMP:
* 05/10/04 08:42:02 spid 0
*
* Exception Address = 77F1D642 (RaiseException + 6a)
* Exception Code = 400042ac P
****************************************
************************************
***
----
--
Short Stack Dump
0x77f1d642 Module(KERNEL32+1d642) (RaiseException+6a)
0x006fe3f0 Module(sqlservr+2fe3f0) (stackTraceException+53)
0x006fe3ad Module(sqlservr+2fe3ad) (stackTrace+255)
0x0078d41e Module(sqlservr+38d41e) (utassert_fail+1a0)
0x005bc618 Module(sqlservr+1bc618) (ExecutionContext::Cleanup+9e)
0x00501daf Module(sqlservr+101daf) (PSS::~PSS+111)
0x00501c9b Module(sqlservr+101c9b) (destroyPssMemory+19)
0x00501a9c Module(sqlservr+101a9c) (freepss+132)
0x004d2f0a Module(sqlservr+d2f0a) (StartDBsInParallel+1b0)
0x41092a60 Module(ums+2a60) (ProcessWorkRequests+102)
0x41093316 Module(ums+3316) (ThreadStartRoutine+139)
0x7800b995 Module(MSVCRT+b995) (beginthread+ce)
0x77f04ef0 Module(KERNEL32+4ef0) (lstrcmpiW+be)
----
--
Dump thread - spid = 1, PSS = 0x411da084, EC = 0x411da23c
Stack Dump being sent to F:\MSSQL7\log\SQL00012.dmp
Please advise'

Friday, March 30, 2012

recovery question

Recovery question

recovery question

recovery question

Recovery question

Recovery problem

Recovery Plan -

Recovery phases

Recovery phases

Recovery options when the transaction log is unavailable

Recovery options when the transaction log is unavailable

Recovery options when the transaction log is unavailable

Recovery of the SQL Server 2000 Database.

Recovery of Suspect Database

Recovery of Suspect Database

Recovery of Suspect Database

Record Locks

Blog Archive

About Me