database in standby mode

How to find using tsql, that the current database is readonly or in warm
standby mode ?This information is available with DATABASEPROPERTY:
SELECT
DATABASEPROPERTY('MyDatabase', 'IsInStandBy') AS IsInStandBy,
DATABASEPROPERTY('MyDatabase', 'IsReadOnly') AS IsReadOnly
See the DATABASEPROPERTY and DATABASEPROPERTYEX in the Books Online for more
info.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"VSS" <aa@.aa> wrote in message news:OiEGqxUuGHA.1512@.TK2MSFTNGP03.phx.gbl...
> How to find using tsql, that the current database is readonly or in warm
> standby mode ?
>|||You can also use the below command
sp_helpdb <DBNAME>
See the status column...
Thanks
Hari
SQL Server MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e6vVo5UuGHA.4968@.TK2MSFTNGP03.phx.gbl...
> This information is available with DATABASEPROPERTY:
> SELECT
> DATABASEPROPERTY('MyDatabase', 'IsInStandBy') AS IsInStandBy,
> DATABASEPROPERTY('MyDatabase', 'IsReadOnly') AS IsReadOnly
> See the DATABASEPROPERTY and DATABASEPROPERTYEX in the Books Online for
> more info.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "VSS" <aa@.aa> wrote in message
> news:OiEGqxUuGHA.1512@.TK2MSFTNGP03.phx.gbl...
>> How to find using tsql, that the current database is readonly or in warm
>> standby mode ?
>>
>sql

Database in Standby / Readonly

I recently restored a database to a 2005 SQL Server and it is now
reporting that it is in standby / readonly mode. I've seen some
suggestion that this could be solved through re-restoring the database
but the database restore isn't an option. I've tried taking it offline
so that I could get access to restore it and haven't had any luck
there as well. Is there something I'm missing here or do I just need
to drop and restore the database all over again?
RESTORE DATABASE [database_name] WITH RECOVERY
"johnvmc@.gmail.com" wrote:

> I recently restored a database to a 2005 SQL Server and it is now
> reporting that it is in standby / readonly mode. I've seen some
> suggestion that this could be solved through re-restoring the database
> but the database restore isn't an option. I've tried taking it offline
> so that I could get access to restore it and haven't had any luck
> there as well. Is there something I'm missing here or do I just need
> to drop and restore the database all over again?
>

Database in Standby / Readonly

I recently restored a database to a 2005 SQL Server and it is now
reporting that it is in standby / readonly mode. I've seen some
suggestion that this could be solved through re-restoring the database
but the database restore isn't an option. I've tried taking it offline
so that I could get access to restore it and haven't had any luck
there as well. Is there something I'm missing here or do I just need
to drop and restore the database all over again?RESTORE DATABASE [database_name] WITH RECOVERY
"johnvmc@.gmail.com" wrote:
> I recently restored a database to a 2005 SQL Server and it is now
> reporting that it is in standby / readonly mode. I've seen some
> suggestion that this could be solved through re-restoring the database
> but the database restore isn't an option. I've tried taking it offline
> so that I could get access to restore it and haven't had any luck
> there as well. Is there something I'm missing here or do I just need
> to drop and restore the database all over again?
>

Database in Standby / Readonly

I recently restored a database to a 2005 SQL Server and it is now
reporting that it is in standby / readonly mode. I've seen some
suggestion that this could be solved through re-restoring the database
but the database restore isn't an option. I've tried taking it offline
so that I could get access to restore it and haven't had any luck
there as well. Is there something I'm missing here or do I just need
to drop and restore the database all over again?RESTORE DATABASE [database_name] WITH RECOVERY
"johnvmc@.gmail.com" wrote:

> I recently restored a database to a 2005 SQL Server and it is now
> reporting that it is in standby / readonly mode. I've seen some
> suggestion that this could be solved through re-restoring the database
> but the database restore isn't an option. I've tried taking it offline
> so that I could get access to restore it and haven't had any luck
> there as well. Is there something I'm missing here or do I just need
> to drop and restore the database all over again?
>

database in simple mode yet txn log file can be out of diskspace ?

Hi ,

I have set up my database to be using "Simple" mode. This will not log any
txns ?

I have got the err message saying the log file is full and i need to do a
txn log backup

i do not understand why , could anyone kindly advise ?

