Removing a Database from Mirroring on the Standby server

[AOTX San Antonio]

I have a database that I was mirroring when the transaction log grew to over
1Gb and the database was only 2Gb total. I wanted to shrink the Transaction
log but I have not found a way to shring the Tlog while Mirroring. So I
stopped mirroring on the Principal server and shrunk the Tlog. I then did a
full backup and also a transaction log backup. I went to the standby server
where the mirror database is in "Recovery". First I tried to Recover the
database with the following command:

RESTORE DATABASE XXXX WITH RECOVERY

I get the following Error:
Msg 3104, Level 16, State 1, Line 1
RESTORE cannot operate on database 'AOTXInventory' because it is configured
for database mirroring. Use ALTER DATABASE to remove mirroring if you intend
to restore the database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.


When I try and look at Properties to stop Mirroring I get error 927

Database XXX cannot be opened. It is in the middle of a restore.

When I try and just delete the database I get error 3743

The database XXX is enabled for database mirroring. Database mirroring must
be removed before you drop the database...

My question then is : How do I get a database from Recovery to non recovery
so I can bring it into an operational mode. If I have a failure on the
principal I don't see how I can get the standby into an operational state.
Right now I have the database on the principal server running fine but with
no mirroring. I have the same database on the standby server indicating (In
Recovery) but unable to do anything with the database. In order to
reinitialize mirroring I need to retore the backup and TLog I took after I
shrank the transaction log...

I am running Sql Server 2005 Enterprise with SP2. I am running Mirroring in
an Asynchronous mode no witness. I am running on Windows 2003 Enterprise
Server.

Thanks for any advice in advance...

[Hate_orphaned_users]

Did you remove mirroring form the principal or just paused it with the SQL
server management studio console?
It looks mirroring is still enabled.

[AOTX San Antonio]

First I paused mirroring but that did not allow me to shrink the log file so
in Management Studio I removed mirroring on the principal server. The
database on the standby server went from (Mirror, Synchronized / Restoring)
to (In Recovery) which I took to mean it was no longer mirroring but still in
recovery mode whereby I could just restore database with recovery...

I continued to research and found an article that said I could restore or I
could use the alter command:

ALTER DATABASE XXX SET PARTNER OFF

I ran this command and then I was able to run the Restore command. I am
guessing that even though it appeared the mirroring had been turned off that
was not the case.

So that issue has been resolved and I now have the database back in
mirroring...Is there some guidelines about mirroring a database and keeping a
TLog from growing to big which is what caused this problem in the first
place. I do a full and a transaction log backup every night but the
transaction log backup does not shrink my log file...Any advice her would be
greatly appreciated...

Thanks

Post by Hate_orphaned_users
Did you remove mirroring form the principal or just paused it with the SQL
server management studio console?
It looks mirroring is still enabled.

[Mohit K. Gupta]

Mirroring was turned off but only for one partner. When you terminate
mirroring session on one server, it does not terminate it on other partner.
You have to disable it on both servers manually for it to fully terminate.


But I use the following script to shrink the log file size on Mirror
databases without breaking mirror.

USE [CMS_ITOWWW_Prod]
GO
CHECKPOINT
GO
BACKUP LOG [CMS_ITOWWW_Prod] TO DISK = 'D:\test.log'
GO
-- Shrink Manually! (might be able to do it with script didn't have time to
test it out)
-- Delete test.log manually.

** Note, you cannot use NO_LOG function when doing the backup **.

Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005

Post by AOTX San Antonio
First I paused mirroring but that did not allow me to shrink the log file so
in Management Studio I removed mirroring on the principal server. The
database on the standby server went from (Mirror, Synchronized / Restoring)
to (In Recovery) which I took to mean it was no longer mirroring but still in
recovery mode whereby I could just restore database with recovery...
I continued to research and found an article that said I could restore or I
ALTER DATABASE XXX SET PARTNER OFF
I ran this command and then I was able to run the Restore command. I am
guessing that even though it appeared the mirroring had been turned off that
was not the case.
So that issue has been resolved and I now have the database back in
mirroring...Is there some guidelines about mirroring a database and keeping a
TLog from growing to big which is what caused this problem in the first
place. I do a full and a transaction log backup every night but the
transaction log backup does not shrink my log file...Any advice her would be
greatly appreciated...
Thanks

