Prev: Sql Server 2005. Best configuration and parameters.
Next: Different MS SQL versions on a server.
From: Niels on 16 Feb 2010 06:26
Hi,

I have detached a database, moved the files to another drive and attached
the DB again. It appeared in read-only mode, so I tried to change the mode to
read-write... but unfortunately it failed
When I look in the error log there is a stack dump where some of the info are

Location: "logmgr.cpp":5527
Expression: !(minLSN.m_fSeqNo < lfcb->lfcb_fSeqNo)
Input Buffer 172 bytes -
ALTER DATABASE [PXPX_PLEngine_Frys] SET READ_WRITE WITH ROLLBACK IMMEDIATE

After this the database became inaccessable and gave the following reply on
various DBCC calls "Database *** is being recovered. Waiting until recovery
is finished."

When I looked in the activity monitor I didnt see any processes working on
that database, and there was no I/O on the disk. So I decided to restart the
service.
Now the database is in recovery mode, but still nothing is happening in the
activity monitor.

I have no clue what to do..... but this is what I am going to try. I will
shut down the service, delete the database files and start the service again.
Hopefully the instance will just mark the database as suspect, and then I can
drop it.

I dont mind loosing data, I have backup of the files as well as a full
database backup.

Do anyone have better ideas of how to enforce it out of recovery mode?
From: Uri Dimant on 16 Feb 2010 06:41
Niels

When you attach/restore the database SQL Server engine checks for consistent
data and if you had non commited transactions then it will be rollbacked and
commited transactions but not flushed to the disk then it will forwared. It
takes some time especially if the batch is large

So shut down the service in that case won't help you. Wait till it will be
recovered or restore the database from last good backup






"Niels" <Niels(a)discussions.microsoft.com> wrote in message
news:C1A9D00E-71B9-4331-9A78-C855712126A2(a)microsoft.com...
> Hi,
>
> I have detached a database, moved the files to another drive and attached
> the DB again. It appeared in read-only mode, so I tried to change the mode
> to
> read-write... but unfortunately it failed
> When I look in the error log there is a stack dump where some of the info
> are
>
> Location: "logmgr.cpp":5527
> Expression: !(minLSN.m_fSeqNo < lfcb->lfcb_fSeqNo)
> Input Buffer 172 bytes -
> ALTER DATABASE [PXPX_PLEngine_Frys] SET READ_WRITE WITH ROLLBACK
> IMMEDIATE
>
> After this the database became inaccessable and gave the following reply
> on
> various DBCC calls "Database *** is being recovered. Waiting until
> recovery
> is finished."
>
> When I looked in the activity monitor I didnt see any processes working on
> that database, and there was no I/O on the disk. So I decided to restart
> the
> service.
> Now the database is in recovery mode, but still nothing is happening in
> the
> activity monitor.
>
> I have no clue what to do..... but this is what I am going to try. I will
> shut down the service, delete the database files and start the service
> again.
> Hopefully the instance will just mark the database as suspect, and then I
> can
> drop it.
>
> I dont mind loosing data, I have backup of the files as well as a full
> database backup.
>
> Do anyone have better ideas of how to enforce it out of recovery mode?


From: Niels on 16 Feb 2010 09:03
Hi,

The thing is that no threads is performing any recovery on that database, so
it will never change out of recovery state. It is simply stuck on the
recovery state.

Anyway... I stopped the service, deleted the files and started the service
again. Now the database was readonly and I could simply drop it. I began all
over by copying the original files and attached them again... no error.....
strange but it works.

Ty for the time anyway :O)

"Uri Dimant" wrote:

> Niels
>
> When you attach/restore the database SQL Server engine checks for consistent
> data and if you had non commited transactions then it will be rollbacked and
> commited transactions but not flushed to the disk then it will forwared. It
> takes some time especially if the batch is large
>
> So shut down the service in that case won't help you. Wait till it will be
> recovered or restore the database from last good backup
>
>
>
>
>
>
> "Niels" <Niels(a)discussions.microsoft.com> wrote in message
> news:C1A9D00E-71B9-4331-9A78-C855712126A2(a)microsoft.com...
> > Hi,
> >
> > I have detached a database, moved the files to another drive and attached
> > the DB again. It appeared in read-only mode, so I tried to change the mode
> > to
> > read-write... but unfortunately it failed
> > When I look in the error log there is a stack dump where some of the info
> > are
> >
> > Location: "logmgr.cpp":5527
> > Expression: !(minLSN.m_fSeqNo < lfcb->lfcb_fSeqNo)
> > Input Buffer 172 bytes -
> > ALTER DATABASE [PXPX_PLEngine_Frys] SET READ_WRITE WITH ROLLBACK
> > IMMEDIATE
> >
> > After this the database became inaccessable and gave the following reply
> > on
> > various DBCC calls "Database *** is being recovered. Waiting until
> > recovery
> > is finished."
> >
> > When I looked in the activity monitor I didnt see any processes working on
> > that database, and there was no I/O on the disk. So I decided to restart
> > the
> > service.
> > Now the database is in recovery mode, but still nothing is happening in
> > the
> > activity monitor.
> >
> > I have no clue what to do..... but this is what I am going to try. I will
> > shut down the service, delete the database files and start the service
> > again.
> > Hopefully the instance will just mark the database as suspect, and then I
> > can
> > drop it.
> >
> > I dont mind loosing data, I have backup of the files as well as a full
> > database backup.
> >
> > Do anyone have better ideas of how to enforce it out of recovery mode?
>
>
> .
>
From: Erland Sommarskog on 16 Feb 2010 11:58
Niels (Niels(a)discussions.microsoft.com) writes:
> I have detached a database, moved the files to another drive and
> attached the DB again. It appeared in read-only mode, so I tried to
> change the mode to read-write... but unfortunately it failed When I look
> in the error log there is a stack dump where some of the info are
>
> Location: "logmgr.cpp":5527
> Expression: !(minLSN.m_fSeqNo < lfcb->lfcb_fSeqNo)
> Input Buffer 172 bytes -
> ALTER DATABASE [PXPX_PLEngine_Frys] SET READ_WRITE WITH ROLLBACK
> IMMEDIATE
>
> After this the database became inaccessable and gave the following reply
> on various DBCC calls "Database *** is being recovered. Waiting until
> recovery is finished."


The error message indicates that there was a serious problem with the
database. Most likely some form of unforseen corruption. I see that you
already gave up on the database and started over. Since hardware errors
usually reoccurs, I would advice you run DBCC CHECKDB on your database
frequently, and if corruption reappears, move to a new disk.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Sql Server 2005. Best configuration and parameters.
Next: Different MS SQL versions on a server.