tks & rdgs

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200510/1Having you database in SIMPLE recovery mode does NOT mean that
transactions are not logged. All it means is that all committed
transactions are removed from the T-log when a checkpoint occurs. So if
you run lot's of and especially longrunning transactions, it still can
happen that your T-log grows out of space.

M

Database in Recovering/suspect mode

Hi,
We have database of around 500 GB. Y'day nite we got a error msg saying
unable to write error to errorlog file.We were not able to login to the
sqlserver this morning.
So we restarted our server. It went to recover mode & started recovering
the database. It was fine till 96% then
we got a error msg
"Could not redo log record (635160:1109455:186),
for transaction ID (0:733497362), on page (4:840931), database 'VADI_NFDW'
(8). Page: LSN = (635147:63129:440), type = 2. Log: OpCode = 3, context 3,
PrevPageLSN: (635160:1100226:202)."
and one more msg as
"Error while redoing logged operation in database 'VADI_NFDW'. Error at log
record ID (635160:1109455:186)"
How could we solve this problem...It's our production db...
Thanks
Muthu
You need to contact PSS to help you with this.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"muthu" <muthu@.discussions.microsoft.com> wrote in message
news:DFB0D7F3-5236-48B9-8A4B-87BB0C1F8D24@.microsoft.com...
> Hi,
> We have database of around 500 GB. Y'day nite we got a error msg saying
> unable to write error to errorlog file.We were not able to login to the
> sqlserver this morning.
> So we restarted our server. It went to recover mode & started recovering
> the database. It was fine till 96% then
> we got a error msg
> "Could not redo log record (635160:1109455:186),
> for transaction ID (0:733497362), on page (4:840931), database 'VADI_NFDW'
> (8). Page: LSN = (635147:63129:440), type = 2. Log: OpCode = 3, context 3,
> PrevPageLSN: (635160:1100226:202)."
> and one more msg as
> "Error while redoing logged operation in database 'VADI_NFDW'. Error at
log
> record ID (635160:1109455:186)"
> How could we solve this problem...It's our production db...
> Thanks
> Muthu
>

Database in Recovering/suspect mode

Hi,
We have database of around 500 GB. Y'day nite we got a error msg saying
unable to write error to errorlog file.We were not able to login to the
sqlserver this morning.
So we restarted our server. It went to recover mode & started recovering
the database. It was fine till 96% then
we got a error msg
"Could not redo log record (635160:1109455:186),
for transaction ID (0:733497362), on page (4:840931), database 'VADI_NFDW'
(8). Page: LSN = (635147:63129:440), type = 2. Log: OpCode = 3, context 3,
PrevPageLSN: (635160:1100226:202)."
and one more msg as
"Error while redoing logged operation in database 'VADI_NFDW'. Error at log
record ID (635160:1109455:186)"
How could we solve this problem...It's our production db...
Thanks
MuthuYou need to contact PSS to help you with this.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"muthu" <muthu@.discussions.microsoft.com> wrote in message
news:DFB0D7F3-5236-48B9-8A4B-87BB0C1F8D24@.microsoft.com...
> Hi,
> We have database of around 500 GB. Y'day nite we got a error msg saying
> unable to write error to errorlog file.We were not able to login to the
> sqlserver this morning.
> So we restarted our server. It went to recover mode & started recovering
> the database. It was fine till 96% then
> we got a error msg
> "Could not redo log record (635160:1109455:186),
> for transaction ID (0:733497362), on page (4:840931), database 'VADI_NFDW'
> (8). Page: LSN = (635147:63129:440), type = 2. Log: OpCode = 3, context 3,
> PrevPageLSN: (635160:1100226:202)."
> and one more msg as
> "Error while redoing logged operation in database 'VADI_NFDW'. Error at
log
> record ID (635160:1109455:186)"
> How could we solve this problem...It's our production db...
> Thanks
> Muthu
>

Database in Recovering/suspect mode