Post by Hate_orphaned_users
Did you remove mirroring form the principal or just paused it with the SQL
server management studio console?
It looks mirroring is still enabled.

[AOTX San Antonio]

Thanks Mohit,

I ran your script and included the DBCC SHRINKFILE on a database I am
mirroring as the last step and it worked perfect...Again thanks very much for
the prompt reply...

Post by Mohit K. Gupta
Mirroring was turned off but only for one partner. When you terminate
mirroring session on one server, it does not terminate it on other partner.
You have to disable it on both servers manually for it to fully terminate.
But I use the following script to shrink the log file size on Mirror
databases without breaking mirror.
USE [CMS_ITOWWW_Prod]
GO
CHECKPOINT
GO
BACKUP LOG [CMS_ITOWWW_Prod] TO DISK = 'D:\test.log'
GO
-- Shrink Manually! (might be able to do it with script didn't have time to
test it out)
-- Delete test.log manually.
** Note, you cannot use NO_LOG function when doing the backup **.
Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005

Post by AOTX San Antonio
First I paused mirroring but that did not allow me to shrink the log file so
in Management Studio I removed mirroring on the principal server. The
database on the standby server went from (Mirror, Synchronized / Restoring)
to (In Recovery) which I took to mean it was no longer mirroring but still in
recovery mode whereby I could just restore database with recovery...
I continued to research and found an article that said I could restore or I
ALTER DATABASE XXX SET PARTNER OFF
I ran this command and then I was able to run the Restore command. I am
guessing that even though it appeared the mirroring had been turned off that
was not the case.
So that issue has been resolved and I now have the database back in
mirroring...Is there some guidelines about mirroring a database and keeping a
TLog from growing to big which is what caused this problem in the first
place. I do a full and a transaction log backup every night but the
transaction log backup does not shrink my log file...Any advice her would be
greatly appreciated...
Thanks

Post by Hate_orphaned_users
Did you remove mirroring form the principal or just paused it with the SQL
server management studio console?
It looks mirroring is still enabled.

[Mohit K. Gupta]

Right on ;-). DBCC ShrinkFile did the magic trick huh. I'll have to add
that, meh been too busy to modify my scripts. Thanks for the input.

[cpchan]

Hello,

I also have a simple problem.
I always get the message :

Msg 1404, Level 16, State 5, Line 1
The command failed because the database mirror is busy. Reissue the command
later.

I feel it is impossible to do anythings on my mirrored database !!!!

My mirrored database is in a state : Mirrored, Disconnected / In Recovery

By the way, how can I post a new question ?
I clicked the "New" button but no response.
I already signed in.

Post by Mohit K. Gupta
Right on ;-). DBCC ShrinkFile did the magic trick huh. I'll have to add
that, meh been too busy to modify my scripts. Thanks for the input.
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005

[Mohit K. Gupta]

Mirrored Disconnected Status implies it can't talk to it's partner.

You can break mirror and reestablish it after loging if that is an option
for you?
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005

Post by cpchan
Hello,
I also have a simple problem.
Msg 1404, Level 16, State 5, Line 1
The command failed because the database mirror is busy. Reissue the command
later.
I feel it is impossible to do anythings on my mirrored database !!!!
My mirrored database is in a state : Mirrored, Disconnected / In Recovery
By the way, how can I post a new question ?
I clicked the "New" button but no response.
I already signed in.

Post by Mohit K. Gupta
Right on ;-). DBCC ShrinkFile did the magic trick huh. I'll have to add
that, meh been too busy to modify my scripts. Thanks for the input.
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005

[PJ]

Thank you for the helpful information. I have a somewhat related question...

On a server pair (for which I have just assumed responsibility), the
physical memory usage is at 96%! It looks like my largest log file (for a
mirrored and witnessed database) was at 23GB, but was shrunk to "only" 7 GB.