Hi,
We have database of around 500 GB. Y'day nite we got a error msg saying
unable to write error to errorlog file.We were not able to login to the
sqlserver this morning.
So we restarted our server. It went to recover mode & started recovering
the database. It was fine till 96% then
we got a error msg
"Could not redo log record (635160:1109455:186),
for transaction ID (0:733497362), on page (4:840931), database 'VADI_NFDW'
(8). Page: LSN = (635147:63129:440), type = 2. Log: OpCode = 3, context 3,
PrevPageLSN: (635160:1100226:202)."
and one more msg as
"Error while redoing logged operation in database 'VADI_NFDW'. Error at log
record ID (635160:1109455:186)"
How could we solve this problem...It's our production db...
Thanks
MuthuYou need to contact PSS to help you with this.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"muthu" <muthu@.discussions.microsoft.com> wrote in message
news:DFB0D7F3-5236-48B9-8A4B-87BB0C1F8D24@.microsoft.com...
> Hi,
> We have database of around 500 GB. Y'day nite we got a error msg saying
> unable to write error to errorlog file.We were not able to login to the
> sqlserver this morning.
> So we restarted our server. It went to recover mode & started recovering
> the database. It was fine till 96% then
> we got a error msg
> "Could not redo log record (635160:1109455:186),
> for transaction ID (0:733497362), on page (4:840931), database 'VADI_NFDW'
> (8). Page: LSN = (635147:63129:440), type = 2. Log: OpCode = 3, context 3,
> PrevPageLSN: (635160:1100226:202)."
> and one more msg as
> "Error while redoing logged operation in database 'VADI_NFDW'. Error at
log
> record ID (635160:1109455:186)"
> How could we solve this problem...It's our production db...
> Thanks
> Muthu
>

Database in 'In recovery' mode

Hi,
There was the most awful case. During a update of a DB there was a
switching-off of power supplies. After switch on of a server the base has
passed in mode 'Suspect'. To restore base by standard way it was not
possible. Substitution of files and had turned out to open changes of the
status of base data.
Has executed substitution of files of a database. Has successfully opened a
database, structure - all is accessible.
Check of a file of a database on integrity is executed successfully, errors
are not present (DBCC CHECKDB).
I can not create backup, gives out the message that are opened deferred
transactions.
Also, at change of some objects there is a lag of a server that leads to
change of the status of database 'In Recovery'. In 2 and more hours the
status has not changed.
Whether it is necessary to wait for restoration? How to commit deferred tran?
Thank you for any help.
p.s. Backups very old :(Not sure if this is going to work ... a little bit risky but worth a try (I
guess)...
It seemed that some transaction are not clearing so try to rollback the
transaction and then try to backup the database and then try to turn the
database back online, so the command would look like:
USE master
GO
-- First set it to read only and rollback any active transaction
ALTER DATABASE [DatabaseName]
SET READ_ONLY WITH ROLLBACK IMMEDIATE
GO
-- Backup the database, hopefully it should work since there should not be
any active transaction
BACKUP DATABASE [DatabaseName]
TO DISK = '[FilePathAndName]'
WITH INIT
GO
-- Turn the database back online
ALTER DATABASE [DatabaseName]
SET ONLINE WITH ROLLBACK IMMEDIATE
GO
Hopefully that would work (but no guarantee...)
Lucas
"int64" wrote:
> Hi,
> There was the most awful case. During a update of a DB there was a
> switching-off of power supplies. After switch on of a server the base has
> passed in mode 'Suspect'. To restore base by standard way it was not
> possible. Substitution of files and had turned out to open changes of the
> status of base data.
> Has executed substitution of files of a database. Has successfully opened a
> database, structure - all is accessible.
> Check of a file of a database on integrity is executed successfully, errors
> are not present (DBCC CHECKDB).
> I can not create backup, gives out the message that are opened deferred
> transactions.
> Also, at change of some objects there is a lag of a server that leads to
> change of the status of database 'In Recovery'. In 2 and more hours the
> status has not changed.
> Whether it is necessary to wait for restoration? How to commit deferred tran?
> Thank you for any help.
> p.s. Backups very old :(
>

database in a suspect mode

We have a try to migrate a winnt server to another domain,
but had to backout. Both database on the sql server are in
a suspect mode after we backedout. What can I do to bring
them back online?Can I restore them from last night's
backup? Any experiance to be shared with me...Thanks very
muchDear dk,
use this one
sp_resetstatus [ @.DBName = ] 'database'
Regards
Faheem
NETWORK SOLUTION PAKISTAN.
>--Original Message--
>We have a try to migrate a winnt server to another
domain,
>but had to backout. Both database on the sql server are
in
>a suspect mode after we backedout. What can I do to bring
>them back online?Can I restore them from last night's
>backup? Any experiance to be shared with me...Thanks very
>much
>.
>sql