The mirror partner datbase is in synch.

I will use your script to backup and shrink the log file, but do you know of
a way to recover the memory? My only other plan is to set up a maintenance
window for this production database then reboot the primary server.

If you have another suggestion, I would love to hear it.

PJ

Post by Mohit K. Gupta
Mirrored Disconnected Status implies it can't talk to it's partner.
You can break mirror and reestablish it after loging if that is an option
for you?
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005

Post by cpchan
Hello,
I also have a simple problem.
Msg 1404, Level 16, State 5, Line 1
The command failed because the database mirror is busy. Reissue the command
later.
I feel it is impossible to do anythings on my mirrored database !!!!
My mirrored database is in a state : Mirrored, Disconnected / In Recovery
By the way, how can I post a new question ?
I clicked the "New" button but no response.
I already signed in.

Post by Mohit K. Gupta
Right on ;-). DBCC ShrinkFile did the magic trick huh. I'll have to add
that, meh been too busy to modify my scripts. Thanks for the input.
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005

[Mohit K. Gupta]

Hi,

SQL Server will release memory if it senses the server is under physical
practice. By default SQL Server 2005 sets the memory usage to maximum; so it
is a good idea to configure maximum memory and minim memory allocated to SQL
Server.

Once done; if you re-start SQL Server it will only have the memory you
allowed it. If not it will release memory as data in the memory gets written
to disk or gets taken out of the buffer pool because it has not been
referenced.
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

Post by PJ
Thank you for the helpful information. I have a somewhat related question...
On a server pair (for which I have just assumed responsibility), the
physical memory usage is at 96%! It looks like my largest log file (for a
mirrored and witnessed database) was at 23GB, but was shrunk to "only" 7 GB.
The mirror partner datbase is in synch.
I will use your script to backup and shrink the log file, but do you know of
a way to recover the memory? My only other plan is to set up a maintenance
window for this production database then reboot the primary server.
If you have another suggestion, I would love to hear it.
PJ

[SMT_NRP_RAY]

I have a similar setup minus the mirror, I get this every time with regard to
a restore that fails with this error "Msg 4319, Level 16, State 3, Line 1
A previous restore operation was interrupted and did not complete processing
on file 'DevRepo_GMNA_Saher_log'. Either restore the backup set that was
interrupted or restart the restore sequence.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally." Is there an SQL statement that
will force the database out of recovery mode?? I have users desperatly
awaiting information... Thank you!

Post by AOTX San Antonio
I have a database that I was mirroring when the transaction log grew to over
1Gb and the database was only 2Gb total. I wanted to shrink the Transaction
log but I have not found a way to shring the Tlog while Mirroring. So I
stopped mirroring on the Principal server and shrunk the Tlog. I then did a
full backup and also a transaction log backup. I went to the standby server
where the mirror database is in "Recovery". First I tried to Recover the
RESTORE DATABASE XXXX WITH RECOVERY
Msg 3104, Level 16, State 1, Line 1
RESTORE cannot operate on database 'AOTXInventory' because it is configured
for database mirroring. Use ALTER DATABASE to remove mirroring if you intend
to restore the database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
When I try and look at Properties to stop Mirroring I get error 927
Database XXX cannot be opened. It is in the middle of a restore.
When I try and just delete the database I get error 3743
The database XXX is enabled for database mirroring. Database mirroring must
be removed before you drop the database...
My question then is : How do I get a database from Recovery to non recovery
so I can bring it into an operational mode. If I have a failure on the
principal I don't see how I can get the standby into an operational state.
Right now I have the database on the principal server running fine but with
no mirroring. I have the same database on the standby server indicating (In
Recovery) but unable to do anything with the database. In order to
reinitialize mirroring I need to retore the backup and TLog I took after I
shrank the transaction log...
I am running Sql Server 2005 Enterprise with SP2. I am running Mirroring in
an Asynchronous mode no witness. I am running on Windows 2003 Enterprise
Server.
Thanks for any advice in advance...